Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7465B64FCAC for ; Wed, 29 Oct 2008 19:30:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 16335-01 for ; Wed, 29 Oct 2008 19:30:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.30]) by postgresql.org (Postfix) with ESMTP id 7E45E65003C for ; Wed, 29 Oct 2008 19:30:21 -0300 (ADT) Received: by yx-out-2324.google.com with SMTP id 8so93136yxb.73 for ; Wed, 29 Oct 2008 15:30:19 -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:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=RqbsoDYlk+ZzSjTrzRVMrfYFoO7xhjhKPeCz1SKRRCs=; b=WWRpMQXVMD8/QUL56mQY++oR5P/vNItWsQxVbM65Ql4zeJo9TrM5slVshSGnYvxsh1 OUy0M2SqvRafrX9QGuZuam8QaDod3Gf+gDT3zovLJTzBY35S4tw0Y94DxJyZ8uGIBS2B 7U6DkJs1lLr6pvFm3SqllabmL41NLIhosKte8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=Sw8Im3wgZr1k/8HGFxKJnlQz7Z0GBcnfhdk7HBRA+D5q2QSDgoSLP96GS3JjjLmFef nqhDC2Twwk3E8nyVadBShuGNS2iFQY1HTwMcNNF2OCZUtHnAauX/IsvknxaqIu61AFH/ gm/Fx4gSF8pAEhPguJchfOhsYKop2aWgSIeP4= Received: by 10.100.164.20 with SMTP id m20mr43917ane.121.1225319419466; Wed, 29 Oct 2008 15:30:19 -0700 (PDT) Received: by 10.100.132.20 with HTTP; Wed, 29 Oct 2008 15:30:19 -0700 (PDT) Message-ID: <72dbd3150810291530q7a95da64j2c39354de9ad91d1@mail.gmail.com> Date: Wed, 29 Oct 2008 15:30:19 -0700 From: "David Rees" To: "Greg Smith" Subject: Re: Occasional Slow Commit Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <72dbd3150810271723w57859013v9c4e3718ae86fba@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/117 X-Sequence-Number: 31698 On Wed, Oct 29, 2008 at 6:26 AM, Greg Smith wrote: > The CentOS 4.7 kernel will happily buffer about 1.6GB of writes with that > much RAM, and the whole thing can get slammed onto disk during the final > fsync portion of the checkpoint. What you should do first is confirm > whether or not the slow commits line up with the end of the checkpoint, > which is easy to see if you turn on log_checkpoints. That gives you timings > for the write and fsync phases of the checkpoint which can also be > informative. OK, log_checkpoints is turned on to see if any delays correspond to checkpoint activity... >> Reading this page[2] indicates that I may want to increase my >> checkpoint_segments, checkpoint_timeout and bgwriter settings, but >> looking at pg_stat_bgwriter seems to indicate that my current settings >> are probably OK? >> >> # select * from pg_stat_bgwriter; >> checkpoints_timed | checkpoints_req | buffers_checkpoint | >> 3834 | 105 | 3,091,905 | >> buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc >> 25876 | 110 | 2,247,576 | 2,889,873 > > I reformatted the above to show what's happening a bit better. Sorry, gmail killed the formatting. > Most of your > checkpoints are the timed ones, which are unlikely to cause interference > from a slow commit period (the writes are spread out over 5 minutes in those > cases). It's quite possible the slow periods are coming only from the > occasional requested checkpoints, which normally show up because > checkpoint_segments is too low and you chew through segments too fast. If > you problems line up with checkpoint time, you would likely gain some > benefit from increasing checkpoint_segments to spread out the checkpoint > writes further; the 10 you're using now is on the low side for your > hardware. OK, I've also bumped up checkpoint_segments to 20 and checkpoint_completion_target to 0.7 in an effort to reduce the effect of checkpoints. > If the problems don't go away after that, you may be suffering from > excessive Linux kernel buffering instead. I've got a blog entry showing how > I tracked down a similar long pause on a Linux server at > http://notemagnet.blogspot.com/2008/08/linux-write-cache-mystery.html you > may find helpful for determining if your issue is this one (which is pretty > common on RHEL systems having relatively large amounts of RAM) or if it's > something else, like the locking you mentioned. Ah, interesting. I've also turned down the dirty_ratio and dirty_background_ratio as suggested, but I don't think this would be affecting things here. The rate of IO on this server is very low compared to what it's capable of. Thanks for the suggestions, I'll report back with results. -Dave