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

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?

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 be altered when building the server). You can use this to estimate space requirements for WAL. Ordinarily, when old log segment files 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 unneeded segment 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 files are needed, so they never got deleted. Perhaps we should add in the docs that pg_xlog should be the size of the DB or larger?

--Richard