measuring and reducing postgres transaction latency
play

Measuring and Reducing Postgres Transaction Latency Fabien Coelho - PowerPoint PPT Presentation

Nov 24, 2022 •171 likes •1.37k views

Measuring and Reducing Postgres Transaction Latency Fabien Coelho MINES ParisTech, PSL Research University pgDay Paris March 23, 2017 1 / 40 Postgres Latency Talk Outline 1 Introduction Subject Typical Web Application Transaction

  1. Default TPC-B-like Transaction pgbench -b tcpb-like TPC-B-like banking transaction Postgres Pattern Latency -- random ids and amount F. Coelho \ set aid random(1, 100000 * :scale) 3 updates Introduction \ set bid random(1, 1 * :scale) 1 insert Subject \ set tid random(1, 10 * :scale) Application \ set delta random(-5000, 5000) Definitions 1 select pgbench -- actual transaction BEGIN; Performance UPDATE pgbench accounts Connection Latency SET abalance = abalance + :delta WHERE aid = :aid; Rate & Limit SELECT abalance Storage Protocol FROM pgbench accounts WHERE aid = :aid; Combinations UPDATE pgbench tellers Distance SET tbalance = tbalance + :delta WHERE tid = :tid; Scalability Miscellaneous UPDATE pgbench branches Conclusion SET bbalance = bbalance + :delta WHERE bid = :bid; Wrap-Up INSERT INTO pgbench history (tid, bid, aid, delta, mtime) Lessons VALUES (:tid, :bid, :aid, :delta, CURRENT TIMESTAMP); Contributions END; 7 / 40

  2. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Connection Two Connection Costs Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 8 / 40

  3. Connection Costs pgbench -C pgbench postgres Postgres Client 8 cores, 16 GB Latency LAN 1 Gbps F. Coelho LAN Server 16 cores, 32 GB, HDD Introduction Client Server Subject Application Definitions Initialization Postgres 9.6.1 pgbench Performance 1.5 GB pgbench -i -s 100 Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 9 / 40

  4. Connection Costs pgbench -C pgbench postgres Postgres Client 8 cores, 16 GB Latency LAN 1 Gbps F. Coelho LAN Server 16 cores, 32 GB, HDD Introduction Client Server Subject Application Definitions Initialization Postgres 9.6.1 pgbench Performance 1.5 GB pgbench -i -s 100 Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 9 / 40

  5. Connection Costs pgbench -C pgbench postgres Postgres Client 8 cores, 16 GB Latency LAN 1 Gbps F. Coelho LAN Server 16 cores, 32 GB, HDD Introduction Client Server Subject Application Definitions Initialization and Benchmarks Postgres 9.6.1 pgbench Performance 1.5 GB pgbench -i -s 100 Connection Latency Rate & Limit pgbench -T 2000 -C "host=server sslmode=require" 36.1 tps Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 9 / 40

  6. Connection Costs pgbench -C pgbench postgres Postgres Client 8 cores, 16 GB Latency LAN 1 Gbps F. Coelho LAN Server 16 cores, 32 GB, HDD Introduction Client Server Subject Application Definitions Initialization and Benchmarks Postgres 9.6.1 pgbench Performance 1.5 GB pgbench -i -s 100 Connection Latency Rate & Limit pgbench -T 2000 -C "host=server sslmode=require" 36.1 tps Storage Protocol 56.4 tps pgbench -T 2000 -C "host=server sslmode=disable" Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 9 / 40

  7. Connection Costs pgbench -C pgbench postgres Postgres Client 8 cores, 16 GB Latency LAN 1 Gbps F. Coelho LAN Server 16 cores, 32 GB, HDD Introduction Client Server Subject Application Definitions Initialization and Benchmarks Postgres 9.6.1 pgbench Performance 1.5 GB pgbench -i -s 100 Connection Latency Rate & Limit pgbench -T 2000 -C "host=server sslmode=require" 36.1 tps Storage Protocol 56.4 tps pgbench -T 2000 -C "host=server sslmode=disable" Combinations Distance pgbench -T 2000 "host=server sslmode=disable" 105.4 tps Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 9 / 40

  8. Connection Costs pgbench -C pgbench postgres Postgres Client 8 cores, 16 GB Latency LAN 1 Gbps F. Coelho LAN Server 16 cores, 32 GB, HDD Introduction Client Server Subject Application Definitions Initialization and Benchmarks Postgres 9.6.1 pgbench Performance 1.5 GB pgbench -i -s 100 Connection Latency Rate & Limit pgbench -T 2000 -C "host=server sslmode=require" 36.1 tps Storage Protocol 56.4 tps pgbench -T 2000 -C "host=server sslmode=disable" Combinations Distance pgbench -T 2000 "host=server sslmode=disable" 105.4 tps Scalability Miscellaneous Conclusion connection AAA 8.2 ms Wrap-Up Lessons SSL negociation 10.0 ms Contributions transfers and transactions 9.5 ms 9 / 40

  9. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Latency Pitfalls Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 10 / 40

  10. Latency Comparison – 9.5 vs 9.6 pgbench -j 4 -c 8 Postgres Version 9.5.5 Version 9.6.1 Latency F. Coelho throughput 329.4 tps throughput 326.4 tps Introduction average latency 24.3 ms average latency 24.4 ms Subject Application Definitions pgbench Performance Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 11 / 40

  11. Latency Comparison – 9.5 vs 9.6 pgbench -j 4 -c 8 Postgres Version 9.5.5 Version 9.6.1 Latency F. Coelho throughput 329.4 tps throughput 326.4 tps Introduction average latency 24.3 ms average latency 24.4 ms Subject Application 600 600 Definitions pgbench 500 500 Performance thousand transactions thousand transactions Connection 400 400 Latency Rate & Limit 300 300 Storage Protocol 200 200 Combinations Distance Scalability 100 100 Miscellaneous 0 0 Conclusion 0 1 2 3 4 5 0 1 2 3 4 5 Wrap-Up transaction latency in seconds transaction latency in seconds Lessons Contributions 11 / 40

  12. Latency Comparison – 9.5 vs 9.6 pgbench -j 4 -c 8 Version 9.5.5 Version 9.6.1 Postgres Latency F. Coelho throughput 329.4 tps throughput 326.4 tps Introduction average latency 24.3 ms average latency 24.4 ms Subject Application 600 600 Definitions pgbench 500 500 Performance thousand transactions thousand transactions Connection 400 400 Latency Rate & Limit 300 300 Storage Protocol 200 200 Combinations Distance 100 100 Scalability Miscellaneous 0 0 Conclusion 0 1 2 3 4 5 0 1 2 3 4 5 Wrap-Up transaction latency in seconds transaction latency in seconds Lessons Contributions latency std. dev. 79.5 ms latency std. dev. 20.3 ms 11 / 40

  13. Latency Comparison – 9.5 vs 9.6 Instant TPS Version 9.5.5 Version 9.6.1 Postgres Latency 500 500 F. Coelho 400 400 Introduction Subject 300 300 Application tps tps Definitions 200 200 pgbench Performance 100 100 Connection Latency Rate & Limit 0 0 Storage 0 500 1000 1500 2000 0 500 1000 1500 2000 Protocol run time in seconds run time in seconds Combinations Distance What is happening? Scalability Miscellaneous Conclusion transaction surges are absorbed in-memory + WAL Wrap-Up Lessons then data are written disk checkpoint Contributions 12 / 40

  14. Latency Comparison – 9.5 vs 9.6 Instant TPS Version 9.5.5 Version 9.6.1 Postgres Latency 500 500 F. Coelho 400 400 Introduction Subject 300 300 Application tps tps Definitions 200 200 pgbench Performance 100 100 Connection Latency Rate & Limit 0 0 Storage 0 500 1000 1500 2000 0 500 1000 1500 2000 Protocol run seconds sorted by tps run seconds sorted by tps Combinations Distance What is happening? Scalability Miscellaneous Conclusion transaction surges are absorbed in-memory + WAL Wrap-Up Lessons then data are written disk checkpoint Contributions 12 / 40

  15. Latency Comparison – 9.5 vs 9.6 Instant TPS Version 9.5.5 Version 9.6.1 Postgres Latency 500 500 F. Coelho 400 400 Introduction Subject 300 300 Application tps tps Definitions 200 200 pgbench Performance 100 100 Connection Latency Rate & Limit 0 0 Storage 0 500 1000 1500 2000 0 500 1000 1500 2000 Protocol run seconds sorted by tps run seconds sorted by tps Combinations Distance What is happening? Buy Now, Pay Later! Scalability Miscellaneous Conclusion transaction surges are absorbed in-memory + WAL Wrap-Up Lessons then data are written disk checkpoint Contributions 12 / 40

  16. Latency Comparison – 9.5 vs 9.6 Checkpointing Postgres Latency Postgres 9.5 Checkpoint F. Coelho data writes spread over some time random I/O Introduction Subject OS choose when to actually write 30s delay on Linux Application Definitions pgbench until fsync is called. . . Performance Connection Latency Rate & Limit Postgres 9.6 Checkpoint Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 13 / 40

  17. Latency Comparison – 9.5 vs 9.6 Checkpointing Postgres Latency Postgres 9.5 Checkpoint F. Coelho data writes spread over some time random I/O Introduction Subject OS choose when to actually write 30s delay on Linux Application Definitions pgbench until fsync is called. . . Performance Connection Latency Rate & Limit Postgres 9.6 Checkpoint Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 13 / 40

  18. Latency Comparison – 9.5 vs 9.6 Checkpointing Postgres Latency Postgres 9.5 Checkpoint F. Coelho data writes spread over some time random I/O Introduction Subject OS choose when to actually write 30s delay on Linux Application Definitions pgbench until fsync is called. . . I/O storm – on low-end HDD Performance Connection Latency Rate & Limit Postgres 9.6 Checkpoint Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 13 / 40

  19. Latency Comparison – 9.5 vs 9.6 Checkpointing Postgres Latency Postgres 9.5 Checkpoint F. Coelho data writes spread over some time random I/O Introduction Subject OS choose when to actually write 30s delay on Linux Application Definitions pgbench until fsync is called. . . I/O storm – on low-end HDD Performance Connection Latency Rate & Limit Postgres 9.6 Checkpoint Storage Protocol Combinations Distance sorted data writes spread over some time sequential I/O Scalability Miscellaneous flush instructions sent regularly (256 kB) checkpoint flush after Conclusion Wrap-Up Lessons Contributions 13 / 40

  20. Latency Comparison – 9.5 vs 9.6 Checkpointing Postgres Latency Postgres 9.5 Checkpoint F. Coelho data writes spread over some time random I/O Introduction Subject OS choose when to actually write 30s delay on Linux Application Definitions pgbench until fsync is called. . . I/O storm – on low-end HDD Performance Connection Latency Rate & Limit Postgres 9.6 Checkpoint Storage Protocol Combinations Distance sorted data writes spread over some time sequential I/O Scalability Miscellaneous flush instructions sent regularly (256 kB) checkpoint flush after Conclusion Wrap-Up when fsync is called ok! Lessons Contributions 13 / 40

  21. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Benchmarking with Rate and Limit Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 14 / 40

  22. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N Postgres Pg 9.5 basic checkpoint Latency F. Coelho slow & skipped Introduction latency Subject Application Definitions pgbench Pg 9.6 sorted checkpoint Performance Connection Latency slow & skipped Rate & Limit Storage latency Protocol Combinations Distance Scalability Pg 9.6 sorted & flushed checkpoint Miscellaneous Conclusion Wrap-Up slow & skipped Lessons Contributions latency 15 / 40

  23. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N 150 Postgres Pg 9.5 basic checkpoint Latency 100 tps F. Coelho 50 slow & skipped Introduction 0 latency Subject 0 500 1000 1500 2000 2500 Application run time in seconds Definitions pgbench Pg 9.6 sorted checkpoint Performance Connection Latency slow & skipped Rate & Limit Storage latency Protocol Combinations Distance Scalability Miscellaneous Pg 9.6 sorted & flushed checkpoint Conclusion Wrap-Up slow & skipped Lessons Contributions latency 15 / 40

  24. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N 150 Postgres Pg 9.5 basic checkpoint Latency 100 tps F. Coelho 50 slow & skipped 24.0% Introduction 0 latency 15.6 ± 158.3 ms Subject 0 500 1000 1500 2000 2500 Application run seconds sorted by tps Definitions pgbench Pg 9.6 sorted checkpoint Performance Connection Latency slow & skipped Rate & Limit Storage latency Protocol Combinations Distance Scalability Miscellaneous Pg 9.6 sorted & flushed checkpoint Conclusion Wrap-Up slow & skipped Lessons Contributions latency 15 / 40

  25. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N 150 Postgres Pg 9.5 basic checkpoint Latency 100 tps F. Coelho 50 slow & skipped 24.0% Introduction 0 latency 15.6 ± 158.3 ms Subject 0 500 1000 1500 2000 2500 Application run seconds sorted by tps Definitions 150 pgbench Pg 9.6 sorted checkpoint Performance 100 Connection tps Latency slow & skipped 50 Rate & Limit Storage 0 latency Protocol 0 500 1000 1500 2000 2500 Combinations run time in seconds Distance Scalability Miscellaneous Pg 9.6 sorted & flushed checkpoint Conclusion Wrap-Up slow & skipped Lessons Contributions latency 15 / 40

  26. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N 150 Postgres Pg 9.5 basic checkpoint Latency 100 tps F. Coelho 50 slow & skipped 24.0% Introduction 0 latency 15.6 ± 158.3 ms Subject 0 500 1000 1500 2000 2500 Application run seconds sorted by tps Definitions 150 pgbench Pg 9.6 sorted checkpoint Performance 100 Connection tps Latency slow & skipped 2.7% 50 Rate & Limit Storage 0 3.6 ± 24.6 ms latency Protocol 0 500 1000 1500 2000 2500 Combinations run seconds sorted by tps Distance Scalability Miscellaneous Pg 9.6 sorted & flushed checkpoint Conclusion Wrap-Up slow & skipped Lessons Contributions latency 15 / 40

  27. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N 150 Postgres Pg 9.5 basic checkpoint Latency 100 tps F. Coelho 50 slow & skipped 24.0% Introduction 0 latency 15.6 ± 158.3 ms Subject 0 500 1000 1500 2000 2500 Application run seconds sorted by tps Definitions 150 pgbench Pg 9.6 sorted checkpoint Performance 100 Connection tps Latency slow & skipped 2.7% 50 Rate & Limit Storage 0 3.6 ± 24.6 ms latency Protocol 0 500 1000 1500 2000 2500 Combinations run seconds sorted by tps Distance Scalability 150 Miscellaneous Pg 9.6 sorted & flushed checkpoint 100 Conclusion tps Wrap-Up slow & skipped 50 Lessons Contributions 0 latency 0 500 1000 1500 2000 2500 run time in seconds 15 / 40

  28. Rate (tps) and Limit (ms) pgbench -R 100 -L 100 -N 150 Postgres Pg 9.5 basic checkpoint Latency 100 tps F. Coelho 50 slow & skipped 24.0% Introduction 0 latency 15.6 ± 158.3 ms Subject 0 500 1000 1500 2000 2500 Application run seconds sorted by tps Definitions 150 pgbench Pg 9.6 sorted checkpoint Performance 100 Connection tps Latency slow & skipped 2.7% 50 Rate & Limit Storage 0 3.6 ± 24.6 ms latency Protocol 0 500 1000 1500 2000 2500 Combinations run seconds sorted by tps Distance Scalability 150 Miscellaneous Pg 9.6 sorted & flushed checkpoint 100 Conclusion tps Wrap-Up slow & skipped 0.5% 50 Lessons Contributions 0 latency 2.6 ± 13.8 ms 0 500 1000 1500 2000 2500 run seconds sorted by tps 15 / 40

  29. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Three Storage Options Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 16 / 40

  30. FILLFACTOR Storage Parameter Postgres CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 100); Latency F. Coelho FILLFACTOR Usage Introduction Subject Application MVCC: UPDATE = DELETE + INSERT up to 3 pages changes Definitions pgbench some free space available in page 1 inside page change Performance Connection but more pages/costs for other operations trade-off Latency Rate & Limit Storage Protocol Combinations Distance FILLFACTOR = 100 FILLFACTOR = 95 Scalability Miscellaneous throughput 406.9 tps throughput Conclusion Wrap-Up Lessons latency 19.7 ± 12.3 ms latency Contributions 17 / 40

  31. FILLFACTOR Storage Parameter Postgres CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 100); Latency F. Coelho FILLFACTOR Usage Introduction Subject Application MVCC: UPDATE = DELETE + INSERT up to 3 pages changes Definitions pgbench some free space available in page 1 inside page change Performance Connection but more pages/costs for other operations trade-off Latency Rate & Limit Storage Protocol Combinations Distance FILLFACTOR = 100 FILLFACTOR = 95 Scalability Miscellaneous throughput 406.9 tps throughput Conclusion Wrap-Up Lessons latency 19.7 ± 12.3 ms latency Contributions 17 / 40

  32. FILLFACTOR Storage Parameter Postgres CREATE TABLE pgbench accounts(...) WITH (FILLFACTOR = 100); Latency F. Coelho FILLFACTOR Usage Introduction Subject Application MVCC: UPDATE = DELETE + INSERT up to 3 pages changes Definitions pgbench some free space available in page 1 inside page change Performance Connection but more pages/costs for other operations trade-off Latency Rate & Limit Storage Protocol Combinations Distance FILLFACTOR = 100 FILLFACTOR = 95 Scalability Miscellaneous throughput 406.9 tps throughput 416.8 tps Conclusion Wrap-Up Lessons latency 19.7 ± 12.3 ms latency 19.2 ± 8.3 ms Contributions 17 / 40

  33. Hardware HDD vs SSD Postgres Latency Hard Disk Drive Solid State Disk F. Coelho mechanics electronics vs Introduction Subject fast sequential I/O fast sequential I/O Application Definitions slow random I/O fast random I/O pgbench Performance Connection pgbench -j 4 -c 8 -T 2500 -M prepared ... Latency Rate & Limit Storage Protocol Postgres 9.6 Combinations Distance Scalability HDD 406.9 tps 19.7 ± 12.3 ms Miscellaneous Conclusion SSD Wrap-Up Lessons Contributions 18 / 40

  34. Hardware HDD vs SSD Postgres Latency Hard Disk Drive Solid State Disk F. Coelho mechanics electronics vs Introduction Subject fast sequential I/O fast sequential I/O Application Definitions slow random I/O fast random I/O pgbench Performance Connection pgbench -j 4 -c 8 -T 2500 -M prepared ... Latency Rate & Limit Storage 600 Protocol Postgres 9.6 500 Combinations 400 Distance tps 300 Scalability HDD 406.9 tps 19.7 ± 12.3 ms Miscellaneous 200 HDD 100 Conclusion SSD 0 Wrap-Up 0 500 1000 1500 2000 2500 Lessons Contributions seconds 18 / 40

  35. Hardware HDD vs SSD Postgres Latency Hard Disk Drive Solid State Disk F. Coelho mechanics electronics vs Introduction Subject fast sequential I/O fast sequential I/O Application Definitions slow random I/O fast random I/O pgbench Performance Connection pgbench -j 4 -c 8 -T 2500 -M prepared ... Latency Rate & Limit Storage 6000 Protocol Postgres 9.6 5000 Combinations 4000 Distance tps 3000 Scalability HDD 19.7 ± 12.3 ms 406.9 tps Miscellaneous SSD 2000 HDD 1000 Conclusion SSD 4,764.9 tps 1.7 ± 2.4 ms 0 Wrap-Up 0 500 1000 1500 2000 2500 Lessons Contributions seconds 18 / 40

  36. Hardware HDD vs SSD Postgres Latency Hard Disk Drive Solid State Disk F. Coelho mechanics electronics vs Introduction Subject fast sequential I/O fast sequential I/O Application Definitions slow random I/O fast random I/O pgbench Performance Connection pgbench -j 4 -c 8 -T 2500 -M prepared ... Latency Rate & Limit Storage 6000 Protocol Postgres 9.6 5000 Combinations 4000 Distance tps 3000 Scalability HDD 406.9 tps 19.7 ± 12.3 ms Miscellaneous SSD 2000 HDD 1000 Conclusion SSD 4,764.9 tps 1.7 ± 2.4 ms 0 Wrap-Up 0 500 1000 1500 2000 2500 Lessons checkpoint full page write effect Contributions seconds 18 / 40

  37. UNLOGGED TABLE Can you loose your data? Postgres Latency F. Coelho CREATE UNLOGGED TABLE pgbench accounts(...); Introduction Subject Application Definitions pgbench Standard ACID UNLOGGED Performance Connection Latency throughput 406.9 tps throughput Rate & Limit Storage Protocol latency 19.7 ± 12.3 ms latency Combinations Distance Scalability Miscellaneous ... Conclusion Wrap-Up Lessons Contributions 19 / 40

  38. UNLOGGED TABLE Can you loose your data? Postgres Latency F. Coelho CREATE UNLOGGED TABLE pgbench accounts(...); Introduction Subject Application Definitions pgbench Standard ACID UNLOGGED Performance Connection Latency throughput 406.9 tps throughput Rate & Limit Storage Protocol latency 19.7 ± 12.3 ms latency Combinations Distance Scalability Miscellaneous ... Conclusion Wrap-Up Lessons Contributions 19 / 40

  39. UNLOGGED TABLE Can you loose your data? Postgres Latency F. Coelho CREATE UNLOGGED TABLE pgbench accounts(...); Introduction Subject Application Definitions pgbench UNLOGGED good luck! Standard ACID Performance Connection Latency throughput 406.9 tps Rate & Limit throughput 5,310.7 tps Storage Protocol latency 19.7 ± 12.3 ms latency 1.5 ± 0.3 ms Combinations Distance Scalability Miscellaneous ... Conclusion Wrap-Up Lessons Contributions 19 / 40

  40. UNLOGGED TABLE Can you loose your data? Postgres Latency CREATE UNLOGGED TABLE pgbench accounts(...); F. Coelho Introduction Subject Application Definitions pgbench UNLOGGED good luck! Standard ACID Performance Connection throughput 406.9 tps throughput 5,310.7 tps Latency Rate & Limit Storage latency 19.7 ± 12.3 ms latency 1.5 ± 0.3 ms Protocol Combinations Distance Scalability Miscellaneous NO! Conclusion Wrap-Up Lessons Contributions 19 / 40

  41. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Two Protocol Impacts Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 20 / 40

  42. Read-Only In-Cache Test ro3.sql \ set aid random(1, 100000 * :scale) Postgres Latency \ set tid random(1, 10 * :scale) F. Coelho \ set bid random(1, :scale) BEGIN; Introduction Subject SELECT abalance FROM pgbench accounts WHERE aid=:aid; Application SELECT tbalance FROM pgbench tellers WHERE tid=:tid; Definitions pgbench SELECT bbalance FROM pgbench branches WHERE bid=:bid; Performance COMMIT; Connection Latency Rate & Limit Storage Operations Queries on 3 tables Protocol Combinations Distance 1 transfers network protocol Scalability Miscellaneous 2 parse query syntax analysis Conclusion Wrap-Up 3 plan query optimization Lessons Contributions 4 execute query cheap if in cache 21 / 40

  43. Read-Only In-Cache Test ro3.sql \ set aid random(1, 100000 * :scale) Postgres Latency \ set tid random(1, 10 * :scale) F. Coelho \ set bid random(1, :scale) BEGIN; Introduction Subject SELECT abalance FROM pgbench accounts WHERE aid=:aid; Application SELECT tbalance FROM pgbench tellers WHERE tid=:tid; Definitions pgbench SELECT bbalance FROM pgbench branches WHERE bid=:bid; Performance COMMIT; Connection Latency Rate & Limit Storage Operations Queries on 3 tables Protocol Combinations Distance 1 transfers network protocol Scalability Miscellaneous 2 parse query syntax analysis Conclusion Wrap-Up 3 plan query optimization Lessons Contributions 4 execute query cheap if in cache 21 / 40

  44. Protocol SSL or not Postgres Latency SSL Costs time & e Benefits F. Coelho negotiation and re-negotiation Confidentiality Introduction Subject cryptographic functions Integrity Application Definitions pgbench certificate Authentication Performance Connection Latency Rate & Limit Storage Protocol Combinations SSL sslmode=require clear sslmode=disable Distance Scalability Miscellaneous throughput throughput 709.7 tps Conclusion latency Wrap-Up latency 1.407 ± 0.132 ms Lessons Contributions 22 / 40

  45. Protocol SSL or not Postgres Latency SSL Costs time & e Benefits F. Coelho negotiation and re-negotiation Confidentiality Introduction Subject cryptographic functions Integrity Application Definitions pgbench certificate Authentication Performance Connection Latency Rate & Limit Storage Protocol Combinations SSL sslmode=require clear sslmode=disable Distance Scalability Miscellaneous throughput throughput 709.7 tps Conclusion latency Wrap-Up latency 1.407 ± 0.132 ms Lessons Contributions 22 / 40

  46. Protocol SSL or not Postgres Latency SSL Costs time & e Benefits Snake Oil! F. Coelho negotiation and re-negotiation Confidentiality Introduction Subject cryptographic functions Integrity Application Definitions pgbench certificate? Authentication Performance Connection Latency Rate & Limit Storage Protocol Combinations SSL sslmode=require clear sslmode=disable Distance Scalability Miscellaneous throughput throughput 709.7 tps Conclusion latency Wrap-Up latency 1.407 ± 0.132 ms Lessons Contributions 22 / 40

  47. Protocol SSL or not Postgres Latency SSL Costs time & e Benefits Snake Oil! F. Coelho negotiation and re-negotiation Confidentiality Introduction Subject cryptographic functions Integrity Application Definitions certificate Authentication pgbench Performance Connection Latency pgbench -j 1 -c 1 -D scale=100 -f ro3.sql -T 30 "host=server ..." Rate & Limit Storage Protocol Combinations sslmode=require SSL sslmode=disable clear Distance Scalability Miscellaneous throughput throughput 709.7 tps Conclusion Wrap-Up latency latency 1.407 ± 0.132 ms Lessons Contributions 22 / 40

  48. Protocol SSL or not Postgres Latency SSL Costs time & e Benefits Snake Oil! F. Coelho negotiation and re-negotiation Confidentiality Introduction Subject cryptographic functions Integrity Application Definitions certificate Authentication pgbench Performance Connection Latency pgbench -j 1 -c 1 -D scale=100 -f ro3.sql -T 30 "host=server ..." Rate & Limit Storage Protocol Combinations sslmode=require SSL sslmode=disable clear Distance Scalability Miscellaneous throughput 781.6 tps throughput 709.7 tps Conclusion Wrap-Up latency 1.277 ± 0.034 ms latency 1.407 ± 0.132 ms Lessons Contributions 22 / 40

  49. Protocol Simple vs Prepared -- prepare once in session Postgres Latency PREPARE Abal(INT) AS Prepare F. Coelho SELECT abalance FROM pgbench accounts Introduction temporary one-cmd function Subject WHERE aid=$1; Application factor out parse cost -- execute multiple times... Definitions pgbench EXECUTE Abal(1); keep plan and execute Performance EXECUTE Abal(5432); Connection pgbench -M prepared . . . Latency EXECUTE Abal(18); Rate & Limit Storage Protocol Combinations Distance ro3.sql simple ro3.sql prepared Scalability Miscellaneous throughput 709.7 tps throughput Conclusion Wrap-Up Lessons latency 1.407 ± 0.132 ms latency Contributions 23 / 40

  50. Protocol Simple vs Prepared -- prepare once in session Postgres Latency PREPARE Abal(INT) AS Prepare F. Coelho SELECT abalance FROM pgbench accounts Introduction temporary one-cmd function Subject WHERE aid=$1; Application factor out parse cost -- execute multiple times... Definitions pgbench EXECUTE Abal(1); keep plan and execute Performance EXECUTE Abal(5432); Connection pgbench -M prepared . . . Latency EXECUTE Abal(18); Rate & Limit Storage Protocol Combinations Distance ro3.sql simple ro3.sql prepared Scalability Miscellaneous throughput 709.7 tps throughput Conclusion Wrap-Up Lessons latency 1.407 ± 0.132 ms latency Contributions 23 / 40

  51. Protocol Simple vs Prepared -- prepare once in session Postgres Latency PREPARE Abal(INT) AS Prepare F. Coelho SELECT abalance FROM pgbench accounts Introduction temporary one-cmd function Subject WHERE aid=$1; Application factor out parse cost -- execute multiple times... Definitions pgbench EXECUTE Abal(1); keep plan and execute Performance EXECUTE Abal(5432); Connection pgbench -M prepared . . . Latency EXECUTE Abal(18); Rate & Limit Storage Protocol Combinations Distance ro3.sql simple ro3.sql prepared Scalability Miscellaneous throughput 709.7 tps throughput 860.0 tps Conclusion Wrap-Up Lessons latency 1.407 ± 0.132 ms latency 1.161 ± 0.082 ms Contributions 23 / 40

  52. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Four Query Combination Tricks Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 24 / 40

  53. Query Combination UPDATE & SELECT -- combined Postgres Latency UPDATE pgbench accounts -- update table SET abalance = abalance + :delta UPDATE pgbench accounts F. Coelho WHERE aid = :aid SET abalance = abalance + :delta RETURNING abalance; Introduction WHERE aid = :aid; Subject Application -- get updated data UPDATE RETURNING Option Definitions pgbench SELECT abalance FROM pgbench accounts Performance return updated rows WHERE aid = :aid; Connection Latency one parse, plan, execute Rate & Limit Storage Protocol Combinations Distance Combined Update Standard Scalability Miscellaneous throughput 406.9 tps throughput Conclusion Wrap-Up latency 19.7 ± 12.3 ms Lessons latency Contributions 25 / 40

  54. Query Combination UPDATE & SELECT -- combined Postgres Latency UPDATE pgbench accounts -- update table SET abalance = abalance + :delta UPDATE pgbench accounts F. Coelho WHERE aid = :aid SET abalance = abalance + :delta RETURNING abalance; Introduction WHERE aid = :aid; Subject Application -- get updated data UPDATE RETURNING Option Definitions pgbench SELECT abalance FROM pgbench accounts Performance return updated rows WHERE aid = :aid; Connection Latency one parse, plan, execute Rate & Limit Storage Protocol Combinations Distance Combined Update Standard Scalability Miscellaneous throughput 406.9 tps throughput Conclusion Wrap-Up latency 19.7 ± 12.3 ms Lessons latency Contributions 25 / 40

  55. Query Combination UPDATE & SELECT -- combined Postgres Latency UPDATE pgbench accounts -- update table SET abalance = abalance + :delta UPDATE pgbench accounts F. Coelho WHERE aid = :aid SET abalance = abalance + :delta RETURNING abalance; Introduction WHERE aid = :aid; Subject Application -- get updated data UPDATE RETURNING Option Definitions pgbench SELECT abalance FROM pgbench accounts Performance return updated rows WHERE aid = :aid; Connection Latency one parse, plan, execute Rate & Limit Storage Protocol Combinations Distance Combined Update Standard Scalability Miscellaneous throughput 406.9 tps throughput Conclusion Wrap-Up latency 19.7 ± 12.3 ms Lessons latency Contributions 25 / 40

  56. Query Combination UPDATE & SELECT -- combined Postgres Latency UPDATE pgbench accounts -- update table SET abalance = abalance + :delta UPDATE pgbench accounts F. Coelho WHERE aid = :aid SET abalance = abalance + :delta RETURNING abalance; Introduction WHERE aid = :aid; Subject Application -- get updated data UPDATE RETURNING Option Definitions pgbench SELECT abalance FROM pgbench accounts Performance return updated rows WHERE aid = :aid; Connection Latency one parse, plan, execute Rate & Limit Storage Protocol Combinations Distance Combined Update Standard Scalability Miscellaneous throughput 406.9 tps throughput 408.2 tps Conclusion Wrap-Up latency 19.7 ± 12.3 ms Lessons latency 19.6 ± 8.7 ms Contributions 25 / 40

  57. Client-combined SQL Queries -- ”ro3c.sql” pgbench script Postgres \ set aid random(1, 100000 * :scale) Latency \ set tid random(1, 10 * :scale) Combine with \ ; F. Coelho \ set bid random(1, :scale) BEGIN \ ; Introduction embedded semi-colon ; SELECT abalance FROM Subject Application pgbench accounts WHERE aid=:aid \ ; request with multiple queries Definitions SELECT tbalance FROM pgbench pgbench tellers WHERE tid=:tid \ ; response with list of results Performance SELECT bbalance FROM Connection pgbench branches WHERE bid=:bid \ ; avoid request-response loop Latency Rate & Limit COMMIT; Storage Protocol Combinations Distance ro3.sql standard ro3c.sql combined Scalability Miscellaneous Conclusion throughput 709.7 tps throughput Wrap-Up Lessons latency 1.407 ± 0.132 ms latency Contributions 26 / 40

  58. Client-combined SQL Queries -- ”ro3c.sql” pgbench script Postgres \ set aid random(1, 100000 * :scale) Latency \ set tid random(1, 10 * :scale) Combine with \ ; F. Coelho \ set bid random(1, :scale) BEGIN \ ; Introduction embedded semi-colon ; SELECT abalance FROM Subject Application pgbench accounts WHERE aid=:aid \ ; request with multiple queries Definitions SELECT tbalance FROM pgbench pgbench tellers WHERE tid=:tid \ ; response with list of results Performance SELECT bbalance FROM Connection pgbench branches WHERE bid=:bid \ ; avoid request-response loop Latency Rate & Limit COMMIT; Storage Protocol Combinations Distance ro3.sql standard ro3c.sql combined Scalability Miscellaneous Conclusion throughput 709.7 tps throughput Wrap-Up Lessons latency 1.407 ± 0.132 ms latency Contributions 26 / 40

  59. Client-combined SQL Queries -- ”ro3c.sql” pgbench script Postgres \ set aid random(1, 100000 * :scale) Latency \ set tid random(1, 10 * :scale) Combine with \ ; F. Coelho \ set bid random(1, :scale) BEGIN \ ; Introduction embedded semi-colon ; SELECT abalance FROM Subject Application pgbench accounts WHERE aid=:aid \ ; request with multiple queries Definitions SELECT tbalance FROM pgbench pgbench tellers WHERE tid=:tid \ ; response with list of results Performance SELECT bbalance FROM Connection pgbench branches WHERE bid=:bid \ ; avoid request-response loop Latency Rate & Limit COMMIT; Storage Protocol Combinations Distance ro3.sql standard ro3c.sql combined Scalability Miscellaneous Conclusion throughput 709.7 tps throughput 1,311.5 tps Wrap-Up Lessons latency 1.407 ± 0.132 ms latency 0.748 ± 0.132 ms Contributions 26 / 40

  60. Server-Side SQL queries CREATE TYPE Balances Postgres AS (abal INT, tbal INT, bbal INT); Latency F. Coelho CREATE FUNCTION getBalSQL(INT, INT, INT) RETURNS Balances AS $$ -- ”ro3sf.sql” pgbench script Introduction SELECT \ set aid random(1, 100000 * :scale) Subject (SELECT abalance \ set tid random(1, 10 * :scale) Application Definitions FROM pgbench accounts WHERE aid=$1), \ set bid random(1, :scale) pgbench (SELECT tbalance SELECT getBalSQL(:aid, :tid, :bid); Performance FROM pgbench tellers WHERE tid=$2), Connection (SELECT bbalance Latency Rate & Limit FROM pgbench branches WHERE bid=$3) Storage $$ LANGUAGE SQL; Protocol Combinations Distance Scalability standard SQL call Miscellaneous ro3.sql ro3sf.sql Conclusion Wrap-Up throughput 709.7 tps throughput Lessons Contributions latency 1.407 ± 0.132 ms latency 27 / 40

  61. Server-Side SQL queries CREATE TYPE Balances Postgres AS (abal INT, tbal INT, bbal INT); Latency F. Coelho CREATE FUNCTION getBalSQL(INT, INT, INT) RETURNS Balances AS $$ -- ”ro3sf.sql” pgbench script Introduction SELECT \ set aid random(1, 100000 * :scale) Subject (SELECT abalance \ set tid random(1, 10 * :scale) Application Definitions FROM pgbench accounts WHERE aid=$1), \ set bid random(1, :scale) pgbench (SELECT tbalance SELECT getBalSQL(:aid, :tid, :bid); Performance FROM pgbench tellers WHERE tid=$2), Connection (SELECT bbalance Latency Rate & Limit FROM pgbench branches WHERE bid=$3) Storage $$ LANGUAGE SQL; Protocol Combinations Distance Scalability standard SQL call Miscellaneous ro3.sql ro3sf.sql Conclusion Wrap-Up throughput 709.7 tps throughput Lessons Contributions latency 1.407 ± 0.132 ms latency 27 / 40

  62. Server-Side SQL queries CREATE TYPE Balances Postgres AS (abal INT, tbal INT, bbal INT); Latency F. Coelho CREATE FUNCTION getBalSQL(INT, INT, INT) RETURNS Balances AS $$ -- ”ro3sf.sql” pgbench script Introduction SELECT \ set aid random(1, 100000 * :scale) Subject (SELECT abalance \ set tid random(1, 10 * :scale) Application Definitions FROM pgbench accounts WHERE aid=$1), \ set bid random(1, :scale) pgbench (SELECT tbalance SELECT getBalSQL(:aid, :tid, :bid); Performance FROM pgbench tellers WHERE tid=$2), Connection (SELECT bbalance Latency Rate & Limit FROM pgbench branches WHERE bid=$3) Storage $$ LANGUAGE SQL; Protocol Combinations Distance Scalability standard SQL call Miscellaneous ro3.sql ro3sf.sql Conclusion Wrap-Up throughput 709.7 tps throughput 1,395.4 tps Lessons Contributions latency 1.407 ± 0.132 ms latency 0.712 ± 0.075 ms 27 / 40

  63. Server-Side PL/pgSQL queries CREATE FUNCTION Postgres getBalPL(a INT, t INT, b INT) Latency RETURNS Balances AS $$ F. Coelho DECLARE -- ”ro3pf.sql” pgbench script abal INT; tbal INT; bbal INT; Introduction \ set aid random(1, 100000 * :scale) BEGIN Subject \ set tid random(1, 10 * :scale) SELECT abalance INTO abal Application \ set bid random(1, :scale) Definitions FROM pgbench accounts WHERE aid=a; pgbench SELECT getBalPL(:aid, :tid, :bid); SELECT tbalance INTO tbal Performance FROM pgbench tellers WHERE tid=t; Connection SELECT bbalance INTO bbal Latency Rate & Limit FROM pgbench branches WHERE bid=b; Storage RETURN (abal, tbal, bbal)::Balances; Protocol END; Combinations Distance $$ LANGUAGE PLpgSQL; Scalability Miscellaneous ro3.sql standard PL/pgSQL call ro3pf.sql Conclusion Wrap-Up Lessons throughput 709.7 tps throughput Contributions latency 1.407 ± 0.132 ms latency 28 / 40

  64. Server-Side PL/pgSQL queries CREATE FUNCTION Postgres getBalPL(a INT, t INT, b INT) Latency RETURNS Balances AS $$ F. Coelho DECLARE -- ”ro3pf.sql” pgbench script abal INT; tbal INT; bbal INT; Introduction \ set aid random(1, 100000 * :scale) BEGIN Subject \ set tid random(1, 10 * :scale) SELECT abalance INTO abal Application \ set bid random(1, :scale) Definitions FROM pgbench accounts WHERE aid=a; pgbench SELECT getBalPL(:aid, :tid, :bid); SELECT tbalance INTO tbal Performance FROM pgbench tellers WHERE tid=t; Connection SELECT bbalance INTO bbal Latency Rate & Limit FROM pgbench branches WHERE bid=b; Storage RETURN (abal, tbal, bbal)::Balances; Protocol END; Combinations Distance $$ LANGUAGE PLpgSQL; Scalability Miscellaneous ro3.sql standard PL/pgSQL call ro3pf.sql Conclusion Wrap-Up Lessons throughput 709.7 tps throughput Contributions latency 1.407 ± 0.132 ms latency 28 / 40

  65. Server-Side PL/pgSQL queries CREATE FUNCTION Postgres getBalPL(a INT, t INT, b INT) Latency RETURNS Balances AS $$ F. Coelho DECLARE -- ”ro3pf.sql” pgbench script abal INT; tbal INT; bbal INT; Introduction \ set aid random(1, 100000 * :scale) BEGIN Subject \ set tid random(1, 10 * :scale) SELECT abalance INTO abal Application \ set bid random(1, :scale) Definitions FROM pgbench accounts WHERE aid=a; pgbench SELECT getBalPL(:aid, :tid, :bid); SELECT tbalance INTO tbal Performance FROM pgbench tellers WHERE tid=t; Connection SELECT bbalance INTO bbal Latency Rate & Limit FROM pgbench branches WHERE bid=b; ? Storage RETURN (abal, tbal, bbal)::Balances; Protocol END; Combinations Distance $$ LANGUAGE PLpgSQL; Scalability Miscellaneous ro3.sql standard PL/pgSQL call ro3pf.sql Conclusion Wrap-Up Lessons throughput 709.7 tps throughput 2,485.5 tps Contributions latency 1.407 ± 0.132 ms latency 0.400 ± 0.055 ms 28 / 40

  66. Server-Side PL/pgSQL queries CREATE FUNCTION Postgres getBalPL(a INT, t INT, b INT) Latency RETURNS Balances AS $$ F. Coelho DECLARE -- ”ro3pf.sql” pgbench script abal INT; tbal INT; bbal INT; \ set aid random(1, 100000 * :scale) Introduction BEGIN Subject \ set tid random(1, 10 * :scale) SELECT abalance INTO abal Application \ set bid random(1, :scale) Definitions FROM pgbench accounts WHERE aid=a; SELECT getBalPL(:aid, :tid, :bid); pgbench SELECT tbalance INTO tbal Performance FROM pgbench tellers WHERE tid=t; Connection SELECT bbalance INTO bbal Latency Rate & Limit FROM pgbench branches WHERE bid=b; PL/pgSQL caches plans! Storage RETURN (abal, tbal, bbal)::Balances; Protocol END; Combinations Distance $$ LANGUAGE PLpgSQL; Scalability Miscellaneous ro3.sql standard PL/pgSQL call ro3pf.sql Conclusion Wrap-Up Lessons throughput 709.7 tps throughput 2,485.5 tps Contributions latency 1.407 ± 0.132 ms latency 0.400 ± 0.055 ms 28 / 40

  67. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Reducting Server Distance Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 29 / 40

  68. Client-Server Distance Postgres Latency Interconnection F. Coelho LAN Local Area Network Ethernet Introduction Subject Application LO loopback interface localhost Definitions pgbench IPC Inter-Process Communication Unix domain socket Performance Connection Latency Rate & Limit Read-Only 3 TPC-B-Like on HDD Storage Protocol Combinations Distance LAN 100.3 tps 9.9 ms LAN 709.7 tps 1.4 ms Scalability Miscellaneous LO LO Conclusion Wrap-Up IPC IPC Lessons Contributions 30 / 40

  69. Client-Server Distance Postgres Latency Interconnection F. Coelho LAN Local Area Network Ethernet Introduction Subject Application LO loopback interface localhost Definitions pgbench IPC Inter-Process Communication Unix domain socket Performance Connection Latency Rate & Limit TPC-B-Like on HDD Read-Only 3 Storage Protocol Combinations LAN 100.3 tps 9.9 ms Distance LAN 709.7 tps 1.4 ms Scalability Miscellaneous LO 114.5 tps 8.7 ms LO Conclusion IPC 113.5 tps 8.8 ms Wrap-Up IPC Lessons Contributions 30 / 40

  70. Client-Server Distance Postgres Latency Interconnection F. Coelho LAN Local Area Network Ethernet Introduction Subject Application LO loopback interface localhost Definitions pgbench IPC Inter-Process Communication Unix domain socket Performance Connection Latency Rate & Limit TPC-B-Like on SSD Read-Only 3 Storage Protocol Combinations LAN 403.8 tps 2.4 ms Distance LAN 709.7 tps 1.4 ms Scalability Miscellaneous LO 1,133.3 tps 0.9 ms LO Conclusion IPC 1,243.1 tps 0.8 ms Wrap-Up IPC Lessons Contributions 30 / 40

  71. Client-Server Distance Postgres Latency Interconnection F. Coelho LAN Local Area Network Ethernet Introduction Subject LO loopback interface Application localhost Definitions pgbench IPC Inter-Process Communication Unix domain socket Performance Connection Latency Rate & Limit Read-Only 3 TPC-B-Like on SSD Storage Protocol Combinations LAN 403.8 tps 2.4 ms Distance LAN 709.7 tps 1.4 ms Scalability Miscellaneous LO 1,133.3 tps 0.9 ms LO 2,515.3 tps 0.4 ms Conclusion IPC 1,243.1 tps 0.8 ms Wrap-Up IPC 3,607.6 tps 0.3 ms Lessons Contributions 30 / 40

  72. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Performance Scalability Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 31 / 40

  73. # Clients Scalability Base Postgres Read-Only 3 – remote SSL simple queries Latency F. Coelho Introduction Best Throughput Subject Application Definitions pgbench Performance Connection Latency Best Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Compromise Wrap-Up Lessons Contributions 32 / 40

  74. # Clients Scalability Base Postgres Read-Only 3 – remote SSL simple queries Latency F. Coelho Introduction Best Throughput 40 Subject thousand tps Application 30 Definitions 37,639 tps 4.103 ms 156/4 pgbench 20 Performance 10 Connection 0 Latency Best Latency 0 20 40 60 80 100 120 140 160 180 Rate & Limit Storage number of clients Protocol Combinations Distance Scalability Miscellaneous Conclusion Compromise Wrap-Up Lessons Contributions 32 / 40

  75. # Clients Scalability Base Postgres Read-Only 3 – remote SSL simple queries Latency F. Coelho Introduction Best Throughput 40 Subject thousand tps Application 30 Definitions 37,639 tps 4.103 ms 156/4 pgbench 20 Performance 10 Connection 0 Latency Best Latency 0 20 40 60 80 100 120 140 160 180 Rate & Limit Storage number of clients Protocol 5,748 tps 1.042 ms 6/1 Combinations 6 Distance 5 latency ms Scalability 4 Miscellaneous 3 Conclusion Compromise 2 1 Wrap-Up Lessons 0 Contributions 0 20 40 60 80 100 120 140 160 180 number of clients 32 / 40

  76. # Clients Scalability Base Postgres Read-Only 3 – remote SSL simple queries Latency F. Coelho Introduction Best Throughput 40 Subject thousand tps Application 30 Definitions 37,639 tps 4.103 ms 156/4 pgbench 20 Performance 10 Connection 0 Latency Best Latency 0 20 40 60 80 100 120 140 160 180 Rate & Limit Storage number of clients Protocol 5,748 tps 1.042 ms 6/1 Combinations 6 Distance 5 latency ms Scalability 4 Miscellaneous 3 Conclusion Compromise 2 1 Wrap-Up Lessons 0 31,494 tps 1.837 ms 58/4 Contributions 0 20 40 60 80 100 120 140 160 180 number of clients 32 / 40

  77. # Clients Scalability Best Postgres Read-Only 3 – remote noSSL prepared PL call Latency F. Coelho Introduction Best Throughput Subject Application Definitions pgbench Performance Connection Latency Best Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Compromise Wrap-Up Lessons Contributions 33 / 40

  78. # Clients Scalability Best Postgres Read-Only 3 – remote noSSL prepared PL call Latency F. Coelho Introduction Best Throughput 200 Subject thousand tps Application 150 Definitions 181,503 tps 0.766 ms 140/4 Best pgbench 100 Base Performance 50 Connection 0 Latency Best Latency 0 20 40 60 80 100 120 140 160 180 Rate & Limit Storage number of clients Protocol Combinations Distance Scalability Miscellaneous Conclusion Compromise Wrap-Up Lessons Contributions 33 / 40

  79. # Clients Scalability Best Postgres Read-Only 3 – remote noSSL prepared PL call Latency F. Coelho Introduction Best Throughput 200 Subject thousand tps Application 150 Definitions 181,503 tps 0.766 ms 140/4 Best pgbench 100 Base Performance 50 Connection 0 Latency Best Latency 0 20 40 60 80 100 120 140 160 180 Rate & Limit Storage number of clients Protocol 39,232 tps 0.254 ms 10/2 Combinations 1.2 Distance 1 latency ms Scalability 0.8 Miscellaneous 0.6 Best Conclusion Compromise 0.4 Base 0.2 Wrap-Up Lessons 0 Contributions 0 20 40 60 80 100 120 140 160 180 number of clients 33 / 40

  80. # Clients Scalability Best Postgres Read-Only 3 – remote noSSL prepared PL call Latency F. Coelho Introduction Best Throughput 200 Subject thousand tps Application 150 Definitions 181,503 tps 0.766 ms 140/4 Best pgbench 100 Base Performance 50 Connection 0 Latency Best Latency 0 20 40 60 80 100 120 140 160 180 Rate & Limit Storage number of clients Protocol 39,232 tps 0.254 ms 10/2 Combinations 1.2 Distance 1 latency ms Scalability 0.8 Miscellaneous 0.6 Best Conclusion Compromise 0.4 Base 0.2 Wrap-Up Lessons 0 156,945 tps 0.381 ms 60/4 Contributions 0 20 40 60 80 100 120 140 160 180 number of clients 33 / 40

  81. Postgres Latency F. Coelho Introduction Subject Application Performance Comparisons Definitions pgbench Performance Miscellaneous Settings Connection Latency Rate & Limit Storage Protocol Combinations Distance Scalability Miscellaneous Conclusion Wrap-Up Lessons Contributions 34 / 40

  82. Miscellaneous Settings App & Postgres Postgres Latency Application framework? F. Coelho Introduction connection persistence Subject Application cache Memcached Redis Definitions pgbench Performance Connection Postgres configuration change defaults Latency Rate & Limit Storage disk block size random page cost Protocol Combinations Distance memory shared buffers effective cache size huge pages Scalability Miscellaneous checkpoint timeout completion target flush after Conclusion Wrap-Up wal max wal size Lessons Contributions 35 / 40

  83. Miscellaneous Settings App & Postgres Postgres Latency Application framework? F. Coelho Introduction connection persistence Subject Application cache Memcached Redis Definitions pgbench Performance Connection Postgres configuration change defaults Latency Rate & Limit Storage disk block size random page cost Protocol Combinations Distance memory shared buffers effective cache size huge pages Scalability Miscellaneous checkpoint timeout completion target flush after Conclusion Wrap-Up wal max wal size Lessons Contributions 35 / 40

  84. Miscellaneous Settings OS & Hardware Postgres Latency OS tweak and choose F. Coelho FS XFS ext4 Btrfs ZFS, mount options Introduction Subject IO io scheduler, queue length, write delay, dirty bytes. . . Application Definitions pgbench others NUMA, . . . Performance Connection Latency Rate & Limit Hardware expensive is (probably) better Storage Protocol Combinations Distance diskS tables wal logs, HDD-with-cache, SSD Scalability Miscellaneous tweaking read ahead, write flush Conclusion Wrap-Up RAID with large caches, BBU Lessons Contributions 36 / 40

