Thread: autovacuum ignores some tables
hi, postgresql8.4.7 here. i checked the pg_stat_user_tables table, and it have a lot of rows there where the "last_autovacuum" and/or "last_autoanalyze" are null. does this mean that autovacuum never worked on those tables? roughly 70% of all the tables have null in those fields.. in those never-autovacuumed tables there are tables that are quite big, and also have a lot of activity, so it's not that they never needed vacuuming... i wonder why autovacuum ignored them. i checked my settings with "SHOW ALL" in psql, and the corresponding settings are: autovacuum on autovacuum_analyze_scale_factor 0.1 autovacuum_analyze_threshold 50 autovacuum_freeze_max_age 200000000 autovacuum_max_workers 3 autovacuum_naptime 1min autovacuum_vacuum_cost_delay 20ms autovacuum_vacuum_cost_limit -1 autovacuum_vacuum_scale_factor 0.2 autovacuum_vacuum_threshold 50 track_counts on any ideas why autovacuum ignores some of the tables? thanks, gabor
2011/6/23 Gábor Farkas <gabor@nekomancer.net>: > hi, > > postgresql8.4.7 here. > > i checked the pg_stat_user_tables table, and it have a lot of rows > there where the "last_autovacuum" and/or "last_autoanalyze" are null. > does this mean that autovacuum never worked on those tables? > > roughly 70% of all the tables have null in those fields.. > in those never-autovacuumed tables there are tables that are quite > big, and also have a lot of activity, so it's not that they never > needed vacuuming... > > i wonder why autovacuum ignored them. i checked my settings with "SHOW > ALL" in psql, and the corresponding settings are: > > autovacuum on > autovacuum_analyze_scale_factor 0.1 > autovacuum_analyze_threshold 50 > autovacuum_freeze_max_age 200000000 > autovacuum_max_workers 3 > autovacuum_naptime 1min > autovacuum_vacuum_cost_delay 20ms > autovacuum_vacuum_cost_limit -1 > autovacuum_vacuum_scale_factor 0.2 > autovacuum_vacuum_threshold 50 > track_counts on > > any ideas why autovacuum ignores some of the tables? The table may have not had enough updates or deletes to trigger a vacuum. Are these insert-only tables? When you look at pg_stat_user_tables, check the n_tup_upd and n_tup_del columns. If autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * rows in the table) > n_dead_tup in pg_stat_user_tables, then the table should be autovacuum'd. If it hasn't yet reached this number, it won't yet be a candidate. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2011/6/23 Thom Brown <thom@linux.com>: > 2011/6/23 Gábor Farkas <gabor@nekomancer.net>: >> hi, >> >> postgresql8.4.7 here. >> >> i checked the pg_stat_user_tables table, and it have a lot of rows >> there where the "last_autovacuum" and/or "last_autoanalyze" are null. >> does this mean that autovacuum never worked on those tables? >> >> roughly 70% of all the tables have null in those fields.. >> in those never-autovacuumed tables there are tables that are quite >> big, and also have a lot of activity, so it's not that they never >> needed vacuuming... >> >> i wonder why autovacuum ignored them. i checked my settings with "SHOW >> ALL" in psql, and the corresponding settings are: >> >> autovacuum on >> autovacuum_analyze_scale_factor 0.1 >> autovacuum_analyze_threshold 50 >> autovacuum_freeze_max_age 200000000 >> autovacuum_max_workers 3 >> autovacuum_naptime 1min >> autovacuum_vacuum_cost_delay 20ms >> autovacuum_vacuum_cost_limit -1 >> autovacuum_vacuum_scale_factor 0.2 >> autovacuum_vacuum_threshold 50 >> track_counts on >> >> any ideas why autovacuum ignores some of the tables? > > The table may have not had enough updates or deletes to trigger a > vacuum. Are these insert-only tables? When you look at > pg_stat_user_tables, check the n_tup_upd and n_tup_del columns. > > If autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * > rows in the table) > n_dead_tup in pg_stat_user_tables, then the table > should be autovacuum'd. If it hasn't yet reached this number, it > won't yet be a candidate. thanks for the explanation, now i understand. just to clarify: you probably meant the opposite, correct? when n_dead_tup is MORE than the threshold... gabor
2011/6/23 Gábor Farkas <gabor@nekomancer.net>: > 2011/6/23 Thom Brown <thom@linux.com>: >> 2011/6/23 Gábor Farkas <gabor@nekomancer.net>: >>> hi, >>> >>> postgresql8.4.7 here. >>> >>> i checked the pg_stat_user_tables table, and it have a lot of rows >>> there where the "last_autovacuum" and/or "last_autoanalyze" are null. >>> does this mean that autovacuum never worked on those tables? >>> >>> roughly 70% of all the tables have null in those fields.. >>> in those never-autovacuumed tables there are tables that are quite >>> big, and also have a lot of activity, so it's not that they never >>> needed vacuuming... >>> >>> i wonder why autovacuum ignored them. i checked my settings with "SHOW >>> ALL" in psql, and the corresponding settings are: >>> >>> autovacuum on >>> autovacuum_analyze_scale_factor 0.1 >>> autovacuum_analyze_threshold 50 >>> autovacuum_freeze_max_age 200000000 >>> autovacuum_max_workers 3 >>> autovacuum_naptime 1min >>> autovacuum_vacuum_cost_delay 20ms >>> autovacuum_vacuum_cost_limit -1 >>> autovacuum_vacuum_scale_factor 0.2 >>> autovacuum_vacuum_threshold 50 >>> track_counts on >>> >>> any ideas why autovacuum ignores some of the tables? >> >> The table may have not had enough updates or deletes to trigger a >> vacuum. Are these insert-only tables? When you look at >> pg_stat_user_tables, check the n_tup_upd and n_tup_del columns. >> >> If autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * >> rows in the table) > n_dead_tup in pg_stat_user_tables, then the table >> should be autovacuum'd. If it hasn't yet reached this number, it >> won't yet be a candidate. > > thanks for the explanation, now i understand. just to clarify: you > probably meant > the opposite, correct? when n_dead_tup is MORE than the threshold... Erk, yes, switch the > to a <. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company