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
running both script together can produce high load with minimal number of
executed queries.
354246.00 93.0% s_lock
/usr/lib/postgresql/9.2/bin/postgres
10503.00 2.8% LWLockRelease
/usr/lib/postgresql/9.2/bin/postgres
8802.00 2.3% LWLockAcquire
/usr/lib/postgresql/9.2/bin/postgres
828.00 0.2% _raw_spin_lock
[kernel.kallsyms]
559.00 0.1% _raw_spin_lock_irqsave
[kernel.kallsyms]
340.00 0.1% switch_mm
[kernel.kallsyms]
305.00 0.1% poll_schedule_timeout
[kernel.kallsyms]
274.00 0.1% native_write_msr_safe
[kernel.kallsyms]
257.00 0.1% _raw_spin_lock_irq
[kernel.kallsyms]
238.00 0.1% apic_timer_interrupt
[kernel.kallsyms]
236.00 0.1% __schedule
[kernel.kallsyms]
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, ...
Regards
Pavel
