Thread: autovacuum on a -mostly- r/o table
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
[Edoardo Ceccarelli - Wed at 06:08:30PM +0200] > We also activated the autovacuum feature to give it a try and that's > were our problems started. (...) > How can I configure the vacuum to run after the daily batch insert/update? I think you shouldn't use autovacuum in your case. We haven't dared testing out autovacuum yet even though we probably should, so we're running vacuum at fixed times of the day. We have a very simple script to do this, the most important part of it reads: echo "vacuum verbose analyze;" | psql $DB_NAME > $logdir/$filename 2>&1
On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote: > How can I configure the vacuum to run after the daily batch insert/update? Check out this: http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html By inserting the right row you can disable autovacuum to vacuum your big tables, and then you can schedule vacuum nightly for those just as before. There's still a benefit in that you don't need to care about vacuuming the rest of the tables, which will be done just in time. Cheers, Csaba.
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.
On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote: > > 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. In some cases the solution to high load is to vacuum the tables being hit the heaviest -- meaning that simply checking machine load isn't enough to make that decision. In fact, that high load problem is exactly why autovacuum was created in the first place. --
Csaba Nagy wrote: > On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote: > >> How can I configure the vacuum to run after the daily batch insert/update? >> > > Check out this: > http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html > > By inserting the right row you can disable autovacuum to vacuum your big > tables, and then you can schedule vacuum nightly for those just as > before. There's still a benefit in that you don't need to care about > vacuuming the rest of the tables, which will be done just in time. In addition autovacuum respects the work of manual or cron based vacuums, so if you issue a vacuum right after a daily batch insert / update, autovacuum won't repeat the work of that manual vacuum.