Re: ??: postgres cpu 100% need help

Поиск
Список
Период
Сортировка
От 657985552@qq.com
Тема Re: ??: postgres cpu 100% need help
Дата
Msg-id 2015102810594920807539@qq.com
обсуждение исходный текст
Ответ на 回复: postgres cpu 100% need help  ("657985552@qq.com" <657985552@qq.com>)
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Edson Richter
Дата:
Сообщение: PostgreSQL Timezone and Brazilian DST
Следующее
От: "657985552@qq.com"
Дата:
Сообщение: Re: ??: postgres cpu 100% need help