postgresql 8.3 tps rate

From: Ibrahim Harrani <ibrahim(dot)harrani(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: postgresql 8.3 tps rate
Date: 2009-01-22 15:47:39
Message-ID: 530068a0901220747s2641fb8eu224afd35ab4cd461@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I am running postgresql 8.3.5 on FreeBSD with Dual core Intel(R)
Xeon(R) CPU 3065 @ 2.33GHz, 2GB RAM and Seagate Technology -
Barracuda 7200.10 SATA 3.0Gb/ (RAID 1).

I made several benchmark test with pgbench, TPS rate is almost 40 +/- 5.
$ pgbench -i pgbench -s 50 -U pgsql

[pgsql(at)$ pgbench -c 200 -t 2 -U pgsql -d pgbench

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 200
number of transactions per client: 2
number of transactions actually processed: 400/400
tps = 39.044088 (including connections establishing)
tps = 41.528795 (excluding connections establishing)

[pgsql(at)$ pgbench -c 100 -t 5 -U pgsql -d pgbench

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 100
number of transactions per client: 5
number of transactions actually processed: 500/500
tps = 30.162271 (including connections establishing)
tps = 30.643256 (excluding connections establishing)

Is this rate is normal or not? What can I do to improve tps and insert
performance?

Here is some changes made in postgresql.conf and sysctl.conf

/etc/sysctl.conf
#1024 MB shmmax
kern.ipc.shmmax=1073741824
#shmall = shmmax / 4096 (page size)
kern.ipc.shmall=262144
kern.ipc.semmsl=512
kern.ipc.semmap=256

postgresql.conf

shared_buffers = 800MB # min 128kB or max_connections*16kB
work_mem = 2MB # min 64kB
maintenance_work_mem = 32MB # min 1MB
max_connections = 600 # (change requires restart)
max_fsm_relations = 2000 # min 100, ~70 bytes each
synchronous_commit = off
wal_buffers = 1024kB # min 32kB
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 is off

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2009-01-22 16:27:31 Re: Slow HashAggregate : How to optimize ?
Previous Message Robert Haas 2009-01-22 15:36:37 Re: caching written values?