Date: Wed, 27 Sep 2006 12:14:54 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: Edoardo Ceccarelli <eddy@axa.it>
Cc: pgsql-performance@postgresql.org, pgsql-admin@postgresql.org
Subject: Re: [PERFORM] autovacuum on a -mostly- r/o table
Message-Id: <20060927121454.36b9fc6c.wmoran@collaborativefusion.com>
In-Reply-To: <451AA1FE.9050704@axa.it>
References: <451AA1FE.9050704@axa.it>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to Edoardo Ceccarelli <eddy@axa.it>:

> Hello,
> 
> we are running a 7.3 postgres db with only a big table (avg 
> 500.000records) and 7 indexes for a search engine.
> we have 2 of this databases and we can switch from one to another.
> Last week we decided to give a try to 8.1 on one of them and everything 
> went fine, db is faster (about 2 or 3 times in our case) and the server 
> load is higher - which should mean that faster response time is achieved 
> by taking a better use of the server.
> 
> We also activated the autovacuum feature to give it a try and that's 
> were our problems started.
> I left the standard autovacuum configuration just to wait and see, pg 
> decided to start a vacuum on the table just midday when users were 
> launching search queries on the table and server load reached a very 
> high value so that in a couple of minutes the db was unusable
> 
> With pg7.3 we use to vacuum the db night time, mostly because the insert 
> and updates in this table is made in a batch way: a single task that 
> puts 100.000 records in the db in 10/20minutes, so the best time to 
> actually vacuum the db would be after this batch.
> 
> I have read that autovacuum cannot check to see pg load before launching 
> vacuum but is there any patch about it? that would sort out the problem 
> in a good and simple way.
> Otherwise, which kind of set of parameters I should put in autovacuum 
> configuration? I am stuck because in our case the table gets mostly read 
> and if I set up things as to vacuum the table after a specific amount of 
> insert/updates, I cannot foresee whether this could happen during 
> daytime when server is under high load.
> How can I configure the vacuum to run after the daily batch insert/update?

It doesn't sound as if your setup is a good match for autovacuum.  You
might be better off going back to the cron vacuums.  That's the
beauty of Postgres -- it gives you the choice.

If you want to continue with autovac, you may want to experiment with
vacuum_cost_delay and associated parameters, which can lessen the
impact of vacuuming.

-- 
Bill Moran
Collaborative Fusion Inc.