Thread: BUG #1910: pg_autovacuum failed on a table with very frequent r/w access
BUG #1910: pg_autovacuum failed on a table with very frequent r/w access
From
"Antoine Bajolet"
Date:
The following bug has been logged online: Bug reference: 1910 Logged by: Antoine Bajolet Email address: antoine.bajolet@tdf.fr PostgreSQL version: 8.0.2 Operating system: Linux 2.4.29 Description: pg_autovacuum failed on a table with very frequent r/w access Details: Hello, Whe have a Database with one table supporting near one throusand INSERT/DELETE per second. pg_autovacuum is working on the server. After near one month of working without trouble, CPU load and System load started to increase constantly each day to the midnight vacuum full, which still works. Launching pg_vacuum -d 3 (debug), there are significant values on this table : [2005-09-26 15:46:21 CEST] INFO: table name: cristal_tnt_bpre."public"."cache_alarme_en_cours" [2005-09-26 15:46:21 CEST] INFO: relid: 29710; relisshared: 0 [2005-09-26 15:46:21 CEST] INFO: reltuples: 366.000000; relpages: 328 [2005-09-26 15:46:21 CEST] INFO: curr_analyze_count: -1082773107; curr_vacuum_count: 2147483647 [2005-09-26 15:46:21 CEST] INFO: last_analyze_count: -1082773107; last_vacuum_count: 2147483647 [2005-09-26 15:46:21 CEST] INFO: analyze_threshold: 566; vacuum_threshold: 1732 It seems to me strange to find *negative* values in curr_analyze_count, and curr_vacuum_count is equal to 2^31 - 1, the maximum value for a 32bits signed integer. VACUUM FULL ANALYZE don't resets thoses values (i'm not a postgresql expert to know what those values means). The only solution i found is to add a cron launching vacuumdb -z each minute on this table : The whole system is in production state and can't be easely restarted. It seems to be an error in pg_autovacuum, or perhaps on the statistics collector. Best regards, Antoine Bajolet