From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Charles Sprickman <spork@biglist.com>
Cc: Jorge Torralba <jorge.torralba@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: autovacuum was not vacuuming
Date: Wed, 27 May 2015 15:00:06 -0300
Message-ID: <20150527180006.GD5885@postgresql.org> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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_autovacuum column:
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
--
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@2ndquadrant.com, spork@biglist.com, jorge.torralba@gmail.com
Subject: Re: autovacuum was not vacuuming
In-Reply-To: <20150527180006.GD5885@postgresql.org>
* 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