さっき、データベースが遅かった
よく言われる言葉です。今触っても遅くありません。遅かった時に実行されていたSQL、いわゆるスロークエリーが何かを知るにはどうすればいいでしょうか。
この記事では後からスロークエリーを見つける方法を紹介します。
環境は IBM Db2 V11.5 Linux と Ubuntu 20.04 LTSです。
データベースの性能を評価したい方は以下の記事もご覧ください。
Db2:性能評価にSQL平均応答時間を用いる
MON_CURRENT_SQL 管理ビュー
現在実行中のSQLを表示するビューです。SQLでselectして照会します。列が多いので必要な列のみをselectします。
select current timestamp,
application_handle,
application_name,
application_id,
activity_state,
elapsed_time_sec,
total_cpu_time,
rows_read,
substr(stmt_text,1,100) as stmt_text
from sysibmadm.mon_current_sql ;
列の詳細についてはIBMのウェブサイトをご覧ください。
MON_CURRENT_SQL - 全メンバーの全アクティビティーに関する主要なメトリックの取得
Db2コマンド・ウィンドウからSQLを実行します。
db2 -tf select_current_sql.sql
$ db2 -tf select_current_sql.sql
1 APPLICATION_HANDLE APPLICATION_NAME APPLICATION_ID ACTIVITY_STATE ELAPSED_TIME_SEC TOTAL_CPU_TIME ROWS_READ STMT_TEXT
-------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------- ---------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
2021-11-04-16.26.57.938503 14226 db2bp *LOCAL.blancdba.211104070911 EXECUTING 0 0 0 select current timestamp, application_handle, application_name, application_id, activity_state, elap
1 レコードが選択されました。
$
実行したSQLそのものが表示されます。
ACTIVITY_STATEが活動状態、ELAPSED_TIME_SECが経過時間(秒)になります。
この管理ビューを毎分selectして、活動状態がEXECUTINGかつ経過時間が1秒以上のSQLワースト5を記録します。
記録用テーブルの作成
遅かったSQLを記録するテーブル（例では、mon_get_sql）を作成します。
Db2コマンド・ウィンドウから以下のSQLを実行します。
db2 -tf create_mon_get_sql.sql
create table mon_get_sql (
ts,
application_handle,
application_name,
application_id,
activity_state,
elapsed_time_sec,
total_cpu_time,
rows_read,
stmt_text
) as (
select current timestamp,
application_handle,
application_name,
application_id,
activity_state,
elapsed_time_sec,
total_cpu_time,
rows_read,
stmt_text
from sysibmadm.mon_current_sql )
with no data in USERSPACE1 ;
create index idx_mon_get_sql_1 on mon_get_sql
(ts asc) allow reverse scans ;
照会結果をインサートするSQLの作成
遅かったSQLをインサートするSQLファイルを作成します。インサートする条件は、活動状態がEXECUTINGかつ経過時間が1秒以上のSQLワースト5です。1カ月以上前のレコードは削除するようにもします。
connect to qiita01 ;
delete from mon_get_sql where ts < current timestamp - 720 hour ;
insert into mon_get_sql
select current timestamp,
application_handle,
application_name,
application_id,
activity_state,
elapsed_time_sec,
total_cpu_time,
rows_read,
stmt_text
from sysibmadm.mon_current_sql
where elapsed_time_sec >= 1
and activity_state = 'EXECUTING'
order by elapsed_time_sec desc
limit 5 ;
connect reset ;
スケジュール実行の設定
インサートするSQLをLinuxならcron、Windowsならタスクスケジューラで毎分実行するように設定します。
記録された結果の参照
照会用のSQLファイルを作成します。elapsed_time_secを降順に指定することにより、遅いSQLが経過時間が長い順に出力されます。
select date(ts) as date ,
time(ts) as time ,
application_handle ,
substr(application_name,1,15) as application_name ,
substr(application_id,1,30) as application_id ,
substr(activity_state,1,15) as activity_state ,
elapsed_time_sec ,
total_cpu_time,
rows_read ,
substr(stmt_text,1,100) as stmt_text
from mon_get_sql
where date(ts) = current date - 0 day
and time(ts) between '08:00:00' and '17:00:00'
order by elapsed_time_sec desc, ts desc
limit 50
for read only ;
Db2コマンド・ウィンドウからSQLファイルを実行します。
db2 -tf select_mon_get_sql.sql
$ db2 -tf select_mon_get_sql.sql
DATE TIME APPLICATION_HANDLE APPLICATION_NAME APPLICATION_ID ACTIVITY_STATE ELAPSED_TIME_SEC TOTAL_CPU_TIME ROWS_READ STMT_TEXT
---------- -------- -------------------- ---------------- ------------------------------ --------------- ---------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
2021-11-01 14:04:22 17128 testhost.exe 192.168.201.6.57745.2111010504 EXECUTING 1 0 0 SELECT HSP_ID, ORD_SYBT, JISSI_DT, SEQ_SYBT, BUMON_NO FROM TC00BMN2 WHERE HSP_ID = '000001' AND ORD_
2021-11-01 13:56:22 16593 testhost.exe 192.168.201.6.65194.2111010456 EXECUTING 1 0 0 UPDATE TPTUKE SET UKE_KBN = '1', UP_DT = '2021-11-01', UP_TM = '13:56:21', UP_USR = '999999', EXAMI_
2 レコードが選択されました。
$
記録したSQLの抽出
記録したSQLは以下のSQLを実行することによりexportできます。where句でexportしたいSQLの条件を指定してください。
db2 -tf export_stmt_text.sql
export to out_stmt_text.sql of del modified by nochardel
select stmt_text || ' ;'
from mon_get_sql
where date(ts) = '2021-10-21'
and time(ts) = '11:36:22'
and application_handle = '13179'
for read only ;
まとめ
1分に1回の収集なのでスロークエリーの全てを補足できるわけではありません。逆に言うと、それで頻繁に記録されているようなら、記録されているSQLを調査する必要があると思います。