Re: autovacuum was not vacuuming - Mailing list pgsql-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/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.