Re: snapshot too old, configured by time

On Thu, Apr 7, 2016 at 3:56 PM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
>> Unfortunately, this does stop recycling from
>> happening early for B-Tree pages, even though that's probably safe in
>> principle. This is probably not so bad -- it just needs to be
>> considered when reviewing this patch (the same is true of logical
>> decoding's RecentGlobalDataXmin; it also doesn't appear in
>> _bt_page_recyclable(), and I guess that that was never a problem).
>> Index relations will not get smaller in some important cases, but they
>> will be made less bloated by VACUUM in a sense that's still probably
>> very useful.
>
> As I see it, if the long-running transaction(s) have written (and
> thus acquired transaction IDs), we can't safely advance the global
> Xmin values until they complete. If the long-running transactions
> with the old snapshots don't have transaction IDs, the bloat will
> be contained.

I'm not really that concerned about it. I'm mostly just explaining my
thought process.

>> Maybe that explains some of what Jeff talked about.
>
> I think he just didn't have autovacuum configured to where it was
> being very effective on the tiny tables involved. See my reply to
> Jeff and the graphs from running his test on my system. I don't
> think the lines could get much more flat than what I'm seeing with
> the patch.
>
> It may be that this general approach could be made more aggressive
> and effective by pushing things in the direction you suggest, but
> we are far past the time to consider that sort of change for 9.6.
> This patch has been through several rounds of 30-day testing; a
> change like you propose would require that those tests be redone.

I think that there is a good argument in favor of this patch that you
may have failed to make yourself, which is: it limits bloat in a way
that's analogous to how RecentGlobalDataXmin can do so for logical
decoding (i.e. where wal_level = logical, and RecentGlobalXmin and
RecentGlobalDataXmin could actually differ). Therefore, it benefits to
a significant degree from the testing that Andres did to make sure
logical decoding doesn't cause excessive bloat when RecentGlobalXmin
is pinned to make historic MVCC catalog snapshots work (he did so at
my insistence at the time; pruning turned out to be very important for
many common workloads, and Andres got that right). I can't really
imagine a way that what you have here could be any less effective than
what Andres did for logical decoding. This is reassuring, since that
mechanism has to be pretty well battle-hardened by now.

--
Peter Geoghegan