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

On Wed, Oct 28, 2015 at 12:12 AM, 657985552(at)qq(dot)com <657985552(at)qq(dot)com> wrote:
> 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'",,,,,,,,""

You really need it during the high-load condition. Perhaps
auto_explain could help.

Cheers,

Jeff