VACUUM always makes all pages dirty

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: VACUUM always makes all pages dirty
Date: 2007-10-24 07:29:19
Message-ID: 20071024155356.6120.ITAGAKI.TAKAHIRO@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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_class C
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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Richard Wang 2007-10-24 07:46:22 postgres under linux can't start because of postmaster.pid
Previous Message Simon Riggs 2007-10-24 06:59:32 Re: Feature Freeze date for 8.4