From: | Edoardo Ceccarelli <eddy(at)axa(dot)it> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | autovacuum on a -mostly- r/o table |
Date: | 2006-09-27 16:08:30 |
Message-ID: | 451AA1FE.9050704@axa.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Hodgson | 2006-09-27 16:13:22 | Re: autovacuum on a -mostly- r/o table |
Previous Message | Tom Lane | 2006-09-27 14:15:58 | Re: What Are Last Steps Performed When PostgreSQL Starts? |
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Hodgson | 2006-09-27 16:13:22 | Re: autovacuum on a -mostly- r/o table |
Previous Message | Dave Dutcher | 2006-09-27 15:51:26 | Re: Forcing the use of particular execution plans |