From: Greg Stark <gsstark@mit.edu>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Quad Opteron stuck in the mud
Date: 14 Jul 2005 02:12:30 -0400
Message-ID: <87oe95ykz5.fsf@stark.xeocode.com> (raw)
In-Reply-To: <E5E21B7B-FFE0-4D24-8CDD-62179AEBBB92@drivefaster.net>
References: <E5E21B7B-FFE0-4D24-8CDD-62179AEBBB92@drivefaster.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, fbsd@drivefaster.net
Subject: Re: Quad Opteron stuck in the mud
In-Reply-To: <87oe95ykz5.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox