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

2015-11-08 14:22 GMT+01:00 莎士比亚说： <657985552(at)qq(dot)com>:

> 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