Recommend

Measuring and Reducing Postgres Transaction Latency (updated version) Fabien Coelho MINES

Measuring and Reducing Postgres Transaction Latency (updated version) Fabien Coelho MINES

Measuring and Reducing Postgres Transaction Latency (updated version) Fabien Coelho MINES ParisTech, PSL Research University pgDay Paris March 23, 2017 1 / 41 Postgres Latency Talk Outline 1 Introduction Subject Typical Web

645 views • 42 slides
High Volume Low Latency Transaction Processing Transaction Processing Presenters : Tony Harrop,

High Volume Low Latency Transaction Processing Transaction Processing Presenters : Tony Harrop,

High Volume Low Latency Transaction Processing Transaction Processing Presenters : Tony Harrop, CTO, LiquidityHub Jeremy Vickers, Technical Architect, Detica 12 th March Date : Reference : QCon 2008 Client Confidential Contents Overview of

352 views • 19 slides
Reducing Latency for Linux Transport Per Hurtig Karlstad University Andreas Petlund Simula

Reducing Latency for Linux Transport Per Hurtig Karlstad University Andreas Petlund Simula

Reducing Latency for Linux Transport Per Hurtig Karlstad University Andreas Petlund Simula Research Laboratory Dublin 06.10.2015 RITE Reducing Internet Transport Latency Reducing Internet Transport Latency The EU-project RITE : Partners

