Re: Extra XLOG in Checkpoint for StandbySnapshot

On Tuesday, January 08, 2013 8:01 PM Andres Freund wrote:
> 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.

Even if the checkpoint is scheduled, it doesn't perform actual operation if
there's nothing logged between
current and previous checkpoint due to below check in CreateCheckPoint()
function.
if (curInsert == ControlFile->checkPoint +
MAXALIGN(SizeOfXLogRecord + sizeof(CheckPoint)) &&
ControlFile->checkPoint ==
ControlFile->checkPointCopy.redo)

But if we set the wal_level as hot_standby, it will log snapshot, now next
time again when function CreateCheckPoint()
will get called due to scheduled checkpoint, the above check will fail and
it will again log snapshot, so this will continue, even if the system is
totally idle.
I understand that it doesn't cause any problem, but I think it is better if
the repeated log of snapshot in this scenario can be avoided.

> There was a thread some time
> back about that and its not as trivial as it seems at the first glance.

I know some part of it that it has been fixed to avoid checkpoint operation
for low activity system and later on that change is rolledback due to
another problem, but I am not sure if it has been agreed that we don't need
to do anything for the above scenario.

With Regards,
Amit Kapila.