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

From: Ascot Moss <ascot(dot)moss(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Autovacuum doesn't work if the table has large number of records
Date: 2013-04-13 16:55:07
Message-ID: CAGiZwvOHpHXfch5r60zDFz-wV2SnfC_x_PiG1j5r8_CvJ8HxpQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

(please ignore if duplicated)

Hi,

I am new to PostgreSQL.

I have a PostgreSQL table named "test" with 300,000,000 records inserted,
it has only two fields (id, and int i). For testing purposes, after
inserts, all records have been updated once in order to make n_dead_tup =
300,000,000

Current PostgreSQL settings:
autovacuum: On (default)
track_counts: ON (default)
autovacuum_vacuum_threshold: 50 (default)
autovacuum_vacuum_scale_factor: 0.2 (default)
the autovacuum checker should be run every minute (default)
autovacuum_freeze_max_age: 200000000 (default)
os: ubnutu 12.04
PostgreSQL: 9.2.4

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,

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

FYI: for the same machine (i.e. same o/s and same postgresql and same
postgresql conf), I have done another test case with reltuples around
60,000,000 records the autovacuum worked well.

Regards

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Figueiredo Jr. 2013-04-13 19:10:27 Git host for postgresql related projects
Previous Message Ascot Moss 2013-04-13 16:34:51 Autovacuum doesn't work