Re: Extra XLOG in Checkpoint for StandbySnapshot

On 2013-01-08 19:51:39 +0530, Amit Kapila wrote:
> On Monday, January 07, 2013 7:15 PM Andres Freund wrote:
> > On 2013-01-07 19:03:35 +0530, Amit Kapila wrote:
> > > On Monday, January 07, 2013 6:30 PM Simon Riggs wrote:
> > > > On 7 January 2013 12:39, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
> > wrote:
> > > >
> > > > > So We can modify to change this in function LogStandbySnapshot as
> > > > below:
> > > > > running = GetRunningTransactionData();
> > > > > if (running->xcnt > 0)
> > > > > LogCurrentRunningXacts(running);
> > > > >
> > > > > So this check will make sure that if there is no operation
> > happening
> > > > i.e. no
> > > > > new running transaction, then no need to log running transaction
> > > > snapshot
> > > > > and hence further checkpoint operations will be skipped.
> > > > >
> > > > > Let me know if I am missing something?
> > > >
> > > > It's not the same test. The fact that nothing is running at that
> > > > moment is not the same thing as saying nothing at all has run since
> > > > last checkpoint.
> > >
> > > But isn't the functionality of LogStandbySnapshot() is to log "all
> > running
> > > xids" and "all current
> > > AccessExclusiveLocks". For RunningTransactionLocks, WAL is avoided in
> > > similar way.
> >
> > The information that no transactions are currently running allows you
> > to
> > build a recovery snapshot, without that information the standby won't
> > start answering queries. Now that doesn't matter if all standbys
> > already
> > have built a snapshot, but the primary cannot know that.
>
> Can't we make sure that checkpoint operation doesn't happen for below conds.
> a. nothing has happened during or after last checkpoint
> OR
> b. nothing except snapshotstanby WAL has happened
>
> Currently it is done for point a.
>
> > Having to issue a checkpoint while ensuring transactions are running
> > just to get a standby up doesn't seem like a good idea to me :)
>
> Simon:
> > If you make the correct test, I'd be more inclined to accept the premise.
>
> Not sure, what exact you are expecting from test?
> The test is do any one operation on system and then keep the system idle.
> Now at each checkpoint interval, it logs WAL for SnapshotStandby.

I can't really follow what you want to do here. The snapshot is only
logged if a checkpoint is performed anyway? As recovery starts at (the
logical) checkpoint's location we need to log a snapshot exactly
there. If you want to avoid activity when the system is idle you need to
prevent checkpoints from occurring itself. There was a thread some time
back about that and its not as trivial as it seems at the first glance.

Greetings,

Andres Freund

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