Re: [HACKERS] pgsql: Avoid archiving XLOG_RUNNING_XACTS on idle server

On 6 April 2016 at 10:09, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2016-04-06 10:04:42 +0100, Simon Riggs wrote:
> > On 6 April 2016 at 09:45, Andres Freund <andres(at)anarazel(dot)de> wrote:
> >
> > > On 2016-04-06 09:18:54 +0100, Simon Riggs wrote:
> > > > Rather than take that option, I went to the trouble of writing a
> patch
> > > that
> > > > does the same thing but simpler, less invasive and more maintainable.
> > > > Primarily, I did that for you, to avoid you having wasted your time
> and
> > > to
> > > > allow you to backpatch a solution.
> > >
> > > But it doesn't. It doesn't solve the longstanding problem of
> checkpoints
> > > needlessly being repeated due to standby snapshots.
>
> > <sigh> I can't see why you say this. I am willing to listen, but this
> > appears to be wrong.
>
> The issue there is that we continue to issue checkpoints if the only
> activity since the last checkpoint was emitting a standby
> snapshot. That's because:
>

I agree this is the current situation in 9.4 and 9.5, hence the bug report.

This no longer occurs with the patches I have proposed. The snapshot is
skipped, so a further checkpoint never triggers.

> The proposed patch allows to fix that in a more principled manner,
> because we can simply check that no "important" records have been
> emitted since the last checkpoint, and skip if that's the case.

I understand the proposal you have made. The patch to implement it is what
I object to; my comments made last Sunday.

> > What issue is that? Previously you said it must not skip it at all for
> > logical.
>
> It's fine to skip the records iff nothing important has happened since
> the last time a snapshot has been logged. Again, the proposed approach
> allowed to detect that.

Agreed, both proposals do that.

> > > We now log more WAL with
> > > XLogArchiveTimeout > 0 than without.
>
> > And the problem with that is what?
>
> That an idle system unnecessarily produces WAL? Waking up disks and
> everything?
>

The OP discussed a problem with archive_timeout > 0. Are you saying we
should put in a fix that applies whatever the setting of archive_timeout?

Are we concerned that a master sends a small amount of data every few
minutes to a standby it is connected to? I hadn't read that in the thread.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services