Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C7C372E00C5 for ; Thu, 15 May 2008 10:57:49 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 13002-01-7 for ; Thu, 15 May 2008 10:57:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rn-out-0910.google.com (rn-out-0910.google.com [64.233.170.184]) by developer.postgresql.org (Postfix) with ESMTP id 3A7DD2E00A6 for ; Thu, 15 May 2008 10:56:26 -0300 (ADT) Received: by rn-out-0910.google.com with SMTP id i24so113640rng.0 for ; Thu, 15 May 2008 06:56:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=uNHawrSw8DpJ1i19Ud2LqxxXbv6NdLyHMNhTi08oXvQ=; b=jE8M9r2ubug+y4+sPcZNUmOEllTMvasvCDxCxiab/LDm8ZG/F3UOueYGHnmdc8znpZgnE7+MFvDBKDZFTZiks0gALsUpoZP8hGGB+AI1yXv4u29UOp/RZYNyLvXfY4tW/1BqcM65eULp/IEUadg/Uw9GviznLAy8wsodSTCjjjI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=I7AU2ftCXj4TFe16jWbUZUvPFof8+ykKvzMC74Cx4qzYlV+KB8Irh42KKKwDZWoIzWMMyC1RSP6VaPal5xf//s8o3xDj619qgeYWxpQRA/mBDli0bEIol7vXdS6axTx7lfxahxzXC27++k3V3zCp0CJgowlljUIR2sfBqO9oOaE= Received: by 10.142.52.9 with SMTP id z9mr985821wfz.237.1210859784379; Thu, 15 May 2008 06:56:24 -0700 (PDT) Received: by 10.142.242.21 with HTTP; Thu, 15 May 2008 06:56:24 -0700 (PDT) Message-ID: Date: Thu, 15 May 2008 09:56:24 -0400 From: "Jeffrey Baker" To: "Subbiah Stalin-XCGF84" , "pgsql-performance@postgresql.org" Subject: Re: Update performance degrades over time In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/210 X-Sequence-Number: 30178 On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84 wrote: > Hi All, > > We are doing some load tests with our application running postgres 8.2.4. At > times we see updates on a table taking longer (around > 11-16secs) than expected sub-second response time. The table in question is > getting updated constantly through the load tests. In checking the table > size including indexes, they seem to be bloated got it confirmed after > recreating it (stats below). We have autovacuum enabled with default > parameters. I thought autovaccum would avoid bloating issues but looks like > its not aggressive enough. Wondering if table/index bloating is causing > update slowness in over a period of time. Any ideas how to troubleshoot this > further. Sometimes it is necessary to not only VACUUM, but also REINDEX. If your update changes an indexed column to a new, distinct value, you can easily get index bloat. Also, you should check to see if you have any old, open transactions on the same instance. If you do, it's possible that VACUUM will have no beneficial effect. -jwb