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

On Sat, Apr 13, 2013 at 9:55 AM, Ascot Moss <ascot(dot)moss(at)gmail(dot)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