Autovacuums not triggering anymore
Autovacuums not triggering anymore
От:
Thomas SIMON <tsimon@neteven.com>
Дата:
Hi all, Since two days, autovacuum_analyze & autovacuum_vacuum does not trigger anymore on any of my databases (but they are started on db template1). Before, I had around at least 30 analyzes and 400 vacuums per hour on these DBs. Manual vacuums still works. No parameters have been changed since the problem occurs. track_counts is on, and autovacuum launcher process is working. Below autovacuums parameters : "autovacuum";"on" "autovacuum_analyze_scale_factor";"0.1" "autovacuum_analyze_threshold";"20" "autovacuum_freeze_max_age";"200000000" "autovacuum_max_workers";"5" "autovacuum_multixact_freeze_max_age";"400000000" "autovacuum_naptime";"60" "autovacuum_vacuum_cost_delay";"5" "autovacuum_vacuum_cost_limit";"-1" "autovacuum_vacuum_scale_factor";"0.07" "autovacuum_vacuum_threshold";"40" "autovacuum_work_mem";"-1" Below an example of table who should be vacuumed : [ RECORD 1 ]-------+------------------------------ relid | 22532 schemaname | public relname | items seq_scan | 605 seq_tup_read | 10892144338 idx_scan | 736356122126 idx_tup_fetch | 487170521700 n_tup_ins | 50843625 n_tup_upd | 3027781777 n_tup_del | 138773 n_tup_hot_upd | 1324753839 n_live_tup | 45982194 n_dead_tup | 131376163 n_mod_since_analyze | 532254 last_vacuum | last_autovacuum | 2016-05-04 06:39:33.936575+02 last_analyze | 2016-05-06 10:33:56.933326+02 last_autoanalyze | 2016-05-04 03:26:16.761463+02 vacuum_count | 0 autovacuum_count | 775 analyze_count | 0 autoanalyze_count | 865 Last entries in logs : 2016-05-04 06:39:41.507 CEST [90898]: [14-1] user=,db=,app=,client= LOG: automatic vacuum of table "db1.public.listings_de": index scans: 1 2016-05-04 06:39:42.514 CEST [90898]: [16-1] user=,db=,app=,client= LOG: automatic vacuum of table "db1.public.listings_uk": index scans: 1 2016-05-04 06:39:58.702 CEST [90898]: [18-1] user=,db=,app=,client= LOG: automatic vacuum of table "db1.public.orders": index scans: 1 2016-05-04 06:40:04.993 CEST [90898]: [21-1] user=,db=,app=,client= LOG: automatic vacuum of table "db1.pg_toast.pg_toast_2619": index scans: 1 2016-05-04 06:39:42.427 CEST [90898]: [15-1] user=,db=,app=,client= LOG: automatic analyze of table "db1.public.listings_de" system usage: CPU 0.02s/0.43u sec elapsed 0.91 sec 2016-05-04 06:39:42.593 CEST [90898]: [17-1] user=,db=,app=,client= LOG: automatic analyze of table "db1.public.listings_uk" system usage: CPU 0.00s/0.03u sec elapsed 0.07 sec 2016-05-04 06:40:00.064 CEST [90898]: [19-1] user=,db=,app=,client= LOG: automatic analyze of table "db1.public.orders" system usage: CPU 0.00s/0.59u sec elapsed 1.36 sec 2016-05-04 06:40:04.729 CEST [90898]: [20-1] user=,db=,app=,client= LOG: automatic analyze of table "db1.public.items_tasks" system usage: CPU 0.05s/0.50u sec elapsed 4.65 sec Does anyone have a clue ? Thanks, Thomas
Re: Autovacuums not triggering anymore
От:
Alvaro Herrera <alvherre@2ndquadrant.com>
Дата:
Thomas SIMON wrote: > Hi all, > > Since two days, autovacuum_analyze & autovacuum_vacuum does not trigger > anymore on any of my databases (but they are started on db template1). > Before, I had around at least 30 analyzes and 400 vacuums per hour on these > DBs. Are autovacuum workers all busy processing some very large? Look for them in pg_stat_activity. If not, identify what is the autovacuum launcher process doing; perhaps attach a debugger to it and see if it ever calls launch_worker() or do_start_worker(). If time longer than autovacuum_naptime lapses and it doesn't, have a look at the "stats collector process"; is that one alive and working? If you attach to it with a debugger, you should see it operate if you do a simple query in a session such as "select * from pg_stat_user_tables". In that query, also verify the autovacuum and autoanalyze timestamps. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services