Pgbench performance tuning?

From: Greg Stark <stark(at)mit(dot)edu>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Pgbench performance tuning?
Date: 2016-08-04 17:48:31
Message-ID: CAM-w4HPVtdHQkTuddhSAdEMeuJ1-9aAuWogw_8xi0=3zVLgeXQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm trying to run pgbench on a moderately beefy machine (4-core 3.4GHz
with 32G of ram and md mirrored spinning rust drives) at scale 300
with 32 clients with duration of 15min. I'm getting TPS numbers
between 60-150 which seems surprisingly low to me and also to several
people on IRC.

Now pg_test_fsync does seem to indicate that's not an unreasonable
commit rate if there was very little commit grouping going on:

Compare file sync methods using one 8kB write:
open_datasync 100.781 ops/sec 9922 usecs/op
fdatasync 71.088 ops/sec 14067 usecs/op

Compare file sync methods using two 8kB writes:
open_datasync 50.286 ops/sec 19886 usecs/op
fdatasync 80.349 ops/sec 12446 usecs/op

And iostat does seem to indicate the drives are ~ 80% utilized with
high write await times So maybe this is just what the system is
capable of with synchronous_commit?

Is anyone really familiar with pg_bench on similar hardware? Are these
numbers reasonable? Any suggestion for how to run it to get the most
realistic measure of Postgres on this machine?

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 300
query mode: simple
number of clients: 32
number of threads: 4
duration: 900 s
number of transactions actually processed: 109424
latency average: 263.196 ms
tps = 121.464536 (including connections establishing)
tps = 121.464824 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.229 BEGIN;
12.589 UPDATE pgbench_accounts SET abalance = abalance +
:delta WHERE aid = :aid;
0.280 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
1.442 UPDATE pgbench_tellers SET tbalance = tbalance +
:delta WHERE tid = :tid;
12.435 UPDATE pgbench_branches SET bbalance = bbalance +
:delta WHERE bid = :bid;
0.222 INSERT INTO pgbench_history (tid, bid, aid, delta,
mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
237.623 END;

And the iostat for the period the pg_bench is running:

avg-cpu: %user %nice %system %iowait %steal %idle
1,31 0,00 0,43 20,48 0,00 77,78

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s
avgrq-sz avgqu-sz await r_await w_await svctm %util
sdb 0,00 2,47 14,31 181,13 175,96 2897,77
31,45 80,85 413,63 67,55 440,97 4,16 81,35
sda 0,01 2,47 23,77 181,13 292,04 2897,77
31,14 66,72 325,59 51,08 361,61 3,92 80,40
md0 0,00 0,00 0,05 0,00 0,20 0,00
8,00 0,00 0,00 0,00 0,00 0,00 0,00
md1 0,00 0,00 0,00 0,00 0,00 0,00
0,00 0,00 0,00 0,00 0,00 0,00 0,00
md2 0,00 0,00 38,04 182,79 467,79 2895,73
30,46 0,00 0,00 0,00 0,00 0,00 0,00
md3 0,00 0,00 0,00 0,01 0,00 0,04
8,00 0,00 0,00 0,00 0,00 0,00 0,00

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2016-08-04 17:49:43 Re: Heap WARM Tuples - Design Draft
Previous Message David G. Johnston 2016-08-04 17:42:16 psql: Missing option to print current buffer to current output channel (i.e., \qprint)