Re: Use pgBufferUsage for block reporting in analyze

From: Anthonin Bonnefoy <anthonin(dot)bonnefoy(at)datadoghq(dot)com>
To: Karina Litskevich <litskevichkarina(at)gmail(dot)com>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Use pgBufferUsage for block reporting in analyze
Date: 2024-07-08 09:35:19
Message-ID: CAO6_XqrdfDen3EivTRHQjeijcog9qkoFpr2g28+jZcv7Cxf9EA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Attachment Content-Type Size
v3-0001-Use-pgBufferUsage-for-block-reporting-in-analyze.patch application/octet-stream 9.8 KB
v3-0002-Output-buffer-and-wal-usage-with-verbose-analyze.patch application/octet-stream 5.6 KB
vacuum_analyze_buffer_usage.sql application/octet-stream 1.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2024-07-08 09:38:22 Re: Interrupts vs signals
Previous Message Aleksander Alekseev 2024-07-08 09:30:09 Re: XID formatting and SLRU refactorings (was: Add 64-bit XIDs into PostgreSQL 15)