656 views • 42 slides
Reducing input latency on the web bit.ly/reduce-input-latency W3C Games Workshop - June 2019

Reducing input latency on the web bit.ly/reduce-input-latency W3C Games Workshop - June 2019

Reducing input latency on the web bit.ly/reduce-input-latency W3C Games Workshop - June 2019 Navid Zolghadr (nzolghadr@chromium.org) Chromium Input Dev Chromium input dev Goals Smooth user interaction and scrolling across the

295 views • 18 slides
Low-Latency Transaction Execution on Graphics Processors: Dream or Reality? Iya Arefyeva,

Low-Latency Transaction Execution on Graphics Processors: Dream or Reality? Iya Arefyeva,

Workgroup Databases and Software Engineering University of Magdeburg Low-Latency Transaction Execution on Graphics Processors: Dream or Reality? Iya Arefyeva, Gabriel Campero Durand, Marcus Pinnecke, David Broneske , Gunter Saake Motivation:

389 views • 35 slides
Latency Reducing TCP modifications for thin-stream interactive applications Andreas Petlund /

Latency Reducing TCP modifications for thin-stream interactive applications Andreas Petlund /

Latency Reducing TCP modifications for thin-stream interactive applications Andreas Petlund / Kristian Evensen Simula Research Laboratory / University of Oslo LinuxKongress 2008 - Hamburg - Germany A long time ago in a game-company far,

