Re: Limiting setting of hint bits by read-only queries; vacuum_delay - Mailing list pgsql-hackers

Simon Riggs <simon@2ndQuadrant.com> wrote:
> On 25 March 2013 20:44, Kevin Grittner <kgrittn@ymail.com> wrote:

>> This is absolutely a real-world problem, but I disagree that the
>> solution you propose is risk-free.  It would be trivial to
>> construct a test which would show massive performance degradation.
>
> It is trivial to show massive performance degredation through the
> *lack* of this feature, as you know.

True, but I think we have to be more careful about causing
performance *regressions* in a new release than perpetuating
*existing* performance problems.

> Since so many people have experienced the pain, doing nothing because
> it isn't auto-tuned is not sensible. Preventing manual control of
> problems just doesn't make sense.

It's a matter of not destabilizing things with brand-new ideas this
late in the release cycle.

>> Consider a single largish table which fits in cache and is subject
>> to frequent seqscans, and a workload which burns through
>> transaction IDs fast enough to cause clog thrashing as these xids
>> get old and still lack hint bits.
>
> That wouldn't happen. I suggested setting hint bits but not dirtying
> the data blocks.

[ thinks about the implications of that a bit more ]

That would make it harder to construct a degenerate case; but it's
hard to be confident that real-world applications couldn't hit some
significant performance regressions.  If we had a performance
testing farm, this would be a lot less scary.

>> I think there are ways to deal with that problem, with the
>> foreground select telling the bgwriter or autovacuum to pay
>> attention to the problem tables (or pages), but now is not the time
>> to start designing that.
>
> We do already tell autovacuum to deal with the problem, but it wakes
> up too late to do anything useful.

I was thinking about some more direct communication to nudge those
into action under certain circumstances.  I think we could come up
with heuristics to ensure that the worst-case regression was small
enough not to be noticed.

> We've been waiting for a solution along those lines for most of a
> decade (late 2004). My guess is we'll spend a whole chunk of time and
> still implement something that doesn't work, just as we did with
> bgwriter in 8.0

So submit this for the first CF in the next release.  If we start
allowing new features to be designed at this point in the 9.3
cycle, 9.3 won't be released any sooner than 9.4 otherwise would
be.  Certainly you must have some features you would like to see
released before this summer is out.  If we open the door to new
ideas for 9.3, there will be an avalanche of "this is important,
too" patches, and you just won't see the release until *much*
later.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company