[Bizgres-general] Performance - WAL bypass + parse

[Mark Kirkwood]

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