Re: Vacuum as "easily obtained" locks

On 8/3/2011 4:47 AM, Michael Graham wrote:
> Hi all,
>
> I have an application that is reading from a queue table, as part of my
> testing I stressed the table to check performance, but after the test
> was completed I have the a very large empty table:
>
> SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size,
> reltuples::bigint FROM pg_class;
>
> relname | size | reltuples
> ----------------------------+----------+-----------
> logdata5queue | 142 GB | 0
>
> From reading the documentation I see that postgres would return this
> space to that system after a normal vacuum if "one or more pages at the
> end of a table become entirely free and an exclusive table lock can be
> easily obtained".
>
> What does "easily obtained" mean in this context? Would my applications
> constant polling of the queue mean that the lock could not be easily
> obtained?
>
> Cheers,

I'm not sure what "easily obtained" means, sorry. But here is what I
can offer.

Depending on how long you ran your test, and the conf settings, and the
size of your database, autovacuum may never have even tried.

If you have lots and lots of tables, autovacuum only checks one at a
time, then wait's a bit. Did you run your test for several days?

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.

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.

At worst case, if your usage does prevent autovacuum from running, you
can still run it yourself. Either at a scheduled downtime, or as part
of your regular client sql.

-Andy