Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 964B2650084 for ; Tue, 28 Oct 2008 09:46:09 -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 36203-01-2 for ; Tue, 28 Oct 2008 09:46:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.177]) by postgresql.org (Postfix) with ESMTP id D6A316500E1 for ; Tue, 28 Oct 2008 09:45:54 -0300 (ADT) Received: by ik-out-1112.google.com with SMTP id c21so755750ika.3 for ; Tue, 28 Oct 2008 05:45:52 -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=9z7Fwv2rit2neifvks5XUM+4mKnVpQHBCL/us8Mjsik=; b=ZA5LV1xC0bYEuDrBXNfZN52X6o9puFtcaViWcWA9621nKMGS9BJ8OR7Qii6UM7BpM9 jadt5flbuo2HMVoBJ44cufI4OjX6UKkFG2Nx+eBig6IHnLjJe/uxZB8Vz3n1THEfQLOM 3qXhFvv9RH5s8KLGHvU0kUe70zYunh/tss1Nw= 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=flwMasyl2M4AqjTZkTm9hnH6haDNKQvXV2vLuvlewlAQKLVrZd9nIathl8uSkh4GkA 8913lkkQgTgd9mcofSZ9ZLO4C2mkefuHezDBsTwU+oCC4Fb/M2X2d5ur1Z1ZJ8VAazx5 F0pBzE1nSUj+f1vniv1Lsc62RVveKIXwebHjE= Received: by 10.103.212.2 with SMTP id o2mr3362977muq.119.1225197952399; Tue, 28 Oct 2008 05:45:52 -0700 (PDT) Received: by 10.102.247.18 with HTTP; Tue, 28 Oct 2008 05:45:52 -0700 (PDT) Message-ID: Date: Tue, 28 Oct 2008 08:45:52 -0400 From: "Merlin Moncure" To: "David Rees" Subject: Re: Occasional Slow Commit Cc: pgsql-performance@postgresql.org In-Reply-To: <72dbd3150810271723w57859013v9c4e3718ae86fba@mail.gmail.com> 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/114 X-Sequence-Number: 31695 On Mon, Oct 27, 2008 at 8:23 PM, David Rees wrote: > Hi, > > I've got an OLTP application which occasionally suffers from slow > commit time. The process in question does something like this: > > 1. Do work > 2. begin transaction > 3. insert record > 4. commit transaction > 5. Do more work > 6. begin transaction > 7. update record > 8. commit transaction > 9. Do more work > > The vast majority of the time, everything runs very quickly. The > median processing time for the whole thing is 7ms. > > However, occasionally, processing time will jump up significantly - > the average processing time is around 20ms with the maximum processing > time taking 2-4 seconds for a small percentage of transactions. Ouch! > > Turning on statement logging and analyzing the logs of the application > itself shows that step #4 is the culprit of the vast majority of the > slow transactions. > > Software: CentOS 4.7, PostgreSQL 8.3.4, Slony-I 1.2.15 (the database > in question is replicated using slony) > > Hardware: 2x Xeon 5130, 4GB RAM, 6-disk RAID10 15k RPM, BBU on the controller > > Notable configuration changes: > shared_buffers = 800MB > temp_buffers = 200MB > work_mem = 16M > maintenance_work_mem = 800MB > vacuum_cost_delay = 10 > checkpoint_segments = 10 > effective_cache_size = 2500MB > > I found this post[1] from a while back which was informative: > > Both situations affect me in that I have Slony which I believe > executes triggers upon commit, and looking at the disk IO stats, there > is an elevated level of IO activity during this time, but it doesn't > appear to be heavy enough to cause the type of delays I am seeing. > > 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 | > buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc > -------------------+-----------------+--------------------+---------------+------------------+-----------------+--------------- > 3834 | 105 | 3091905 | > 25876 | 110 | 2247576 | 2889873 > > Any suggestions on how to proceed and debug the problem from here? > > My only other guess is that there is some sort of locking issues going > on which is slowing things down and that it may also be slony related, > as I also see a high number of slony related queries taking longer > than 1 second... I bet your problem is disk syncing. Your xlogs are on the data volume so any type of burst activity can push back commit times. If this is the case, you have basically three solutions to this problem: *) buy more disks (i's start with pushing the xlogs out to dedicated volume) *) disable fsync (very unsafe) or synchronous commit (somewhat less unsafe) *) checkpoint/bgwriter tuning: can provide incremental gains. This is not magic...at best you can smooth out bursty checkpoints. If your problems are really serious (yours don't seem to be), you have to look at the previous options. Have you temporarily disabling slony to see if the problem goes away? (My guess is it's not slony). merlin