From: Charles Sprickman <spork@biglist.com>
To: Jorge Torralba <jorge.torralba@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: autovacuum was not vacuuming
Date: Wed, 27 May 2015 13:53:48 -0400
Message-ID: <CACC9F4B-546D-40EA-8EB0-F0A2DBC4BE7B@biglist.com> (raw)
In-Reply-To: <CACut7uRA-af5LzYExWnZmRLdhLAO_Xoh+Nf=iVBg=rkLL7Gvdw@mail.gmail.com>
References: <1E6196B0-6B63-4BC9-8E14-DF54AC2A1F34@biglist.com>
<CACut7uRA-af5LzYExWnZmRLdhLAO_Xoh+Nf=iVBg=rkLL7Gvdw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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/tables getting vacuumed that had not been vacuumed in over a year. Some of these tables have at least a few thousand insert/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 doing its 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 sender that you have received this email in error and delete the copy you received. Thank You.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: spork@biglist.com, jorge.torralba@gmail.com
Subject: Re: autovacuum was not vacuuming
In-Reply-To: <CACC9F4B-546D-40EA-8EB0-F0A2DBC4BE7B@biglist.com>
References: <1E6196B0-6B63-4BC9-8E14-DF54AC2A1F34@biglist.com> <CACut7uRA-af5LzYExWnZmRLdhLAO_Xoh+Nf=iVBg=rkLL7Gvdw@mail.gmail.com> <CACC9F4B-546D-40EA-8EB0-F0A2DBC4BE7B@biglist.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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