Re: Vacuum as "easily obtained" locks

On Wed, 2011-08-03 at 09:03 -0500, Andy Colson wrote:
> Depending on how long you ran your test, and the conf settings, and
> the size of your database, autovacuum may never have even tried.

I know that the vacuum is definitely running (in fact isn't it the
vacuum that set the reltuples to 0?), the test was running for a number
of weeks but this is the first time it has emptied the queue.

> You can take a look on pg_stat_all_tables, under the *vacuum columns,
> to see if it ever even ran.  (a date in last_autovacuum would indicate
> a successful run, it wont show failures).  It should, however, write
> something to the system log.  I recall something like "autovacuum
> canceled because...something or other" type a message.

Yeah it said it last ran yesterday (and is currently running now), but I
did I notice in the log:

2011-08-02 19:43:35 BST ERROR:  canceling autovacuum task
2011-08-02 19:43:35 BST CONTEXT:  automatic vacuum of table
"traffic.public.logdata5queue"

Which is interesting if not particularly useful.

> While you are running a test, you could keep an eye on the log to see
> if you get any of those messages.  I think that would indicate
> autovacuum could not get a lock.  You can also watch pg_stat_activity
> during the test, current_query will show you what table is being
> vacuumed.

I'm pretty certain the autovacuumer is running while the test is on
going what I can't work out is under what circumstances it will be able
to return unused space to the OS in when it can't.

Cheers,
--
Michael Graham <mgraham@bloxx.com>