On Sat, 5 Jan 2008, Clodoaldo wrote:
I created a patch to change XLOG_SEG_SIZE and built with it: -#define XLOG_SEG_SIZE (16*1024*1024) +#define XLOG_SEG_SIZE (1024*1024*1024)
People sometimes get a small improvement setting this to 2-4 times larger than the default when running a large server (one with lots of disks where WAL issues are a bottleneck). Making it 64X as large as you're doing here is so gigantic I have no idea how much it degrades performance during a segment change or checkpoint; it's certainly not good.
Just so you understand what you're playing with here a bit better: the standard WAL segment is 16MB. You're making it 1GB instead. Every time your system needs a new WAL segment, it first creates that file, writes a 0 to every byte in it, then the segment is ready for use. So on your server, whenever a new WAL segment is required, the entire system grinds to a halt as the database server writes 1GB worth of data before that segment is ready. Will that slow things to take 10X as long? Sure is possible.
Since you've got checkpoint_segments at 60, this also means that eventually your database server will have (2*60+1)*1GB=121GB worth of disk space used just by the WAL segments--that's on top of whatever is used by the database.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
