Subject: Re: cost-based vacuum
From: Ian Westmacott <ianw@intellivid.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
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>
Content-Type: text/plain
Organization: Intellivid Corp.
Message-Id: <1121190651.10346.342.camel@spectre.intellivid.com>
Mime-Version: 1.0
Date: Tue, 12 Jul 2005 13:50:51 -0400
Content-Transfer-Encoding: 7bit

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