On Wed, Jul 7, 2010 at 3:32 PM, Richard Yen <rich...@iparadigms.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
