9.4 performance improvements test

All,

Thought I'd share some pgbench runs I did on two servers, one running
9.3 and one running 9.4.

A small (512MB) pgbench test didn't show much difference between the two:

9.3.5:

transaction type: TPC-B (sort of)
scaling factor: 200
query mode: simple
number of clients: 16
number of threads: 4
duration: 600 s
number of transactions actually processed: 7686217
latency average: 1.249 ms
tps = 12810.135226 (including connections establishing)
tps = 12810.277332 (excluding connections establishing)
statement latencies in milliseconds:
0.001833 \set nbranches 1 * :scale
0.000513 \set ntellers 10 * :scale
0.000447 \set naccounts 100000 * :scale
0.000597 \setrandom aid 1 :naccounts
0.000585 \setrandom bid 1 :nbranches
0.000506 \setrandom tid 1 :ntellers
0.000507 \setrandom delta -5000 5000
0.053684 BEGIN;
0.161115 UPDATE pgbench_accounts SET abalance = abalance
+ :delta WHERE aid = :aid;
0.143763 SELECT abalance FROM pgbench_accounts WHERE aid
= :aid;
0.168801 UPDATE pgbench_tellers SET tbalance = tbalance +
:delta WHERE tid = :tid;
0.183900 UPDATE pgbench_branches SET bbalance = bbalance
+ :delta WHERE bid = :bid;
0.137570 INSERT INTO pgbench_history (tid, bid, aid,
delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.389587 END;

9.4b3:

transaction type: TPC-B (sort of)
scaling factor: 200
query mode: simple
number of clients: 16
number of threads: 4
duration: 600 s
number of transactions actually processed: 7822118
latency average: 1.227 ms
tps = 13036.312006 (including connections establishing)
tps = 13036.498067 (excluding connections establishing)
statement latencies in milliseconds:
0.001817 \set nbranches 1 * :scale
0.000506 \set ntellers 10 * :scale
0.000439 \set naccounts 100000 * :scale
0.000587 \setrandom aid 1 :naccounts
0.000497 \setrandom bid 1 :nbranches
0.000487 \setrandom tid 1 :ntellers
0.000506 \setrandom delta -5000 5000
0.053509 BEGIN;
0.160929 UPDATE pgbench_accounts SET abalance = abalance
+ :delta WHERE aid = :aid;
0.145014 SELECT abalance FROM pgbench_accounts WHERE aid
= :aid;
0.169506 UPDATE pgbench_tellers SET tbalance = tbalance +
:delta WHERE tid = :tid;
0.188648 UPDATE pgbench_branches SET bbalance = bbalance
+ :delta WHERE bid = :bid;
0.141014 INSERT INTO pgbench_history (tid, bid, aid,
delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.358340 END;

However, on a big disk-bound database, 9.4 was 20% better throughput.
The database in this case is around 200GB, for a server with 128GB RAM:

9.3.5:

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10000
query mode: simple
number of clients: 16
number of threads: 4
duration: 3600 s
number of transactions actually processed: 1944320
latency average: 29.625 ms
tps = 539.675140 (including connections establishing)
tps = 539.677426 (excluding connections establishing)

9.4b3:

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10000
query mode: simple
number of clients: 16
number of threads: 4
duration: 3600 s
number of transactions actually processed: 2422502
latency average: 23.777 ms
tps = 672.816239 (including connections establishing)
tps = 672.821433 (excluding connections establishing)

I suspect this is due to the improvements in writing less to WAL. If
so, good work, guys!

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com