Postgres Pro
Downloads
Home   >   mailing lists

Re: Re: [GENERAL] 回复: postgres cpu 100% need help - Mailing list pgsql-general

From 657985552@qq.com
Subject Re: Re: [GENERAL] 回复: postgres cpu 100% need help
Date
Msg-id 2015102815121537363650@qq.com
Whole thread Raw
In response to 回复: postgres cpu 100% need help  ("657985552@qq.com" <657985552@qq.com>)
Responses Re: Re: Re: [GENERAL] 回复: postgres cpu 100% need help
Re: Re: [GENERAL] 回复: postgres cpu 100% need help
List pgsql-general
Tree view
Thank you for your reply.
tshow=> explain (analyze, buffers)  select count(t.*)  from tshow.res_room_weight t,tshow.res_room_info r 
tshow->   where t.subcatlg_id=46 
tshow->   and t.roomid = r.actorid
tshow->   and r.levels>=0;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=24581.57..24581.58 rows=1 width=60) (actual time=112.613..112.613 rows=1 loops=1)
   Buffers: shared hit=23439
   ->  Hash Join  (cost=1737.31..24572.21 rows=3744 width=60) (actual time=16.375..111.879 rows=3730 loops=1)
         Hash Cond: (r.actorid = t.roomid)
         Buffers: shared hit=23439
         ->  Seq Scan on res_room_info r  (cost=0.00..22322.20 rows=22365 width=4) (actual time=0.031..86.026 rows=22365 loops=1)
               Filter: (levels >= 0)
               Rows Removed by Filter: 291
               Buffers: shared hit=22039
         ->  Hash  (cost=1689.90..1689.90 rows=3793 width=64) (actual time=16.274..16.274 rows=3793 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 329kB
               Buffers: shared hit=1397
               ->  Seq Scan on res_room_weight t  (cost=0.00..1689.90 rows=3793 width=64) (actual time=0.348..14.663 rows=3793 loops=1)
                     Filter: (subcatlg_id = 46)
                     Rows Removed by Filter: 19641
                     Buffers: shared hit=1397
 Total runtime: 112.754 ms

cost only  112.754 ms . the High load time log is :

2015-10-28 00:00:17.177 CST "SELECT",2015-10-23 19:30:20 CST,276/59546142,0,LOG,00000,"duration: 3300.237 ms  execute <unnamed>:                 SELECT          o_count                 FROM            tshow.p_hpart_getcount_intitle($1)","parameters: $1 = '46'",,,,,,,,""
657985552@qq.com
 
发件人： Jeff Janes
发送时间： 2015-10-28 03:33
收件人： 657985552@qq.com
抄送： pgsql-general
主题： Re: [GENERAL] 回复: postgres cpu 100% need help
On Mon, Oct 26, 2015 at 8:30 PM, 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.

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:

explain (analyze, buffers) select o_count from ....

Cheers,

Jeff

pgsql-general by date:

Previous
From: Edson Richter
Date:
Subject: Re: PostgreSQL Timezone and Brazilian DST
Next
From: John R Pierce
Date:
Subject: Re: Re: Re: [GENERAL] 回复: postgres cpu 100% need help