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

On Mon, Mar 25, 2013 at 1:05 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 25 March 2013 14:26, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> This is pretty similar to the proposal Atri and I just recently made.
>> I am 100% in agreement that something must be done here...SELECT has
>> none of the i/o mitigation features that vacuum has. Is your idea
>> better? probably (although you have to give a small penalty for a user
>> facing tunable)
>
> I was hoping this was a new idea entirely, since I was focusing on
> simply limiting foreground work rather than trying to work out how to
> optimise foreground work or work out how to make background tasks work
> better.

They are very similar, in that based on $simple_condition hint bits do
not get written out during a scan. Also, the effect in both cases is
to push more work into vacuum.

Our $simple_condition was a little different and maybe less good than
yours, but that should be proven. A good starting point would be to
run the battery of performance tests that Amit and Hari ran against
what Atri proposed. After seeing the results, I hedged on pushing the
patch further -- it wasn't clear that the results were win-win and I
think your patch idea mostly has the same pros/cons (see:
http://postgresql.1045698.n5.nabble.com/WIP-patch-for-hint-bit-i-o-mitigation-td5730963i20.html).

merlin