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
areno spindles.
-- However, I didn't take into account the fact that pg_xlog might grow in size when autovacuum does its work when
vacuumingto prevent XID wraparound. I *just* discovered this when one of my other replication nodes decided to die on
meand fill up its disk.
-- Unfortunately, my db is 114GB (including indexes) or 60GB (without indexes), leaving ~37GB for pg_xlog (since they
aresharing a partition). So I'm guessing what happened was that when autovacuum ran to prevent XID wraparound, it
takeseach table and changes the XID, and it gets recorded in WAL, causing WAL to bloat. This this the correct
understanding?
Question for now is, documentation says:
> There will always be at least one WAL segment file, and will normally not be more than (2 +
checkpoint_completion_target)* checkpoint_segments + 1 files. Each segment file is normally 16 MB (though this size can
bealtered when building the server). You can use this to estimate space requirements for WAL. Ordinarily, when old log
segmentfiles are no longer needed, they are recycled (renamed to become the next segments in the numbered sequence).
If,due to a short-term peak of log output rate, there are more than 3 * checkpoint_segments + 1 segment files, the
unneededsegment files will be deleted instead of recycled until the system gets back under this limit.
This means my pg_xlog partition should be (2 + checkpoint_completion_target) * checkpoint_segments + 1 = 41 files, or
656MB. Then, if there are more than 49 files, unneeded segment files will be deleted, but in this case all segment
filesare needed, so they never got deleted. Perhaps we should add in the docs that pg_xlog should be the size of the
DBor larger?
--Richard