233 views • 21 slides
Reducing Latency in Multi-Tenant Data Centers via Cautious Congestion Watch The 49th

Reducing Latency in Multi-Tenant Data Centers via Cautious Congestion Watch The 49th

Reducing Latency in Multi-Tenant Data Centers via Cautious Congestion Watch The 49th International Conference on Parallel Processing (ICPP) 2020 Ahmed M. Abdelmoniem, Hengky Susanto, and Brahim Bensaou Outline Introduction and background

498 views • 31 slides
Reducing Web Latency: The Virtue of Gentle Aggression Tobias Flach , Nandita Dukkipati, Andreas

Reducing Web Latency: The Virtue of Gentle Aggression Tobias Flach , Nandita Dukkipati, Andreas

Reducing Web Latency: The Virtue of Gentle Aggression Tobias Flach , Nandita Dukkipati, Andreas Terzis, Barath Raghavan, Yuchung Cheng, Neal Cardwell, Ankur Jain, Shuai Hao, Ethan Katz-Bassett, and Ramesh Govindan USC & Google August 14,

1k views • 36 slides
Reducing SSD Read Latency via NAND Flash Program and Erase Suspension Guanying Wu and Xubin He

Reducing SSD Read Latency via NAND Flash Program and Erase Suspension Guanying Wu and Xubin He

