Обсуждение: VACUUM always makes all pages dirty

VACUUM in 8.3dev always makes all pages dirty even if there are no jobs.
In 8.2.5, VACUUM produces no dirty pages in the same workload. Therefore,
VACUUM on 8.3 takes longer time than 8.2. I doubt some bugs in the
HOT-related codes here, but I cannot point out the actual position yet...

Do you have any idea on this issue?

----# CREATE VIEW buffers AS    SELECT nbuf, ndirty, N.nspname AS schemaname, relname    FROM (SELECT count(*) AS nbuf,
        sum(CASE WHEN isdirty THEN 1 ELSE 0 END) AS ndirty,          relnamespace, relname      FROM pg_buffercache B,
pg_classC      WHERE B.relfilenode = C.relfilenode GROUP BY relnamespace, relname) AS T    LEFT JOIN pg_namespace N ON
relnamespace= N.oid    ORDER BY schemaname, relname;
 
# BEGIN;# CREATE TABLE test (i integer, filler char(100) default '');# INSERT INTO test(i) SELECT generate_series(1,
1000000);#COMMIT;# SELECT * FROM buffers WHERE relname = 'test';   nbuf  | ndirty | schemaname | relname
-------+--------+------------+---------  17242 |  17242 | public     | test
 

First query makes all pages dirty because of hit bits.# CHECKPOINT;# SELECT count(*) FROM test;    count  ---------
1000000#SELECT * FROM buffers WHERE relname = 'test';   nbuf  | ndirty | schemaname | relname
-------+--------+------------+---------  17242 |  17242 | public     | test
 

First vacuum makes all pages dirty. Why?# CHECKPOINT;# VACUUM test;# SELECT * FROM buffers WHERE relname = 'test';
nbuf | ndirty | schemaname | relname  -------+--------+------------+---------   17242 |  17242 | public     | test
 

Second vacuum makes all pages dirty, too. Why?# CHECKPOINT;# VACUUM test;# SELECT * FROM buffers WHERE relname =
'test';  nbuf  | ndirty | schemaname | relname  -------+--------+------------+---------   17242 |  17242 | public     |
test

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

ITAGAKI Takahiro wrote:
> VACUUM in 8.3dev always makes all pages dirty even if there are no jobs.
> In 8.2.5, VACUUM produces no dirty pages in the same workload. Therefore,
> VACUUM on 8.3 takes longer time than 8.2. I doubt some bugs in the
> HOT-related codes here, but I cannot point out the actual position yet...

Yeah, it's definitely a HOT-introdued thing. Vacuum calls
heap_page_prune on every page, and this in heap_page_prune is dirtying
the buffer:

>     else
>     {
>         /*
>          * If we didn't prune anything, we have nonetheless updated the
>          * pd_prune_xid field; treat this as a non-WAL-logged hint.
>          */
>         SetBufferCommitInfoNeedsSave(buffer);
>     }

I don't have time to dig deeper at this moment. I'll take a look later
today, unless someone beats me to it. We obviously don't want to call
SetBufferCommitInfoNeedsSave if we didn't really change the pd_prune_xid
field.

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com

"Pavan Deolasee" <pavan.deolasee@gmail.com> writes:
> The attached patch should fix this. We mark the buffer dirty only if there
> is any state change in the page header.

Applied, with minor additional tweak to avoid duplicate calls to
SetBufferCommitInfoNeedsSave --- that seems (just) expensive enough
to be worth the trouble.
        regards, tom lane