From: Peter Schuller <peter.schuller@infidyne.com>
To: pgsql-performance@postgresql.org
Subject: VACUUM ANALYZE blocking both reads and writes to a table
Date: Mon, 30 Jun 2008 16:59:03 +0200
Message-ID: <20080630145903.GA15197@hyperion.scode.org> (raw)
Hello,
my understanding, and generally my experience, has been that VACUUM
and VACUUM ANALYZE (but not VACUUM FULL) are never supposed to block
neither SELECT:s nor UPDATE:s/INSERT:s/DELETE:s to a table.
This is seemingly confirmed by reading the "explicit locking"
documentation, in terms of the locks acquired by various forms of
vacuuming, and with which other lock modes they conflict.
I have now seen it happen twice that a VACUMM ANALYZE has seemingly
been the triggering factor to blocking queries.
In the first instance, we had two particularly interesting things
going on:
VACUUM ANALYZE thetable
LOCK TABLE thetable IN ACCESS SHARE MODE
In addition there was one SELECT from the table, and a bunch of
INSERT:s (this is based on pg_stat_activity).
While I am unsure of why there is an explicit LOCK going on with
ACCESS SHARE MODE (no explicit locking is ever done on this table by
the application), it is supposed to be the locking used for selects. I
suspect it may be a referential integrity related acquisition
generated by PG.
The second time it happned, there was again a single SELECT, a bunch
of INSERT:s, and then:
VACUUM ANALYZE thetable
This time there was no explicit LOCK visible.
In both cases, actitivy was completely blocked until the VACUUM
ANALYZE completed.
Does anyone have input on why this could be happening? The PostgreSQL
version is 8.2.4[1]. Am I correct in that it *should* not be possible
for this to happen?
For the next time this happens I will try to have a query prepared
that will dump as much relevant information as possible regarding
acquired locks.
If it makes a difference the SELECT does have a subselect that also
selcts from the same table - a MAX(colum) on an indexed column.
[1] I did check the ChangeLog for 8.2.x releases above .4, and the 8.3
releases, but did not see anything that indicated locking/conflict
related fixes in relation to vacuums.
--
/ Peter Schuller
PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: peter.schuller@infidyne.com
Subject: Re: VACUUM ANALYZE blocking both reads and writes to a table
In-Reply-To: <20080630145903.GA15197@hyperion.scode.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox