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
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 |