I've got a new server and am myself new to tuning postgres.
Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/ BBU.
It's serving as the DB for a fairly write intensive (maybe 25-30%) Web
application in PHP. We are not using persistent connections, thus the
high max connections.
I've done the following so far:
> cat /boot/loader.conf
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
> cat /etc/sysctl.conf
kern.ipc.shmall=393216
kern.ipc.shmmax=1610612736
kern.ipc.semmap=256
kern.ipc.shm_use_phys=1
postgresql.conf settings (changed from Default):
max_connections = 180
shared_buffers = 1024MB
maintenance_work_mem = 128MB
wal_buffers = 1024kB
I then set up a test database for running pgbench with scaling factor
100. I then ran:
> pgbench -c 100 -t 1000 testdb
and got:
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 100
number of transactions per client: 1000
number of transactions actually processed: 100000/100000
tps = 557.095867 (including connections establishing)
tps = 558.013714 (excluding connections establishing)
Just for testing, I tried turning off fsync and got:
> pgbench -c 100 -t 1000 testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 100
number of transactions per client: 1000
number of transactions actually processed: 100000/100000
tps = 4014.075114 (including connections establishing)
tps = 4061.662041 (excluding connections establishing)
Do these numbers sound inline with what I should be seeing? What else
can I do to try to get better performance in the more general sense
(knowing that specifics are tied to real world data and testing). Any
hints for FreeBSD specific tuning would be helpful.