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

▶
pgbench work in progress
SMART_READER_LITE
LIVE PREVIEW

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

Jan 16, 2023 33 likes •327 views

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

slide-1
SLIDE 1

PgBench – Work in Progress

Fabien Coelho

MINES ParisTech, PSL Research University

PostgreSQL Session #9, Paris – November 17, 2017

1 / 29

slide-2
SLIDE 2

PgBench – Work in Progress Talk Outline

1 PgBench History Capabilities Caveats 3 Future Needs CommitFest Conclusion 2 Performance Overheads Loading Connection Costs SSL Costs Index Fill Factor

2 / 29

slide-3
SLIDE 3

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Pgbench History

3 / 29

slide-4
SLIDE 4

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench History 2000-

Simple tool based on TPC-B Tatsuo Ishii 2000

  • external. . . then

contrib/ initialization and scale

  • i -s 10

2 benchmarks

  • t 10000 -c 4

Tatsuo Ishii by Oleg Bartunov

Visible and invisible developments 2001- Initializing Scripting Running Reporting

  • Using. . .

Debugging Refactoring Testing

4 / 29

slide-5
SLIDE 5

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench History Initializing

COPY initialization Takahiro Itagaki 2007 FILLFACTOR taux de remplissage Pavan Deolasee 2007 UNLOGGED tables Robert Haas 2011 TABLESPACE

  • n tables or index

– 2011

  • -foreign-key

declarations Jeff Janes 2012

  • I ...

initialization steps Masahiko Sawada 2017

5 / 29

slide-6
SLIDE 6

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench History Scripting

  • N

simple update Tatsuo Ishii 2002

  • f

script file Tomoaki Sato 2005 \set basic arithmetic – 2006 \sleep sleeping Jan Wieck 2007 \shell

  • shelling. . .

Micha¨ el Paquier 2009 gaussian random Mitsumasa Kondo 2014 exponential random Fabien Coelho 2014 expression integer arithmetic Robert Haas 2015 double arithmetic and functions Fabien Coelho 2016 non-ascii variable names – 2017

6 / 29

slide-7
SLIDE 7

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench History Running

  • C

connection Tatsuo Ishii 2001

  • M

query mode Takahiro Itagaki 2008

  • T

run time – 2008

  • j

threading – 2009

  • R

throttling Fabien Coelho 2013

  • L

latency limit – 2014

  • f/b ...

weighted scripts – 2016

7 / 29

slide-8
SLIDE 8

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench History Reporting

  • l

logging Neil Conway 2002

  • r

per statement stats Florian Pflug 2010

  • -sampling-rates

sample stats Tomas Vondra 2012

  • -aggregate-interval

aggregated stats – 2013

  • P

progress Fabien Coelho 2013

  • f ...

per script stats – 2016

8 / 29

slide-9
SLIDE 9

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench Capabilities pgbench

9 / 29

slide-10
SLIDE 10

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench Capabilities pgbench

Initialize a database

  • i -s 1000 ...

create and fill, with scaling

  • ptions: PK, FK, unlogged, fillfactor, tablespace. . .

Run scripts

  • T 1000 -c 32 -j 8 ...

psql-like, 3 builtins or custom, weighted, prepared, throttled parallelism: threads, clients, re-connections. . . Measure and report performance

  • r -l -P 1 ...

tps, latency, timeout; per script, per command. . . detailed, sampled or aggregated; stdout or file

10 / 29

slide-11
SLIDE 11

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Benchmarking Caveats

Beware long enough warm-up, checkpoint and vacuum several times reproducibility representative pedal-to-the-metal?

11 / 29

slide-12
SLIDE 12

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Benchmarking vs Performance Testing

Benchmarking System comparison standard schema and transaction maximum load pedal to the metal report transaction per second tps latency should good enough. . . s Performance Testing Does it work for me? YOUR schema and transaction YOUR load. . . throttling load must be processed latency must match application constraints

12 / 29

slide-13
SLIDE 13

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Deceptive Performance pgbench -j 4 -c 8

Version 9.5.5 throughput 329.4 tps average latency 24.3 ms

100 200 300 400 500 600 1 2 3 4 5 thousand transactions transaction latency in seconds

latency stddev. 79.5 ms Version 9.6.1 throughput 326.4 tps average latency 24.4 ms

100 200 300 400 500 600 1 2 3 4 5 thousand transactions transaction latency in seconds

latency stddev. 20.3 ms

13 / 29

slide-14
SLIDE 14

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Deceptive Performance pgbench -P 1

Version 9.5.5

100 200 300 400 500 500 1000 1500 2000 tps run seconds sorted by tps

Version 9.6.1

100 200 300 400 500 500 1000 1500 2000 tps run seconds sorted by tps

What is happening? Buy Now, Pay Later! transaction surges are absorbed in-memory + WAL then data are written disk checkpoint

14 / 29

slide-15
SLIDE 15

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Performance

15 / 29

slide-16
SLIDE 16

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Tool Overheads pgbench -T 10 -P 1 -f script.sql

Sleep zero 13.4 Mtps, 75 ns \sleep 0 Set a variable 9.5 Mtps, 105 ns \set i 0 Empty command 97,222 tps, 10.3 µs ; Empty SELECT 51,631 tps, 19.4 µs SELECT;

