=?gb18030?B?u9i4tKO6IFtHRU5FUkFMXSA/PzogcG9zdGdyZXMg?= =?gb18030?B?Y3B1IDEwMCUgbmVlZCBoZWxw?=
| От | 莎士比亚说： |
|---|---|
| Тема | =?gb18030?B?u9i4tKO6IFtHRU5FUkFMXSA/PzogcG9zdGdyZXMg?= =?gb18030?B?Y3B1IDEwMCUgbmVlZCBoZWxw?= |
| Дата | |
| Msg-id | tencent_543DABA91E50034B12AF3E51@qq.com обсуждение исходный текст |
| Ответ на | Re: ??: postgres cpu 100% need help (Merlin Moncure <mmoncure@gmail.com>) |
| Ответы |
Re: [GENERAL] 回复： [GENERAL] ??: postgres cpu 100% need help
(Pavel Stehule <pavel.stehule@gmail.com>)
Re: ??: postgres cpu 100% need help (Merlin Moncure <mmoncure@gmail.com>) |
| Список | pgsql-general |
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!
------------------ 原始邮件 ------------------
发件人: "Merlin Moncure";<mmoncure@gmail.com>;
发送时间: 2015年10月28日(星期三) 凌晨2:35
收件人: "Bill Moran"<wmoran@potentialtech.com>;
抄送: "莎士比亚说："<657985552@qq.com>; "pgsql-general"<pgsql-general@postgresql.org>;
主题: Re: [GENERAL] ??: postgres cpu 100% need help
> On Tue, 27 Oct 2015 11:30:45 +0800
> "657985552@qq.com" <657985552@qq.com> wrote:
>
>> Dear sir:
>> Recently a wired question about postgresql database really bothered me a lot, so i really need your help. Here is the problem, in the most situations the postgre database work very well, Average 3500tps/s per day, the cpu usage of its process is 3%~10% and every query can be responsed in less than 20ms, but sometimes the cpu usages of its process can suddenly grow up to 90%+ , at that time a simple query can cost 2000+ms. ps: My postgresql version is 9.3.5 and the database is oltp server.
>
> 9.3.5 is pretty old, you should probably schedule an upgrade.
>
>> shared_buffers | 25GB
>
> Try setting this to 16GB. It's been a while since I tested on
> large-memory/high-load systems, but I seem to remember that
> shared_buffers above 16G could cause these sorts of intermittant
> stalls.
>
> If that doesn't improve the situation, you'll probably need to
> provide more details, specifically the layout of the table in
> question, as well as the queries that are active when the
> problem occurs, and the contents of the pg_locks table when
> the problem is occurring.
possible culprits:
*) io based problems (check iowait, rule this out first)
*) THP compaction (rule this out second)
*) runaway query plan
*) concurrency problems within postgres itself (perf top capture
during load is essential)
maybe some other things I'm not thinking of.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
В списке pgsql-general по дате отправления: