On Jan 5, 2008 1:15 PM, Greg Smith <gsmith@gregsmith.com> wrote:
> 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.
Actually, even more importantly, the OP should strive to make the
8.3b4 environment as much like the 8.2.5 environment, then benchmark
it, before setting off on experiments in performance enhancements. He
needs to set a baseline before messing about with any of the settings.