Re: Autovacuum doesn't work if the table has large number of records - Mailing list pgsql-general

From Jeff Janes
Subject Re: Autovacuum doesn't work if the table has large number of records
Date
Msg-id CAMkU=1wvAgN6PN7C13jSwrqpnnC11Mpo7ODh-+dN2fxTNmEfog@mail.gmail.com
Whole thread Raw
In response to Autovacuum doesn't work if the table has large number of records  (Ascot Moss <ascot.moss@gmail.com>)
Responses Re: Autovacuum doesn't work if the table has large number of records
List 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 by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: Git host for postgresql related projects
Next
From: "Francisco Figueiredo Jr."
Date:
Subject: Re: Git host for postgresql related projects