PgBench Work in Progress Fabien Coelho MINES ParisTech, PSL - PowerPoint PPT Presentation

PgBench – Work in Progress Fabien Coelho MINES ParisTech, PSL Research University PostgreSQL Session #9, Paris – November 17, 2017 1 / 29

PgBench – Work in Progress Talk Outline 1 PgBench History Capabilities Caveats 2 Performance Overheads Loading Connection Costs SSL Costs Index 3 Future Fill Factor Needs CommitFest Conclusion 2 / 29

Pgbench History PgBench WIP F. Coelho PgBench History Capabilities Caveats Performance Overheads Loading Connection SSL Index Fill Factor Future Needs CommitFest Conclusion 3 / 29

PgBench History 2000- PgBench WIP Simple tool based on TPC-B Tatsuo Ishii 2000 F. Coelho PgBench external. . . then contrib/ History Capabilities initialization and scale -i -s 10 Caveats Performance 2 benchmarks -t 10000 -c 4 Overheads Tatsuo Ishii by Oleg Bartunov Loading Connection SSL Visible and invisible developments 2001- Index Fill Factor Initializing Using. . . Future Needs Scripting Debugging CommitFest Conclusion Running Refactoring Reporting Testing 4 / 29

PgBench History Initializing PgBench WIP F. Coelho PgBench History Capabilities initialization Takahiro Itagaki 2007 COPY Caveats Performance FILLFACTOR taux de remplissage Pavan Deolasee 2007 Overheads Loading tables Robert Haas 2011 UNLOGGED Connection SSL TABLESPACE on tables or index – 2011 Index Fill Factor declarations Jeff Janes 2012 --foreign-key Future Needs -I ... initialization steps Masahiko Sawada 2017 CommitFest Conclusion 5 / 29

PgBench History Scripting PgBench WIP F. Coelho simple update Tatsuo Ishii 2002 -N PgBench script file Tomoaki Sato 2005 -f History Capabilities Caveats \ set basic arithmetic – 2006 Performance \ sleep sleeping Jan Wieck 2007 Overheads Loading Connection Micha¨ \ shell shelling. . . el Paquier 2009 SSL Index gaussian random Mitsumasa Kondo 2014 Fill Factor Future exponential random Fabien Coelho 2014 Needs CommitFest expression integer arithmetic Robert Haas 2015 Conclusion double arithmetic and functions Fabien Coelho 2016 non-ascii variable names – 2017 6 / 29

PgBench History Running PgBench WIP F. Coelho PgBench History connection Tatsuo Ishii 2001 -C Capabilities Caveats -M query mode Takahiro Itagaki 2008 Performance Overheads run time – 2008 -T Loading Connection threading – 2009 -j SSL Index Fill Factor -R throttling Fabien Coelho 2013 Future latency limit – 2014 -L Needs CommitFest Conclusion -f/b ... weighted scripts – 2016 7 / 29

PgBench History Reporting PgBench WIP F. Coelho PgBench History Capabilities logging Neil Conway 2002 -l Caveats Performance -r per statement stats Florian Pflug 2010 Overheads Loading sample stats Tomas Vondra 2012 --sampling-rates Connection SSL --aggregate-interval aggregated stats – 2013 Index Fill Factor progress Fabien Coelho 2013 -P Future Needs -f ... per script stats – 2016 CommitFest Conclusion 8 / 29

PgBench Capabilities pgbench PgBench WIP F. Coelho PgBench History Capabilities Caveats Performance Overheads Loading Connection SSL Index Fill Factor Future Needs CommitFest Conclusion 9 / 29

PgBench Capabilities pgbench PgBench WIP Initialize a database -i -s 1000 ... F. Coelho create and fill, with scaling PgBench options: PK, FK, unlogged, fillfactor, tablespace. . . History Capabilities Caveats Performance Run scripts -T 1000 -c 32 -j 8 ... Overheads Loading Connection psql-like, 3 builtins or custom, weighted, prepared, throttled SSL Index Fill Factor parallelism: threads, clients, re-connections. . . Future Needs CommitFest Measure and report performance -r -l -P 1 ... Conclusion tps, latency, timeout; per script, per command. . . detailed, sampled or aggregated; stdout or file 10 / 29

Benchmarking Caveats PgBench WIP Beware F. Coelho long enough warm-up, checkpoint and vacuum PgBench History several times reproducibility Capabilities Caveats representative pedal-to-the-metal? Performance Overheads Loading Connection SSL Index Fill Factor Future Needs CommitFest Conclusion 11 / 29

Benchmarking vs Performance Testing PgBench WIP Benchmarking System comparison F. Coelho standard schema and transaction PgBench History maximum load pedal to the metal Capabilities Caveats report transaction per second tps Performance Overheads latency should good enough. . . s Loading Connection SSL Index Performance Testing Does it work for me? Fill Factor Future Needs YOUR schema and transaction CommitFest Conclusion YOUR load. . . throttling load must be processed latency must match application constraints 12 / 29

