Re: Use pgBufferUsage for block reporting in analyze
Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
To: Karina Litskevich <litskevichkarina@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>,
PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-07-08T09:35:19Z
Lists: pgsql-hackers
Attachments
- v3-0001-Use-pgBufferUsage-for-block-reporting-in-analyze.patch (application/octet-stream) patch v3-0001
- v3-0002-Output-buffer-and-wal-usage-with-verbose-analyze.patch (application/octet-stream) patch v3-0002
- vacuum_analyze_buffer_usage.sql (application/octet-stream)
Hi,
Thanks for the review, I've updated the patches with the suggestions:
- moved renaming of misses to reads to the first patch
- added intermediate variables for total blks usage
I've also done some additional tests using the provided
vacuum_analyze_buffer_usage.sql script. It relies on
pg_stat_statements to check the results (only pgss gives information
on dirtied buffers). It gives the following output:
psql:vacuum_analyze_buffer_usage.sql:21: INFO: vacuuming
"postgres.pg_temp_7.vacuum_blks_stat_test"
...
buffer usage: 105 hits, 3 reads, 6 dirtied
...
query | sum_hit | sum_read | sum_dirtied
--------------------+---------+----------+-------------
VACUUM (VERBOSE... | 105 | 3 | 6
For vacuum, we have the same results with SKIP_DATABASE_STATS. Without
this setting, we would have block usage generated by
vac_update_datfrozenxid outside of vacuum_rel and therefore not
tracked by the verbose output. For the second test, the second patch
is needed to have ANALYZE (VERBOSE) output the block usage. It will
output the following:
psql:vacuum_analyze_buffer_usage.sql:29: INFO: analyzing
"pg_temp_7.vacuum_blks_stat_test"
...
buffer usage: 84 hits, 33 reads, 2 dirtied
...
query | sum_hit | sum_read | sum_dirtied
---------------------+---------+----------+-------------
ANALYZE (VERBOSE... | 91 | 38 | 2
There's additional buffer hits/reads reported by pgss, those are from
analyze_rel opening the relations in try_relation_open and are not
tracked by the ANALYZE VERBOSE.
Commits
-
Add WAL usage reporting to ANALYZE VERBOSE output.
- bb7775234273 18.0 landed
-
Add resource statistics reporting to ANALYZE VERBOSE.
- 4c1b4cdb86a7 18.0 landed
-
Use pgBufferUsage for buffer usage tracking in analyze.
- c584781bcc68 18.0 landed
-
Fix parallel vacuum buffer usage reporting.
- 5cd72cc0c501 17.0 cited