From: | Anthonin Bonnefoy <anthonin(dot)bonnefoy(at)datadoghq(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Fix parallel vacuum buffer usage reporting |
Date: | 2024-02-09 09:09:43 |
Message-ID: | CAO6_XqrQk+QZQcYs_C6nk0cMfHuUWk85vT9CrcA1NffFbAVE2A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
With a db setup with pgbench, we add an additional index:
CREATE INDEX ON pgbench_accounts(abalance)
And trigger several updates and vacuum to reach a stable amount of
dirtied pages:
UPDATE pgbench_accounts set abalance = abalance + 1 WHERE aid=1; CHECKPOINT;
VACUUM (VERBOSE, INDEX_CLEANUP ON) pgbench_accounts
The vacuum will report the following:
INFO: vacuuming "postgres.public.pgbench_accounts"
INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
INFO: finished vacuuming "postgres.public.pgbench_accounts": index scans: 1
...
buffer usage: 122 hits, 165 misses, 4 dirtied
4 pages were reported dirtied. However, we have 5 dirtied blocks at
the end of the vacuum when looking at pg_buffercache:
SELECT c.relname, b.relfilenode
FROM
pg_buffercache b LEFT JOIN pg_class c
ON b.relfilenode =
pg_relation_filenode(c.oid)
WHERE isdirty=true;
relname | relfilenode
-------------------------------+-------------
pg_class | 1259
pgbench_accounts | 16400
pgbench_accounts | 16400
pgbench_accounts_pkey | 16408
pgbench_accounts_abalance_idx | 16480
The missing dirty block comes from the parallel worker vacuuming the
abalance index. Running vacuum with parallel disabled will give the
correct result.
Vacuum uses dedicated VacuumPage{Hit,Miss,Dirty} globals to track
buffer usage. However, those values are not collected at the end of
parallel vacuum workers, leading to incorrect buffer count.
Those vacuum specific globals are redundant with the existing
pgBufferUsage and only used in the verbose output. This patch removes
them and replaces them by pgBufferUsage which is already correctly
collected at the end of parallel workers, fixing the buffer count.
Regards,
Anthonin
Attachment | Content-Type | Size |
---|---|---|
v01-0001-Fix-parallel-vacuum-buffer-usage-reporting.patch | application/octet-stream | 9.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Maiquel Grassi | 2024-02-09 09:18:44 | RE: Psql meta-command conninfo+ |
Previous Message | Andy Fan | 2024-02-09 09:05:21 | Re: Extract numeric filed in JSONB more effectively |