Re: I/O on select count(*)

> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> 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.

Yeah, that's pretty-much how I imagined it would be. Nice and compact. I
would imagine that if there are only a few transactions, doing the pg_clog
lookup would be remarkably quick. However, once there have been a
bazillion transactions, with tuples pointing to the whole range of them,
it would degenerate into having to perform an extra seek for each tuple,
and that's why you added the hint bits.

On Fri, 16 May 2008, Tom Lane wrote:
> 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.

Does it really take that long to zero out 8kB of RAM? I thought CPUs were
really quick at doing that!

Anyway, the main thing you need to avoid is all the rest of the
transactions waiting for the new pg_clog page. The trick is to generate
the new page early, outside any locks on existing pages. It doesn't
necessarily need to be done by a daemon thread at all.

Matthew

--
I'm NOT paranoid! Which of my enemies told you this?