Re: Limiting setting of hint bits by read-only queries; vacuum_delay

On 25 March 2013 20:44, Kevin Grittner <kgrittn@ymail.com> wrote:
> Simon Riggs <simon@2ndQuadrant.com> wrote:
>> Merlin Moncure <mmoncure@gmail.com> wrote:
>
>>> we need testing against real world workloads, or at least a much
>>> better synthetic one than pgbench, which per recent discussions
>>> is probably the top objective of the project (a performance
>>> farm, etc.).
>>
>> Self-tuning the background workloads needs lots of testing.
>> Limiting foreground work needs very little, or none.
>
> 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.

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.

> 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.

> 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.

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

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