Обсуждение: 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 --