intro to benchmarking with pgbench

1. intro to benchmarking with pgbench Melanie Plageman (Microsoft)
2. Load data: pgbench –i –-scale Run pgbench: pgbench [options]
3. it is • Common performance language for hackers • Convenience tool Use it to: • Compare two versions of Postgres • Compare two Postgres configurations • Test the performance of your server or instance
4. it isn’t • Real schema or workload performance analysis tool • Database comparison tool Don’t use it to: • Test the performance of your database schema or specific workload • Test patches without a specific hypothesis
5. performance language for Postgres hackers • other developers can run your benchmarks • common understanding of behavior of built-in scripts and options • reproducible benchmarks
8. convenience tool for developers • Replicate specific performance scenarios with load and run options • Generate and collect output stats
9. generate standard table data with options • --foreign-keys • --index-tablespace=index_tablespace • --partition-method=NAME • --partitions=NUM • --tablespace=tablespace • --unlogged-tables • --fillfactor=fillfactor
10. control pgbench run • --latency-limit=limit • --protocol=querymode • --max-tries=number_of_tries • --rate=rate • --client=clients
11. detailed progress output and stats • --log • --progress=sec • --report-per-command • --aggregate-interval=seconds • --sampling-rate=rate
12. pgbench options and workloads
13. important pgbench options • Load options • --scale • Run options • --protocol • --client • --builtin • --file • --time/--transactions
14. --scale how much data is loaded into default tables • branches = 1x • tellers = 10x • accounts = 100,000x
15. --protocol (query mode) simple: query text sent to the server and parsed on every execution extended: query execution split into parse, bind, and execute but doesn’t save and reuse prepared statements prepared: saves and reuses the prepared statements from the first execution
16. Prepared query mode performs better
17. --client • Number of concurrent Postgres sessions
18. Specify run duration --time --transactions
19. --builtin and --file --builtin • tpcb-like • select-only • simple-update custom script with --file • testing specific scenarios • testing on specific data
20. what do the built-in scripts actually do?
21. TPCB-like 1.BEGIN; 2.UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 3.SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 4.UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 5.UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 6.INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 1.END;
22. Simple UPDATE 1.BEGIN; 2.UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 3.SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 4.UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 5.UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 6.INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 1.END;
23. SELECT-only 1.BEGIN; 2.UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 3.SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 4.UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 5.UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 6.INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 1.END;
24. pgbench variables SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
25. Actual SELECT-only workload commands \set aid random(1, naccounts * :scale) SELECT abalance FROM pgbench_accounts WHERE aid = :aid; naccounts is hard-coded to 100,000
26. pgbench meta commands • \set varname expression • \gset [prefix] \aset [prefix] • \if expression \elif expression \else \endif • \sleep number [ us | ms | s ] • \setshell varname command \ [ argument ... ] • \shell command [ argument ... ] • \start|endpipeline • similar syntax to equivalent psql commands • does not use psql-style SQL interpolation for variables
27. \set varname expression • Sets variable varname to a value calculated from expression • may contain • NULL • Boolean, integer, or double constants • variable references • pgbench built-in operators • pgbench built-in function calls • SQL CASE generic conditional expressions and parentheses
28. \gset [prefix] \aset [prefix] • \gset • stores columns of preceding SQL query (returning one row) into variables named after column names preceded with prefix • valid in psql • \aset • stores columns in all preceding combined SQL queries (separated by \;) into variables named after column names preceded with prefix • not valid in psql
29. \gset Use case table1 (id serial, time default now(), status int) INSERT INTO table1 (status) VALUES (1) RETURNING id AS target \gset UPDATE table1 SET status = 2 WHERE id = :target;
30. \set aid random(1, naccounts * :scale) SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
31. built-in functions for pgbench meta commands • abs(), exp(), ln(), mod(), pow(), pi(), sqrt(), greatest(), least() • double(), int() • hash(), hash_fnv1a(), hash_murmur2() • permute(), random(), random_exponential(), random_gaussian(), random_zipfian() • debug()
32. built-in TPCB-like access distribution \set aid random(1, naccounts * :scale) \set bid random(1, nbranches * :scale) \set tid random(1, ntellers * :scale) \set delta random(-5000, 5000) BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END;
33. TPCB-like variant with Gaussian access distribution \set aid random_gaussian(1, naccounts * :scale, 6) \set bid random_gaussian(1, nbranches * :scale, 6) \set tid random_gaussian(1, ntellers * :scale, 6) \set delta random_gaussian(-5000, 5000) BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END;
34. \set aid random(1, naccounts * :scale) SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
35. automatic variables • client_id • default_seed • random_seed • scale
36. passing and overriding variables • -D
37. automatic variables • client_id • default_seed • random_seed • scale
38. client_id use case for each client psql –c "CREATE TABLE table_$client_id(…)" COPY table_:client_id FROM 'copysource';
39. pgbench output
40. pgbench output • load summary • run summary • --progress • --log
41. summary output Load Summary done in 0.86 s drop tables 0.07 s, create tables 0.11 s, client-side generate 0.25 s, vacuum 0.21 s, primary keys 0.23 s Run Summary transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 4 s number of transactions actually processed: 158 latency average = 25.341 ms latency stddev = 3.607 ms initial connection time = 4.897 ms tps = 39.459091 (without initial connection time)
42. --progess [interval] progress: 1.0 s, 37.9 tps, lat 25.753 ms stddev 4.787 progress: 2.0 s, 41.0 tps, lat 24.850 ms stddev 1.014 progress: 3.0 s, 40.1 tps, lat 24.569 ms stddev 0.900 progress: 4.0 s, 37.9 tps, lat 26.263 ms stddev 5.225
43. viewing performance metrics over time
44. designing a benchmark to test the performance of a feature
45. picking a workload • patch to change the default bulk write ring buffer size • testing this with pgbench built-in TPCB-like does not exercise the code BufferAccessStrategy GetAccessStrategy(…) ... switch (btype) { case BAS_NORMAL: return NULL; case BAS_BULKREAD: ring_size_kb = 256; break; case BAS_BULKWRITE: ring_size_kb = 16 * 1024; break; case BAS_VACUUM: ring_size_kb = 256; break; }
46. evaluating the results • summary output • --progress • --log
47. pgbench –c 1 –M prepared –P 1 –t 50\ –f <(echo “COPY table(…) FROM 'copysource’”) output summary patched pgbench (17devel) scaling factor: 1 query mode: prepared number of clients: 1 number of threads: 1 maximum number of tries: 1 number of transactions per client: 50 number of transactions processed: 50/50 number of failed transactions: 0 latency average = 4316.597 ms latency stddev = 101.553 ms initial connection time = 1.653 ms tps = 0.231663 output summary unpatched pgbench (17devel) scaling factor: 1 query mode: prepared number of clients: 1 number of threads: 1 maximum number of tries: 1 number of transactions per client: 50 number of transactions processed: 50/50 number of failed transactions: 0 latency average = 5015.483 ms latency stddev = 314.739 ms initial connection time = 1.124 ms tps = 0.199382
48. COPY –progress often isn’t useful
49. --log client_id|transaction_no| time |script_no| time_epoch | time_us 0| 1| 4815382| 0| 1696097811| 347611 0| 2| 4878592| 0| 1696097816| 226217 0| 3| 4846731| 0| 1696097821| 72959 0| 4| 4920091| 0| 1696097825| 993061 0| 5| 5669546| 0| 1696097831| 662617 0| 6| 4869671| 0| 1696097836| 532298 0| 7| 4848832| 0| 1696097841| 381138 0| 8| 4746861| 0| 1696097846| 128010 0| 9| 5812907| 0| 1696097851| 940926 0| 10| 4826624| 0| 1696097856| 767560 0| 11| 4829888| 0| 1696097861| 597458 0| 12| 5114546| 0| 1696097866| 712011 0| 13| 5364114| 0| 1696097872| 76135
50. COPY benchmark results from --log patched master total time (ms) 215,829 250,774 average time (ms) 4,316 5,015 median time (ms) 4,302 4,876 minimum time (ms) 4,111 4,628 maximum time (ms) 4,695 5,812 standard dev time (ms) 101 314
51. Plotting COPY --log
52. Postgres and OS Configuration
53. factors affecting benchmark results • OS configuration • OS page size • block device settings • Hardware considerations • age of storage device • CPU power management settings • Filesystem choice and mount options • Postgres compile options • Postgres configuration • Run steps • CPU affinity • initdb before benchmark
54. configure the options that matter to your benchmark • Is workload storage or CPU bound? • Does working set fit in shared buffers? • Is workload read or write heavy or mixed?
55. if you only configure two things…
56. Postgres build • applicable to all benchmarks • non-assert • non-debug • optimization level > 0
57. optimized build flame graphs courtesy of Andres Freund
58. non-optimized build flame graphs courtesy of Andres Freund
59. non-optimized assert debug build flame graphs courtesy of Andres Freund
60. postgres shared buffers
61. OS configuration matters when it controls your workload’s bottleneck
62. read_ahead_kb target readahead = sequential BW * latency
63. Larger read_ahead_kb finishes slightly sooner pgbench, SELECT * FROM large_table 5 GB table 1 client 3 transactions 8 GB shared buffers
64. Read request size is much larger
65. With 1ms added latency via dmsetup delay, run with read_ahead_kb 2048 finishes in 30 seconds
66. Large request size and large read throughput
67. next steps • Multiple workloads to simulate more complex scenarios • Combining metrics from other sources
68. resources • PgCon Ottawa 2023 benchmarking talk • https://speakerdeck.com/melanieplageman/o-performance-for-development • https://www.youtube.com/watch?v=CxyPZHG5beI • pgbench docs • https://www.postgresql.org/docs/devel/pgbench.html