pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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?)





view thread (26+ messages)  latest in thread

Message-ID: <405614CA.5030101@zeut.net>
Permalink:  ../405614CA.5030101@zeut.net/
Also on:    postgresql.org/message-id/405614CA.5030101@zeut.net

 · 

reply

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