X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C8A099FA19B for ; Wed, 17 May 2006 06:33:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 58810-06 for ; Wed, 17 May 2006 06:33:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sand2.gxn.net (sand2.gxn.net [195.147.249.208]) by postgresql.org (Postfix) with ESMTP id 267999F9C9E for ; Wed, 17 May 2006 06:33:31 -0300 (ADT) Received: from [62.232.55.118] (helo=[192.168.0.2]) by sand2.gxn.net with esmtp (Exim 4.50) id 1FgIQU-0002YM-Nb; Wed, 17 May 2006 10:34:42 +0100 Subject: Re: does wal archiving block the current client connection? From: Simon Riggs To: Tom Lane Cc: Jeff Frost , pgsql-admin@postgresql.org In-Reply-To: <15699.1147840569@sss.pgh.pa.us> References: <1147728181.5074.73.camel@localhost.localdomain> <1147733193.5074.87.camel@localhost.localdomain> <1147781287.2646.32.camel@localhost.localdomain> <15699.1147840569@sss.pgh.pa.us> Content-Type: text/plain Date: Wed, 17 May 2006 10:33:18 +0100 Message-Id: <1147858398.2646.150.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 (2.6.1-1.fc5.2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/232 X-Sequence-Number: 21764 On Wed, 2006-05-17 at 00:36 -0400, Tom Lane wrote: > Jeff Frost writes: > > On Tue, 16 May 2006, Simon Riggs wrote: > >> Whatever happened between 02:08 and 02:14 seems important. > > > I have the logs and after reviewing /var/log/messages for that time period, > > there is no other activity besides postgres. > > I have a lurking feeling that the still-hypothetical connection between > archiver and foreground operations might come into operation at pg_clog > page boundaries (which require emitting XLOG events) --- that is, every > 32K transactions something special happens. The time delay between > archiver wedging and foreground wedging would then correspond to how > long it took the XID counter to reach the next 32K multiple. (Jeff, > what transaction rate do you see on that server --- is that a plausible > delay for some thousands of transactions to pass?) > > This is just a guess, but if you check the archives for Chris K-L's > out-of-disk-space server meltdown a year or three ago, you'll see > something similar. You'll have to explain a little more. I checked the archives... archiver looks for archive_status files that end with .ready and that has got nothing at all to do with transactions, LWlocks etc. If there's a file ready, it will archive it, if there's not - it won't. There is very deliberately a very low amount of synchronization there: archiver holds no locks, LWLocks or spinlocks at any time. The "lurking feeling" scenario above might or might nor be an issue here, but I can't see how the archiver could be involved at all. I see no evidence for the archiver to be the source of a problem here and that the only reason we're checking that is as a result of Jeff's original conjecture that there was a connection. There *was* a problem, yes, but I think we're looking in the wrong place for the murder weapon. pg_clog page extension does look like it can offer problems, generally. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com