Re: ??: postgres cpu 100% need help

Thank you for your reply. i will try to change it and Waiting for it to appear again.the sql is :
select count(t.*) into o_count from tshow.res_room_weight t,tshow.res_room_info r
where t.subcatlg_id=:i_title_id
and t.roomid = r.actorid
and r.levels>=0;

tshow=> \d res_room_info;
Table "tshow.res_room_info"
Column | Type | Modifiers
----------------+-----------------------------+---------------
actorid | integer | not null
nickname | text | not null
livetype | integer |
people_inroom | integer |
poster | character varying(128) |
actor_level | integer |
operatorid | integer |
jointime | timestamp without time zone |
signtime | timestamp without time zone |
levels | integer |
note | text |
leavereason | text |
register_city | integer |
vedio_level | integer |
is_good | integer | default 0
is_display | integer |
live_starttime | timestamp without time zone |
live_endtime | timestamp without time zone |
next_starttime | timestamp without time zone |
max_count | integer | default 40000
is_recommend | integer |
icon | integer |
rich_level | integer |
type | integer |
room_mode | integer |
room_theme | text |
portrait | text |
gender | integer | default 0
tag | text |
live_poster | text |
family_id | integer |
room_lock | integer | default 0
Indexes:
"res_room_info_pkey" PRIMARY KEY, btree (actorid)
"idx_res_room_info_cityid" btree (register_city)

tshow=> \d tshow.res_room_weight
Table "tshow.res_room_weight"
Column | Type | Modifiers
--------------+-----------------------------+-----------
subcatlg_id | integer | not null
roomid | integer | not null
weight | integer | default 0
is_recommend | integer |
update_time | timestamp without time zone |
product_id | integer | default 1
create_time | timestamp without time zone |
Indexes:
"res_room_weight_pkey" PRIMARY KEY, btree (subcatlg_id, roomid) CLUSTER

tshow=> select count(*) from tshow.res_room_info ;
count
-------
22648
(1 row)

tshow=> select count(*) from tshow.res_room_weight ;
count
-------
23417

i don't see any Exclusive lock in pg_lock view .

657985552(at)qq(dot)com

From: Bill Moran
Date: 2015-10-28 01:14
To: 657985552(at)qq(dot)com
CC: pgsql-general
Subject: Re: [GENERAL]??: postgres cpu 100% need help
On Tue, 27 Oct 2015 11:30:45 +0800
"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.

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.

--
Bill Moran