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

On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule <pavel.stehule@gmail.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.

Cheers,

Jeff