Re: finding tables about to be vacuum freezed

Поиск
Список
Период
Сортировка
От Steve Kehlet
Тема Re: finding tables about to be vacuum freezed
Дата
Msg-id CA+bfosGg9QkrfB-9sRmG0_AKH4hm6MWx8rd-e83iHhxiBH0+tA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: finding tables about to be vacuum freezed  (Jeff Janes <jeff.janes@gmail.com>)
Ответы Re: finding tables about to be vacuum freezed
Список pgsql-general
On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.janes@gmail.com> wrote:
vacuum_freeze_table_age controls when it promotes a vacuum *which is already going to occur* so that it scans the whole table.  It doesn't specially schedule a vacuum to occur.  When those tables see enough activity to pass autovacuum_vacuum_scale_factor then the vacuum that occurs will get promoted to be a full scan.

Thank you Jeff, that really helps understand this.
 
If they never see that amount of activity, then the tables will continue to age until autovacuum_freeze_max_age, at which point a vacuum will get launched specifically for the purpose of advancing relfrozenxid.

So based on this, I created this query to show autovacuum and analyze data, including if the next autovacuum will be a freeze; and how close the table is to being force-freezed. This borrows heavily from queries I found at Heroku (https://github.com/heroku/heroku-pg-extras/blob/master/lib/heroku/command/pg.rb):


My output looks like:


I'm thinking I'm in for a world of pain when all my tables in my 3.5TB db simultaneously hit autovacuum_freeze_max_age, and I think I'm about 83% of the way there. 

What do you think? I'm thinking I should start doing a VACUUM FREEZE on tables at night to head this off.

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: finding tables about to be vacuum freezed
Следующее
От: Yves Dorfsman
Дата:
Сообщение: detached query?