pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuums not triggering anymore
2+ messages / 2 participants
[nested] [flat]
* Autovacuums not triggering anymore
@ 2016-05-06 10:12 Thomas SIMON <tsimon@neteven.com>
2016-05-06 16:58 ` Re: Autovacuums not triggering anymore Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Thomas SIMON @ 2016-05-06 10:12 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Autovacuums not triggering anymore
2016-05-06 10:12 Autovacuums not triggering anymore Thomas SIMON <tsimon@neteven.com>
@ 2016-05-06 16:58 ` Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 2+ messages in thread
From: Alvaro Herrera @ 2016-05-06 16:58 UTC (permalink / raw)
To: Thomas SIMON <tsimon@neteven.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-05-06 16:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-06 10:12 Autovacuums not triggering anymore Thomas SIMON <tsimon@neteven.com>
2016-05-06 16:58 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox