Skip site navigation (1) Skip section navigation (2)

Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum

From: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Robert Haas <robertmhaas(at)gmail(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Florian Helmberger <fh(at)25th-floor(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: 2011-05-29 23:32:54
Message-ID: BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackers
2011/5/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> =?ISO-8859-1?Q?C=E9dric_Villemain?= <cedric(dot)villemain(dot)debian(at)gmail(dot)com> writes:
>> 2011/5/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> OK, do you like the attached version of that logic?  (Other fragments
>>> of the patch as before.)
>
>> The idea was that remove only one page from the VACUUM will prevent
>> relfrozenxid update and reltuples (and relpages) update.
>> Now, I beleive that once we've skip at least one page thanks to
>> SKIP_PAGES_THRESHOLD, then we should be more agressive and remove as
>> many as possible pages from the VACUUM, tks to the VM.
>
> That would require proof, not just suggestion.  Skipping pages will
> defeat the OS read-ahead algorithm, and so could easily cost more than
> reading them.

Correct, it needs proof. Parenthesis: I did learn also that reading
the first block of a file make read-ahead have its larger window from
the beginning (the one that posix_fadvise_sequential set too), so
remove those initial reads might be counter-productive also. But this
is damn hard to benchmark because the read ahead is also influenced by
memory pressure for example.

From theory, 1. readahead algo is a bit smarter and can work with
read-with-holes (if the holes are not larger than the read-ahead
window) and 2. if holes are that large then maybe it is not so good to
keep a larger read-ahead window (which keep trashing our buffer
cache).




-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

In response to

pgsql-hackers by date

Next:From: Greg SmithDate: 2011-05-30 02:04:20
Subject: Re: pgbench--new transaction type
Previous:From: Joe AbbateDate: 2011-05-29 22:36:52
Subject: Re: Getting a bug tracker for the Postgres project

pgsql-admin by date

Next:From: Rabi JayDate: 2011-05-30 00:07:52
Subject: Insert statement deletes older table records
Previous:From: Pavan DeolaseeDate: 2011-05-29 17:19:00
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group