From: Ian Westmacott <ianw@intellivid.com>
To: pgsql-performance@postgresql.org
Subject: cost-based vacuum
Date: Fri, 08 Jul 2005 12:25:02 -0400
Message-ID: <1120839902.20657.197.camel@spectre.intellivid.com> (raw)
I am beginning to look at Postgres 8, and am particularly
interested in cost-based vacuum/analyze. I'm hoping someone
can shed some light on the behavior I am seeing.
Suppose there are three threads:
writer_thread
every 1/15 second do
BEGIN TRANSACTION
COPY table1 FROM stdin
...
COPY tableN FROM stdin
perform several UPDATEs, DELETEs and INSERTs
COMMIT
reader_thread
every 1/15 second do
BEGIN TRANSACTION
SELECT FROM table1 ...
...
SELECT FROM tableN ...
COMMIT
analyze_thread
every 5 minutes do
ANALYZE table1
...
ANALYZE tableN
Now, Postgres 8.0.3 out-of-the-box (all default configs) on a
particular piece of hardware runs the Postgres connection for
writer_thread at about 15% CPU (meaningless, I know, but for
comparison) and runs the Postgres connection for reader_thread
at about 30% CPU. Latency for reader_thread seeing updates
from writer_thread is well under 1/15s. Impact of
analyze_thread is negligible.
If I make the single configuration change of setting
vacuum_cost_delay=1000, each iteration in analyze_thread takes
much longer, of course. But what I also see is that the CPU
usage of the connections for writer_thread and reader_thread
spike up to well over 80% each (this is a dualie) and latency
drops to 8-10s, during the ANALYZEs.
I don't understand why this would be. I don't think there
are any lock issues, and I don't see any obvious I/O issues.
Am I missing something? Is there any way to get some
insight into what those connections are doing?
Thanks,
--Ian
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: ianw@intellivid.com
Subject: Re: cost-based vacuum
In-Reply-To: <1120839902.20657.197.camel@spectre.intellivid.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