To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Quad Opteron stuck in the mud
References: <E5E21B7B-FFE0-4D24-8CDD-62179AEBBB92@drivefaster.net>
In-Reply-To: <E5E21B7B-FFE0-4D24-8CDD-62179AEBBB92@drivefaster.net>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 14 Jul 2005 02:12:30 -0400
Message-ID: <87oe95ykz5.fsf@stark.xeocode.com>
Lines: 57
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Dan Harris <fbsd@drivefaster.net> writes:

> I keep the entire database vacuumed regularly.

How often is "regularly"? We get frequent posts from people who think daily or
every 4 hours is often enough. If the table is very busy you can need vacuums
as often as every 15 minutes. 

Also, if you've done occasional massive batch updates like you describe here
you may need a VACUUM FULL or alternatively a CLUSTER command to compact the
table -- vacuum identifies the free space but if you've doubled the size of
your table with a large update that's a lot more free space than you want
hanging around waiting to be used.

> For example, as I'm writing this, I am running an UPDATE statement  that will
> affect a small part of the table, and is querying on an  indexed boolean field.
...
> update eventactivity set ftindex = false where ftindex = true;  ( added the
> where clause because I don't want to alter where ftindex  is null )

It's definitely worthwhile doing an "EXPLAIN UPDATE..." to see if this even
used the index. It sounds like it did a sequential scan.

Sequential scans during updates are especially painful. If there isn't free
space lying around in the page where the updated record lies then another page
has to be used or a new page added. If you're doing a massive update you can
exhaust the free space available making the update have to go back and forth
between the page being read and the end of the table where pages are being
written.

> #####
> 
> vmstat output ( as I am waiting for this to finish ):
> procs -----------memory---------- ---swap-- -----io---- --system--
> ----cpu----
> r  b   swpd   free    buff   cache   si   so    bi    bo   in    cs  us sy id wa
> 0  1   5436 2823908  26140 9183704    0    1  2211   540  694   336   9  2 76 13

[I assume you ran "vmstat 10" or some other interval and then waited for at
least the second line? The first line outputted from vmstat is mostly
meaningless]

Um. That's a pretty meager i/o rate. Just over 2MB/s. The cpu is 76% idle
which sounds fine but that could be one processor pegged at 100% while the
others are idle. If this query is the only one running on the system then it
would behave just like that.

Is it possible you have some foreign keys referencing these records that
you're updating? In which case every record being updated might be causing a
full table scan on another table (or multiple other tables). If those tables
are entirely in cache then it could cause these high cpu low i/o symptoms.

Or are there any triggers on this table?


-- 
greg