I recently purchased some used hardware for some performance testing of PostgreSQL. I didn't want to interrupt the great work that Mark Wong was doing with the PostgreSQL Performance Lab. The testing I am doing is a bit different than Mark's. Where Mark is testing various filesystem performance via PostgreSQL using DBT2 and FIO and wanted to go up a level.
I am testing using the PostgreSQL tool pgbench which is available in contrib. I am also testing in a basically default environment as a way to see how changing different parameters of the postgresql.conf changes overall performance.
Hardware Configuration
pgbench configuration I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was:
Newisys Quad Opteron 846 32GB of memory (2) MSA 30s (RAID 10, 14 drives each) (2) HP 6402 Controllers (one for each MSA)Operating System Configuration
Ubuntu Hardy LTS x86_64 /array1 , ext3, data=writeback elevator=deadlineOutside of the minor operating system changes the system remained in default. The only postgresql.conf parameter I changed was to set checkpoint_segments to 30.
pgbench configuration I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was:
/array1/jd/pgsql/bin/pgbench -U bench01 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench01& /array1/jd/pgsql/bin/pgbench -U bench02 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench02& /array1/jd/pgsql/bin/pgbench -U bench03 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench03& /array1/jd/pgsql/bin/pgbench -U bench04 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench04&Results
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 92.279931 (including connections establishing) tps = 92.279960 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.674708 (including connections establishing) tps = 91.674739 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.583754 (including connections establishing) tps = 91.583782 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.616330 (including connections establishing) tps = 91.616355 (excluding connections establishing)Each pgbench process executed 4M transactions with an average of ~ 24TPS per client. That is pretty miserable. However remember this is defaults and the defaults will make you checkpoint quite a bit (every 20 seconds or so) with the above stress test. Stay tuned for results as we change specific parameters to see the effect each one has.