Re: [Slony1-general] WAL partition overloaded--by autovacuum?

On Wed, Jul 7, 2010 at 3:32 PM, Richard Yen <richyen(at)iparadigms(dot)com> wrote:
> On Jul 6, 2010, at 8:25 PM, Scott Marlowe wrote:
>
>> Tell us what you can about your hardware setup.
>
> Sorry, I made the bad assumption that the hardware setup would be irrelevant--dunno why I thought that.
>
> My hardware setup is 2 FusionIO 160GB drives in a RAID-1 configuration, running on an HP DL360 G5
>
> I think I figured out the problem:
>
> -- I figured that pg_xlog and data/base could both be on the FusionIO drive, since there would be no latency when there are no spindles.
> -- However, I didn't take into account the fact that pg_xlog might grow in size when autovacuum does its work when vacuuming to prevent XID wraparound.  I *just* discovered this when one of my other replication nodes decided to die on me and fill up its disk.
> -- Unfortunately, my db is 114GB (including indexes) or 60GB (without indexes), leaving ~37GB for pg_xlog (since they are sharing a partition).  So I'm guessing what happened was that when autovacuum ran to prevent XID wraparound, it takes each table and changes the XID, and it gets recorded in WAL, causing WAL to bloat.  This this the correct understanding?

That seems logical (and un-fun), but I don't understand how you
managed to fill up 37GB of disk with WAL files. Every time you fill
up checkpoint_segments * 16MB of WAL files, you ought to get a
checkpoint. When it's complete, WAL segments completely written
before the start of the checkpoint should be recyclable. Unless I'm
confused, which apparently I am.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company