From: | Rob Emery <re-pgsql(at)codeweavers(dot)net> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Vacuum & pg_class.relallvisible |
Date: | 2019-10-18 11:26:31 |
Message-ID: | CAPCETps-0_w4WjrnyqEcm8Ob25UVDeMuegprd1yDhL=FOTjHaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hiya,
Thanks for the reply. I should have mentioned we're 9.5.19 so I don't believe
that behaviour would apply (even though it sounds absolutely like what I want).
However with fresh eyes this morning, I can't find any tables with
age(relfrozenxid)
greater than autovacuum_freeze_max_age; so I think I misinterpreted a number
somewhere and then ended up deeply confused.
Thanks,
Rob
On 17/10/2019, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> On Thu, 2019-10-17 at 17:32 +0100, Rob Emery wrote:
>> I've been attempting to figure out if the autovacuum/vacuum process will
>> use
>> pgclass.relallvisible when vacuuming a table to know if it's able to
>> skip freezing at all.
>>
>> Basically we have tables that this query:
>> ```
>> SELECT c.oid::regclass as table_name,
>> greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
>> FROM pg_class c
>> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
>> WHERE c.relkind IN ('r', 'm')
>> ORDER BY age DESC
>> ```
>>
>> returns the age as greater than 'autovacuum_freeze_max_age' which was
>> making
>> us believe that autovacuum wasn't running.
>>
>> When we looked into the actual rows with:
>>
>> ```
>> SELECT t_infomask::bit(16) as bits, t_infomask::bit(16) & (x'0100' |
>> x'0200') as isFrozen FROM heap_page_items(get_raw_page('tablename',
>> 0))
>> ```
>> we could see that it looks like all the rows in the table are frozen;
>> so it would never need a vacuum!
>>
>> I don't understand how the autovacuum knows that it can skip that
>> table without looking at all the rows, which is the process of
>> vacuuming that table!
>>
>> Much appreciated if someone can clean up my understanding.
>
> PostgreSQL 9.6 had this new feature:
>
> Avoid re-vacuuming pages containing only frozen tuples (Masahiko Sawada,
> Robert Haas, Andres Freund)
>
> Formerly, anti-wraparound vacuum had to visit every page of a table, even
> pages where there
> was nothing to do. Now, pages containing only already-frozen tuples are
> identified in the table's
> visibility map, and can be skipped by vacuum even when doing transaction
> wraparound prevention.
> This should greatly reduce the cost of maintaining large tables containing
> mostly-unchanging data.
>
> So, to the best of my knowledge (I didn't read the code),
> autovacuum should still launch an anti-wraparound worker, but that will
> look at the visibility map, determine it has nothing to do and just update
> "relfrozenxid".
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
--
Robert Emery
Infrastructure Director
01785 711633
<> Codeweavers
Phone: 0800 021 0888
Website: codeweavers.net
Barn 4, Dunston Business Village, ST18 9AB. Registered in England and
Wales No. 04092394 VAT registration no. 974 9705 63
--
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Wolf | 2019-10-21 12:50:24 | pgAgent forces postgresql-9.4 |
Previous Message | Vera Green | 2019-10-17 17:01:00 | Issue with ArcMap connection after PG 10.5 to 10.10 upgrade |