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
657985...@qq.com
From: Merlin Moncure
Date: 2015-10-28 02:35
To: Bill Moran
CC: 657985...@qq.com; pgsql-general
Subject: Re: [GENERAL] ??: postgres cpu 100% need help
On Tue, Oct 27, 2015 at 12:14 PM, Bill Moran <wmo...@potentialtech.com> wrote:
> On Tue, 27 Oct 2015 11:30:45 +0800
> "657985...@qq.com" <657985...@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.
>
> 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
