Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7DB699F9CA8 for ; Thu, 13 Sep 2007 12:03:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 22402-02 for ; Thu, 13 Sep 2007 12:03:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.libertyrms.com (vgateway.libertyrms.info [207.219.45.62]) by postgresql.org (Postfix) with ESMTP id 3D6579F9D5E for ; Thu, 13 Sep 2007 12:03:23 -0300 (ADT) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1IVqDz-0006ev-5T for pgsql-performance@postgresql.org; Thu, 13 Sep 2007 11:03:23 -0400 Subject: Re: Long Running Commits - Not Checkpoints From: Brad Nicholson To: pgsql-performance@postgresql.org In-Reply-To: <1189692915.8624.22.camel@bnicholson-desktop> References: <1189692915.8624.22.camel@bnicholson-desktop> Content-Type: text/plain Date: Thu, 13 Sep 2007 11:03:22 -0400 Message-Id: <1189695802.8624.25.camel@bnicholson-desktop> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 7bit X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/245 X-Sequence-Number: 26790 On Thu, 2007-09-13 at 10:15 -0400, Brad Nicholson wrote: > I'm having a problem with long running commits appearing in my database > logs. It may be hardware related, as the problem appeared when we moved > the database to a new server connected to a different disk array. The > disk array is a lower class array, but still more than powerful enough > to handle the IO requirements. One big difference though is that the > old array had 16 GB of cache, the new one has 4 GB. > > Running Postgres 8.1.8 on AIX 5.3 > > We have enough IO to spare that we have the bgwriter cranked up pretty > high, dirty buffers are getting quickly. Vmstat indicates 0 io wait > time, no swapping or anything nasty like that going on. > > The long running commits do not line up with checkpoint times. > > The postgresql.conf config are identical except that wal_buffers was 8 > on the old master, and it is set to 16 on the new one. > > We have other installations of this product running on the same array > (different servers though) and they are not suffering from this > problem. > > The only other thing of note is that the wal files sit on the same disk > as the data directory. This has not changed between the old and new > config, but the installs that are running fine do have their wal files > on a separate partition. > > Any ideas where the problem could lie? Could having the wal files on > the same data partition cause long running commits when there is plenty > of IO to spare? More on this - we also have long running commits on installations that do have the wal files on a separate partition. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.