Skip site navigation (1) Skip section navigation (2)

Re: I/O on select count(*)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Greg Smith <gsmith(at)gregsmith(dot)com>, Matthew Wakeling <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: I/O on select count(*)
Date: 2008-05-17 02:45:02
Message-ID: 18382.1210992302@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Alvaro Herrera <alvherre(at)commandprompt(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Jeremy HarrisDate: 2008-05-17 13:13:52
Subject: Re: I/O on select count(*)
Previous:From: Scott MarloweDate: 2008-05-16 21:44:26
Subject: Re: Regexps - never completing join.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group