Reducing SSD Read Latency via NAND Flash Program and Erase Suspension Guanying Wu and Xubin He {wug, xhe2}@vcu.edu Department of Electrical and Computer Engineering Virginia Commonwealth University Richmond, VA This research is sponsored in

364 views • 18 slides
Deep Postgres Extensions in Rus t: postgres-extension.rs Jefg Davis <jdavis@postgresql.org>

Deep Postgres Extensions in Rus t: postgres-extension.rs Jefg Davis <jdavis@postgresql.org>

Deep Postgres Extensions in Rus t: postgres-extension.rs Jefg Davis <jdavis@postgresql.org> Citus Data / Microsoft <Jefgrey.Davis@microsoft.com> Motivation Postgres relies on an ecosystem of extensions This is a good thing!

380 views • 16 slides
Postgres As Your New DevStack Postgres as a tool for software developers Susanne Schmidt

Postgres As Your New DevStack Postgres as a tool for software developers Susanne Schmidt

Postgres As Your New DevStack Postgres as a tool for software developers Susanne Schmidt PGConf.eu 2018 (Lisbon) Who am I? Susanne Su-Shee Schmidt tech lead at SysEleven GmbH (Berlin) political scientist by education doing open source

426 views • 29 slides
Viva, the NoSQL Postgres ! Oleg Bartunov Lomonosov Moscow University, Postgres Professional

