From: Matthew T. O'Connor <matthew@zeut.net>
To: Joe Conway <mail@joeconway.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: Josh Berkus <josh@agliodbs.com>
Subject: Re: rapid degradation after postmaster restart
Date: Mon, 15 Mar 2004 15:40:42 -0500
Message-ID: <405614CA.5030101@zeut.net> (raw)
In-Reply-To: <4055F343.6070600@joeconway.com>
References: <4052661D.5060505@joeconway.com>
<14884.1079150555@sss.pgh.pa.us>
<4055F343.6070600@joeconway.com>
Joe Conway wrote:
> A few pg_autovacuum questions came out of this:
>
> First, the default vacuum scaling factor is 2, which I think implies
> the big table should only get vacuumed every 56 million or so changes.
> I didn't come anywhere near that volume in my tests, yet the table did
> get vacuumed more than once (I was watching the pg_autovacuum log
> output). Do I misunderstand this setting?
I think you understand correctly. A table with 1,000,000 rows should
get vacuumed approx every 2,000,000 changes (assuming default values for
-V ). FYI and insert and a delete count as one change, but and update
counts as two.
Unfortunately, the running with -d2 would show the numbers that
pg_autovacuum is using to decide if it when it should vacuum or
analyze. Also, are you sure that it vacuumed more than once and
wasn't doing analyzes most of the time?
Also, I'm not sure if 2 is a good default value for the scaling factor
but I erred on the side of not vacuuming too often.
> Second, Matthew requested pg_autovacuum run with -d2; I found that
> with -d2 set, pg_autovacuum would immediately exit on start. -d0 and
> -d1 work fine however.
That's unfortunate as that is the detail we need to see what
pg_autovacuum thinks is really going on. We had a similar sounding
crash on FreeBSD due to some unitialized variables that were being
printed out by the debug code, however that was fixed a long time ago.
Any chance you can look into this?
> That's all I can think of at the moment. I'd like to try the 7.4 patch
> that makes vacuum sleep every few pages -- can anyone point me to the
> latest and greatest that will apply to 7.4?
Yes I would be very curious to see the results with the vacuum delay
patch installed (is that patch applied to HEAD?)
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: matthew@zeut.net, mail@joeconway.com, tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: rapid degradation after postmaster restart
In-Reply-To: <405614CA.5030101@zeut.net>
* 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