Re: Autovacuum not functioning for large tables but it is working for few other small tables.

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Autovacuum not functioning for large tables but it is working for few other small tables.
Дата
Msg-id CAHOFxGoNmFZFJyR8e_W2CingN6ofOto9MEjHYh82C5U=ZRZZZQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Autovacuum not functioning for large tables but it is working for few other small tables.  (Martín Marqués <martin.marques@gmail.com>)
Список pgsql-general
Absolutely check the logs, or do a manual vacuum verbose with setting cost delay and cost limit (and maintenance work mem) the same as the values for auto vacuum runs. It should work out the same and you could time it for a period when the system is more lightly used it applicable.

If you have many very large indexes on the tables with a high number of tuples and bloat, that may be slowing the execution particularly if your allowed work memory for the operation doesn't allow a single pass of the index.

If you are on PG12+, you can reindex concurrently and then run vacuum and see how it goes.

Freezing will automatically happen according to settings, but if it is near the threshold then it could be that autovacuum is doing more work scanning old data. A manual vacuum freeze would mitigate that. That may not be significant though.

For your larger tables, or system in general, turning down your scale factor settings will qualify tables for autovacuum sooner. If it hurts, you aren't doing it often enough.

Also, reducing cost delays may be needed to pause for less time in the middle of autovacuum executions. The default changed from 20ms to 2ms with PG12 but if your I/O system can handle it, lower may be prudent to get the work done more quickly.

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

Предыдущее
От: Martín Marqués
Дата:
Сообщение: Re: Autovacuum not functioning for large tables but it is working for few other small tables.
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: Autovacuum not functioning for large tables but it is working for few other small tables.