From: Joe Conway <mail@joeconway.com>
To: Matthew T. O'Connor <matthew@zeut.net>
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 20:59:16 -0800
Message-ID: <405689A4.4080406@joeconway.com> (raw)
In-Reply-To: <405614CA.5030101@zeut.net>
References: <4052661D.5060505@joeconway.com>
<14884.1079150555@sss.pgh.pa.us>
<4055F343.6070600@joeconway.com>
<405614CA.5030101@zeut.net>
Matthew T. O'Connor wrote:
> 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?
Yeah, I'm sure. Snippets from the log:
[...lots-o-tables...]
[2004-03-14 12:44:48 PM] added table: specdb."public"."parametric_states"
[2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE
"public"."transaction_data"
[2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE
"public"."transaction_data"
[2004-03-14 02:08:26 PM] Performing: ANALYZE "public"."out_of_spec"
[2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE
"public"."transaction_data"
[2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."out_of_spec"
[2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE
"public"."transaction_data"
[2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 03:21:09 PM] Performing: ANALYZE "public"."parametric_states"
[2004-03-14 03:54:57 PM] Performing: ANALYZE "public"."out_of_spec"
[2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE
"public"."transaction_data"
[2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 04:09:33 PM] Performing: ANALYZE "public"."equip_status_history"
[2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE
"public"."parametric_states"
[2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."out_of_spec"
[2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE
"public"."transaction_data"
[2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 04:58:32 PM] Performing: ANALYZE "public"."parametric_states"
[2004-03-14 05:28:58 PM] added database: specdb
This is the entire period of the first test, with default autovac
settings. The table "public"."transaction_data" is the one with 28
million active rows. The entire test run inserts about 600 x 600 =
360,000 rows, out of which roughly two-thirds are later deleted.
> 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?
I can try. The server belongs to another department, and they are under
the gun to get back on track with their testing. Also, they compiled
without debug symbols, so I need to get permission to recompile.
> Yes I would be very curious to see the results with the vacuum delay
> patch installed (is that patch applied to HEAD?)
Any idea where I can get my hands on the latest version. I found the
original post from Tom, but I thought there was a later version with
both number of pages and time to sleep as knobs.
Thanks,
Joe
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: mail@joeconway.com, matthew@zeut.net, tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: rapid degradation after postmaster restart
In-Reply-To: <405689A4.4080406@joeconway.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