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

On Tue, Oct 27, 2015 at 4:04 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Oct 27, 2015 at 2:33 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> On Mon, Oct 26, 2015 at 8:30 PM, 657985552(at)qq(dot)com <657985552(at)qq(dot)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.
>>
>>
>> From all the lseeks on the same file, it looks like you are doing a
>> sequential scan on a table for the inside of a nested loop. What is the
>> query it is running, and what is the execution plan for it? To get that,
>> run:
>
> this smells like thp compaction:

drat -- sorry for partial email.
check this link:
http://structureddata.org/2012/06/18/linux-6-transparent-huge-pages-and-hadoop-workloads/

if that turns out not to be productive, need to check 'perf top', and
also rule out problems with individual queries.

merlin