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

2008/1/5, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> writes:
> >> How did you get 8.3-beta4?
>
> > I built from the source rpm, which i installed in my machine. There is
> > something I forgot to mention. 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)
>
> What made you decide that was a good idea??
>
> Please revert that change and see if the performance goes back to
> normal.

I discovered i had a faulty raid, one of the drives were not working:

$ cat /proc/mdstat
Personalities : [raid1] [raid6] [raid5] [raid4] [raid0]
md2 : active raid1 sda1[0] sdb1[1]
224768 blocks [2/2] [UU]

md0 : active raid0 sda2[0] sdb2[1]
4497920 blocks 256k chunks

md1 : active raid1 sdb3[1]
310094592 blocks [2/1] [_U]

After some web reading I managed to make the raid functional:

# cat /proc/mdstat
Personalities : [raid1] [raid6] [raid5] [raid4] [raid0]
md2 : active raid1 sda1[0] sdb1[1]
224768 blocks [2/2] [UU]

md0 : active raid0 sda2[0] sdb2[1]
4497920 blocks 256k chunks

md1 : active raid1 sda3[0] sdb3[1]
310094592 blocks [2/2] [UU]

Just to remember the production server completes the insert query in 800 sec.

The new server with the faulty raid and 1GB xlog_seg_size did it in 6,889 sec.

With the fixed raid and 1GB xlog_seg_size the time lowered to 1,380 sec.

Then I rebuilt and reinstalled postgresql with the xlog_seg_size set
to the default 16MB and did initdb. Now the time is 7,642 sec.

I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB
but again it is slower than the production server which uses the
default xlog_seg_size.

Regards, Clodoaldo Pinto Neto