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

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(at)qq(dot)com

发件人： Jeff Janes
发送时间： 2015-10-28 03:33
收件人： 657985552(at)qq(dot)com
抄送： pgsql-general
主题： Re: [GENERAL] 回复: postgres cpu 100% need help
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:

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

Cheers,

Jeff