Visualizing Postgres I/O Performance for Development

Transcript

1. Visualizing Postgres I/O Performance for Development Melanie Plageman
2. Total TPS != User Experience Total TPS 22,029 21,861
3. View Performance Metrics over Time
4. Use Multiple Systems and Tools to Gather Information
5. Storage Stack Layers
6. Metrics Sources - Postgres - pg_stat_io - pg_buffercache_summary - pg_stat_wal - pg_stat_activity waits - pg_total_relation_size() - Operating System - /proc/meminfo - pidstat - iostat - Benchmark - pgbench latency - pgbench TPS
7. Benchmark Setup For Scenarios • 16 core, 32 thread AMD CPU • Linux 5.19 • Sabrent Rocket NVMe 4.0 2TB (seq r/w 5000/4400 MBps, random r/w 750000 IOPS) • ext4 w noatime,data=writeback • 64 GB RAM • 2 MB huge pages • Postgres compiled from source at O2 • pgbench
8. Using Metrics Together to Understand the Why
9. backend_flush_after
10. backend_flush_after 1MB finishes faster pgbench, 10 MB file COPY 16 clients 700 transactions 20 GB shared buffers
11. More backend writebacks
12. Latency spikes without backend_flush_after as queue fills up
13. Kernel writing out dirty data
14. Initial TPS dip likely caused by memory pressure. Free memory hits 0
15. Second dip coincides with checkpoint
16. Using Metrics to Clarify other Metrics
17. wal_compression
18. Fewer Transactions without wal_compression pgbench, TPCB-like built-in, mode=prepared data scale 4000 16 clients 600 seconds 20 GB shared buffers
19. Higher latency and lower TPS without WAL compression
20. Fewer Full Page Writes without wal_compression because fewer transactions
21. Fewer writes/second without WAL compression
22. Higher write throughput without WAL compression, so more larger writes
23. WAL bytes higher without WAL compression, so the increased writes were WAL I/O
24. WAL syncs much higher without compression, so additional flush requests are WAL
25. Backends doing fewer writes and reads without compression. Bottlenecked on WAL I/O
26. Benchmark Setup For Correct Comparisons
27. initdb before every benchmark
28. Without doing initdb first, 2000 COPY FROMs complete sooner pgbench, 1 MB file COPY 16 clients 2000 transactions 20 GB shared buffers
29. More flush requests issued after just having done initdb
30. Waiting for WAL Init Sync after having done intidb
31. TPS dip at 40 seconds corresponds with running out of system memory
32. Increase wal_segment_size to 1GB, COPY FROMs take much longer, TPS is very spikey after initdb
33. Fewer flush requests because each one takes longer and WAL file allocation takes longer with bigger WAL segment size
34. With reduced min_wal_size and pause after loading data, performance without initdb is similar to with initdb
35. The number of flush requests is the same as with initdb
36. WAL Init Sync Waits with pause and decreased min_wal_size
37. Benchmark Configuration Choices
38. prepared vs simple
39. Higher TPS with prepared query mode vs simple
40. Additional CPU usage with simple query mode
41. Benchmark Choice and Reflecting Customer Workloads
42. data access distribution
43. Gaussian data access distribution often performs better than uniform random access and is similar to real workloads pgbench, TPCB-like built-in and custom, mode=prepared, sync commit = off data scale 4200 16 clients 500 seconds 20 GB shared buffers
44. Uniform random access does more reads and writes because working set doesn’t fit in memory
45. Usage count is low for random data access distribution
46. Backend cache hit ratio is worse for uniform random access
47. More evictions of shared buffers
48. Backends are doing more reads and writes
49. Determine when System Configurations Matter
50. readahead
51. read_ahead_kb target readahead = sequential BW * latency
52. Larger read_ahead_kb finishes slightly sooner pgbench, SELECT * FROM large_table 5 GB table 1 client 3 transactions 8 GB shared buffers
53. Read request size is much larger
54. With 1ms added latency via dmsetup delay, run with read_ahead_kb 2048 finishes in 30 seconds
55. Large request size and large read throughput
56. Questioning Your Assumptions
57. autovacuum_vacuum_cost_delay
58. TPS starts high and gradually goes down with autovacuum_vacuum_cost_delay > 0 pgbench, TPCB-like@1 + INSERT/DELETE@9 , mode=prepared data scale 4300 32 clients 600 seconds 16 GB shared buffers
59. Latency increases proportionally
60. % time I/O requests being issued is much lower with higher cost delay
61. Autovacuum mostly waiting
62. Spike in reads not from autovacuum
63. Size of the relations being thrashed increasing and backend cache hit ratio is plummeting
64. System CPU usage is increasing. Potentially caused by swapping
65. Comparing only autovacuum_vacuum_cost_delay 2ms (default) vs 0
66. Relation size relatively constant for delay = 0
67. More autovacuum cache hits and fewer reads with cost delay 0
68. More shared buffer evictions by autovacuum with default cost delay
69. Autovacuum cleaning buffers and putting them on the freelist so more unused buffers
70. No backend flushes required because there are clean buffers
71. Finding the Real Root Cause
72. wal_buffers
73. COPY FROMs with larger wal_buffers finish faster pgbench, 20MB file, COPY FROM 16 clients 100 transactions 10 GB shared buffers
74. wal_buffers are full less often
75. Smaller wal_buffers end up contending the WALInsert lock meaning they are waiting much more often
76. Smaller wal_buffers causes those runs to do less I/O overall
77. Smaller wal_buffers fill up and then cause waiting for WAL Sync
78. Much higher throughput with larger wal_buffers but how can dips be explained
79. At 20 seconds, start doing more smaller writes
80. Fewer write merges and more requests in the queue
81. Dirty data has built up, then it starts being flushed by kernel before second slowdown
82. Shared buffers fills up around 20 seconds, faster with larger wal_buffers
83. System memory fills up at 40 seconds explaining the second dip
84. Needed pages are being swapped out and have to be read back in
85. COPY FROM workload impacted by wal_buffers but a transactional workload would not be
86. Benchmarking as a Developer • Not just configuring databases but identifying bottlenecks that can be addressed with code • Understanding system interactions when designing new features and performance enhancements • Designing scenarios that put the right things under test