Postgres Pro
Downloads
Home   >   mailing lists

Re: ??: postgres cpu 100% need help - Mailing list pgsql-general

From 657985552@qq.com
Subject Re: ??: postgres cpu 100% need help
Date
Msg-id 2015102810594920807539@qq.com
Whole thread Raw
In response to 回复: postgres cpu 100% need help  ("657985552@qq.com" <657985552@qq.com>)
List pgsql-general
Tree view
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@qq.com
 
From: Bill Moran
Date: 2015-10-28 01:14
Subject: Re: [GENERAL]??: postgres cpu 100% need help
On Tue, 27 Oct 2015 11:30:45 +0800
"657985552@qq.com" <657985552@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.
 
--
Bill Moran

pgsql-general by date:

Previous
From: Edson Richter
Date:
Subject: PostgreSQL Timezone and Brazilian DST
Next
From: "657985552@qq.com"
Date:
Subject: Re: ??: postgres cpu 100% need help