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

2014-02-24 16:09 GMT+01:00 Andres Freund <andres(at)2ndquadrant(dot)com>:

> 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.
>
>
I had no experience with perf, so maybe it is not what you want

- 19.59% postmaster postgres
- s_lock
- 55.06% LWLockAcquire
+ 99.84% TransactionIdIsInProgress
- 44.63% LWLockRelease
+ 99.91% TransactionIdIsInProgress
- 13.84% postmaster postgres
- tas
- 97.97% s_lock
+ 55.01% LWLockAcquire
+ 44.99% LWLockRelease
- 1.10% LWLockAcquire
+ 99.89% TransactionIdIsInProgress
- 0.93% LWLockRelease
+ 99.93% TransactionIdIsInProgress

- 19.59% postmaster postgres
- s_lock
- 55.06% LWLockAcquire
- 99.84% TransactionIdIsInProgress
HeapTupleSatisfiesMVCC
heap_hot_search_buffer
index_fetch_heap
index_getnext
get_actual_variable_range
ineq_histogram_selectivity
scalarineqsel
mergejoinscansel
cached_scansel
initial_cost_mergejoin
try_mergejoin_path
sort_inner_and_outer
add_paths_to_joinrel
make_join_rel
make_rels_by_clause_joins
join_search_one_level
standard_join_search
make_rel_from_joinlist
make_one_rel
query_planner
grouping_planner
subquery_planner
standard_planner
planner
pg_plan_query
pg_plan_queries
BuildCachedPlan
GetCachedPlan
exec_bind_message
PostgresMain
ExitPostmaster
BackendStartup
ServerLoop
PostmasterMain
startup_hacks

regards

Pavel

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