Re: [GENERAL] 回复： [GENERAL] ??: postgres cpu 100% need help

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2015-11-08 14:22 GMT+01:00 莎士比亚说： <657985552@xxxxxx>:

Hi moran and others;

    yesterday i get the pg  problem  again, and i use perf top Observation follows:

PerfTop:   11574 irqs/sec  kernel: 2.2%  exact:  0.0% [4000Hz cycles],  (all, 32 CPUs)     

    81.39%  postgres       [.] s_lock

     5.42%  postgres       [.] LWLockAcquire

     4.59%  postgres       [.] LWLockRelease

     3.06%  postgres       [.] TransactionIdIsInProgress

     0.38%  postgres       [.] PinBuffer

     0.31%  postgres       [.] TransactionIdPrecedes

     0.27%  postgres       [.] UnpinBuffer

     0.19%  postgres       [.] TransactionIdIsCurrentTransactionId

     0.16%  postgres       [.] heap_hot_search_buffer

     0.15%  [kernel]       [k] number.isra.1

     0.14%  [kernel]       [k] kallsyms_expand_symbol.constprop.1

     0.10%  [kernel]       [k] module_get_kallsym

     0.10%  libc-2.17.so   [.] __strcmp_sse42

     0.09%  [kernel]       [k] _raw_spin_lock

     0.09%  postgres       [.] hash_search_with_hash_value

is spin lock problem ？  I need everyone's help to solve the problem.thsnks!

yes, it is spin lock problem. you can try to limit number of active connections - http://www.enterprisedb.com/postgres-plus-edb-blog/amit-kapila/read-scalability-postgresql-95. You can try 9.4, where some issues is fixed

When you hit this issue, the basic prerequisite is test case. If you can run the simulation with this issue, then we can identify bottleneck.

You can try to check lwlocks via systemtap http://postgres.cz/wiki/Monitorov%C3%A1n%C3%AD_lwlocku_pomoc%C3%AD_systemtapu - the article is in Czech language, but this page support Google transalator.

 Regards

Pavel