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

On 2014-02-23 20:04:39 +0100, Pavel Stehule wrote:
> 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

It's odd that there are so many exclusive acquisition
ProcArrayLocks... A hierarchical profile would be interesting. I'd
suggest compiling postgres with -fno-omit-frame-pointer and doing a
profile with perf.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services