Viva, the NoSQL Postgres ! Oleg Bartunov Lomonosov Moscow University, Postgres Professional

Viva, the NoSQL Postgres ! Oleg Bartunov Lomonosov Moscow University, Postgres Professional FOSDEM, Feb 4, 2018, Brussels, Belgium Oleg Bartunov M ajor PostgreSQL contributor CEO, Postgres Professional Research scientst at Lomonosov

551 views • 26 slides
Reducing Latency in Linux Wireless Network Drivers Tim Shepard shep@alum.mit.edu netdev 1.1

Reducing Latency in Linux Wireless Network Drivers Tim Shepard shep@alum.mit.edu netdev 1.1

Proceedings of NetDev 1.1: The Technical Conference on Linux Networking (February 10th-12th 2016. Seville, Spain) Reducing Latency in Linux Wireless Network Drivers Tim Shepard shep@alum.mit.edu netdev 1.1 Sevilla, Espa na 10-12

520 views • 18 slides
Measuring the impacts of the Preempt-RT patch maxime.chevallier@smile.fr October 25, 2017 RT

Measuring the impacts of the Preempt-RT patch maxime.chevallier@smile.fr October 25, 2017 RT

Measuring the impacts of the Preempt-RT patch maxime.chevallier@smile.fr October 25, 2017 RT Linux projects Simulation platform : bi-xeon, lots ot RAM 200 s wakeup latency, networking Test bench : Intel atom 1s max latency, I/O and networking

