Обсуждение: Vacuum & pg_class.relallvisible

Hiya,

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.

Thanks,
-- 
Rob

<> Codeweavers

-- 

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

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.albe@cybertec.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

--