measuring and reducing postgres transaction latency › ~coelho › cours › si › pg... ·...
TRANSCRIPT
![Page 1: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/1.jpg)
Measuring and ReducingPostgres Transaction Latency
Fabien Coelho
MINES ParisTech, PSL Research University
pgDay Paris – March 23, 2017
1 / 40
![Page 2: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/2.jpg)
Postgres Latency Talk Outline
2 Performance ComparisonsTwo Connection CostsLatency PitfallsBenchmarking with Rate and LimitThree Storage OptionsTwo Protocol ImpactsFour Query Combination TricksReducting Server DistancePerformance ScalabilityMiscellaneous Settings
1 IntroductionSubjectTypical Web ApplicationTransaction Performance Definitionspgbench
3 ConclusionLatency and Throughput Wrap-UpLessons LearnedContributions to Postgres
2 / 40
![Page 3: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/3.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Subject
Small OLTP OnLine Transaction Processing
CRUD queries ... WHERE pk=?
data fit in shared buffers small, few GBRW, RO pgbench builtins
Focus and Motivation
performance with emphasis on latency interactive web app
3 / 40
![Page 4: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/4.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Subject
Small OLTP OnLine Transaction Processing
CRUD queries ... WHERE pk=?
data fit in shared buffers small, few GBRW, RO pgbench builtins
Focus and Motivation
performance with emphasis on latency interactive web app
3 / 40
![Page 5: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/5.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Subject
Small OLTP OnLine Transaction Processing
CRUD queries ... WHERE pk=?
data fit in shared buffers small, few GBRW, RO pgbench builtins
Focus and Motivation
performance with emphasis on latency interactive web appexperiments & measures do not assume!
3 / 40
![Page 6: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/6.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Subject
Small OLTP OnLine Transaction Processing
CRUD queries ... WHERE pk=?
data fit in shared buffers small, few GBRW, RO pgbench builtins
Focus and Motivation
performance with emphasis on latency interactive web appexperiments & measures do not assume!
latency performance : RW ×63, RO ×219
3 / 40
![Page 7: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/7.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Typical Web Application
3-Tier Architecture
Client user acts on user-agent, sends toServer process request, database operations to
Database stores and retrieves data
User Client Server Database
Database Operations
Connection TCP/IP, SSL & AAARequest-Response cycles transfer, parse, plan, execute, transfer back
4 / 40
![Page 8: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/8.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Typical Web Application
3-Tier Architecture
Client user acts on user-agent, sends toServer process request, database operations to
Database stores and retrieves data
User Client Server Database
Database Operations
Connection TCP/IP, SSL & AAARequest-Response cycles transfer, parse, plan, execute, transfer back
4 / 40
![Page 9: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/9.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Transaction Performance
Definitions time & operations
Throughput operations per time unit tx/susual approach, load measured in tps
Latency time for one operation ms/txmust fit application requirements
Comments
correlated and contradictorymax vs enough and vice-versasensitive to many settings net, soft & hardthroughput bottleneck & latency additivity
5 / 40
![Page 10: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/10.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Transaction Performance
Definitions time & operations
Throughput operations per time unit tx/susual approach, load measured in tps
Latency time for one operation ms/txmust fit application requirements
Comments
correlated and contradictorymax vs enough and vice-versasensitive to many settings net, soft & hardthroughput bottleneck & latency additivity
5 / 40
![Page 11: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/11.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Transaction Performance
Definitions time & operations
Throughput operations per time unit tx/susual approach, load measured in tps
Latency time for one operation ms/txmust fit application requirements
Comments
correlated and contradictorymax vs enough and vice-versasensitive to many settings net, soft & hardthroughput bottleneck & latency additivity
5 / 40
![Page 12: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/12.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Transaction Performance
Definitions time & operations
Throughput operations per time unit tx/susual approach, load measured in tps
Latency time for one operation ms/txmust fit application requirements
Comments
correlated and contradictorymax vs enough and vice-versasensitive to many settings net, soft & hardthroughput bottleneck & latency additivity
5 / 40
![Page 13: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/13.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Transaction Performance
Definitions time & operations
Throughput operations per time unit tx/susual approach, load measured in tps
Latency time for one operation ms/txmust fit application requirements
Comments
correlated and contradictorymax vs enough and vice-versasensitive to many settings net, soft & hardthroughput bottleneck & latency additivity deep voodoo!
5 / 40
![Page 14: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/14.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Postgres Performance Swiss Army Knife pgbench
Available Features
input SQL-like scripts with minimal client-side languageoptions time to run, prepared, reconnections, . . .
parallelism threads, clients, asynchronous callsoutput statistical performance data
Caveats
long enough warm-up, checkpoint and vacuumseveral times reproducibilitypedal-to-the-metal max speed test not representative
6 / 40
![Page 15: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/15.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Postgres Performance Swiss Army Knife pgbench
Available Features
input SQL-like scripts with minimal client-side languageoptions time to run, prepared, reconnections, . . .
parallelism threads, clients, asynchronous callsoutput statistical performance data
Caveats
long enough warm-up, checkpoint and vacuumseveral times reproducibilitypedal-to-the-metal max speed test not representative
6 / 40
![Page 16: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/16.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Default TPC-B-like Transaction pgbench -b tcpb-like
Pattern
3 updates1 insert1 select
TPC-B-like banking transaction
-- random ids and amount\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
-- actual transactionBEGIN;
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;
7 / 40
![Page 17: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/17.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Default TPC-B-like Transaction pgbench -b tcpb-like
Pattern
3 updates1 insert1 select
TPC-B-like banking transaction
-- random ids and amount\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
-- actual transactionBEGIN;
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;
7 / 40
![Page 18: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/18.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Two Connection Costs
8 / 40
![Page 19: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/19.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Connection Costs pgbench -C
pgbench
Client
LAN
postgres
Server
Client 8 cores, 16 GBLAN 1 GbpsServer 16 cores, 32 GB, HDD
Initialization Postgres 9.6.1
pgbench -i -s 100 1.5 GB
9 / 40
![Page 20: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/20.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Connection Costs pgbench -C
pgbench
Client
LAN
postgres
Server
Client 8 cores, 16 GBLAN 1 GbpsServer 16 cores, 32 GB, HDD
Initialization Postgres 9.6.1
pgbench -i -s 100 1.5 GB
9 / 40
![Page 21: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/21.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Connection Costs pgbench -C
pgbench
Client
LAN
postgres
Server
Client 8 cores, 16 GBLAN 1 GbpsServer 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
9 / 40
![Page 22: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/22.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Connection Costs pgbench -C
pgbench
Client
LAN
postgres
Server
Client 8 cores, 16 GBLAN 1 GbpsServer 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 tpspgbench -T 2000 -C "host=server sslmode=disable" 56.4 tps
9 / 40
![Page 23: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/23.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Connection Costs pgbench -C
pgbench
Client
LAN
postgres
Server
Client 8 cores, 16 GBLAN 1 GbpsServer 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 tpspgbench -T 2000 -C "host=server sslmode=disable" 56.4 tpspgbench -T 2000 "host=server sslmode=disable" 105.4 tps
9 / 40
![Page 24: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/24.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Connection Costs pgbench -C
pgbench
Client
LAN
postgres
Server
Client 8 cores, 16 GBLAN 1 GbpsServer 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 tpspgbench -T 2000 -C "host=server sslmode=disable" 56.4 tpspgbench -T 2000 "host=server sslmode=disable" 105.4 tps
connection AAA 8.2 msSSL negociation 10.0 mstransfers and transactions 9.5 ms
9 / 40
![Page 25: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/25.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Latency Pitfalls
10 / 40
![Page 26: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/26.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 pgbench -j 4 -c 8
Version 9.5.5
throughput 329.4 tpsaverage latency 24.3 ms
Version 9.6.1
throughput 326.4 tpsaverage latency 24.4 ms
11 / 40
![Page 27: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/27.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 pgbench -j 4 -c 8
Version 9.5.5
throughput 329.4 tpsaverage latency 24.3 ms
0
100
200
300
400
500
600
0 1 2 3 4 5
thousa
nd t
ransa
ctio
ns
transaction latency in seconds
Version 9.6.1
throughput 326.4 tpsaverage latency 24.4 ms
0
100
200
300
400
500
600
0 1 2 3 4 5
thousa
nd t
ransa
ctio
ns
transaction latency in seconds
11 / 40
![Page 28: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/28.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 pgbench -j 4 -c 8
Version 9.5.5
throughput 329.4 tpsaverage latency 24.3 ms
0
100
200
300
400
500
600
0 1 2 3 4 5
thousa
nd t
ransa
ctio
ns
transaction latency in seconds
latency std. dev. 79.5 ms
Version 9.6.1
throughput 326.4 tpsaverage latency 24.4 ms
0
100
200
300
400
500
600
0 1 2 3 4 5
thousa
nd t
ransa
ctio
ns
transaction latency in seconds
latency std. dev. 20.3 ms11 / 40
![Page 29: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/29.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Instant TPS
Version 9.5.5
0
100
200
300
400
500
0 500 1000 1500 2000
tps
run time in seconds
Version 9.6.1
0
100
200
300
400
500
0 500 1000 1500 2000
tps
run time in seconds
What is happening?
transaction surges are absorbed in-memory + WALthen data are written disk checkpoint
12 / 40
![Page 30: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/30.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Instant TPS
Version 9.5.5
0
100
200
300
400
500
0 500 1000 1500 2000
tps
run seconds sorted by tps
Version 9.6.1
0
100
200
300
400
500
0 500 1000 1500 2000
tps
run seconds sorted by tps
What is happening?
transaction surges are absorbed in-memory + WALthen data are written disk checkpoint
12 / 40
![Page 31: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/31.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Instant TPS
Version 9.5.5
0
100
200
300
400
500
0 500 1000 1500 2000
tps
run seconds sorted by tps
Version 9.6.1
0
100
200
300
400
500
0 500 1000 1500 2000
tps
run seconds sorted by tps
What is happening? Buy Now, Pay Later!
transaction surges are absorbed in-memory + WALthen data are written disk checkpoint
12 / 40
![Page 32: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/32.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Checkpointing
Postgres 9.5 Checkpoint
data writes spread over some time random I/OOS choose when to actually write 30s delay on Linuxuntil fsync is called. . .
Postgres 9.6 Checkpoint
13 / 40
![Page 33: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/33.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Checkpointing
Postgres 9.5 Checkpoint
data writes spread over some time random I/OOS choose when to actually write 30s delay on Linuxuntil fsync is called. . .
Postgres 9.6 Checkpoint
13 / 40
![Page 34: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/34.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Checkpointing
Postgres 9.5 Checkpoint
data writes spread over some time random I/OOS choose when to actually write 30s delay on Linuxuntil fsync is called. . . I/O storm – on low-end HDD
Postgres 9.6 Checkpoint
13 / 40
![Page 35: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/35.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Checkpointing
Postgres 9.5 Checkpoint
data writes spread over some time random I/OOS choose when to actually write 30s delay on Linuxuntil fsync is called. . . I/O storm – on low-end HDD
Postgres 9.6 Checkpoint
sorted data writes spread over some time sequential I/Oflush instructions sent regularly (256 kB) checkpoint flush after
13 / 40
![Page 36: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/36.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Latency Comparison – 9.5 vs 9.6 Checkpointing
Postgres 9.5 Checkpoint
data writes spread over some time random I/OOS choose when to actually write 30s delay on Linuxuntil fsync is called. . . I/O storm – on low-end HDD
Postgres 9.6 Checkpoint
sorted data writes spread over some time sequential I/Oflush instructions sent regularly (256 kB) checkpoint flush after
when fsync is called ok!
13 / 40
![Page 37: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/37.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Benchmarking with Rate and Limit
14 / 40
![Page 38: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/38.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skippedlatency
Pg 9.6 sorted checkpoint
slow & skippedlatency
Pg 9.6 sorted & flushed checkpoint
slow & skippedlatency
15 / 40
![Page 39: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/39.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skippedlatency 0
50
100
150
0 500 1000 1500 2000 2500
tps
run time in seconds
Pg 9.6 sorted checkpoint
slow & skippedlatency
Pg 9.6 sorted & flushed checkpoint
slow & skippedlatency
15 / 40
![Page 40: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/40.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skipped 24.0%latency 15.6 ± 158.3 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted checkpoint
slow & skippedlatency
Pg 9.6 sorted & flushed checkpoint
slow & skippedlatency
15 / 40
![Page 41: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/41.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skipped 24.0%latency 15.6 ± 158.3 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted checkpoint
slow & skippedlatency 0
50
100
150
0 500 1000 1500 2000 2500
tps
run time in seconds
Pg 9.6 sorted & flushed checkpoint
slow & skippedlatency
15 / 40
![Page 42: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/42.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skipped 24.0%latency 15.6 ± 158.3 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted checkpoint
slow & skipped 2.7%latency 3.6 ± 24.6 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted & flushed checkpoint
slow & skippedlatency
15 / 40
![Page 43: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/43.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skipped 24.0%latency 15.6 ± 158.3 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted checkpoint
slow & skipped 2.7%latency 3.6 ± 24.6 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted & flushed checkpoint
slow & skippedlatency 0
50
100
150
0 500 1000 1500 2000 2500
tps
run time in seconds 15 / 40
![Page 44: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/44.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N
Pg 9.5 basic checkpoint
slow & skipped 24.0%latency 15.6 ± 158.3 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted checkpoint
slow & skipped 2.7%latency 3.6 ± 24.6 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps
Pg 9.6 sorted & flushed checkpoint
slow & skipped 0.5%latency 2.6 ± 13.8 ms 0
50
100
150
0 500 1000 1500 2000 2500
tps
run seconds sorted by tps 15 / 40
![Page 45: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/45.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Three Storage Options
16 / 40
![Page 46: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/46.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
FILLFACTOR Storage Parameter
CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 100);
FILLFACTOR Usage
MVCC: UPDATE = DELETE + INSERT up to 3 pages changessome free space available in page 1 inside page changebut more pages/costs for other operations trade-off
FILLFACTOR = 100
throughput 406.9 tpslatency 19.7 ± 12.3 ms
FILLFACTOR = 95
throughputlatency
17 / 40
![Page 47: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/47.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
FILLFACTOR Storage Parameter
CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 100);
FILLFACTOR Usage
MVCC: UPDATE = DELETE + INSERT up to 3 pages changessome free space available in page 1 inside page changebut more pages/costs for other operations trade-off
FILLFACTOR = 100
throughput 406.9 tpslatency 19.7 ± 12.3 ms
FILLFACTOR = 95
throughputlatency
17 / 40
![Page 48: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/48.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
FILLFACTOR Storage Parameter
CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 100);
FILLFACTOR Usage
MVCC: UPDATE = DELETE + INSERT up to 3 pages changessome free space available in page 1 inside page changebut more pages/costs for other operations trade-off
FILLFACTOR = 100
throughput 406.9 tpslatency 19.7 ± 12.3 ms
FILLFACTOR = 95
throughput 416.8 tpslatency 19.2 ± 8.3 ms
17 / 40
![Page 49: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/49.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Hardware HDD vs SSD
Hard Disk Drive
mechanicsfast sequential I/Oslow random I/O
vs
Solid State Disk
electronicsfast sequential I/Ofast random I/O
pgbench -j 4 -c 8 -T 2500 -M prepared ...
Postgres 9.6
HDD 406.9 tps 19.7 ± 12.3 ms
SSD
18 / 40
![Page 50: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/50.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Hardware HDD vs SSD
Hard Disk Drive
mechanicsfast sequential I/Oslow random I/O
vs
Solid State Disk
electronicsfast sequential I/Ofast random I/O
pgbench -j 4 -c 8 -T 2500 -M prepared ...
Postgres 9.6
HDD 406.9 tps 19.7 ± 12.3 ms
SSD 0
100
200
300
400
500
600
0 500 1000 1500 2000 2500
tps
seconds
HDD
18 / 40
![Page 51: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/51.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Hardware HDD vs SSD
Hard Disk Drive
mechanicsfast sequential I/Oslow random I/O
vs
Solid State Disk
electronicsfast sequential I/Ofast random I/O
pgbench -j 4 -c 8 -T 2500 -M prepared ...
Postgres 9.6
HDD 406.9 tps 19.7 ± 12.3 msSSD 4,764.9 tps 1.7 ± 2.4 ms 0
1000
2000
3000
4000
5000
6000
0 500 1000 1500 2000 2500
tps
seconds
SSDHDD
18 / 40
![Page 52: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/52.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Hardware HDD vs SSD
Hard Disk Drive
mechanicsfast sequential I/Oslow random I/O
vs
Solid State Disk
electronicsfast sequential I/Ofast random I/O
pgbench -j 4 -c 8 -T 2500 -M prepared ...
Postgres 9.6
HDD 406.9 tps 19.7 ± 12.3 msSSD 4,764.9 tps 1.7 ± 2.4 ms
checkpoint full page write effect 0
1000
2000
3000
4000
5000
6000
0 500 1000 1500 2000 2500
tps
seconds
SSDHDD
18 / 40
![Page 53: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/53.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
UNLOGGED TABLE Can you loose your data?
CREATE UNLOGGED TABLE pgbench accounts(...);
Standard ACID
throughput 406.9 tpslatency 19.7 ± 12.3 ms
UNLOGGED
throughputlatency
...
19 / 40
![Page 54: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/54.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
UNLOGGED TABLE Can you loose your data?
CREATE UNLOGGED TABLE pgbench accounts(...);
Standard ACID
throughput 406.9 tpslatency 19.7 ± 12.3 ms
UNLOGGED
throughputlatency
...
19 / 40
![Page 55: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/55.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
UNLOGGED TABLE Can you loose your data?
CREATE UNLOGGED TABLE pgbench accounts(...);
Standard ACID
throughput 406.9 tpslatency 19.7 ± 12.3 ms
UNLOGGED good luck!
throughput 5,310.7 tpslatency 1.5 ± 0.3 ms
...
19 / 40
![Page 56: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/56.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
UNLOGGED TABLE Can you loose your data?
CREATE UNLOGGED TABLE pgbench accounts(...);
Standard ACID
throughput 406.9 tpslatency 19.7 ± 12.3 ms
UNLOGGED good luck!
throughput 5,310.7 tpslatency 1.5 ± 0.3 ms
NO!19 / 40
![Page 57: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/57.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Two Protocol Impacts
20 / 40
![Page 58: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/58.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Read-Only In-Cache Test ro3.sql
\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
BEGIN;
SELECT abalance FROM pgbench accounts WHERE aid=:aid;
SELECT tbalance FROM pgbench tellers WHERE tid=:tid;
SELECT bbalance FROM pgbench branches WHERE bid=:bid;
COMMIT;
Operations Queries on 3 tables
1 transfers network protocol2 parse query syntax analysis3 plan query optimization4 execute query cheap if in cache
21 / 40
![Page 59: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/59.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Read-Only In-Cache Test ro3.sql
\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
BEGIN;
SELECT abalance FROM pgbench accounts WHERE aid=:aid;
SELECT tbalance FROM pgbench tellers WHERE tid=:tid;
SELECT bbalance FROM pgbench branches WHERE bid=:bid;
COMMIT;
Operations Queries on 3 tables
1 transfers network protocol2 parse query syntax analysis3 plan query optimization4 execute query cheap if in cache
21 / 40
![Page 60: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/60.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol SSL or not
SSL Costs time & e
negotiation and re-negotiationcryptographic functionscertificate
Benefits
ConfidentialityIntegrityAuthentication
sslmode=require SSL
throughput 709.7 tpslatency 1.407 ± 0.132 ms
sslmode=disable clear
throughputlatency
22 / 40
![Page 61: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/61.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol SSL or not
SSL Costs time & e
negotiation and re-negotiationcryptographic functionscertificate
Benefits
ConfidentialityIntegrityAuthentication
sslmode=require SSL
throughput 709.7 tpslatency 1.407 ± 0.132 ms
sslmode=disable clear
throughputlatency
22 / 40
![Page 62: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/62.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol SSL or not
SSL Costs time & e
negotiation and re-negotiationcryptographic functionscertificate?
Benefits Snake Oil!
ConfidentialityIntegrityAuthentication
sslmode=require SSL
throughput 709.7 tpslatency 1.407 ± 0.132 ms
sslmode=disable clear
throughputlatency
22 / 40
![Page 63: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/63.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol SSL or not
SSL Costs time & e
negotiation and re-negotiationcryptographic functionscertificate
Benefits Snake Oil!
ConfidentialityIntegrityAuthentication
pgbench -j 1 -c 1 -D scale=100 -f ro3.sql -T 30 "host=server ..."
sslmode=require SSL
throughput 709.7 tpslatency 1.407 ± 0.132 ms
sslmode=disable clear
throughputlatency
22 / 40
![Page 64: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/64.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol SSL or not
SSL Costs time & e
negotiation and re-negotiationcryptographic functionscertificate
Benefits Snake Oil!
ConfidentialityIntegrityAuthentication
pgbench -j 1 -c 1 -D scale=100 -f ro3.sql -T 30 "host=server ..."
sslmode=require SSL
throughput 709.7 tpslatency 1.407 ± 0.132 ms
sslmode=disable clear
throughput 781.6 tpslatency 1.277 ± 0.034 ms
22 / 40
![Page 65: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/65.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol Simple vs Prepared
-- prepare once in sessionPREPARE Abal(INT) AS
SELECT abalance
FROM pgbench accounts
WHERE aid=$1;
-- execute multiple times...EXECUTE Abal(1);
EXECUTE Abal(5432);
EXECUTE Abal(18);
Prepare
temporary one-cmd functionfactor out parse costkeep plan and executepgbench -M prepared . . .
ro3.sql simple
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3.sql prepared
throughputlatency
23 / 40
![Page 66: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/66.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol Simple vs Prepared
-- prepare once in sessionPREPARE Abal(INT) AS
SELECT abalance
FROM pgbench accounts
WHERE aid=$1;
-- execute multiple times...EXECUTE Abal(1);
EXECUTE Abal(5432);
EXECUTE Abal(18);
Prepare
temporary one-cmd functionfactor out parse costkeep plan and executepgbench -M prepared . . .
ro3.sql simple
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3.sql prepared
throughputlatency
23 / 40
![Page 67: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/67.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Protocol Simple vs Prepared
-- prepare once in sessionPREPARE Abal(INT) AS
SELECT abalance
FROM pgbench accounts
WHERE aid=$1;
-- execute multiple times...EXECUTE Abal(1);
EXECUTE Abal(5432);
EXECUTE Abal(18);
Prepare
temporary one-cmd functionfactor out parse costkeep plan and executepgbench -M prepared . . .
ro3.sql simple
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3.sql prepared
throughput 860.0 tpslatency 1.161 ± 0.082 ms
23 / 40
![Page 68: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/68.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Four Query Combination Tricks
24 / 40
![Page 69: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/69.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Query Combination UPDATE & SELECT
-- update tableUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid;
-- get updated dataSELECT abalance
FROM pgbench accounts
WHERE aid = :aid;
-- combinedUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid
RETURNING abalance;
UPDATE RETURNING Option
return updated rowsone parse, plan, execute
Standard
throughput 406.9 tpslatency 19.7 ± 12.3 ms
Combined Update
throughputlatency
25 / 40
![Page 70: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/70.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Query Combination UPDATE & SELECT
-- update tableUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid;
-- get updated dataSELECT abalance
FROM pgbench accounts
WHERE aid = :aid;
-- combinedUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid
RETURNING abalance;
UPDATE RETURNING Option
return updated rowsone parse, plan, execute
Standard
throughput 406.9 tpslatency 19.7 ± 12.3 ms
Combined Update
throughputlatency
25 / 40
![Page 71: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/71.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Query Combination UPDATE & SELECT
-- update tableUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid;
-- get updated dataSELECT abalance
FROM pgbench accounts
WHERE aid = :aid;
-- combinedUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid
RETURNING abalance;
UPDATE RETURNING Option
return updated rowsone parse, plan, execute
Standard
throughput 406.9 tpslatency 19.7 ± 12.3 ms
Combined Update
throughputlatency
25 / 40
![Page 72: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/72.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Query Combination UPDATE & SELECT
-- update tableUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid;
-- get updated dataSELECT abalance
FROM pgbench accounts
WHERE aid = :aid;
-- combinedUPDATE pgbench accounts
SET abalance = abalance + :delta
WHERE aid = :aid
RETURNING abalance;
UPDATE RETURNING Option
return updated rowsone parse, plan, execute
Standard
throughput 406.9 tpslatency 19.7 ± 12.3 ms
Combined Update
throughput 408.2 tpslatency 19.6 ± 8.7 ms
25 / 40
![Page 73: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/73.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-combined SQL Queries
-- ”ro3c.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
BEGIN \;SELECT abalance FROM
pgbench accounts WHERE aid=:aid \;SELECT tbalance FROM
pgbench tellers WHERE tid=:tid \;SELECT bbalance FROM
pgbench branches WHERE bid=:bid \;COMMIT;
Combine with \;
embedded semi-colon ;
request with multiple queriesresponse with list of resultsavoid request-response loop
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3c.sql combined
throughputlatency
26 / 40
![Page 74: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/74.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-combined SQL Queries
-- ”ro3c.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
BEGIN \;SELECT abalance FROM
pgbench accounts WHERE aid=:aid \;SELECT tbalance FROM
pgbench tellers WHERE tid=:tid \;SELECT bbalance FROM
pgbench branches WHERE bid=:bid \;COMMIT;
Combine with \;
embedded semi-colon ;
request with multiple queriesresponse with list of resultsavoid request-response loop
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3c.sql combined
throughputlatency
26 / 40
![Page 75: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/75.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-combined SQL Queries
-- ”ro3c.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
BEGIN \;SELECT abalance FROM
pgbench accounts WHERE aid=:aid \;SELECT tbalance FROM
pgbench tellers WHERE tid=:tid \;SELECT bbalance FROM
pgbench branches WHERE bid=:bid \;COMMIT;
Combine with \;
embedded semi-colon ;
request with multiple queriesresponse with list of resultsavoid request-response loop
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3c.sql combined
throughput 1,311.5 tpslatency 0.748 ± 0.132 ms
26 / 40
![Page 76: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/76.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side SQL queries
CREATE TYPE Balances
AS (abal INT, tbal INT, bbal INT);
CREATE FUNCTION getBalSQL(INT, INT, INT)
RETURNS Balances AS $$
SELECT
(SELECT abalance
FROM pgbench accounts WHERE aid=$1),
(SELECT tbalance
FROM pgbench tellers WHERE tid=$2),
(SELECT bbalance
FROM pgbench branches WHERE bid=$3)
$$ LANGUAGE SQL;
-- ”ro3sf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalSQL(:aid, :tid, :bid);
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3sf.sql SQL call
throughputlatency
27 / 40
![Page 77: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/77.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side SQL queries
CREATE TYPE Balances
AS (abal INT, tbal INT, bbal INT);
CREATE FUNCTION getBalSQL(INT, INT, INT)
RETURNS Balances AS $$
SELECT
(SELECT abalance
FROM pgbench accounts WHERE aid=$1),
(SELECT tbalance
FROM pgbench tellers WHERE tid=$2),
(SELECT bbalance
FROM pgbench branches WHERE bid=$3)
$$ LANGUAGE SQL;
-- ”ro3sf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalSQL(:aid, :tid, :bid);
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3sf.sql SQL call
throughputlatency
27 / 40
![Page 78: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/78.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side SQL queries
CREATE TYPE Balances
AS (abal INT, tbal INT, bbal INT);
CREATE FUNCTION getBalSQL(INT, INT, INT)
RETURNS Balances AS $$
SELECT
(SELECT abalance
FROM pgbench accounts WHERE aid=$1),
(SELECT tbalance
FROM pgbench tellers WHERE tid=$2),
(SELECT bbalance
FROM pgbench branches WHERE bid=$3)
$$ LANGUAGE SQL;
-- ”ro3sf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalSQL(:aid, :tid, :bid);
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3sf.sql SQL call
throughput 1,395.4 tpslatency 0.712 ± 0.075 ms
27 / 40
![Page 79: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/79.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side PL/pgSQL queries
CREATE FUNCTION
getBalPL(a INT, t INT, b INT)
RETURNS Balances AS $$
DECLARE
abal INT; tbal INT; bbal INT;
BEGIN
SELECT abalance INTO abal
FROM pgbench accounts WHERE aid=a;
SELECT tbalance INTO tbal
FROM pgbench tellers WHERE tid=t;
SELECT bbalance INTO bbal
FROM pgbench branches WHERE bid=b;
RETURN (abal, tbal, bbal)::Balances;
END;
$$ LANGUAGE PLpgSQL;
-- ”ro3pf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalPL(:aid, :tid, :bid);
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3pf.sql PL/pgSQL call
throughputlatency
28 / 40
![Page 80: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/80.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side PL/pgSQL queries
CREATE FUNCTION
getBalPL(a INT, t INT, b INT)
RETURNS Balances AS $$
DECLARE
abal INT; tbal INT; bbal INT;
BEGIN
SELECT abalance INTO abal
FROM pgbench accounts WHERE aid=a;
SELECT tbalance INTO tbal
FROM pgbench tellers WHERE tid=t;
SELECT bbalance INTO bbal
FROM pgbench branches WHERE bid=b;
RETURN (abal, tbal, bbal)::Balances;
END;
$$ LANGUAGE PLpgSQL;
-- ”ro3pf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalPL(:aid, :tid, :bid);
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3pf.sql PL/pgSQL call
throughputlatency
28 / 40
![Page 81: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/81.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side PL/pgSQL queries
CREATE FUNCTION
getBalPL(a INT, t INT, b INT)
RETURNS Balances AS $$
DECLARE
abal INT; tbal INT; bbal INT;
BEGIN
SELECT abalance INTO abal
FROM pgbench accounts WHERE aid=a;
SELECT tbalance INTO tbal
FROM pgbench tellers WHERE tid=t;
SELECT bbalance INTO bbal
FROM pgbench branches WHERE bid=b;
RETURN (abal, tbal, bbal)::Balances;
END;
$$ LANGUAGE PLpgSQL;
-- ”ro3pf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalPL(:aid, :tid, :bid);
?
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3pf.sql PL/pgSQL call
throughput 2,485.5 tpslatency 0.400 ± 0.055 ms
28 / 40
![Page 82: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/82.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Server-Side PL/pgSQL queries
CREATE FUNCTION
getBalPL(a INT, t INT, b INT)
RETURNS Balances AS $$
DECLARE
abal INT; tbal INT; bbal INT;
BEGIN
SELECT abalance INTO abal
FROM pgbench accounts WHERE aid=a;
SELECT tbalance INTO tbal
FROM pgbench tellers WHERE tid=t;
SELECT bbalance INTO bbal
FROM pgbench branches WHERE bid=b;
RETURN (abal, tbal, bbal)::Balances;
END;
$$ LANGUAGE PLpgSQL;
-- ”ro3pf.sql” pgbench script\set aid random(1, 100000 * :scale)
\set tid random(1, 10 * :scale)
\set bid random(1, :scale)
SELECT getBalPL(:aid, :tid, :bid);
PL/pgSQL caches plans!
ro3.sql standard
throughput 709.7 tpslatency 1.407 ± 0.132 ms
ro3pf.sql PL/pgSQL call
throughput 2,485.5 tpslatency 0.400 ± 0.055 ms
28 / 40
![Page 83: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/83.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Reducting Server Distance
29 / 40
![Page 84: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/84.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-Server Distance
Interconnection
LAN Local Area Network EthernetLO loopback interface localhost
IPC Inter-Process Communication Unix domain socket
TPC-B-Like on HDD
LAN 100.3 tps 9.9 msLO
IPC
Read-Only 3
LAN 709.7 tps 1.4 msLO
IPC
30 / 40
![Page 85: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/85.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-Server Distance
Interconnection
LAN Local Area Network EthernetLO loopback interface localhost
IPC Inter-Process Communication Unix domain socket
TPC-B-Like on HDD
LAN 100.3 tps 9.9 msLO 114.5 tps 8.7 ms
IPC 113.5 tps 8.8 ms
Read-Only 3
LAN 709.7 tps 1.4 msLO
IPC
30 / 40
![Page 86: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/86.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-Server Distance
Interconnection
LAN Local Area Network EthernetLO loopback interface localhost
IPC Inter-Process Communication Unix domain socket
TPC-B-Like on SSD
LAN 403.8 tps 2.4 msLO 1,133.3 tps 0.9 ms
IPC 1,243.1 tps 0.8 ms
Read-Only 3
LAN 709.7 tps 1.4 msLO
IPC
30 / 40
![Page 87: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/87.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Client-Server Distance
Interconnection
LAN Local Area Network EthernetLO loopback interface localhost
IPC Inter-Process Communication Unix domain socket
TPC-B-Like on SSD
LAN 403.8 tps 2.4 msLO 1,133.3 tps 0.9 ms
IPC 1,243.1 tps 0.8 ms
Read-Only 3
LAN 709.7 tps 1.4 msLO 2,515.3 tps 0.4 ms
IPC 3,607.6 tps 0.3 ms
30 / 40
![Page 88: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/88.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Performance Scalability
31 / 40
![Page 89: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/89.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Base
Read-Only 3 – remote SSL simple queries
Best Throughput
Best Latency
Compromise
32 / 40
![Page 90: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/90.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Base
Read-Only 3 – remote SSL simple queries
Best Throughput
37,639 tps 4.103 ms 156/4
Best Latency
Compromise
0
10
20
30
40
0 20 40 60 80 100 120 140 160 180
thousa
nd
tp
s
number of clients
32 / 40
![Page 91: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/91.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Base
Read-Only 3 – remote SSL simple queries
Best Throughput
37,639 tps 4.103 ms 156/4
Best Latency
5,748 tps 1.042 ms 6/1
Compromise
0
10
20
30
40
0 20 40 60 80 100 120 140 160 180
thousa
nd
tp
s
number of clients
0 1 2 3 4 5 6
0 20 40 60 80 100 120 140 160 180la
tency
ms
number of clients
32 / 40
![Page 92: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/92.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Base
Read-Only 3 – remote SSL simple queries
Best Throughput
37,639 tps 4.103 ms 156/4
Best Latency
5,748 tps 1.042 ms 6/1
Compromise
31,494 tps 1.837 ms 58/4
0
10
20
30
40
0 20 40 60 80 100 120 140 160 180
thousa
nd
tp
s
number of clients
0 1 2 3 4 5 6
0 20 40 60 80 100 120 140 160 180la
tency
ms
number of clients
32 / 40
![Page 93: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/93.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Best
Read-Only 3 – remote noSSL prepared PL call
Best Throughput
Best Latency
Compromise
33 / 40
![Page 94: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/94.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Best
Read-Only 3 – remote noSSL prepared PL call
Best Throughput
181,503 tps 0.766 ms 140/4
Best Latency
Compromise
0
50
100
150
200
0 20 40 60 80 100 120 140 160 180
thousa
nd
tp
s
number of clients
BestBase
33 / 40
![Page 95: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/95.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Best
Read-Only 3 – remote noSSL prepared PL call
Best Throughput
181,503 tps 0.766 ms 140/4
Best Latency
39,232 tps 0.254 ms 10/2
Compromise
0
50
100
150
200
0 20 40 60 80 100 120 140 160 180
thousa
nd
tp
s
number of clients
BestBase
0 0.2 0.4 0.6 0.8
1 1.2
0 20 40 60 80 100 120 140 160 180
late
ncy
ms
number of clients
BestBase
33 / 40
![Page 96: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/96.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
# Clients Scalability Best
Read-Only 3 – remote noSSL prepared PL call
Best Throughput
181,503 tps 0.766 ms 140/4
Best Latency
39,232 tps 0.254 ms 10/2
Compromise
156,945 tps 0.381 ms 60/4
0
50
100
150
200
0 20 40 60 80 100 120 140 160 180
thousa
nd
tp
s
number of clients
BestBase
0 0.2 0.4 0.6 0.8
1 1.2
0 20 40 60 80 100 120 140 160 180
late
ncy
ms
number of clients
BestBase
33 / 40
![Page 97: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/97.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Performance Comparisons
Miscellaneous Settings
34 / 40
![Page 98: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/98.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Miscellaneous Settings App & Postgres
Application framework?
connection persistencecache Memcached Redis
Postgres configuration change defaults
disk block size random page cost
memory shared buffers effective cache size huge pages
checkpoint timeout completion target flush after
wal max wal size
35 / 40
![Page 99: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/99.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Miscellaneous Settings App & Postgres
Application framework?
connection persistencecache Memcached Redis
Postgres configuration change defaults
disk block size random page cost
memory shared buffers effective cache size huge pages
checkpoint timeout completion target flush after
wal max wal size
35 / 40
![Page 100: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/100.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Miscellaneous Settings OS & Hardware
OS tweak and choose
FS XFS ext4 Btrfs ZFS, mount optionsIO io scheduler, queue length, write delay, dirty bytes. . .
others NUMA, . . .
Hardware expensive is (probably) better
diskS tables wal logs, HDD-with-cache, SSDtweaking read ahead, write flush
RAID with large caches, BBU
36 / 40
![Page 101: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/101.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Miscellaneous Settings OS & Hardware
OS tweak and choose
FS XFS ext4 Btrfs ZFS, mount optionsIO io scheduler, queue length, write delay, dirty bytes. . .
others NUMA, . . .
Hardware expensive is (probably) better
diskS tables wal logs, HDD-with-cache, SSDtweaking read ahead, write flush
RAID with large caches, BBU
36 / 40
![Page 102: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/102.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Conclusion
37 / 40
![Page 103: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/103.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
38 / 40
![Page 104: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/104.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
×3 to ×14
38 / 40
![Page 105: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/105.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
×4 to =
38 / 40
![Page 106: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/106.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
+15% to +18%
38 / 40
![Page 107: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/107.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
+2% to +28%
38 / 40
![Page 108: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/108.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
×3 to ×4
38 / 40
![Page 109: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/109.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
×1.7 to ×3.7
38 / 40
![Page 110: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/110.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Wrap-up pgbench -j 1 -c 1 ...
TPC-B-like Read-Only 3tps ms tps ms
HDD -C SSL 36.1 27.7 52.7 18.96HDD -C noSSL 56.4 17.7 110.1 9.08HDD SSL 105.4 9.5 709.7 1.41SSD SSL 403.8 2.47 695.1 1.44SSD noSSL 465.4 2.15 820.1 1.22. . . + prepared 548.1 1.82 974.0 1.02– returning 529.4 1.89 – –. . . + prepared 681.2 1.47 – –– combined 857.8 1.15 1,536.4 0.64– SQL func 940.3 1.06 1,818.1 0.55. . . + prepared 957.9 1.04 2,144.7 0.46– PL func 1,279.4 0.78 2,778.0 0.36. . . + prepared 1,323.2 0.75 3,040.4 0.33localhost 1,907.6 0.52 10,006.8 0.10socket 2,273.1 0.44 11,545.5 0.09
connectionHDD to SSDSSL to nonesimple to preparedcombinations. . .remote to local
× 63 to × 219
and scaling effects
38 / 40
![Page 111: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/111.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Lessons
Things to Bring Home in-memory OLTP load
NoTPS not only TPS latency matters!latency-throughput compromise
Performance experiment and measure do not assume!pgbench is improving. . .
Postgres version 9.6!sorted and flushed checkpoints
High costs network, parse & planRW load ACID SSD� HDDRO load pg as a cache manager SSD = HDD
39 / 40
![Page 112: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/112.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Lessons
Things to Bring Home in-memory OLTP load
NoTPS not only TPS latency matters!latency-throughput compromise
Performance experiment and measure do not assume!pgbench is improving. . .
Postgres version 9.6!sorted and flushed checkpoints
High costs network, parse & planRW load ACID SSD� HDDRO load pg as a cache manager SSD = HDD
39 / 40
![Page 113: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/113.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Lessons
Things to Bring Home in-memory OLTP load
NoTPS not only TPS latency matters!latency-throughput compromise
Performance experiment and measure do not assume!pgbench is improving. . .
Postgres version 9.6!sorted and flushed checkpoints
High costs network, parse & planRW load ACID SSD� HDDRO load pg as a cache manager SSD = HDD
39 / 40
![Page 114: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/114.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Lessons
Things to Bring Home in-memory OLTP load
NoTPS not only TPS latency matters!latency-throughput compromise
Performance experiment and measure do not assume!pgbench is improving. . .
Postgres version 9.6!sorted and flushed checkpoints
High costs network, parse & planRW load ACID SSD� HDDRO load pg as a cache manager SSD = HDD
39 / 40
![Page 115: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/115.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Lessons
Things to Bring Home in-memory OLTP load
NoTPS not only TPS latency matters!latency-throughput compromise
Performance experiment and measure do not assume!pgbench is improving. . .
Postgres version 9.6!sorted and flushed checkpoints
High costs network, parse & planRW load ACID SSD� HDDRO load pg as a cache manager SSD = HDD
39 / 40
![Page 116: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/116.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Lessons
Things to Bring Home in-memory OLTP load
NoTPS not only TPS latency matters!latency-throughput compromise
Performance experiment and measure do not assume!pgbench is improving. . .
Postgres version 9.6!sorted and flushed checkpoints
High costs network, parse & planRW load ACID SSD� HDDRO load pg as a cache manager SSD = HDD
39 / 40
![Page 117: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/117.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Contributions provided or provoked
About Core & Andres Freund
sorted checkpointsflushed checkpoints
About pgbench & Robert Haas
expressions \set ...
mixed and weighted scripts and builtins -b/-f ...@...
better statistics stddev, per script. . .improved usability -c/-j -P. . .rate and limit load -R -L
debug. . .40 / 40
![Page 118: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/118.jpg)
PostgresLatency
F. Coelho
IntroductionSubject
Application
Definitions
pgbench
PerformanceConnection
Latency
Rate & Limit
Storage
Protocol
Combinations
Distance
Scalability
Miscellaneous
ConclusionWrap-Up
Lessons
Contributions
Contributions provided or provoked
About Core & Andres Freund
sorted checkpointsflushed checkpoints
About pgbench & Robert Haas
expressions \set ...
mixed and weighted scripts and builtins -b/-f ...@...
better statistics stddev, per script. . .improved usability -c/-j -P. . .rate and limit load -R -L
debug. . .40 / 40
![Page 119: Measuring and Reducing Postgres Transaction Latency › ~coelho › cours › si › pg... · 2017-03-23 · pgbench 3Conclusion Latency and Throughput Wrap-Up Lessons Learned Contributions](https://reader034.vdocuments.us/reader034/viewer/2022042409/5f2607e8210c16503b079c9b/html5/thumbnails/119.jpg)
Measuring and ReducingPostgres Transaction Latency
Fabien Coelho
MINES ParisTech, PSL Research University
pgDay Paris – March 23, 2017
1 / 1