I got a example of code, that generate relatively high load with minimal

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
  EXECUTE pr(...)
  sleep(10 ms)
end loop

running both script together can produce high load with minimal number of
executed queries.

           354246.00 93.0% s_lock
            10503.00  2.8% LWLockRelease
             8802.00  2.3% LWLockAcquire
              828.00  0.2% _raw_spin_lock
              559.00  0.1% _raw_spin_lock_irqsave
              340.00  0.1% switch_mm
              305.00  0.1% poll_schedule_timeout
              274.00  0.1% native_write_msr_safe
              257.00  0.1% _raw_spin_lock_irq
              238.00  0.1% apic_timer_interrupt
              236.00  0.1% __schedule
              213.00  0.1% HeapTupleSatisfiesMVCC

With systemtap I got list of spin locks

light weight locks
            lockname       mode      count        avg (time)
        DynamicLocks  Exclusive       2804       1025
        DynamicLocks     Shared        106        130
       ProcArrayLock  Exclusive         63     963551
       ProcArrayLock     Shared         50       4160
        LockMgrLocks  Exclusive         18        159
     IndividualLock   Exclusive          2          7

There is relative few very long ProcArrayLocks lwlocks

This issue is very pathologic on fast computers with more than 8 CPU. This
issue was detected after migration from 8.4 to 9.2. (but tested with same
result on 9.0)  I see it on devel 9.4 today actualized.

When I moved PREPARE from cycle, then described issues is gone. But when I
use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is
related to planner, ...



Reply via email to