9.4 performance improvements test - Mailing list pgsql-performance

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