From: Gregory Stark <stark@enterprisedb.com>
To: Karl Wright <kwright@metacarta.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Tue, 19 Jun 2007 15:19:34 +0100
Message-ID: <877iq081s9.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <4677E1E4.903@metacarta.com>
References: <4676DD57.7070304@metacarta.com>
<14291.1182221811@sss.pgh.pa.us>
<4677B831.8060704@metacarta.com>
<4677D298.2010109@metacarta.com>
<87bqfc83bp.fsf@oxford.xeocode.com>
<4677E1E4.903@metacarta.com>
"Karl Wright" <kwright@metacarta.com> writes:
> Fine - but what if the previous vacuum is still in progress, and does not
> finish in 5 minutes?
Yes, well, there are problems with this design but the situation is already
much improved in 8.2 and there are more improvements on the horizon.
But it's likely that much of your pain is artificial here and once your
database is cleaned up a bit more it will be easier to manage.
> Well, the smaller tables don't change much, but the bigger tables have a lively
> mix of inserts and updates, so I would expect these would need vacuuming often.
Hm, I wonder if you're running into a performance bug that was fixed sometime
back around then. It involved having large numbers of tuples indexed with the
same key value. Every search for a single record required linearly searching
through the entire list of values.
If you have thousands of updates against the same tuple between vacuums you'll
have the same kind of situation and queries against that key will indeed
require lots of cpu.
To help any more you'll have to answer the basic questions like how many rows
are in the tables that take so long to vacuum, and how large are they on disk.
On 7.4 I think the best way to get the table size actually is by doing
"select relfilenode from pg_class where relname = 'tablename'" and then looking
in the postgres directory for the files in base/*/<relfilenode>*
The best information would be to do vacuum verbose and report the data it
prints out.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
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: stark@enterprisedb.com, kwright@metacarta.com, tgl@sss.pgh.pa.us
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <877iq081s9.fsf@oxford.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