Re: autovacuum was not vacuuming

Charles Sprickman wrote:
> On May 27, 2015, at 1:37 PM, Jorge Torralba <jorge(dot)torralba(at)gmail(dot)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