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

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Sat, Apr 13, 2013 at 9:55 AM, Ascot Moss <ascot.moss@xxxxxxxxx> 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