[Bizgres-general] Performance - WAL bypass + parse

[Luke Lonergan]

That concurrent read/write perf is terrible.  But the file being read from may be in I/O cache (would be for Linux) if you had just created it.

When you compiled, you did so with optimization, right?

The profile will be interesting, you should also look at vmstat for the I/O rates while loading and see how much CPU is consumed.

Is this a 2 CPU machine?  Can you try a load from a networked client using backslash copy?

Luke
---
Sent from my Treo

 -----Original Message-----
From: 	Mark Kirkwood [mailto:markir at paradise.net.nz]
Sent:	Tue Jun 07 21:01:42 2005
To:	Luke Lonergan
Cc:	bizgres-general
Subject:	Re: [Bizgres-general] Performance - WAL bypass + parse

Luke Lonergan wrote:
> I can save you some time - I think you are seeing expected behavior.
> 
> Your bottleneck on your BSD system is probably in the disk access, not in
> the CPU processing.  You need to check how fast your disk subsystem is with
> a simple test like:
>   time dd if=/dev/zero of=bigfile bs=1024k count=4000
> 
> This times the write of 4GB of data to a file using a 1MB blocksize.  If you
> then divide 4000/time you will get the MB/s number that represents your
> sequential filesystem write speed (with minimal buffering because you used
> 4GB or roughly 2x memory).  Do the same for read:
>   time dd if=bigfile of=/dev/null bs=1024k
> 
> My guess is that you'll find your IDE RAID 0 is slow for some reason.
> 

The above tests produce :

i) write 4G @1024k blocks 30M/s (4G @8k blocks 44M/s)
ii) read 4G @1024k blocks 44M/s (4G @8k blocks 55M/s)

I have included the same test using 8k blocks, as this mimics what 
postgres is doing a bit better. Note that the numbers are quite a bit 
better, I am guessing that my using a RAID stripe of 256k means that 
sequential writes of blocks > 256k are penalized (not usually a problem...).

Now, this performance is not world-beating, but is quite reasonable - I 
don't think IO is bottlenecked.

Another test that could be significant is reading and writing a file in 
the same filesystem (as this is what is kind of happening):

iii) read 4G and write 4G @8k blocks 9M/s

This would seem to mean that the best performance I could ever see doing 
these loads would be 9M/s. This is believable (note that 6M/s has been 
achieved...).

My money is on the IO *not* being the bottleneck - I will see if I can 
get these profiles going!

Cheers

Mark