pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Can autovacuum result in large surges in WAL files 2+ messages / 2 participants [nested] [flat]
* Can autovacuum result in large surges in WAL files @ 2011-05-03 17:22 Todd Gugler <tgugler11@gmail.com> 2011-05-03 17:31 ` Re: Can autovacuum result in large surges in WAL files Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 1 reply; 2+ messages in thread From: Todd Gugler @ 2011-05-03 17:22 UTC (permalink / raw) To: pgsql-admin I have a Postgres 8.3 system that is configured to run autovacuum. It triggered a vacuum analyze on multiple tables and since this started, we are getting large numbers of WAL files generated about every 15 minutes or so. This same vacuum appears to continuing to run. Other database activity is at its usual levels. Should a vacuum analyze lead to large amounts of data being written to WAL files? We are seeing 10x the number WAL files that we would expect. Any ideas on the best approach to diagnose what is going on? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Can autovacuum result in large surges in WAL files 2011-05-03 17:22 Can autovacuum result in large surges in WAL files Todd Gugler <tgugler11@gmail.com> @ 2011-05-03 17:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 0 replies; 2+ messages in thread From: Kevin Grittner @ 2011-05-03 17:31 UTC (permalink / raw) To: Todd Gugler <tgugler11@gmail.com>; pgsql-admin Todd Gugler <tgugler11@gmail.com> wrote: > Should a vacuum analyze lead to large amounts of data being > written to WAL files? Yes, if it has to "freeze" the rows to prevent transaction wraparound. > We are seeing 10x the number WAL files that we would expect. > > Any ideas on the best approach to diagnose what is going on? Is it vacuuming a table which was bulk loaded at some time in the past? If so, this can happen any time later (usually during busy periods when many transactions numbers are being assigned). To prevent this in the future, run VACUUM FREEZE ANALYZE after any bulk load. There has been much talk about possible ways to avoid this behavior by initially setting bulk loaded data to "frozen", but there are technical problems which haven't been adequately addressed yet. -Kevin ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-05-03 17:31 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-05-03 17:22 Can autovacuum result in large surges in WAL files Todd Gugler <tgugler11@gmail.com> 2011-05-03 17:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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