Re: often PREPARE can generate high load (and sometimes minutes long unavailability)

2014-02-23 20:35 GMT+01:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
>
>> Hello
>>
>> I got a example of code, that generate relatively high load with minimal
>> connections.
>>
>> This code is +/- bad - it repeatedly generate prepare statement, but
>> somewhere uses prepared statements as protections against SQL injections
>> and they can use same use case.
>>
>> Pseudocode (I can send a test case privately):
>>
>> Script a:
>>
>> -- A,B are in RAM
>> for i in 1 .. N loop
>> insert into A values();
>> for j in 1 .. M loop
>> insert into B values();
>> end loop;
>> end loop;
>>
>> Script b:
>>
>> -- query is extremely fast - returns 0 or 1 rows usually
>> 40 threads execute
>> while true loop
>> pr = PREPARE SELECT * FROM A LEFT JOIN B ON ..
>> EXECUTE pr(...)
>> sleep(10 ms)
>> end loop
>>
>
>
> Digging through uncommitted tuples at the top or bottom of an index (which
> happenings during planning, especially the planner of merge joins) is very
> contentious. Tom proposed changing the snapshot used for planning to
> Dirty, but the proposal didn't go anywhere because no one did the testing
> to confirm that it solved the problem in the field. Perhaps you can help
> do that.
>

I am able to test some patches. Thank you for info

Regards

Pavel

>
> See:
>
> "[PERFORM] Performance bug in prepared statement binding in 9.2?" and
> several related threads.
>
> Cheers,
>
> Jeff
>