Fix parallel vacuum buffer usage reporting

Поиск
Список
Период
Сортировка
От Anthonin Bonnefoy
Тема Fix parallel vacuum buffer usage reporting
Дата
Msg-id CAO6_XqrQk+QZQcYs_C6nk0cMfHuUWk85vT9CrcA1NffFbAVE2A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Fix parallel vacuum buffer usage reporting  (Masahiko Sawada <sawada.mshk@gmail.com>)
Список 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

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Andy Fan
Дата:
Сообщение: Re: Extract numeric filed in JSONB more effectively
Следующее
От: Maiquel Grassi
Дата:
Сообщение: RE: Psql meta-command conninfo+