SQL Profiling処理について

by afaik

投稿日: 12月 26, 2023

SQLの処理に時間がかかっている場合は、基本的にEXPLAINで実行プランを確認して頂き、
必要に応じてパフォーマンスチューニングして頂きますが、更にSQLのどのイベントで時間がかかっているか確認したい時はprofilingを利用していましたがDeprecate Noticeが以前から出ているので、
Performance Schemaを用いたProfilingに慣れていく必要があります。どうしても慣れているProfilingをいまだに使いがちなので、こちらに簡単にメモしておきます。色々なサイトで方法を説明して頂いているので詳細は記載しませんが、ここでは少しだけアウトプットを確認し易くしています。

環境

root@localhost [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.00 sec)

1: 事前準備 (Performance SchemaはON)

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';

2: SQL処理を実行してみる

select c.CountryCode,c.Population,cl.Language from city c inner join countrylanguage cl
on c.CountryCode = cl.CountryCode where c.CountryCode = 'JPN' order by c.Population desc limit 10;

3: 実行したSQLに絞ってEVENT IDを確認

SQL_TEXTは遅い処理に含まれる文字に入れ替えて下さい。
THREAD_IDはここでは自分の接続を指定してますが、SHOW PROCESS LIST等で確認したConnection_IDをPS_THREAD_IDで変換して入力してください。

select THREAD_ID,EVENT_ID,replace(replace(replace(left(SQL_TEXT,40),'\)r\n',''),'\r',''),'\n','') as sql_text,
DATE_ADD(SYSDATE(), INTERVAL - ((select Variable_value from sys.metrics where Variable_name = 'uptime') - TIMER_START*POW(10,-12)) SECOND) 'Start_Time'
from performance_schema.events_statements_history_long where SQL_TEXT like '%city%' and THREAD_ID = PS_THREAD_ID(connection_id()) 
order by Start_Time limit 3;

4: 上記で取得したEVENT IDを指定してSQLのパフォーマンスを確認 (565は上記で取得したEVENT ID)

SELECT event_name,source,format_pico_time(timer_wait) from performance_schema.events_stages_history_long 
where NESTING_EVENT_ID = 565

以下の様に実行したSQLのどのEVENTにて時間がかかっているか確認する事が可能

上記スレッドのEVENTの種類や概要に関しては、以下のマニュアルを参照して頂くと良いでしょう。

https://dev.mysql.com/doc/refman/8.0/ja/general-thread-states.html

カテゴリー:

タグ:

MySQL Profiling