From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Limiting setting of hint bits by read-only queries; vacuum_delay |
Date: | 2013-03-25 21:23:23 |
Message-ID: | CA+U5nMJzJzYhh4RcOXO_EX-GRZFrZC1dcbEw8OSCo7WNFmOJLg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 25 March 2013 20:44, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>> Merlin Moncure <mmoncure(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Brendan Jurd | 2013-03-25 21:50:48 | Re: adding support for zero-attribute unique/etc keys |
Previous Message | Merlin Moncure | 2013-03-25 21:21:38 | Re: Limiting setting of hint bits by read-only queries; vacuum_delay |