On Fri, 08 Mar 2013 20:39:45 +0200, AI Rumman <rummandba@xxxxxxxxx> wrote:
Knowing your problem, I read the docs and found that :
*
*
*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<http://www.postgresql.org/docs/9.2/static/release-9-2-3.html#>.
This fix also greatly reduces the average time before autovacuum releases
the lock after a conflicting request arrives.*
This could be a reason of your locking.
Yes, I saw this. It is commit b19e4250b45e91c9cbdd18d35ea6391ab5961c8d by
Jan Wieck. He also seems worried in the commit message about this patch. Do
you think this is the exact reason of the problem?
I have downgraded to 9.2.2, decreased the autovacuum_max_workers to 2 from
10 and increase max_connections to 500 from 200 in the mean time. There are
not any ExclusiveLock's since then.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