16 / 29

slide-17
SLIDE 17

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Loading data. . . pgbench -i -I ...

Impact of schema

  • n loading time

steps (d)rop (t)able (g)enerate (v)accum (p)rimary and (f)oreign key pgbench -i -s 100 -I ’dtgv’ 18 s pgbench -i -s 100 -I ’dtgvp’ 29 s pgbench -i -s 100 -I ’dtgvpf’ 32 s pgbench -i -s 100 -I ’dtpgvf’ 39 s pgbench -i -s 100 -I ’dtpfgv’ 103 s Impact summary Primary key 50-100% Foreign key 20-300%

17 / 29

slide-18
SLIDE 18

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Connection Costs pgbench -C

pgbench

Client LAN

postgres

Server

Client 8 cores, 16 GB LAN 1 Gbps Server 16 cores, 32 GB, HDD Initialization and Benchmarks

Postgres 9.6.1

pgbench -i -s 100 1.5 GB pgbench -T 2000 -C "host=server sslmode=require" 36.1 tps pgbench -T 2000 -C "host=server sslmode=disable" 56.4 tps pgbench -T 2000 "host=server sslmode=disable" 105.4 tps connection AAA 8.2 ms SSL negociation 10.0 ms transfers and transactions 9.5 ms

18 / 29

slide-19
SLIDE 19

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

SSL or not? pgbench ... "sslmode=..."

SSL Costs time & e negotiation and re-negotiation cryptographic functions certificate ? Benefits Snake Oil! Confidentiality Integrity Authentication

pgbench -j 1 -c 1 -D scale=100 -f ro3.sql -T 30 "host=server ..."

sslmode=require SSL throughput 709.7 tps latency 1.407 ± 0.132 ms sslmode=disable clear throughput 781.6 tps latency 1.277 ± 0.034 ms

19 / 29

slide-20
SLIDE 20

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Select Only Index pgbench -T 10 -P 1 -S

With primary key 17,225 tps initialization pgbench -i -s 10 -I "dtgvp" No primary key 23 tps initialization pgbench -i -s 10 -I "dtgv" With hash index 18,289 tps initialization pgbench -i -s 10 -I "dtgv" plus non unique hash index CREATE INDEX ah ON pgbench accounts USING HASH(aid);

20 / 29

slide-21
SLIDE 21

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Fill Factor pgbench -i -F ...

Update intensive load with MVCC UPDATE = DELETE + INSERT induce about 3 page writes

  • r keep some free space available

Initialization pgbench -i -s 100 -F 95 CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 95); ...

21 / 29

slide-22
SLIDE 22

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Fill Factor pgbench -f update.sql ...

Only UPDATE script

\set naccounts 100000 * :scale \set aid random(1, :naccounts) \set delta random(-5000, 5000) UPDATE pgbench accounts SET abalance = abalance + :delta, filler = NOW()::TEXT WHERE aid = :aid;

22 / 29

slide-23
SLIDE 23

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Future

23 / 29

slide-24
SLIDE 24

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

PgBench Future Where to stop?

24 / 29

slide-25
SLIDE 25

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Needed Capabilities

TPC-B Simple Banking Example version 2.0, June 1994 schema creation and initialization simple but non trivial SQL scripting

SQL commands (SELECT INSERT UPDATE COMMIT. . . ) variables, expressions, (uniform) random numbers, if. . .

constant performance monitoring and reporting stability, steady-state, statistics, errors. . . explicit anti-cheating constraints 1.3.2 application must retrieve the balance Cannot be implemented with PgBench (yet)

25 / 29

slide-26
SLIDE 26

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Needed Capabilities

Missing features in development cset/gset get query results into variables Fabien Coelho boolean comparisons and logical operators – if/endif conditional expression refactoring done, waiting on boolean Other features in development pow another function Ra´ ul Mar´ ın ppoll for handling more clients Doug Rady stats initialization step timing – perm pseudo random permutation . . . error handling. . . ?

26 / 29

slide-27
SLIDE 27

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

TPC-B Real Transaction Profile

\set tbid random(1, :scale) \set tid 10 * (:tbid - 1) + random(1, 10) \if :scale = 1 OR random(0, 99) < 85 -- same branch \set bid :tbid \else -- other branch \set bid 1 + (:tbid + random(1, :scale - 1)) % :scale \endif \set aid :bid * 100000 + random(1, 100000) \set delta random(-999999, 999999) BEGIN; UPDATE pgbench accounts SET abalance = abalance + :delta WHERE aid = :aid RETURNING abalance AS balance \gset 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;

27 / 29

slide-28
SLIDE 28

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

CommitFest Patch Catchup Process

Work in slow Progress commitfest.postgresql.org submit patch get a review and review others get a decision wait for a committer. . . and maybe get one

28 / 29

slide-29
SLIDE 29

PgBench WIP

  • F. Coelho

PgBench

History Capabilities Caveats

Performance

Overheads Loading Connection SSL Index Fill Factor

Future

Needs CommitFest Conclusion

Conclusion

Easy to use and improving tool pgbench write your custom script run it against your data for your load Need test data? datafiller directives on SQL declarations generators for many types and constraints

29 / 29