Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C4E171337BBC for ; Thu, 3 Mar 2011 10:11:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 59952-06 for ; Thu, 3 Mar 2011 14:10:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh7.mail.rice.edu (mh7.mail.rice.edu [128.42.199.46]) by mail.postgresql.org (Postfix) with ESMTP id 16BAB1337B69 for ; Thu, 3 Mar 2011 10:10:55 -0400 (AST) Received: from mh7.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh7.mail.rice.edu (Postfix) with ESMTP id B76FF28FBB7; Thu, 3 Mar 2011 08:10:55 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh7.mail.rice.edu, auth channel Received: from mh7.mail.rice.edu ([127.0.0.1]) by mh7.mail.rice.edu (mh7.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id d-IVDnNNAZs1; Thu, 3 Mar 2011 08:10:55 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh7.mail.rice.edu (Postfix) with ESMTP id 81A4628FBA3; Thu, 3 Mar 2011 08:10:55 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id B2E3A2D5A6; Thu, 3 Mar 2011 08:10:54 -0600 (CST) Date: Thu, 3 Mar 2011 08:10:54 -0600 From: Kenneth Marshall To: "Vaughn, Adam (IMS)" Cc: 'Kevin Grittner' , "'pgsql-admin@postgresql.org'" , "Depuy, Scott (IMS)" , "Meagher, Kevin (IMS)" Subject: Re: long running commits Message-ID: <20110303141054.GU8169@aart.is.rice.edu> References: <6B760A41CEE02F46BADF29B4A874D6010F11C13A63@FALCON.omni.imsweb.com> <4D67DC90020000250003B04B@gw.wicourts.gov> <6B760A41CEE02F46BADF29B4A874D6010F11C13ABF@FALCON.omni.imsweb.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6B760A41CEE02F46BADF29B4A874D6010F11C13ABF@FALCON.omni.imsweb.com> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/31 X-Sequence-Number: 36903 On Wed, Mar 02, 2011 at 01:10:37PM -0500, Vaughn, Adam (IMS) wrote: > Thanks for the suggestions. I made all of the changes you mentioned except for the shared_buffers (which will require a downtime I have set for tonight). I do have another question though, why did you pick 512 MB for the new setting of shared_buffers? Everything I've ever read says that 25% of available RAM is a conservative value for shared_buffers. > > Also, we had another one of these instances earlier today. During the 23 minute commit a single CPU was at 98% and it looked like all writes were backed up waiting for the commit to finalize. During the time our writing never got above 25 MB/s (far less than we can handle). Is it possible that we're missing an index somewhere or there's something else going on? > > Thanks again in advance > Hi Adam, Having recently been benchmarking NFS filesystem performance with random I/O streams, I think that you do not have the I/O performance from your filer that you think you do. A 13-disk RAID-DP gives you at best 11 spindles for random I/O and a single spindle can give a best 5 MB/sec write throughput for an aggregate throughput of 55MB/sec if you were the only user of the filer. It is not unreasonable to assume that you only have 50% of the total throughput available if it is in use as you stated in your original message. The changes that have been suggested should allow you to keep the write needs below your apparent 27MB/sec cap and reduce or eliminate the pauses. Cheers, Ken