Re: [PERFORM] autovacuum on a -mostly- r/o table

In response to Edoardo Ceccarelli <eddy(at)axa(dot)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.