Skip site navigation (1) Skip section navigation (2)

Re: Can autovacuum result in large surges in WAL files

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Todd Gugler" <tgugler11(at)gmail(dot)com>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Can autovacuum result in large surges in WAL files
Date: 2011-05-03 17:31:42
Message-ID: 4DBFF5AE020000250003D1D9@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-admin
Todd Gugler <tgugler11(at)gmail(dot)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

In response to

pgsql-admin by date

Next:From: Joshua D. DrakeDate: 2011-05-03 18:44:07
Subject: Re: [ADMIN] Can we Flush the Postgres Shared Memory ?
Previous:From: Todd GuglerDate: 2011-05-03 17:22:33
Subject: Can autovacuum result in large surges in WAL files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group