| From: | Anthonin Bonnefoy <anthonin(dot)bonnefoy(at)datadoghq(dot)com> |
|---|---|
| To: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
| Cc: | Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Fix parallel vacuum buffer usage reporting |
| Date: | 2024-04-30 06:33:50 |
| Message-ID: | CAO6_Xqr7_Yp8cJ_6HbVxaWW=owj4VJFbzcjmjgS4Y0CS7hL0TQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
I've done some additional tests to validate the reported numbers. Using
pg_statio, it's possible to get the minimum number of block hits (Full
script attached).
-- Save block hits before vacuum
SELECT pg_stat_force_next_flush();
SELECT heap_blks_hit, idx_blks_hit FROM pg_statio_all_tables where
relname='vestat' \gset
vacuum (verbose, index_cleanup on) vestat;
-- Check the difference
SELECT pg_stat_force_next_flush();
SELECT heap_blks_hit - :heap_blks_hit as delta_heap_hit,
idx_blks_hit - :idx_blks_hit as delta_idx_hit,
heap_blks_hit - :heap_blks_hit + idx_blks_hit - :idx_blks_hit as sum
FROM pg_statio_all_tables where relname='vestat';
Output:
...
buffer usage: 14676 hits, 0 misses, 667 dirtied
buffer usage (new): 16081 hits, 0 misses, 667 dirtied
...
-[ RECORD 1 ]--+------
delta_heap_hit | 9747
delta_idx_hit | 6325
sum | 16072
From pg_statio, we had 16072 blocks for the relation + indexes.
Pre-patch, we are under reporting with 14676.
Post-patch, we have 16081. The 9 additional block hits come from vacuum
accessing catalog tables like pg_class or pg_class_oid_index.
| Attachment | Content-Type | Size |
|---|---|---|
| vacuum_block_with_pgstatio.sql | application/octet-stream | 842 bytes |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Amit Kapila | 2024-04-30 06:34:17 | Re: speed up a logical replica setup |
| Previous Message | Ashutosh Bapat | 2024-04-30 06:19:33 | Re: tablecmds.c/MergeAttributes() cleanup |