Re: Proposal for CSN based snapshots

On 2014-05-15 15:40:06 -0400, Robert Haas wrote:
> On Thu, May 15, 2014 at 2:34 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > On Mon, May 12, 2014 at 06:01:59PM +0300, Heikki Linnakangas wrote:
> >> >Some of the stuff in here will be influence whether your freezing
> >> >replacement patch gets in. Do you plan to further pursue that one?
> >>
> >> Not sure. I got to the point where it seemed to work, but I got a
> >> bit of a cold feet proceeding with it. I used the page header's LSN
> >> field to define the "epoch" of the page, but I started to feel
> >> uneasy about it. I would be much more comfortable with an extra
> >> field in the page header, even though that uses more disk space. And
> >> requires dealing with pg_upgrade.
> >
> > FYI, pg_upgrade copies pg_clog from the old cluster, so there will be a
> > pg_upgrade issue anyway.
> >
> > I am not excited about a 32x increase in clog size, especially since we
> > already do freezing at 200M transactions to allow for more aggressive
> > clog trimming.  Extrapolating that out, it means we would freeze every
> > 6.25M transactions.

The default setting imo is far too low for a database of any relevant
activity. If I had the stomach for the fight around it I'd suggest
increasing it significantly by default. People with small databases
won't be hurt significantly because they simply don't have that many
transactions and autovacuum will get around to cleanup long before
normally.

> It seems better to allow clog to grow larger than to force
> more-frequent freezing.

Yes.

> If the larger clog size is a show-stopper (and I'm not sure I have an
> intelligent opinion on that just yet), one way to get around the
> problem would be to summarize CLOG entries after-the-fact.  Once an
> XID precedes the xmin of every snapshot, we don't need to know the
> commit LSN any more.  So we could read the old pg_clog files and write
> new summary files.  Since we don't need to care about subcommitted
> transactions either, we could get by with just 1 bit per transaction,
> 1 = committed, 0 = aborted.  Once we've written and fsync'd the
> summary files, we could throw away the original files.  That might
> leave us with a smaller pg_clog than what we have today.

I think the easiest way for now would be to have pg_clog with the same
format as today and a rangewise much smaller pg_csn storing the lsns
that are needed. That'll leave us with pg_upgrade'ability without
needing to rewrite pg_clog during the upgrade.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services