Re: ??: postgres cpu 100% need help

["657985552@xxxxxx" <657985552@xxxxxx>]

Thank you for your reply.

1. I'm sure the io is not problem .but  I didn't record the IO status at that time.

2.what is THP compaction ? How can i rule this out?

3. explain 

 tshow=> explain  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.50..24581.51 rows=1 width=60)
   ->  Hash Join  (cost=1737.27..24572.14 rows=3744 width=60)
         Hash Cond: (r.actorid = t.roomid)
         ->  Seq Scan on res_room_info r  (cost=0.00..22322.19 rows=22364 width=4)
               Filter: (levels >= 0)
         ->  Hash  (cost=1689.86..1689.86 rows=3793 width=64)
               ->  Seq Scan on res_room_weight t  (cost=0.00..1689.86 rows=3793 width=64)
                     Filter: (subcatlg_id = 46)

4. next i will use perf top to  capture during load is essential

---

657985552@xxxxxx

 

From: Merlin Moncure

Date: 2015-10-28 02:35

To: Bill Moran

CC: 657985552@xxxxxx; pgsql-general

Subject: Re: ??: postgres cpu 100% need help

On Tue, Oct 27, 2015 at 12:14 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:

> On Tue, 27 Oct 2015 11:30:45 +0800

> "657985552@xxxxxx" <657985552@xxxxxx> 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