Home > mailing lists

Re: often PREPARE can generate high load (and sometimes minutes long unavailability) - Mailing list pgsql-hackers

From	Pavel Stehule
Subject	Re: often PREPARE can generate high load (and sometimes minutes long unavailability)
Date	February 24, 2014 21:04:35
Msg-id	CAFj8pRCKda-OcxOwNXBcVBRutWsVaeq5a+rAmUJ8ZSbaYYyrnQ@mail.gmail.com Whole thread Raw
In response to	Re: often PREPARE can generate high load (and sometimes minutes long unavailability) (Andres Freund <andres@2ndquadrant.com>)
List	pgsql-hackers

Tree view

2014-02-24 16:09 GMT+01:00 Andres Freund <andres@2ndquadrant.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

pgsql-hackers by date:

From: Bruce Momjian
Date: 24 February 2014, 20:56:58
Subject: Re: old warning in docs

From: Bruce Momjian
Date: 24 February 2014, 21:05:08
Subject: Re: Auto-tuning work_mem and maintenance_work_mem

Re: often PREPARE can generate high load (and sometimes minutes long unavailability) - Mailing list pgsql-hackers

Previous

Next