Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UE3PB-00064l-34 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2013 19:56:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UE3PA-0007Nz-BO for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2013 19:56:36 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UE3P9-0007Nt-8u for pgsql-performance@postgresql.org; Fri, 08 Mar 2013 19:56:35 +0000 Received: from mail.sihirhosting.com ([77.92.143.143]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UE3P5-0004dc-TV for pgsql-performance@postgresql.org; Fri, 08 Mar 2013 19:56:34 +0000 Received: (qmail 30583 invoked by uid 509); 8 Mar 2013 21:56:28 +0200 Received: from 212.156.129.218 by gaya (envelope-from , uid 507) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.7/2391. spamassassin: 3.0.2. perlscan: 1.25-st-qms. Clear:RC:0(212.156.129.218):SA:0(-97.0/6.7):. Processed in 3.450739 secs); 08 Mar 2013 19:56:28 -0000 X-Spam-Status: No, hits=-97.0 required=6.7 X-Antivirus-MYDOMAIN-Mail-From: emre.hasegeli@tart.com.tr via gaya X-Antivirus-MYDOMAIN: 1.25-st-qms (Clear:RC:0(212.156.129.218):SA:0(-97.0/6.7):. Processed in 3.450739 secs Process 30564) Received: from unknown (HELO hasegeli.local) (emre.hasegeli@tart.com.tr@212.156.129.218) by mail.sihirhosting.com with AES256-SHA encrypted SMTP; 8 Mar 2013 21:56:24 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "AI Rumman" Subject: Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks References: Date: Fri, 08 Mar 2013 21:56:23 +0200 Cc: "pgsql-performance@postgresql.org" , "Kevin Grittner" , "Jan Wieck" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: "Emre Hasegeli" Organization: =?utf-8?B?VGFydCDEsG50ZXJuZXQgVGVrbm9sb2ppbGVyaSBUaWNhcmV0IEHFng==?= Message-ID: In-Reply-To: User-Agent: Opera Mail/12.14 (MacIntel) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Fri, 08 Mar 2013 20:39:45 +0200, AI Rumman 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. > 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