From: Emre Hasegeli <emre.hasegeli@tart.com.tr>
To: AI Rumman <rummandba@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Kevin Grittner <kgrittn@postgresql.org>
Cc: Jan Wieck <JanWieck@yahoo.com>
Subject: Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks
Date: Fri, 08 Mar 2013 21:56:23 +0200
Message-ID: <op.wtm8cay7k2xoe5@hasegeli.local> (raw)
In-Reply-To: <CAGoODpdUCN8AQG6-972qropbsNnw3SAZpE90w1CwNEc2z3gccA@mail.gmail.com>
References: <op.wtmkrq0nk2xoe5@hasegeli.local>
<CAGoODpdUCN8AQG6-972qropbsNnw3SAZpE90w1CwNEc2z3gccA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: emre.hasegeli@tart.com.tr, rummandba@gmail.com, kgrittn@postgresql.org, JanWieck@yahoo.com
Subject: Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks
In-Reply-To: <op.wtm8cay7k2xoe5@hasegeli.local>
* 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