Re: [PERFORM] Help tuning postgres

[Csaba Nagy]

[snip]
> Yes, but it could be a disk issue because you're doing more work than
> you need to.  If your UPDATEs are chasing down a lot of dead tuples,
> for instance, you'll peg your I/O even though you ought to have I/O
> to burn.

OK, this sounds interesting, but I don't understand: why would an update
"chase down a lot of dead tuples" ? Should I read up on some docs, cause
I obviously don't know enough about how updates work on postgres...

And how would the analyze help in finding this out ? I thought it would
only show me additionally the actual timings, not more detail in what
was done...

Thanks,
Csaba.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[PERFORM] Optimizer misconfigured ?

[Nörder-Tuitje , Marcus]

Title: Optimizer misconfigured ?








Hello, 


I have a strange effect on upcoming structure :



DEX_OBJ  ---< DEX_STRUCT >--- DEX_LIT


DEX_OBJ : 100 records (#DOO_ID, DOO_NAME)

DEX_STRUCT : 2,5 million records  (#(DST_SEQ, FK_DOO_ID, FK_LIT_ID))

DEX_LIT : 150K records  (#LIT_ID, LIT_TEXT)


(# marks primary key)


i'd like to count all LIT occurences in struct for a set of LITs.


so i indexed DEX_STRUCT using (FK_LIT_ID, FK_DOO_ID)

and i indexed DEX_LIT using BTREE (LIT_TEXT, LIT_ID)


but if i query


SELECT DOO_ID

    ,    COUNT(FK_LIT_ID) AS occurences

   FROM DEX_STRUCT  STR

  ,  DEX_LITERAL  LIT

WHERE STR.FK_LIT_ID = LIT.LIT_ID

 AND  LIT_TEXT IN ('foo', 'bar', 'foobar')

  GROUP BY DOO_ID


postgresql always runs a seq scan on DEX_STRUCT. I tried several indices and also very different kinds of queries (from EXISTS via INNER JOIN up to subqueries), but Pgsql does not use any index on dex_struct.

What can I do ? Is this a optimizer misconfiguration (hence, it is still in default config) ?


How can I make Pg using the indices on doc_struct ? The index on LIT is used :-(


I expect 30 - 60 millions of records in the struct table, so I urgently need indexed access.


Thanks a lot !


Marcus

Re: [PERFORM] Help tuning postgres

[Markus Wollny]

[EMAIL PROTECTED] wrote:

>>> Have you tried reindexing your active tables?

> It will cause some performance hit while you are doing it. It
> sounds like something is bloating rapidly on your system and
> the indexes is one possible place that could be happening.

You might consider using contrib/oid2name to monitor physical growth of
tables and indexes. There have been some issues with bloat in PostgreSQL
versions prior to 8.0, however there might still be some issues under
certain circumstances even now, so it does pay to cast an eye on what's
going on. If you haven't run vaccum regularly, this might lead to
regular vacuums not reclaiming enough dead tuples in one go, so if
you've had quite a lot of UPDATE/DELETE activity going onin the past and
only just started to use pg_autovacuum after the DB has been in
production for quite a while, you might indeed have to run a VACUUM FULL
and/or REINDEX on the affected tables, both of which will more or less
lock out any client access to the tables als long as they're running.

Kind regards

   Markus

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Help tuning postgres

[Markus Wollny]

[EMAIL PROTECTED] wrote:

> Next we'll upgrade the postgres hardware, and then I'll come
> back to report if it's working better... sorry for the noise for now.

There have been some discussions about which hardware suits PostgreSQL's
needs best under certain load-characteristics. We have experienced quite
a write-performance burst just from switching from a RAID5-config to a
RAID10 (mirroring&striping), even though we had been using some
supposedly sufficiently powerful dedicated battery-backuped
SCSI-RAID-adapters with lots of on-board cache. You can't beat simple,
although it will cost disk-space. Anyway, you might want to search the
archives for discussion on RAID-configurations.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Optimizer misconfigured ?

[Richard Huxton]

Nörder-Tuitje wrote:


Hello, 


I have a strange effect on upcoming structure :


People will be wanting the output of EXPLAIN ANALYSE on that query.

They'll also ask whether you've VACUUMed, ANALYSEd and configured your 
postgresql.conf correctly.


--
  Richard Huxton
  Archonet Ltd



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Help tuning postgres

[Andrew Sullivan]

On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote:
> 
> OK, this sounds interesting, but I don't understand: why would an update
> "chase down a lot of dead tuples" ? Should I read up on some docs, cause
> I obviously don't know enough about how updates work on postgres...

Right.  Here's the issue:

MVCC does not replace rows when you update.  Instead, it marks the
old row as expired, and sets the new values.  The old row is still
there, and it's available for other transactions who need to see it. 
As the docs say (see
),
"In effect, a SELECT query sees a snapshot of the database as of the
instant that that query begins to run."  And that can be true because
the original data is still there, although marked as expired for
subsequent transactions.

UPDATE works the same was as SELECT in terms of searching for rows
(so does any command that searches for data).  

Now, when you select data, you actually have to traverse all the
existing versions of the tuple in order to get the one that's live
for you.  This is normally not a problem: VACUUM goes around and
cleans out old, expired data that is not live for _anyone_.  It does
this by looking for the oldest transaction that is open.  (As far as
I understand it, this is actually the oldest transaction in the
entire back end; but I've never understood why that should the the
case, and I'm too incompetent/dumb to understand the code, so I may
be wrong on this point.)  If you have very long-running transactions,
then, you can end up with a lot of versions of dead tuples on the
table, and so reading the few records you want can turn out actually
to be a very expensive operation, even though it ought to be cheap.

You can see this by using the VERBOSE option to VACUUM:

test=# VACUUM VERBOSE eval1 ;
INFO:  vacuuming "public.eval1"
INFO:  "eval1": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_18831"
INFO:  index "pg_toast_18831_index" now contains 0 row versions in 1
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_18831": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Note those "removable" and "nonremovable" row versions.  It's the
unremovable ones that can hurt.  WARNING: doing VACUUM on a big table
on a disk that's already pegged is going to cause you performance
pain, because it scans the whole table.  In some cases, though, you
have no choice: if the winds are already out of your sails, and
you're effectively stopped, anything that might get you moving again
is an improvement.

> And how would the analyze help in finding this out ? I thought it would
> only show me additionally the actual timings, not more detail in what
> was done...

Yes, it shows the actual timings, and the actual number of rows.  But
if the estimates that the planner makes are wildly different than the
actual results, then you know your statistics are wrong, and that the
planner is going about things the wrong way.  ANALYSE is a big help. 
There's also a verbose option to it, but it's usually less useful in
production situations.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
It is above all style through which power defers to reason.
--J. Robert Oppenheimer

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Help tuning postgres

[Csaba Nagy]

Thanks Andrew, this explanation about the dead rows was enlightening.
Might be the reason for the slowdown I see on occasions, but not for the
case which I was first observing. In that case the updated rows are
different for each update. It is possible that each row has a few dead
versions, but not too many, each row is updated just a limited number of
times.

However, we have other updates which access the same row 1000s of times
(up to millions of times), and that could hurt if it's like you said,
i.e. if each update has to crawl over all the dead rows... I have now
autovacuum in place, and I'm sure it will kick in at ~ a few 1s of
updates, but in the meantime it could get bad.
In any case, I suppose that those disk pages should be in OS cache
pretty soon and stay there, so I still don't understand why the disk
usage is 100% in this case (with very low CPU activity, the CPUs are
mostly waiting/idle)... the amount of actively used data is not that
big.

I'll try to vacuum through cron jobs the most exposed tables to this
multiple-dead-row-versions symptom, cause autovacuum probably won't do
it often enough. Let's see if it helps...

Thanks,
Csaba.


On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote:
> > 
> > OK, this sounds interesting, but I don't understand: why would an update
> > "chase down a lot of dead tuples" ? Should I read up on some docs, cause
> > I obviously don't know enough about how updates work on postgres...
> 
> Right.  Here's the issue:
> 
> MVCC does not replace rows when you update.  Instead, it marks the
> old row as expired, and sets the new values.  The old row is still
> there, and it's available for other transactions who need to see it. 
> As the docs say (see
> ),
> "In effect, a SELECT query sees a snapshot of the database as of the
> instant that that query begins to run."  And that can be true because
> the original data is still there, although marked as expired for
> subsequent transactions.
> 
> UPDATE works the same was as SELECT in terms of searching for rows
> (so does any command that searches for data).  
> 
> Now, when you select data, you actually have to traverse all the
> existing versions of the tuple in order to get the one that's live
> for you.  This is normally not a problem: VACUUM goes around and
> cleans out old, expired data that is not live for _anyone_.  It does
> this by looking for the oldest transaction that is open.  (As far as
> I understand it, this is actually the oldest transaction in the
> entire back end; but I've never understood why that should the the
> case, and I'm too incompetent/dumb to understand the code, so I may
> be wrong on this point.)  If you have very long-running transactions,
> then, you can end up with a lot of versions of dead tuples on the
> table, and so reading the few records you want can turn out actually
> to be a very expensive operation, even though it ought to be cheap.
> 
> You can see this by using the VERBOSE option to VACUUM:
> 
> test=# VACUUM VERBOSE eval1 ;
> INFO:  vacuuming "public.eval1"
> INFO:  "eval1": found 0 removable, 0 nonremovable row versions in 0
> pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  vacuuming "pg_toast.pg_toast_18831"
> INFO:  index "pg_toast_18831_index" now contains 0 row versions in 1
> pages
> DETAIL:  0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "pg_toast_18831": found 0 removable, 0 nonremovable row
> versions in 0 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> VACUUM
> 
> Note those "removable" and "nonremovable" row versions.  It's the
> unremovable ones that can hurt.  WARNING: doing VACUUM on a big table
> on a disk that's already pegged is going to cause you performance
> pain, because it scans the whole table.  In some cases, though, you
> have no choice: if the winds are already out of your sails, and
> you're effectively stopped, anything that might get you moving again
> is an improvement.
> 
> > And how would the analyze help in finding this out ? I thought it would
> > only show me additionally the actual timings, not more detail in what
> > was done...
> 
> Yes, it shows the actual timings, and the actual number of rows.  But
> if the estimates that the planner makes are wildly different than the
> actual results, then you know your statistics are wrong, and that the
> planner is going about things the wrong way.  ANALYSE is a big help. 
> There's also a verbose option to it, but it's usually less useful in
> production situations.
> 
> A


---(end of broadcast)---
TIP 9: In 

Re: [PERFORM] Help tuning postgres

[Matthew Nuzum]

On 10/13/05, Csaba Nagy <[EMAIL PROTECTED]> wrote:
> On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> > On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote:
> > > And how would the analyze help in finding this out ? I thought it would
> > > only show me additionally the actual timings, not more detail in what
> > > was done...
> >
> > Yes, it shows the actual timings, and the actual number of rows.  But
> > if the estimates that the planner makes are wildly different than the
> > actual results, then you know your statistics are wrong, and that the
> > planner is going about things the wrong way.  ANALYSE is a big help.
> > There's also a verbose option to it, but it's usually less useful in
> > production situations.

This is the point I was trying to make. I've seen special instances
where people have posted an explain annalyze for a select/update to
the list and suggestions have arisen allowing major performance
improvements.

If this task is where your database is performing its worst then it is
the best place to start with optimizing, short of the obvious stuff,
which it sounds like you've covered.

Sometimes, and I think this has often been true for databases that are
either very large or very small, statistics can be tweaked to get
better performance. One good example is when a sequential scan is
being chosen when an index scan may be better; something like this
would definately peg your disk i/o.

Throwing more hardware at your problem will definately help, but I'm a
performance freak and I like to optimize everything to the max.
*Sometimes* you can get drastic improvements without adding any
hardware. I have seen some truly miraculus turn-arounds by tweaking
some non-obvious settings based on suggestions made on this list.
--
Matthew Nuzum
www.bearfruit.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Server misconfiguration???

[Andrew Sullivan]

On Mon, Oct 10, 2005 at 05:31:10PM +0300, Andy wrote:
> I read some tuning things, I made the things that are written there, but I 
> think that there improvements can be made.

Have you tried the suggestions people made?  Because if I were you,
I'd be listing very carefully to what Chris and Tom were telling me
about how to tune my database.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
--Brad Holland

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Help tuning postgres

[Andrew Sullivan]

On Thu, Oct 13, 2005 at 03:14:44PM +0200, Csaba Nagy wrote:
> In any case, I suppose that those disk pages should be in OS cache
> pretty soon and stay there, so I still don't understand why the disk
> usage is 100% in this case (with very low CPU activity, the CPUs are
> mostly waiting/idle)... the amount of actively used data is not that
> big.

Ah, but if the sum of all the dead rows is large enough that they
start causing your shared memory (== Postgres buffers) to thrash,
then you start causing the memory subsystem to thrash on the box,
which means less RAM is available for disk buffers because the OS is
doing more work; and the disk buffers are full of a lot of garbage
_anyway_, so then you may find that you're ending up hitting the disk
for some of these reads after all.  Around the office I have called
this the "buffer death spiral".  And note that once you've managed to
get into a vacuum-starvation case, your free space map might be
exceeded, at which point your database performance really won't
recover until you've done VACUUM FULL (prior to 7.4 there's also an
index problem that's even worse, and that needs occasional REINDEX to
solve; I forget which version you said you were using).

The painful part about tuning a production system is really that you
have to keep about 50 variables juggling in your head, just so you
can uncover the one thing that you have to put your finger on to make
it all play nice.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[PERFORM] slow update

[Patrick Hatcher]

Pg 7.4.5
RH 7.3
Quad Xeon 3Gz
12G ram

Trying to do a update of fields on 23M row database.
Is it normal for this process to take 16hrs and still clocking?  Both join
fields are indexed and I have removed any indexes on the updated columns.
Also both tables are vacuumed regularly.
I'm weary to cancel the job for fear that it is just slow and I'll have to
repeat the 16hr job.
Any suggestions of what I can check for the bottleneck?

Below is my update statement and table structure:

update cdm.cdm_ddw_tran_item
set dept_id = dept,
vend_id = vend,
mkstyl = mstyle
from flbasics
where flbasics.upc = cdm.cdm_ddw_tran_item.item_upc;


CREATE TABLE cdm.cdm_ddw_tran_item
(
  appl_xref varchar(22),
  intr_xref varchar(13),
  tran_typ_id char(1),
  tran_ship_amt numeric(8,2),
  fill_store_div int4,
  soldto_cust_id int8,
  soldto_cust_seq int4,
  shipto_cust_id int8,
  shipto_cust_seq int4,
  itm_qty int4,
  itm_price numeric(8,2),
  item_id int8,
  item_upc int8,
  item_pid varchar(20),
  item_desc varchar(30),
  nrf_color_name varchar(10),
  nrf_size_name varchar(10),
  dept_id int4,
  vend_id int4,
  mkstyl int4,
  ddw_tran_key bigserial NOT NULL,
  price_type_id int2 DEFAULT 999,
  last_update date DEFAULT ('now'::text)::date,
  CONSTRAINT ddw_tritm_pk PRIMARY KEY (ddw_tran_key)
)
WITHOUT OIDS;

CREATE TABLE flbasics
(
  upc int8,
  dept int4,
  vend int4,
  mstyle int4,
  xcolor int4,
  size int4,
  owned float8,
  cost float8,
  xclass int2,
  firstticket float8,
  status char(2),
  last_receipt date,
  description varchar(50),
  pack_qty int2,
  discontinue_date date,
  std_rcv_units int4,
  std_rcv_cost float8,
  std_rcv_retail float8,
  first_receipt date,
  last_pchange varchar(9),
  ticket float8,
  std_mkd_units int4,
  std_mkd_dollars float8
)
WITHOUT OIDS;

Patrick Hatcher
Development Manager  Analytics/MIO
Macys.com



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] slow update

[Tom Lane]

Patrick Hatcher <[EMAIL PROTECTED]> writes:
> Pg 7.4.5

> Trying to do a update of fields on 23M row database.
> Is it normal for this process to take 16hrs and still clocking?

Are there foreign keys pointing at the table being updated?  If so,
failure to index the referencing columns could create this sort of
performance problem.  Also, in 7.4 you'd better be sure the referencing
columns are the same datatype as the referenced column.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] slow update

[Patrick Hatcher]

Thanks.  No foreign keys and I've been bitten by the mismatch datatypes and
checked that before sending out the message :)

Patrick Hatcher
Development Manager  Analytics/MIO
Macys.com




   
 Tom Lane  
 <[EMAIL PROTECTED] 
 s> To 
   Patrick Hatcher 
 10/13/2005 11:34  <[EMAIL PROTECTED]>
 AM cc 
   postgres performance list   
 
   Subject 
   Re: [PERFORM] slow update   
   
   
   
   
   
   




Patrick Hatcher <[EMAIL PROTECTED]> writes:
> Pg 7.4.5

> Trying to do a update of fields on 23M row database.
> Is it normal for this process to take 16hrs and still clocking?

Are there foreign keys pointing at the table being updated?  If so,
failure to index the referencing columns could create this sort of
performance problem.  Also, in 7.4 you'd better be sure the referencing
columns are the same datatype as the referenced column.

 regards, tom lane



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org