Re: Berserk Autovacuum (let's save next Mandrill)
| От | Darafei "Komяpa" Praliaskouski |
|---|---|
| Тема | Re: Berserk Autovacuum (let's save next Mandrill) |
| Дата | |
| Msg-id | CAC8Q8tJWQJYToVKww6W5G8dNQGkGZe+E3b8JP5POwcjc=gWPpg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Berserk Autovacuum (let's save next Mandrill) (Masahiko Sawada <sawada.mshk@gmail.com>) |
| Ответы |
Re: Berserk Autovacuum (let's save next Mandrill)
|
| Список | pgsql-hackers |
Hi,
> > Why not select a table that has inserts, updates and deletes for autovacuum just like we do for autoanalyze, not only deletes and updates like we do now?
>
> Sounds like a good idea, although I do agree with Alvaro when he
> mentions that it would be good to only invoke a worker that was only
> going to freeze tuples and not look at the indexes.
The invoking autovacuum on table based on inserts, not only deletes
and updates, seems good idea to me. But in this case, I think that we
can not only freeze tuples but also update visibility map even when
setting all-visible. Roughly speaking I think vacuum does the
following operations.
1. heap vacuum
2. HOT pruning
Is it worth skipping it if we're writing a page anyway for the sake of hint bits and new xids? This will all be no-op anyway on append-only tables and happen only when we actually need something?
3. freezing tuples
4. updating visibility map (all-visible and all-frozen)
These two are needed, and current autovacuum launch process does not take into account that this is also needed for non-dead tuples.
5. index vacuum/cleanup
There is a separate patch for that. But, since https://commitfest.postgresql.org/16/952/ for almost a year already Postgres skips index cleanup on tables without new dead tuples, so this case is taken care of already?
6. truncation
This shouldn't be a heavy operation?
With the proposed patch[1] we can control to do 5 or not. In addition
to that, another proposed patch[2] allows us to control 6.
For append-only tables (and similar tables), what we periodically want
to do would be 3 and 4 (possibly we can do 2 as well). So maybe we
need to have both an option of (auto)vacuum to control whether to do 1
and something like a new autovacuum threshold (or an option) to invoke
the vacuum that disables 1, 5 and 6. The vacuum that does only 2, 3
and 4 would be much cheaper than today's vacuum and anti-wraparound
vacuum would be able to skip almost pages.
Why will we want to get rid of 1? It's a noop from write perspective and saves a scan to do it if it's not noop.
Why make it faster in emergency situations when situation can be made non-emergency from the very beginning instead?
[1] https://commitfest.postgresql.org/22/1817/
[2] https://commitfest.postgresql.org/22/1981/
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
В списке pgsql-hackers по дате отправления: