pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
autovac hitting too many tables at once 4+ messages / 2 participants [nested] [flat]
* autovac hitting too many tables at once @ 2012-06-22 18:58 Greg Williamson <gwilliamson39@yahoo.com> 2012-06-22 19:13 ` Re: autovac hitting too many tables at once Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 1 reply; 4+ messages in thread From: Greg Williamson @ 2012-06-22 18:58 UTC (permalink / raw) To: pgsql-admin 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 the largest 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovac hitting too many tables at once 2012-06-22 18:58 autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> @ 2012-06-22 19:13 ` Kevin Grittner <Kevin.Grittner@wicourts.gov> 2012-06-22 19:41 ` Re: autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> 0 siblings, 1 reply; 4+ messages in thread From: Kevin Grittner @ 2012-06-22 19:13 UTC (permalink / raw) To: pgsql-admin; Greg Williamson <gwilliamson39@yahoo.com> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovac hitting too many tables at once 2012-06-22 18:58 autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> 2012-06-22 19:13 ` Re: autovac hitting too many tables at once Kevin Grittner <Kevin.Grittner@wicourts.gov> @ 2012-06-22 19:41 ` Greg Williamson <gwilliamson39@yahoo.com> 2012-06-23 23:09 ` Re: autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> 0 siblings, 1 reply; 4+ messages in thread From: Greg Williamson @ 2012-06-22 19:41 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovac hitting too many tables at once 2012-06-22 18:58 autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> 2012-06-22 19:13 ` Re: autovac hitting too many tables at once Kevin Grittner <Kevin.Grittner@wicourts.gov> 2012-06-22 19:41 ` Re: autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> @ 2012-06-23 23:09 ` Greg Williamson <gwilliamson39@yahoo.com> 0 siblings, 0 replies; 4+ messages in thread From: Greg Williamson @ 2012-06-23 23:09 UTC (permalink / raw) To: pgsql-admin 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-06-23 23:09 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-06-22 18:58 autovac hitting too many tables at once Greg Williamson <gwilliamson39@yahoo.com> 2012-06-22 19:13 ` Kevin Grittner <Kevin.Grittner@wicourts.gov> 2012-06-22 19:41 ` Greg Williamson <gwilliamson39@yahoo.com> 2012-06-23 23:09 ` Greg Williamson <gwilliamson39@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox