Message-ID: <451AA1FE.9050704@axa.it>
Date: Wed, 27 Sep 2006 18:08:30 +0200
From: Edoardo Ceccarelli <eddy@axa.it>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: autovacuum on a -mostly- r/o table
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

Any help appreciated
Thank you very much
Edoardo