ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Opened 13 years ago

Closed 12 years ago

#1151 closed enhancement (fixed)

負荷高いSNSのスロークエリログを元にしたSQLチューニング

Reported by: kunitada Owned by: nobody
Priority: major Milestone:
Component: pne-sysadmin Version:
Keywords: Cc:

Description (last modified by ogawa)

手嶋屋ASPの中の、とあるヘビーなSNSにおけるスロークエリ頻出ワースト10
(ユーザー数52000、集計期間:6/15~8/21)


1位 3600件:SELECT c_commu.*, COUNT(c_commu_member.c_member_id) AS count_commu_member FROM c_commu, c_commu_member WHERE N AND
c_commu_member.c_commu_id = c_commu.c_commu_id GROUP BY c_commu_member.c_commu_id ORDER BY count_commu_member DESC LIMIT N, N


2位 373件: SELECT COUNT(*) FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N LIMIT N, N

3位 262件: SELECT d.c_diary_id, d.subject, d.c_member_id, MAX(dc.r_datetime) AS r_datetime, COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment FROM c_diary AS d INNER JOIN c_diary_comment AS dc USING (c_diary_id), c_diary_comment AS mydc WHERE mydc.c_member_id = N AND mydc.c_diary_id = d.c_diary_id AND mydc.c_member_id <> d.c_member_id AND d.c_member_id NOT IN
(N) AND (d.public_flag = 'S' OR (d.public_flag = 'S' AND d.c_member_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
,N))) GROUP BY dc.c_diary_id ORDER BY r_datetime DESC LIMIT N, N

4位 146件:SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

5位 92件: SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N, N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT N, N

6位 82件: SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N, N, N, N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT N, N

7位 80件: SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT N, N

8位 77件: SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N, N, N, N, N, N, N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT
N, N

8位 77件: SELECT COUNT(*) FROM c_message WHERE c_member_id_from = N AND is_deleted_from = N AND is_send = N LIMIT N, N

9位 76件: SELECT d.c_diary_id, d.subject, d.c_member_id, MAX(dc.r_datetime) AS r_datetime, COUNT(DISTINCT dc.c_diary_comm
ent_id) AS num_comment FROM c_diary AS d INNER JOIN c_diary_comment AS dc USING (c_diary_id), c_diary_comment AS mydc WHERE mydc.c_member_id = N AND mydc.c_diary_id = d.c_diary_id AND mydc.c_member_id <> d.c_member_id AND d.c_member_id NOT IN
(N,N) AND (d.public_flag = 'S' OR (d.public_flag = 'S' AND d.c_member_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N))) GROUP BY dc.c_diary_id ORDER BY r_datetime DESC LIMIT N, N
手嶋屋ASPの中の、別のヘビーなSNSにおけるスロークエリ頻出ワースト10 (ユーザー数24000集計期間:7/19~8/21)

1位 1142件 : SELECT COUNT(*) FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N LIMIT N, N

2位 404件 : SELECT c_commu.*, COUNT(c_commu_member.c_member_id) AS count_commu_member FROM c_commu, c_commu_member WHERE N AND c_commu_member.c_commu_id = c_commu.c_commu_id GROUP BY c_commu_member.c_commu_id ORDER BY count_commu_member DESC LIMIT N, N

3位 297件 : SELECT c_message_id FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N AND c_message_id > N ORDER BY r_datetime LIMIT N, N

4位 155件 : SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

5位 76件 : SELECT c_member_id FROM c_member_profile WHERE c_profile_id = N AND public_flag = 'S' AND c_profile_option_id = N ORDER BY c_member_id DESC

6位 55件 : SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N,N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

6位 55件 :SELECT COUNT(*) FROM c_message WHERE c_member_id_from = N AND is_deleted_from = N AND is_send = N LIMIT N, N

7位 42件 : SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N,N,N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

8位 39件 :SELECT * FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N ORDER BY r_datetime DESC LIMIT N, N

9位 35件 : SELECT c_commu.*, COUNT(c_commu_member.c_member_id) AS count_commu_member FROM c_commu, c_commu_member WHERE N AND c_commu.c_commu_category_id = N AND c_commu_member.c_commu_id = c_commu.c_commu_id GROUP BY c_commu_member.c_commu_id ORDER BY count_commu_member DESC LIMIT N, N

-------------
これは別のSNSの集計。
メッセージがかなり活用されてるため、その1と結果が異なる。

関連チケット:#562

http://openpne.jp/?m=pc&a=page_fh_diary&target_c_diary_id=12435

Change History (9)

comment:1 Changed 13 years ago by kunitada

Description: modified (diff)

comment:2 Changed 13 years ago by kunitada

Milestone: OpenPNE2.10OpenPNE2.10beta2
Priority: criticalblocker

comment:3 Changed 13 years ago by kunitada

Milestone: OpenPNE2.10beta2OpenPNE2.10beta1

comment:4 Changed 13 years ago by kunitada

Priority: blockermajor

comment:5 Changed 13 years ago by ogawa

Description: modified (diff)

関連チケット:#562

comment:6 Changed 13 years ago by ogawa

Milestone: OpenPNE2.10beta1OpenPNE2.10

comment:7 Changed 13 years ago by ogawa

Component: pne-adminpne-sysadmin

comment:8 Changed 12 years ago by kunitada

Milestone: OpenPNE2.10OpenPNE2.12

comment:9 Changed 12 years ago by ogawa

Milestone: OpenPNE2.12.0
Resolution: fixed
Status: newclosed

2.12で改善されたSQLが多数含まれます。

再度、スロークエリログを取りなおしてチューニングを進めましょう。

Note: See TracTickets for help on using tickets.