Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks

On Fri, 08 Mar 2013 20:39:45 +0200, AI Rumman <rummandba@gmail.com> 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.