431 views • 25 slides
Reducing the Latency-Tail of Short-Lived Flows: Adding Forward Error Correction in Data Centers

Reducing the Latency-Tail of Short-Lived Flows: Adding Forward Error Correction in Data Centers

ATP TCP Reducing the Latency-Tail of Short-Lived Flows: Adding Forward Error Correction in Data Centers Klaus-Tycho Foerster Demian Jaeger David Stolz Roger Wattenhofer ETH Zurich 1 Time is Money 2 Datacenter Traffic S. Kandula et al.,

138 views • 10 slides
Measuring and Optimizing Tail Latency Kathryn S McKinley, Google CRA-W Undergraduate Town Hall

Measuring and Optimizing Tail Latency Kathryn S McKinley, Google CRA-W Undergraduate Town Hall

Measuring and Optimizing Tail Latency Kathryn S McKinley, Google CRA-W Undergraduate Town Hall April 5 th , 2018 Speaker & Moderator The image part with relationship ID rId3 was not found in the file. Lori Pollock Kathryn S McKinley Dr.

677 views • 40 slides
Towards 0-Latency Durability Sang-Won Lee (swlee@skku.edu) Ack.: Moon, Yang, Oh and SKKU VLDB

Towards 0-Latency Durability Sang-Won Lee (swlee@skku.edu) Ack.: Moon, Yang, Oh and SKKU VLDB

