Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Visualizing Postgres I/O Performance for Develo...

Melanie
June 12, 2023

Visualizing Postgres I/O Performance for Development

Melanie

June 12, 2023
Tweet

More Decks by Melanie

Other Decks in Technology

Transcript

  1. 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
  2. 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
  3. backend_flush_after 1MB finishes faster pgbench, 10 MB file COPY 16

    clients 700 transactions 20 GB shared buffers
  4. Without doing initdb first, 2000 COPY FROMs complete sooner pgbench,

    1 MB file COPY 16 clients 2000 transactions 20 GB shared buffers
  5. Fewer flush requests because each one takes longer and WAL

    file allocation takes longer with bigger WAL segment size
  6. 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
  7. Larger read_ahead_kb finishes slightly sooner pgbench, SELECT * FROM large_table

    5 GB table 1 client 3 transactions 8 GB shared buffers
  8. 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
  9. COPY FROMs with larger wal_buffers finish faster pgbench, 20MB file,

    COPY FROM 16 clients 100 transactions 10 GB shared buffers
  10. 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