From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: often PREPARE can generate high load (and sometimes minutes long unavailability) |
Date: | 2014-02-23 20:16:51 |
Message-ID: | CAFj8pRC8WgvzH_KMWiHaDaY8p+8XuRSPi45iumR4+jDW-aFdGg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
>
> See:
>
> "[PERFORM] Performance bug in prepared statement binding in 9.2?" and
> several related threads.
>
yes, it is very similar. Only it is little bit worse - on 16CPU it can
produce a 20-60 minutes unavailability
regards
Pavel
>
> Cheers,
>
> Jeff
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-02-23 20:28:18 | Re: Re: [HACKERS] Re: BUG #9210: PostgreSQL string store bug? not enforce check with correct characterSET/encoding |
Previous Message | Pavel Stehule | 2014-02-23 20:04:38 | Re: often PREPARE can generate high load (and sometimes minutes long unavailability) |