postgresql-performance using sysbench

[Mike Tancsa]

At 04:31 AM 1/30/2008, Kris Kennaway wrote:
>Claus Guttesen wrote:
>>>>I forgot to mention in my first post that I'm using ULE. The p800
>>>>controller has a (factory set) 25/75 read/write cache ratio.
>>>There's maybe one additional thing: do you dual-boot Linux and FreeBSD?
>>>If so, you'll need to set up a separate additional partition for the
>>>database, instead of benchmarking it with the file systems used by the
>>>OS, because different areas of the drive(s) have different performance -
>>>you can verify this with diskinfo -t.
>>I installed FreeBSD onto a boot-partition (p400i-controller) and used
>>the external storage (p800) as database-partition (eight 15K rpm
>>sas-disks in raid 1+0). Same with Ubuntu. When I re-installed FreeBSD
>>and ubuntu I wiped and formatted the previous partitions. Ubuntu used
>>ext3 which I guess is default fs.
>
>Write performance is something that we are working on, expect to 
>hear about progress over the coming weeks/months.

I tried both ronly and read write, and using a very large table on 
RELENG_7, AMD64, ULE, 8G of RAM, Xeon 3060 dual core

sysbench --pgsql-host=""  --test=oltp --pgsql-user=pgsql 
--oltp-table-size=29400000 prepare
run=3
clients=40


for a in 2 8 16 32
do
  for ((b=1; b<=$run; b++))
  do
   echo loop $b of clients $a
   /usr/local/pgsql/bin/psql -d sbtest -U pgsql -c "vacuum analyse;"
   sysbench --test=oltp --oltp-read-only --num-threads=${a} 
--pgsql-user=pgsql --pgsql-host="" --max-time=300 
--max-requests=10000 --oltp-table-size=29400000 run >> 
sysbench-clients-linux-ronly-mike-${a}
  done
done


This created ~ an 8G table.  The database was on a dedicated Areca 
RAID10 array and the OS was on a separate disk (IDE). The results are 
pretty similar. At least for me, they are close enough.  For my 
customer, its a lot of reading from a 18G database and not too many 
writes. Looking at how his app typically runs against FreeBSD and 
Fedora, there was very little difference as well.

For the ronly test the FreeBSD values were all pretty tight, but for 
the 2 client one, the initial run for Fedora really was 
crappy.  After that, they had pretty similar distributions.  The 
values below are averages for 3 runs and an average for 5 on the rw 
tests that had smaller table sizes (--oltp-table-size=1900000)

Threads      FreeBSD                               Fedora 8
2            1213 
(1219.07,1210.66,1211.44)         674  (128.18,746.81,1266.14)
8            1155                                  1406
16           1118                                  1358
32           1069                                  1192


On the RW tests,
Threads      FreeBSD                               Fedora 8
2             404                                   491
8             222                                   366
16            198                                   349
32            162                                   265

This was also without too much tweaking of the FreeBSD side.

kern.ipc.shmall=532768
kern.ipc.shmmax=134217728
kern.ipc.semmap=256

shared_buffers = 24MB
max_fsm_pages = 153600
update_process_title = off


         ---Mike