Re: autovacuum was not vacuuming

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