From: Ian Westmacott <ianw@intellivid.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: cost-based vacuum
Date: Tue, 12 Jul 2005 13:50:51 -0400
Message-ID: <1121190651.10346.342.camel@spectre.intellivid.com> (raw)
In-Reply-To: <1121154338.3970.106.camel@localhost.localdomain>
References: <1120839902.20657.197.camel@spectre.intellivid.com>
<1121081504.3970.53.camel@localhost.localdomain>
<1121087266.27427.35.camel@spectre.intellivid.com>
<1121093488.3970.70.camel@localhost.localdomain>
<1121154338.3970.106.camel@localhost.localdomain>
On Tue, 2005-07-12 at 03:45, Simon Riggs wrote:
> Since vacuum_cost_delay is a userset parameter, you should be able to
> SET this solely for the analyze_thread. That way we will know with more
> certainty that it is the analyze_thread that is interfering.
That is what I have been doing. In fact, I have eliminated
the reader_thread and analyze_thread. I just have the
writer_thread running, and a psql connection with which I
perform ANALYZE, for various vacuum_cost_* parameters.
(I'm trying to extract a reproducible experiment)
It appears not to matter whether it is one of the tables
being written to that is ANALYZEd. I can ANALYZE an old,
quiescent table, or a system table and see this effect.
> What is your default_statistics_target?
All other configs are default; default_statistics_target=10.
> Do you have other stats targets set?
No. The only thing slightly out of the ordinary with the
tables is that they are created WITHOUT OIDS. Some indexes,
but no primary keys. All columns NOT NULL.
> How long does ANALYZE take to run, with/without the vacuum_cost_delay?
Well, on one table with about 50K rows, it takes about 1/4s
to ANALYZE with vacuum_cost_delay=0, and about 15s with
vacuum_cost_delay=1000.
Other things of note:
- VACUUM has the same effect. If I VACUUM or ANALYZE the
whole DB, the CPU spikes reset between tables.
- vmstat reports blocks written drops as the CPU rises.
Don't know if it is cause or effect yet. On a small test
system, I'm writing about 1.5MB/s. After about 20s
of cost-based ANALYZE, this drops under 0.5MB/s.
- this is a dual Xeon. I have tried both with and without
hyperthreading. I haven't tried to reproduce it
elsewhere yet, but will.
- Looking at oprofile reports for 10-minute runs of a
database-wide VACUUM with vacuum_cost_delay=0 and 1000,
shows the latter spending a lot of time in LWLockAcquire
and LWLockRelease (20% each vs. 2%).
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, simon@2ndquadrant.com
Subject: Re: cost-based vacuum
In-Reply-To: <1121190651.10346.342.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