Deceptive Performance pgbench -j 4 -c 8 Version 9.5.5 Version 9.6.1 PgBench WIP F. Coelho throughput 329.4 tps throughput 326.4 tps PgBench average latency 24.3 ms average latency 24.4 ms History Capabilities Caveats 600 600 Performance 500 500 Overheads Loading thousand transactions thousand transactions Connection 400 400 SSL Index 300 300 Fill Factor 200 200 Future Needs 100 100 CommitFest Conclusion 0 0 0 1 2 3 4 5 0 1 2 3 4 5 transaction latency in seconds transaction latency in seconds latency stddev. 79.5 ms latency stddev. 20.3 ms 13 / 29

Deceptive Performance pgbench -P 1 Version 9.5.5 Version 9.6.1 PgBench WIP 500 500 F. Coelho 400 400 PgBench History 300 300 Capabilities Caveats tps tps Performance 200 200 Overheads Loading 100 100 Connection SSL Index 0 0 0 500 1000 1500 2000 0 500 1000 1500 2000 Fill Factor run seconds sorted by tps run seconds sorted by tps Future Needs What is happening? Buy Now, Pay Later! CommitFest Conclusion transaction surges are absorbed in-memory + WAL then data are written disk checkpoint 14 / 29

PgBench WIP F. Coelho PgBench History Capabilities Caveats Performance Performance Overheads Loading Connection SSL Index Fill Factor Future Needs CommitFest Conclusion 15 / 29

Tool Overheads pgbench -T 10 -P 1 -f script.sql PgBench WIP Sleep zero 13.4 Mtps, 75 ns F. Coelho \ sleep 0 PgBench History Capabilities Caveats Set a variable 9.5 Mtps, 105 ns Performance Overheads \ set i 0 Loading Connection SSL Index Empty command 97,222 tps, 10.3 µ s Fill Factor Future ; Needs CommitFest Conclusion Empty SELECT 51,631 tps, 19.4 µ s SELECT; 16 / 29

Loading data. . . pgbench -i -I ... PgBench WIP Impact of schema on loading time F. Coelho steps (d)rop (t)able (g)enerate (v)accum (p)rimary and (f)oreign key PgBench History Capabilities pgbench -i -s 100 -I ’dtgv’ 18 s Caveats Performance 29 s pgbench -i -s 100 -I ’dtgvp’ Overheads Loading pgbench -i -s 100 -I ’dtgvpf’ 32 s Connection SSL Index 39 s pgbench -i -s 100 -I ’dtpgvf’ Fill Factor Future pgbench -i -s 100 -I ’dtpfgv’ 103 s Needs CommitFest Conclusion Impact summary Primary key 50-100% Foreign key 20-300% 17 / 29

Connection Costs pgbench -C pgbench postgres PgBench WIP Client 8 cores, 16 GB F. Coelho LAN 1 Gbps LAN PgBench Server 16 cores, 32 GB, HDD History Client Server Capabilities Caveats Initialization and Benchmarks Postgres 9.6.1 Performance Overheads Loading 1.5 GB pgbench -i -s 100 Connection SSL pgbench -T 2000 -C "host=server sslmode=require" 36.1 tps Index Fill Factor 56.4 tps pgbench -T 2000 -C "host=server sslmode=disable" Future Needs pgbench -T 2000 "host=server sslmode=disable" 105.4 tps CommitFest Conclusion connection AAA 8.2 ms SSL negociation 10.0 ms transfers and transactions 9.5 ms 18 / 29

SSL or not? pgbench ... "sslmode=..." PgBench WIP SSL Costs time & e Benefits Snake Oil! F. Coelho PgBench negotiation and re-negotiation Confidentiality History Capabilities cryptographic functions Integrity Caveats Performance certificate ? Authentication Overheads Loading Connection SSL pgbench -j 1 -c 1 -D scale=100 -f ro3.sql -T 30 "host=server ..." Index Fill Factor Future sslmode=require SSL sslmode=disable clear Needs CommitFest Conclusion throughput 781.6 tps throughput 709.7 tps latency 1.277 ± 0.034 ms latency 1.407 ± 0.132 ms 19 / 29

Select Only Index pgbench -T 10 -P 1 -S PgBench WIP With primary key 17,225 tps F. Coelho initialization pgbench -i -s 10 -I "dtgvp" PgBench History Capabilities Caveats No primary key 23 tps Performance Overheads Loading initialization pgbench -i -s 10 -I "dtgv" Connection SSL Index Fill Factor With hash index 18,289 tps Future Needs CommitFest initialization pgbench -i -s 10 -I "dtgv" Conclusion plus non unique hash index CREATE INDEX ah ON pgbench accounts USING HASH(aid); 20 / 29

Fill Factor pgbench -i -F ... PgBench WIP Update intensive load with MVCC F. Coelho PgBench UPDATE = DELETE + INSERT History Capabilities induce about 3 page writes Caveats Performance or keep some free space available Overheads Loading Connection SSL Index Initialization pgbench -i -s 100 -F 95 Fill Factor Future CREATE TABLE pgbench accounts(...) Needs CommitFest WITH (FILLFACTOR = 95); Conclusion ... 21 / 29