Date: Wed, 27 May 2015 15:00:06 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Charles Sprickman <spork@biglist.com>
Cc: Jorge Torralba <jorge.torralba@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: autovacuum was not vacuuming
Message-ID: <20150527180006.GD5885@postgresql.org>
References: <1E6196B0-6B63-4BC9-8E14-DF54AC2A1F34@biglist.com>
 <CACut7uRA-af5LzYExWnZmRLdhLAO_Xoh+Nf=iVBg=rkLL7Gvdw@mail.gmail.com>
 <CACC9F4B-546D-40EA-8EB0-F0A2DBC4BE7B@biglist.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CACC9F4B-546D-40EA-8EB0-F0A2DBC4BE7B@biglist.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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