Обсуждение: autovacuum was not vacuuming
I’m having some trouble figuring out what was going on that led up to this, but after neglecting maintenance for some
time,I noticed that even though autovacuum was not disabled, it never seemed to be getting around to vacuuming.
Some basics - PG 9.2, about 150 databases, autovacuum default settings
After an initial read of some vacuuming tips, I came up with the following settings:
autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 100 # -1 disables, 0 logs all actions and
autovacuum_max_workers = 8 # max number of autovacuum subprocesses
autovacuum_naptime = 40min # time between autovacuum runs
autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for
# autovacuum, in milliseconds;
autovacuum_vacuum_cost_limit = 1000 # default vacuum cost limit for
# autovacuum, -1 means use
My main interest there actually was getting logs, but something obviously kicked in as I started seeing a number of
databases/tablesgetting vacuumed that had not been vacuumed in over a year. Some of these tables have at least a few
thousandinsert/delete/updates per day, some much more than that.
Was the combination of the large(?) number of databases and default settings effectively preventing autovacuum from
doingits thing?
Thanks,
Charles
On May 27, 2015, at 1:37 PM, Jorge Torralba <jorge.torralba@gmail.com> wrote:
> run this for validation
>
> select relname, last_autovacuum from pg_stat_user_tables order by last_autovacuum;
Hi - thanks, I’d run some similar query before to see what wasn’t being vacuumed.
While some tables seem to be now, here’s an example of an active db that’s still got some blanks in the last_autovacuum
column:
relname | last_autovacuum
---------------------+-------------------------------
optin | 2014-07-21 06:31:47.66319-04
log | 2014-10-18 04:55:22.227628-04
complaint | 2014-10-18 10:43:18.224247-04
arch_subscriber | 2014-11-19 08:27:32.895688-05
subscriber | 2014-12-05 10:12:48.340305-05
transactions | 2015-02-18 12:47:42.406487-05
list_sub | 2015-05-22 10:12:27.755863-04
post | 2015-05-23 01:46:47.039179-04
post_job | 2015-05-23 21:49:03.25371-04
post_actions | 2015-05-26 10:03:43.259077-04
bounce | 2015-05-26 10:29:34.888937-04
bounce_reason | 2015-05-26 10:29:34.918845-04
list_data | 2015-05-27 09:50:21.202492-04
limit_tracking | 2015-05-27 13:10:51.673256-04
post_stats | 2015-05-27 13:10:52.921319-04
email |
sending_rule_fields |
sending_rule_files |
post_threads |
forwards |
list |
sub_active |
subscriber_db_field |
slice_fields |
sending_rule_esps |
client |
slices |
fields |
arch_list_sub |
sending_rules |
subscriber_db_query |
engagement |
reject_rule |
list_criterion |
repltest |
post_attachment |
filters |
messages |
post_domain |
track_url |
subscriber_data |
message_attachments |
migrations |
subscriptions |
mailer |
subscriber_db_file |
track_hit |
engagement_rules |
(48 rows)
Any insights based on that?
Thanks,
Charles
> On Tue, May 26, 2015 at 10:02 PM, Charles Sprickman <spork@biglist.com> wrote:
> I’m having some trouble figuring out what was going on that led up to this, but after neglecting maintenance for some
time,I noticed that even though autovacuum was not disabled, it never seemed to be getting around to vacuuming.
>
> Some basics - PG 9.2, about 150 databases, autovacuum default settings
>
> After an initial read of some vacuuming tips, I came up with the following settings:
>
> autovacuum = on # Enable autovacuum subprocess? 'on'
> log_autovacuum_min_duration = 100 # -1 disables, 0 logs all actions and
> autovacuum_max_workers = 8 # max number of autovacuum subprocesses
> autovacuum_naptime = 40min # time between autovacuum runs
> autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before vacuum
> autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
> autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for
> # autovacuum, in milliseconds;
> autovacuum_vacuum_cost_limit = 1000 # default vacuum cost limit for
> # autovacuum, -1 means use
>
> My main interest there actually was getting logs, but something obviously kicked in as I started seeing a number of
databases/tablesgetting vacuumed that had not been vacuumed in over a year. Some of these tables have at least a few
thousandinsert/delete/updates per day, some much more than that.
>
> Was the combination of the large(?) number of databases and default settings effectively preventing autovacuum from
doingits thing?
>
> Thanks,
>
> Charles
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
> --
> Thanks,
>
> Jorge Torralba
> ----------------------------
>
> Note: This communication may contain privileged or other confidential information. If you are not the intended
recipient,please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the
senderthat you have received this email in error and delete the copy you received. Thank You.
Charles Sprickman wrote: > On May 27, 2015, at 1:37 PM, Jorge Torralba <jorge.torralba@gmail.com> wrote: > > > run this for validation > > > > select relname, last_autovacuum from pg_stat_user_tables order by last_autovacuum; > > Hi - thanks, I’d run some similar query before to see what wasn’t being vacuumed. > > While some tables seem to be now, here’s an example of an active db that’s still got some blanks in the last_autovacuumcolumn: Does your system crash frequently? autovacuum would not process files unless they appear to cross the threshold values; and the statistics are reset whenever there's a crash recovery. Perhaps those tables don't have enough recent activity. I see your oldest autovac dates are 2014-07 so perhaps you had crashes sometime before that. How many workers are currently processing tables? (See pg_stat_activity) The more workers there are, the slower they become. Perhaps they are all busy processing large tables and they never finish because of the vacuum_delay. On the other hand, naptime=40min means that from one worker start to the next one there's a 40min wait, which seems a bit too long. Normally the default of 1min is appropriate; why did you change that? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On May 27, 2015, at 2:00 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Charles Sprickman wrote:
>> On May 27, 2015, at 1:37 PM, Jorge Torralba <jorge.torralba@gmail.com> wrote:
>>
>>> run this for validation
>>>
>>> select relname, last_autovacuum from pg_stat_user_tables order by last_autovacuum;
>>
>> Hi - thanks, I’d run some similar query before to see what wasn’t being vacuumed.
>>
>> While some tables seem to be now, here’s an example of an active db that’s still got some blanks in the
last_autovacuumcolumn:
>
> Does your system crash frequently? autovacuum would not process files
> unless they appear to cross the threshold values; and the statistics are
> reset whenever there's a crash recovery.
No frequent crashes. Not any that I know of even since moving to
this server (up 1046 days). So thats one thing to rule out. I also
know that no one has manually reset any of the stats counters.
> Perhaps those tables don't
> have enough recent activity. I see your oldest autovac dates are
> 2014-07 so perhaps you had crashes sometime before that.
This is starting to make sense.
On the same database, this data is kind of interesting:
relname | n_tup_ins | n_tup_upd | n_tup_del | last_autovacuum | autovacuum_count
---------------------+-----------+-----------+-----------+-------------------------------+------------------
optin | 7252274 | 0 | 1975631 | 2014-07-21 06:31:47.66319-04 | 3
log | 43045279 | 0 | 31641637 | 2014-10-18 04:55:22.227628-04 | 8
complaint | 3002663 | 6154 | 3002973 | 2014-10-18 10:43:18.224247-04 | 31
arch_subscriber | 8472659 | 8184029 | 96798 | 2014-11-19 08:27:32.895688-05 | 2
subscriber | 13625052 | 28592779 | 8473154 | 2014-12-05 10:12:48.340305-05 | 14
transactions | 2514676 | 5182 | 257117 | 2015-02-18 12:47:42.406487-05 | 3
post | 32521 | 50722 | 1557 | 2015-05-23 01:46:47.039179-04 | 1
post_actions | 98395891 | 43836 | 44881522 | 2015-05-26 10:03:43.259077-04 | 3
bounce | 631024 | 81 | 595087 | 2015-05-26 10:29:34.888937-04 | 13
bounce_reason | 7961281 | 32736 | 7953428 | 2015-05-26 10:29:34.918845-04 | 6
list_sub | 14562995 | 69515258 | 9009496 | 2015-05-28 15:58:13.754569-04 | 30
post_job | 8127 | 15908 | 8070 | 2015-05-28 21:52:54.923614-04 | 263
list_data | 7350 | 756705 | 6063 | 2015-05-29 12:34:42.323142-04 | 425
post_stats | 90155 | 40750025 | 108 | 2015-05-29 13:14:43.307775-04 | 569
limit_tracking | 30248 | 12689580 | 30192 | 2015-05-29 13:54:42.06234-04 | 50780
email | 0 | 0 | 0 | | 0
sending_rule_fields | 0 | 0 | 0 | | 0
sending_rule_files | 0 | 0 | 0 | | 0
post_threads | 0 | 0 | 0 | | 0
forwards | 0 | 0 | 0 | | 0
list | 9 | 28 | 7 | | 0
sub_active | 840019 | 0 | 0 | | 0
subscriber_db_field | 0 | 0 | 0 | | 0
slice_fields | 0 | 0 | 0 | | 0
sending_rule_esps | 0 | 0 | 0 | | 0
client | 1 | 0 | 0 | | 0
slices | 0 | 0 | 0 | | 0
fields | 11 | 0 | 0 | | 0
arch_list_sub | 8909636 | 0 | 101140 | | 0
sending_rules | 0 | 0 | 0 | | 0
subscriber_db_query | 0 | 0 | 0 | | 0
engagement | 0 | 0 | 0 | | 0
reject_rule | 608 | 0 | 0 | | 0
list_criterion | 0 | 0 | 0 | | 0
repltest | 0 | 0 | 0 | | 0
post_attachment | 1 | 0 | 0 | | 0
filters | 0 | 0 | 0 | | 0
messages | 0 | 0 | 0 | | 0
post_domain | 262398 | 1177 | 290 | | 0
track_url | 786 | 1394 | 1 | | 0
subscriber_data | 0 | 0 | 0 | | 0
message_attachments | 0 | 0 | 0 | | 0
migrations | 11 | 0 | 0 | | 0
subscriptions | 8606373 | 463446 | 0 | | 0
mailer | 0 | 0 | 0 | | 0
subscriber_db_file | 0 | 0 | 0 | | 0
track_hit | 29733544 | 86038 | 6383080 | | 0
engagement_rules | 0 | 0 | 0 | | 0
(48 rows)
Of all the tables that have NOT been autovacuumed, only a handful
have any real write activity. And on those I cant really get any
data as to WHEN the last activity was (unless I reset stats and
wait) although looking a bit closer I see some timestamped columns
and none of these tables have been touched in years. Good, I think.
Should I be worried about limit_tracking which has been vacuumed
about 50,000 times in the last few days? :) That seems a little
extreme.
Now if we assume the tables that werent vacuumed are not seeing any
new activity, my next problem is how to satisfy my nagios
check_postgres checks that look for tables that have not been
vacuumed in X days.
> How many workers are currently processing tables? (See
> pg_stat_activity) The more workers there are, the slower they become.
By workers, I assume you just mean pg processes? It varies, but
anywhere between a few dozen and a hundred.
> Perhaps they are all busy processing large tables and they never finish
> because of the vacuum_delay. On the other hand, naptime=40min means
> that from one worker start to the next one there's a 40min wait, which
> seems a bit too long. Normally the default of 1min is appropriate; why
> did you change that?
I think because I dont fully grasp how all these variables interact.
IIRC, when I set this it had something to do with compensating for
the number of databases and the number of workers.
Im looking at pgbadger’s analysis of vacuums and I think Im mostly OK
with whats happening.
Im still stumped on why changes from defaults were necessary to get
autovacuum to kick in though
Thanks,
Charles
>
> --
> Á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