Re: Fix parallel vacuum buffer usage reporting

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-24 09:19:26
Message-ID: CAO6_XqoVz4pBZ1H7as7CVwjUX7FLsviHW7i_BKStNNcrY+k4+g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Thanks for the review!

> I think that if the anayze command doesn't have the same issue, we
> don't need to change it.

Good point, I've wrongly assumed that analyze was also impacted but there's
no parallel analyze so the block count is correct.

> (a) make lazy vacuum use BufferUsage instead of
> VacuumPage{Hit,Miss,Dirty}. (backpatched down to pg13).
> (b) make analyze use BufferUsage and remove VacuumPage{Hit,Miss,Dirty}
> variables for consistency and simplicity (only for HEAD, if we agree).
>
I've isolated the fix in the first patch. I've kept the analyze change and
removal of VacuumPage* variables split for clarity sake.

BTW I realized that VACUUM VERBOSE running on a temp table always
> shows the number of dirtied buffers being 0, which seems to be a bug.
> The patch (a) will resolve it as well.
>
Indeed, it visible with the following test:

SET temp_buffers='1024';
CREATE TEMPORARY TABLE vacuum_fix (aid int, bid int);
INSERT INTO vacuum_fix SELECT *, * FROM generate_series(1, 1000000);
VACUUM vacuum_fix;
UPDATE vacuum_fix SET bid = bid+1;
VACUUM (VERBOSE, INDEX_CLEANUP ON) vacuum_fix;

Pre-patch:
avg read rate: 254.751 MB/s, avg write rate: 0.029 MB/s
buffer usage: 8853 hits, 8856 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 3049 bytes
The dirtied page is from pg_class (so not a local buffer)

With the fix:
avg read rate: 250.230 MB/s, avg write rate: 250.230 MB/s
buffer usage: 8853 hits, 8856 misses, 8856 dirtied
WAL usage: 1 records, 1 full page images, 3049 bytes

Attachment Content-Type Size
v4-0002-Use-pgBufferUsage-for-analyze-block-reporting.patch application/octet-stream 3.0 KB
v4-0001-Fix-parallel-and-temp-vacuum-buffer-usage-reporti.patch application/octet-stream 3.7 KB
v4-0003-Remove-unused-variables-for-vacuum-buffer-usage.patch application/octet-stream 3.9 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2024-04-24 09:19:59 Re: Use XLOG_CONTROL_FILE macro everywhere?
Previous Message Richard Guo 2024-04-24 09:13:12 Short-circuit sort_inner_and_outer if there are no mergejoin clauses