Re: Fix parallel vacuum buffer usage reporting - Mailing list pgsql-hackers

On Tue, Apr 30, 2024 at 3:34 PM Anthonin Bonnefoy
<anthonin.bonnefoy@datadoghq.com> wrote:
>
> I've done some additional tests to validate the reported numbers. Using pg_statio, it's possible to get the minimum
numberof 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.
>

Thank you for further testing! I've pushed the patch.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com