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