Greetings all,
I have been spending some time looking into how auto-vacuum is performing on one of our servers. After putting the PostgreSQL logs in debug I noticed that the threshold for ANALYZE was never being hit for a particular table because the calculated value becomes increasingly negative.
We have an entry in the pg_autovacuum table for a table that has quite frequent inserts and updates, but not deletes. Here are the values:
-[ RECORD 1 ]----+------
vacrelid | #####
enabled | t
vac_base_thresh | 500
vac_scale_factor | 0.1
anl_base_thresh | 200
anl_scale_factor | 0.05
vac_cost_delay | -1
vac_cost_limit | -1
I've noticed that the threshold for ANALYZE never gets met because the threshold for VACUUM is hit first, therefore resetting the counters. Here is a snippet of the log that shows what's happening:
DEBUG: tablename: vac: 961 (threshold 14217), anl: -9756 (threshold 7058)
DEBUG: tablename: vac: 1924 (threshold 14217), anl: -8792 (threshold 7058)
DEBUG: tablename: vac: 2953 (threshold 14217), anl: -7763 (threshold 7058)
DEBUG: tablename: vac: 3998 (threshold 14217), anl: -6718 (threshold 7058)
DEBUG: tablename: vac: 5170 (threshold 14217), anl: -5546 (threshold 7058)
DEBUG: tablename: vac: 6405 (threshold 14217), anl: -4311 (threshold 7058)
DEBUG: tablename: vac: 7635 (threshold 14217), anl: -3081 (threshold 7058)
DEBUG: tablename: vac: 8818 (threshold 14217), anl: -1898 (threshold 7058)
DEBUG: tablename: vac: 9917 (threshold 14217), anl: -798 (threshold 7058)
DEBUG: tablename: vac: 10987 (threshold 14217), anl: 272 (threshold 7058)
DEBUG: tablename: vac: 12016 (threshold 14217), anl: 1301 (threshold 7058)
DEBUG: tablename: vac: 12929 (threshold 14217), anl: 2214 (threshold 7058)
DEBUG: tablename: vac: 13717 (threshold 14217), anl: 3002 (threshold 7058)
DEBUG: tablename: vac: 14441 (threshold 14217), anl: 3727 (threshold 7058)
...vacuum threshold is hit...
DEBUG: tablename: vac: 752 (threshold 14217), anl: -9962 (threshold 7058)
DEBUG: tablename: vac: 1491 (threshold 14217), anl: -9223 (threshold 7058)
DEBUG: tablename: vac: 2213 (threshold 14217), anl: -8501 (threshold 7058)
DEBUG: tablename: vac: 2984 (threshold 14217), anl: -7730 (threshold 7058)
The outcome of this is that an ANALYZE is never run, as after the counters are reset for each VACUUM the counter for ANALYZE gets increasingly larger. But as you can see from our entries in pg_autovacuum above, an ANALYZE should occur much more frequently than a VACUUM.
We're running PostgreSQL 8.1.4 on a RHEL-3 with a 2.4.21-27.0.2.ELsmp kernel.
Input is appreciated to explain exactly what is happening here. Thanks so much!
--
Dylan Hansen
Enterprise Systems Developer