Towards 0-Latency Durability Sang-Won Lee (swlee@skku.edu) Ack.: Moon, Yang, Oh and SKKU VLDB Lab. Members NVRAMOS 2014 1 NVRAM is for 0-latency Durability 2 (DB) Transaction and ACID E.g. 100$ transfer from A to B account BUFFER POOL

634 views • 32 slides
Cache Management Improving Memory Locality and Reducing Memory Latency Introduction Memory

Cache Management Improving Memory Locality and Reducing Memory Latency Introduction Memory

Cache Management Improving Memory Locality and Reducing Memory Latency Introduction Memory system performance is critical in modern architectures DO I = 1, M Accessing memory takes much longer than DO J = 1, N accessing cache

364 views • 19 slides
NoSQL Postgres Oleg Bartunov Ivan Panchenko Postgres Professional Moscow University PGDay,

NoSQL Postgres Oleg Bartunov Ivan Panchenko Postgres Professional Moscow University PGDay,

NoSQL Postgres Oleg Bartunov Ivan Panchenko Postgres Professional Moscow University PGDay, Saint Petersburg, July 6, 2017 NoSQL () Relatjonal DBMS - integratjonal All APPs communicatjes through RDBMS SQL

1.02k views • 70 slides
Measuring Query Latency of Top Level DNS Servers Jinjin Liang 1 , Jian Jiang 1 , Haixin Duan 1 ,

Measuring Query Latency of Top Level DNS Servers Jinjin Liang 1 , Jian Jiang 1 , Haixin Duan 1 ,

Measuring Query Latency of Top Level DNS Servers Jinjin Liang 1 , Jian Jiang 1 , Haixin Duan 1 , Kang Li 2 and Jianping Wu 1 Tsinghua University, China 1 University of Georgia 2 PAM 2013 DNS Overview Domain Name System Translate domain

1.28k views • 42 slides
The War on Latency Reducing Dead Time Kirk Pepperdine Principle Kodewerk Ltd. Me Work as a

The War on Latency Reducing Dead Time Kirk Pepperdine Principle Kodewerk Ltd. Me Work as a

Kodewerk tm Java Performance Services The War on Latency Reducing Dead Time Kirk Pepperdine Principle Kodewerk Ltd. Me Work as a performance tuning freelancer Nominated Sun Java Champion www.kodewerk.com kirk.blog-city.com

666 views • 51 slides
Whats Missing For Postgres Monitoring @LukasFittl l t t i F s a k u L @ What are

Whats Missing For Postgres Monitoring @LukasFittl l t t i F s a k u L @ What are

Whats Missing For Postgres Monitoring @LukasFittl l t t i F s a k u L @ What are the problems with Postgres monitoring? Its incomplete. Its hard to access & understand. It contains sensitive information. Its

1.19k views • 60 slides
Reducing Dynamic Compilation Latency Igor Bhm P rocessor A utomated S ynthesis by i T erative A

Reducing Dynamic Compilation Latency Igor Bhm P rocessor A utomated S ynthesis by i T erative A

LLVM12 - European Conference, London Reducing Dynamic Compilation Latency Igor Bhm P rocessor A utomated S ynthesis by i T erative A nalysis The U niversity o f E dinburgh LLVM12 - European Conference, London Concurrent and Parallel

1.32k views • 81 slides
1 Transaction State Transaction State (Cont.) Active, the initial state; the transaction stays in

1 Transaction State Transaction State (Cont.) Active, the initial state; the transaction stays in

Transactions Transaction Concept Transaction State Implementation of Atomicity and Durability Lecture 8 Concurrent Executions Transactions Serializability Recoverability Implementation of Isolation Transaction

376 views • 6 slides

More recommend