Additional I/O Observability with pg_stat_io

Transcript

1. Increased I/O Observability with pg_stat_io Postgres Performance Observability Sources and Analysis Techniques
2. Melanie Plageman • Open source Postgres hacking: executor, planner, storage, and statistics sub-systems • I/O Benchmarking and Linux kernel storage performance tuning • Recently worked on prefetching for direct I/O and I/O statistics https://github.com/melanieplageman @ Microsoft
3. Transactional Workload I/O Performance Goals High transactions per second (TPS) Consistent low latency
4. Common I/O Performance Issue Causes Working set is not in memory Spikey checkpoint I/O Autovacuum frequency too low
5. Postgres I/O Tuning Targets Shared buffers Background writer Checkpointer Autovacuum
6. Postgres I/O Statistics Views pg_stat_database • hits, reads, read time, write time pg_statio_all_tables • hits, reads pg_stat_bgwriter • backend writes, backend fsyncs pg_stat_statements • shared and local buffer hits, reads, writes, read time, write time
7. Gaps in Postgres I/O Statistics Views • Writes = flushes + extends • Reads and writes combined for all backend types • I/O combined for all contexts and on all objects
8. pg_stat_io (PG 16) • backend_type, io_object, io_context • reads*, writes*, extends*, op_bytes, hits, evictions, reuses, fsyncs*
9. Why Count Flushes and Extends Separately? pg_stat_io • write = flush • extend = extend
10. Postgres UPDATE/INSERT I/O Workflow 1. Find a disk block with enough space to fit the new data INSERT INTO foo VALUES(1,1); foo
11. Postgres UPDATE/INSERT I/O Workflow 1. Find a disk block with enough space to fit the new data i. If no block has enough free space, extend the file. INSERT INTO foo VALUES(1,1); foo
12. Postgres UPDATE/INSERT I/O Workflow 1. Find a disk block with enough space to fit the new data i. If no block has enough free space, extend the file. 2. Check for the block in shared buffers. i. If it is already loaded, cache hit! INSERT INTO foo VALUES(1,1); foo shared buffers No I/O needed
13. Postgres UPDATE/INSERT I/O Workflow 1. Find a disk block with enough space to fit the new data i. If no block has enough free space, extend the file. 2. Check for the block in shared buffers. i. If it is already loaded, success! 3. Otherwise, find a shared buffer we can use. i. If it is dirty, flush it. INSERT INTO foo VALUES(1,1); foo shared buffers flush Flush = “write” in pg_stat_io
14. Postgres UPDATE/INSERT I/O Workflow 1. Find a disk block with enough space to fit the new data i. If no block has enough free space, extend the file. 2. Check for the block in shared buffers. i. If it is already loaded, success! 3. Otherwise, find a shared buffer we can use. i. If it is dirty, flush it. 4. Read our block into the buffer. INSERT INTO foo VALUES(1,1); foo shared buffers load
15. Postgres UPDATE/INSERT I/O Workflow 1. Find a disk block with enough space to fit the new data i. If no block has enough free space, extend the file. 2. Check for the block in shared buffers. i. If it is already loaded, success! 3. Otherwise, find a shared buffer we can use. i. If it is dirty, flush it. 4. Read our block into the buffer. 5. Write our data into the buffer. INSERT INTO foo VALUES(1,1); foo (1,1) shared buffers
16. Why Count Flushes and Extends Separately? • Synchronous flushes are avoidable • Extends are unavoidable • Separating them allows tuning discretion load flush flush extend
17. Why Count Flushes and Extends Separately? • Extends are normal for bulk writes • COPY FROM does lots of extends • Data loaded may not be part of transactional workload working set R R shared buffers R R foo flush
18. Why Track I/O Per Context or Per Backend Type? pg_stat_io • backend_type • io_context
19. Postgres Autovacuum Workflow 1. Identify the next block to vacuum. foo 0, 3, 5, 6
20. Postgres Autovacuum Workflow 1. Identify the next block to vacuum. 2. Check for the block in shared buffers. i. If it is, vacuum it! (cache hit) foo shared buffers 0, 3, 5, 6
21. Postgres Autovacuum Workflow 1. Identify the next block to vacuum. 2. Check for the block in shared buffers. i. If it is, vacuum it! 3. Otherwise, find the next reserved buffer to use. i. If we are not at the reservation cap, evict a shared buffer. reservation cap: 4 used: 3 foo R shared buffers R R 0, 3, 5, 6
22. Postgres Autovacuum Workflow 1. Identify the next block to vacuum. 2. Check for the block in shared buffers. i. If it is, vacuum it! 3. Otherwise, find the next reserved buffer to use. i. If we are not at the reservation cap, evict a shared buffer. ii. If we are reusing a dirty, reserved buffer, flush it. reservation cap: 4 used: 4 foo R R shared buffers flush R R 0, 3, 5, 6
23. Postgres Autovacuum Workflow 1. Identify the next block to vacuum. 2. Check for the block in shared buffers. i. If it is, vacuum it! 3. Find the next reserved buffer to use. i. If we are not at the reservation cap, evict a shared buffer. ii. If we are reusing a dirty, reserved buffer, flush it. 4. Read the block into the buffer. reservation cap: 4 used: 4 foo R R shared buffers load R R 0, 3, 5, 6
24. Postgres Autovacuum Workflow 1. Identify the next block to vacuum. 2. Check for the block in shared buffers. i. If it is, vacuum it! 3. Find the next reserved buffer to use. i. If we are not at the reservation cap, evict a shared buffer. ii. If we are reusing a dirty, reserved buffer, flush it. 4. Read the block into the buffer. 5. Vacuum the buffer and mark it dirty. reservation cap: 4 used: 4 foo R R shared buffers R R 0, 3, 5, 6
25. Why Track I/O Per Backend Type? • Not all I/O is for blocks that are part of the working set • Autovacuum worker reads are often of older data client backend read autovacuum worker read load R load
26. Why Track I/O Per Context? • High number of reads during bulk read operations of data not in shared buffers. • Shared buffers not used for all I/O • Large* SELECTs not in shared buffers large SELECT (bulkread context) read client backend normal context cache miss foo R R shared buffers R R load load evict *large = table blocks > shared buffers / 4
27. Data-Driven Tuning with pg_stat_io
28. Background Writer Too Passive • client backend normal context writes high • background writer normal context writes high • checkpointer writes lower than bgwriter
29. Shared Buffers Too Small • client backend normal context reads high • evictions high • client backend writes/read ≈ 1 • cache hit ratio ≈ 60%
30. Cache hit ratio query SELECT (hits / (reads + hits)::float) * 100 FROM pg_stat_io WHERE backend_type = 'client backend' AND io_object = 'relation' AND io_context = 'normal';
31. Shared Buffers Not Too Small • client backend normal context reads low • client backend bulkread context reads high • vacuum reads high
32. Calculating accurate cache hit ratios pg_stat_database SELECT (sum(blks_hit) / (sum(blks_read) + sum(blks_hit))::float) * 100 FROM pg_stat_database; pg_stat_io SELECT (hits/(reads + hits)::float) * 100 FROM pg_stat_io; 45% 45%
33. Calculating accurate cache hit ratios pg_stat_database SELECT (sum(blks_hit) / (sum(blks_read) + sum(blks_hit))::float) * 100 FROM pg_stat_database; pg_stat_io SELECT (hits/(reads + hits)::float) * 100 FROM pg_stat_io WHERE backend_type = 'client backend’ AND io_object = 'relation’ AND io_context = 'normal'; 45% 99.9%
34. Future additions • “bypass” IO • per connection IO stats • consolidated WAL stats Contact me: @melanieplageman