Re: ??: postgres cpu 100% need help

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

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

 

From: Bill Moran

Date: 2015-10-28 01:14

To: 657985552@xxxxxx

CC: pgsql-general

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

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.

 

--

Bill Moran