Re: autovaccum task got cancelled

On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu <gfu(at)sigmaspace(dot)com> wrote:
> I'm running an application (with programs in Perl) through pgpool 3.1 with
> replication mode to two postgresql db servers (version 9.0.13). Recently, I
> noticed that the following messages repeatedly showed in postgres log files.
> As far as I know, the application programs do not make any specific lock on
> the 'file' table. I'm not sure if it is caused by the pgpool or something
> else.

[...]

> 2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614
> 2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for ShareRowExclusiveLock
> on relation 11959608 of database 596746.
> 2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW EXCLUSIVE
> MODE
> 2013-10-31 18:01:30 UTCERROR: canceling autovacuum task
> 2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table
> "sd3ops1.public.file"

From the release notes to 9.0.12 [1]:

<<Fix performance problems with autovacuum truncation in busy
workloads (Jan Wieck)

Truncation of empty pages at the end of a table requires exclusive
lock, but autovacuum was coded to fail (and release the table lock)
when there are conflicting lock requests. Under load, it is easily
possible that truncation would never occur, resulting in table bloat.
Fix by performing a partial truncation, releasing the lock, then
attempting to re-acquire the lock and continue. This fix also greatly
reduces the average time before autovacuum releases the lock after a
conflicting request arrives.>>

[1]: http://www.postgresql.org/docs/9.0/static/release-9-0-12.html

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com