Thread: autovac hitting too many tables at once
Hi, I've got an 8.4.11 system that I am relatively new to and I am seeing multiple autovac processes kick off on several of thelargest tables at once and it is causing pain. Are there any suggestions to a) quickly relieve the immediate pain and b) prevent such issues in the future (other than going to manual vacuuming on a schedule). Thanks, Greg Williamson
Greg Williamson <gwilliamson39@yahoo.com> wrote: > I've got an 8.4.11 system that I am relatively new to and I am > seeing multiple autovac processes kick off on several of the > largest tables at once and it is causing pain. > > Are there any suggestions to > > a) quickly relieve the immediate pain Set autovacuum_cost_limit to a smaller value. (Change the postgresql.conf file and do a reload.) I would start by cutting the current value in half. > and > > b) prevent such issues in the future (other than going to manual > vacuuming on a schedule). If it is suddenly doing this on lots of big tables at once, it seems somewhat likely that you've hit the transaction wraparound protection threshold. Because the vacuum necessary for this can be painful, and they tend to come at the worst possible time (the more your workload looks like a really heavy OLTP workload at any given moment, the higher the probability that this is about to happen), I always follow a bulk load (like from restoring pg_dump output) with a VACUUM FREEZE ANALYZE. You might also want to consider running off-hours vacuums to supplement autovacuum. Upgrading to a more recent version of PostgreSQL is likely to help some, too. -Kevin
Kevin -- <...> >> a) quickly relieve the immediate pain > > Set autovacuum_cost_limit to a smaller value. (Change the > postgresql.conf file and do a reload.) I would start by cutting the > current value in half. > Thanks -- would not have known how to start. But for now, in observance of your idea on XID wraparound, I am letting everything run (this is actually a low point in regular traffic for this site and I am not seeing firect proof that autovac is in fact doing anything out of the ordinary). >> and >> >> b) prevent such issues in the future (other than going to manual >> vacuuming on a schedule). > > If it is suddenly doing this on lots of big tables at once, it seems > somewhat likely that you've hit the transaction wraparound > protection threshold. Because the vacuum necessary for this can be > painful, and they tend to come at the worst possible time (the more > your workload looks like a really heavy OLTP workload at any given > moment, the higher the probability that this is about to happen), I > always follow a bulk load (like from restoring pg_dump output) with > a VACUUM FREEZE ANALYZE. > > You might also want to consider running off-hours vacuums to > supplement autovacuum. Upgrading to a more recent version of > PostgreSQL is likely to help some, too. > Duly noted. Thanks! Greg
A follow up -- <...> >>> a) quickly relieve the immediate pain >> >> Set autovacuum_cost_limit to a smaller value. (Change the >> postgresql.conf file and do a reload.) I would start by cutting the >> current value in half. >> > >Thanks -- would not have known how to start. But for now, in >observance of your idea on XID wraparound, I am letting >everything run (this is actually a low point in regular traffic for this >site and I am not seeing direct proof that autovac is in fact doing >anything out of the ordinary). > While sleuthing logs for some data these 3 processes began to finish so the immediate issue went away. We'll make these changes next week I suspect. Lig searches showed that the vast majority of our autovacs were on replication related tables in ordinary processing so reducing the max number autovacs was not to be contemplated. >>> and >>> >>> b) prevent such issues in the future (other than going to manual >>> vacuuming on a schedule). >> >> If it is suddenly doing this on lots of big tables at once, it seems >> somewhat likely that you've hit the transaction wraparound >> protection threshold. Because the vacuum necessary for this can be >> painful, and they tend to come at the worst possible time (the more >> your workload looks like a really heavy OLTP workload at any given >> moment, the higher the probability that this is about to happen), I >> always follow a bulk load (like from restoring pg_dump output) with >> a VACUUM FREEZE ANALYZE. >> >> You might also want to consider running off-hours vacuums to >> supplement autovacuum. Upgrading to a more recent version of >> PostgreSQL is likely to help some, too. >> >Duly noted. The autovac processes were not the core problem, natch. Someone deleted 50000 rows from one table, generating larger deletes in the two other tables, which in turn provoked replication issues, all of which got autovac's attention. Given time everything calmed down and a lesson learned has been inscribed. Don't do mass deletes -- throttle them back, one way or another. If you must tread this path make some adjustments to relevant tables and values first. Thanks, Kevin, for the quick response. Greg W.