Visualizing Postgres I O Performance for Development
summary
Local benchmarks use pgbench plus PostgreSQL views (pg_stat_io, pg_buffercache, pg_stat_wal, pg_stat_activity), iostat/diastat, pidstat, and /proc/meminfo to explain why numbers move. Setting backend_flush_after=1MB improves cumulative TPS while iostat shows average queue size spikes aligning with latency; enabling WAL compression (zstd) raises sustained TPS and reduces spikes by shifting to larger, fewer writes; larger wal_segment_size produces higher peaks and lower troughs; initdb causes extra WAL sync cost; read_ahead helps only when device latency is ~1ms; autovacuum delay increases bloat and device utilization. A collection of scripts plus a JSON-based renderer is used to align timelines and compare runs.
AI-written summary · about
chapters (16)
- 0:00Introduction to talk
- 1:07Why visualize IO performance
- 3:36Tools and metrics used
- 7:51Understanding performance dips
- 9:13Wall compression impact
- 13:28Separating WAL and data IO
- 14:11Benchmarking setup importance
- 17:22Wall segment size effects
- 21:58Benchmark workload choices
- 25:09Kernel read ahead tuning
- 28:40Questioning vacuum assumptions
- 35:50Auto vacuum effects on cache
- 36:12Wall buffers performance
- 39:25Peaks and valleys in performance
- 43:16Designing effective benchmarks
- 44:28Q&A session
related talks
50mPerformance Archaeology
31mDemanding the impossible rigorous database benchm
26mAdditional IO Observability in Postgres with pg_stat_io
31mPostgres Performance for Humans
41mOptimizing your app by understanding your PostgreSQL database
36mAutomated Performance Testing of Postgres
41mLinux IO internals for PostgreSQL administrators in 2023
43mTuning PostgreSQL for High Write Workloads