Re: Performance problem. Could it be related to 8.3-beta4?

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD