Re: Autovacuum doesn't work if the table has large number of records

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Autovacuum doesn't work if the table has large number of records
Дата
Msg-id CAMkU=1wvAgN6PN7C13jSwrqpnnC11Mpo7ODh-+dN2fxTNmEfog@mail.gmail.com
обсуждение исходный текст
Ответ на Autovacuum doesn't work if the table has large number of records  (Ascot Moss <ascot.moss@gmail.com>)
Ответы Re: Autovacuum doesn't work if the table has large number of records
Список pgsql-general
Дерево обсуждения
On Sat, Apr 13, 2013 at 9:55 AM, Ascot Moss <ascot.moss@gmail.com> wrote:

Current stat of "test" table:
pg_class.reltuples: 3.8415e+08 
pg_class.relpages: 1703069
last_autovacuum: null (or blank)
last_autoanalyze: 2013-04-13 20:27:12.396048+08
pg_stat_user_tables.n_dead_tup: 300000000

The autovacuum threshold should be about : 76,830,130 (50 + 3.8415e+08 x 0.2)

I expected the autovacuum should be run automatically to clear the dead tuples, however, after over 3 hours, by checking pg_stat_user_tables,  the last_autovacuum is still null and n_dead_tup still equals to 300000000, 

Every page is going to be both read and dirtied, so with default vacuum_cost_* settings you are going to get have 1703069 * (10+20) / 200 = 255,460.35 delays of 0.020 seconds, for  5,109.207 second of sleeping.  Plus it actually has to do the work, including fsync the WAL log about once every 32 buffers.  So it is going to take a while.

 

Can anyone advise me why the autovacuum is not running or if the autovacuum is running but it is not yet completed?

You can check if it is ongoing:

select * from pg_stat_activity where query like 'autovacuum%' \x\g\x

Cheers,

Jeff

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

Предыдущее
От: Magnus Hagander
Дата:
Сообщение: Re: Git host for postgresql related projects
Следующее
От: "Francisco Figueiredo Jr."
Дата:
Сообщение: Re: Git host for postgresql related projects