Re: often PREPARE can generate high load (and sometimes minutes long unavailability)
От | Pavel Stehule |
---|---|
Тема | Re: often PREPARE can generate high load (and sometimes minutes long unavailability) |
Дата | |
Msg-id | CAFj8pRCKda-OcxOwNXBcVBRutWsVaeq5a+rAmUJ8ZSbaYYyrnQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: often PREPARE can generate high load (and sometimes minutes long unavailability) (Andres Freund <andres@2ndquadrant.com>) |
Список | pgsql-hackers |
2014-02-24 16:09 GMT+01:00 Andres Freund <andres@2ndquadrant.com>:
On 2014-02-23 20:04:39 +0100, Pavel Stehule wrote:It's odd that there are so many exclusive acquisition> 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
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
- 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
Pavel
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
В списке pgsql-hackers по дате отправления: