To: Alvaro Herrera <alvherre@commandprompt.com>
cc: Greg Smith <gsmith@gregsmith.com>, Matthew Wakeling <matthew@flymine.org>,
	pgsql-performance@postgresql.org
Subject: Re: I/O on select count(*) 
In-reply-to: <20080516201528.GJ13061@alvh.no-ip.org> 
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com>
	<482B1D5F.EE98.0025.0@wicourts.gov>
	<Pine.GSO.4.64.0805142133250.27290@westnet.com>
	<20080515020553.GN9838@alvh.no-ip.org>
	<Pine.LNX.4.64.0805151245360.16756@aragorn.flymine.org>
	<20080515155232.GC8221@alvh.no-ip.org>
	<Pine.GSO.4.64.0805161118210.4987@westnet.com>
	<20080516201528.GJ13061@alvh.no-ip.org>
Comments: In-reply-to Alvaro Herrera <alvherre@commandprompt.com>
	message dated "Fri, 16 May 2008 16:15:28 -0400"
Date: Fri, 16 May 2008 22:45:02 -0400
Message-ID: <18382.1210992302@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Greg Smith wrote:
>> After reading the code and that documentation a bit, the part I'm still  
>> not sure about is whether the CLOG entry is created when the XID is  
>> assigned and then kept current as the state changes, or whether that 
>> isn't even in CLOG until the transaction is committed.

> pg_clog is allocated in pages of 8kB apiece(*).  On allocation, pages are
> zeroed, which is the bit pattern for "transaction in progress".  So when
> a transaction starts, it only needs to ensure that the pg_clog page that
> corresponds to it is allocated, but it need not write anything to it.

One additional point: this means that one transaction in every 32K
writing transactions *does* have to do extra work when it assigns itself
an XID, namely create and zero out the next page of pg_clog.  And that
doesn't just slow down the transaction in question, but the next few
guys that would like an XID but arrive on the scene while the
zeroing-out is still in progress.

This probably contributes to the behavior that Simon and Josh regularly
complain about, that our transaction execution time is subject to
unpredictable spikes.  I'm not sure how to get rid of it though.

			regards, tom lane