背景
OLTPのようなSQLを短時間に大量に発行するアプリケーションでは、SQLのwhere条件の値にリテラル(文字列)を使うと値が異なるたびにハードパース(アクセスプランの作成)が発生してDBサーバーのCPU使用率が高くなります。where条件の値にバインド変数(パラメーターマーカー)を使うと値が異なってもソフトパース(キャッシュにあるアクセスプランを再利用)になるため、CPU使用率の高騰を防ぐことができます。
ただし、処理速度から考えると実際の値から統計情報を考慮してアクセスプランを作成できるリテラルの方がバインド変数より優れています。 この課題を解決する方法として、Db2にはバインド変数を使っていても実際の値を使ってアクセスプランを作成するREOPTという機能があります。
ちなみにDb2のReoptに相当する機能は他のDBMSにもあります。Oracleならバインドピーク(Bind Peek)、SQL Serverならパラメータースニッフィング(Parameter Sniffing)です。
概要
REOPTには3種類の設定があります。NONEがデフォルトです。
- NONE ：値をセットせず、バインド変数のままSQLを実行
- ONCE ：最初のSQLを実行時に値をセットしてハードパース、以降はソフトパース
- ALWAYS：SQLを実行するたびに値をセットしてハードパース
REOPTはSQL単位、データソース単位などで設定できます。TomcatのJDBC設定でONCEをデフォルトとして指定しておいて特定のSQLのみALWAYSにすることも可能です。
| 設定 | 処理速度 | CPU使用率 | 安定性 | デメリット |
|---|---|---|---|---|
| NONE | △ | 〇 | 〇 | アクセスプランが最適にならない可能性 |
| ONCE | 〇 | 〇 | △ | 最初のセットが外れ値だとスロークエリー |
| ALWAYS | 〇 | △ | 〇 | 毎回ハードパースするため、CPUを消費 |
REOPTの詳細はIBMのサイトに記載されています。
[DB2 LUW] パラメーターマーカー使用時の SQL パフォーマンス
問題点
処理速度とCPU使用率から考えるとデフォルトはREOPT ONCEを使いたいです。REOPT ONCEの弱点は最初にセットされる値によってスロークエリーになる可能性があることです。OLTPアプリケーションですと、ほとんどのSQLは単純ですので最初にセットされる値でスロークエリーになること少ないと思いますが、一部の複雑なSQLは最初にセットされる値によってスロークエリーになる場合があります。
この最初の値が外れ値問題は、Oracleのバインドピーク、SQL Serverのパラメータースニッフィングも同じ問題を抱えています。
例えば、データ量の偏りが大きいテーブルで1回目のselectの条件はデータ量の検索が少ない値、2回目はデータ量の検索が多い値の場合、1回目で作成されたアクセスプランを2回目の処理が再利用することによりスロークエリーになるケースです。特にジョインを多用するSQLの場合、データ量が少ない場合とデータ量が多い場合とでアクセスプランが違ってスロークエリーになる印象があります。多くの場合、データ量が少ない場合もデータ量が多いアクセスプランを使っても体感速度では変わらないので、データ量が多いアクセスプランを採用してスロークエリーになるのを避けたいと思います。
スロークエリーになるSQLへの対策は以下が挙げられます。
| No. | 対策 | デメリット |
|---|---|---|
| 1 | 対象SQLのみをREOPT NONE | アクセスプランが最適にならない |
| 2 | 対象SQLのみをREOPT ALWAYS | CPU使用率が増加 |
| 3 | 対象SQLをヒント句でIndex指定 | 人間がアクセスパスを要解析 |
| 4 | 対象SQLのみをリテラルで記述 | CPU使用率が増加 |
| 5 | 対象SQLをシンプルに書き換える | 時間と工数がかかる |
| 6 | 対象SQLを最適値で定期実行 | ピーク時にキャッシュアウトの可能性 |
1～3はSQLにヒント句(最適化ガイドライン)を指定することにより実装できます。
1～5までが王道な対応と思いますが人間系の問題が伴います。SQLの修正はアプリケーションの修正なので開発部門に依頼、時間と人手がかかります。 6は邪道な気がしますが今回説明する内容です。
やりたいこと
スロークエリーになるSQLに対し、where条件の値に最適値をセットしたSQLを用意、シェルスクリプトで定期実行することによりキャッシュに常に最適なアクセスプランがあるようにする(以降、SQL学習スクリプト)。
- オフピーク時にパッケージキャッシュをフラッシュ
- フラッシュ直後にSQL学習スクリプトを実行
- 定期的にSQL学習スクリプトの実行を繰り返す
お手本SQLのメリットは以下が挙げられます。
| No. | メリット | 説明 |
|---|---|---|
| 1 | アプリケーション修正不要 | DB管理者がすぐに対応可能 |
| 2 | 外れ値がセットされたかを確認可能 | 最適値で実行により判断可能 |
| 3 | システムに対する影響が低い | selectしているだけなので |
- 1のメリットはスロークエリーが発生しても当日から翌日のスピード感で対応できることです。アプリケーション修正でこのスピード感は厳しい会社もあるかと思います。
- 2はスロークエリーの原因が外れ値問題とは限らないのでそれかどうかを確認できるのがメリットです。開発部門に対策1～5をやってもらうにしても前提として外れ値問題である証拠が必要です。
- 3はアプリケーション修正ではなくSelectのSQLを実行しているだけなのでリスクが低いのがメリットです。
時節よりSQL学習スクリプトの実装を説明します。
SQL学習スクリプトの実装
処理内容
- シェルスクリプトからJavaサーブレットをコンパイル、実行
- JavaサーブレットはJDBC経由でSQLを発行
環境
アプリケーションの開発言語はJavaです。SQL学習スクリプトもAPサーバーと同じ環境で稼働させます。
- DBサーバー：IBM Db2 V11.5, Ubuntu 20.04 LTS
- APサーバー：Tomcat 9, JDK 8, Ubuntu 18.04 LTS
パッケージキャッシュの種類
Db2は同じSQLを実行しても環境によってパッケージキャッシュが別になります。最初、SQLが同じならパッケージキャッシュも同じと考え、シェルスクリプトからDb2コマンドで同じSQLを実行したのですが、Javaアプリケーションとは別のパッケージキャッシュになりました。 対象アプリケーションとSQL学習スクリプトとの環境を合わせる必要があります。以下の単位でパッケージキャッシュが分かれることがわかりました。
- 開発言語：ドライバーが違うので分かれると思います。今回はJavaなのでJDBCです。
- 分離レベル：UR, CS, RS, RRでパッケージキャッシュが分かれます。
- SQL発行方式：Javaの場合、プリペアードステートメントかどうかでパッケージキャッシュが分かれます。
スロークエリーの収集
Qiitaの記事、Db2:後からスロークエリーを見つけたいを参考にスロークエリーを収集します。後述するステートメント・コンセントレーターでリテラルをバインド変数に変換している場合、値は変換前のリテラルが表示されます。値をバインド変数で書いている場合、セットすべき最適値を開発部門に確認するか、IBMのサイト、DB2: SQLステートメントのパラメーター・マーカーにセットされた値を確認する方法を参考にスロークエリーでセットされている値を確認します。
Db2にはリテラルで書かれているSQLをバインド変数のSQLに変換するステートメント・コンセントレーターという機能があります。全てのリテラルをバインド変数のSQLに変換するという大胆なことをしますが、SQLがリテラルで書かれているアプリケーションをバインド変数に修正する時間と人手がない場合にはありがたい機能です。
Javaプログラムの準備
Db2のマニュアルにあるサンプルプログラム、単純な JDBC アプリケーションの例を参考にJavaプログラムを準備します。サンプルは通常ステートメントでSQLを発行していますが、アプリケーションがプリペアードステートメントで発行している場合はそのように書き換えます。 スロークエリーをプログラムの中で書くか、便利に使えるようにSQLファイルにして外出しにするかは適宜にお願いします。
シェルスクリプトの準備
Javaをコンパイルして実行しているだけのスクリプトです。接続文字列にcurrentPackageSet=NULLIDR1を追加することによってREOPT ONCEで実行されます。
#!/bin/bash
SQL_DIR=/db2jcc/execfiles
JDK_DIR=/usr/lib/jvm/temurin-8-jdk-amd64/bin
DB2_JAR=/db2jcc/lib/db2jcc4.jar
ISO_UR=defaultIsolationLevel=1\;
CON_URL=//192.168.1.1:50000/appdb01:currentSchema=MYSCHEMA\;currentPackageSet=NULLIDR1\;
DB_USERNAME=myuser
DB_PASSWORD=mypassword
# コンパイル
$JDK_DIR/javac -cp $SQL_DIR:$DB2_JAR $SQL_DIR/EzJava.java > $SQL_DIR/javac_EzJava.log 2>&1
# SQLを実行して結果をファイル出力：分離レベルCS
$JDK_DIR/java -cp $SQL_DIR:$DB2_JAR EzJava $CON_URL $DB_USERNAME $DB_PASSWORD > $SQL_DIR/EzJava_cs.log 2>&1
# SQLを実行して結果をファイル出力：分離レベルUR
$JDK_DIR/java -cp $SQL_DIR:$DB2_JAR EzJava $CON_URL$ISO_UR $DB_USERNAME $DB_PASSWORD > $SQL_DIR/EzJava_ur.log 2>&1
注意点としてはアプリケーションが分離レベルCSでSQLを実行している場合、CSだけでなく分離レベルURでもSQLを学習させる必要があります。スクリプトでは接続文字列にdefaultIsolationLevel=1を追加してURで実行しています。どうやらDb2はアプリケーションが実行したSQLを裏で分離レベルURにして再実行しているようです。そのため、UR用のアクセスプランも用意しないとDb2が裏で実行したSQLがスロークエリーになる可能性が大です。
スクリプトのスケジュール
オフピーク時に以下のコマンドでパッケージキャッシュをフラッシュ、直後にSQL学習スクリプトを実行、その後、定期的に繰り返すようにcronに設定します。定期的に繰り返す間隔は後述のパッケージキャッシュの生存時間をもとに決めてください。
db2 flush package cache dynamic
パッケージキャッシュの確認方法
モニター表関数でパッケージキャッシュの中身を確認できます。where条件にSQLステートメントの前半をlike節で記述して指定のSQLを見ることが可能です。
SELECT DATE(insert_timestamp) AS INS_DATE,
TIME(insert_timestamp) AS INS_TIME,
DATE(last_metrics_update) AS UPD_DATE,
TIME(last_metrics_update) AS UPD_TIME,
stmt_exec_time / num_exec_with_metrics AS exec_time_per_num_#,
rows_read / num_exec_with_metrics AS rows_read_per_num,
num_exec_with_metrics,
query_cost_estimate,
effective_isolation as iso ,
Rtrim(stmt_text) AS statement,
executable_id
FROM (SELECT t.*,
RANK()
OVER(
PARTITION BY executable_id
ORDER BY (stmt_exec_time / num_exec_with_metrics )desc) AS RANK
FROM TABLE(Mon_get_pkg_cache_stmt(NULL, NULL, NULL, -2)) t
WHERE stmt_exec_time > 0
AND rows_read > 0)
WHERE RANK = 1
and stmt_text like 'SELECT COL1, COL2 FROM MYTABLE%'
ORDER BY INS_DATE, INS_TIME
FETCH first 20 ROWS only;
以下のように表示されます。
INS_DATE INS_TIME UPD_DATE UPD_TIME EXEC_TIME_PER_NUM_# ROWS_READ_PER_NUM NUM_EXEC_WITH_METRICS QUERY_COST_ESTIMATE ISO STATEMENT EXECUTABLE_ID
---------- -------- ---------- -------- -------------------- -------------------- --------------------- -------------------- --- ------------------------------------- -------------------------------------------------------------
2023-03-24 09:50:15 2023-03-24 14:10:16 11 1444 76 316 CS SELECT COL1, COL2 FROM MYTABLE x'0100000000000000394E0A000000000000000000020020230324095015248057'
2023-03-24 09:50:29 2023-03-24 14:10:33 15 2332 159 316 UR SELECT COL1, COL2 FROM MYTABLE x'0100000000000000FA4E0A000000000000000000020020230324095029971113'
2 レコードが選択されました。
insert_timestampがアクセスプランを作成した時間、last_metrics_updateがアクセスプランを再利用した時間です。
列の詳細はDb2のマニュアルを見てください。
MON_GET_PKG_CACHE_STMT 表関数 - パッケージ・キャッシュ・ステートメント・メトリックの取得
パッケージキャッシュの生存時間
パッケージキャッシュの生存時間はいっぱいになると古いものから削除されます。SQL学習スクリプトはスロークエリーを最適値でアクセスプランを作成、パッケージキャッシュに常駐させるためのものなので、常駐させるアクセスプランがキャッシュアウトされないように生存時間を把握する必要があります。 アプリケーションが使わないダミーSQLをSQL学習スクリプトで定期的に発行し、パッケージキャッシュのlast_metrics_updateの日時を更新します。次のSQLで現在時刻とlast_metrics_updateの最小値との差を求めます。このSQLでcron定期実行しログを取得します。
select current timestamp as timestamp, min(last_metrics_update) as last_metrics_update,
TIMESTAMPDIFF(4,char(current timestamp - min(last_metrics_update))) as minutes
from table(mon_get_pkg_cache_stmt(null, null, null, -2))
where section_type = 'D' ;
以下のように表示されます。MINUTES列にパッケージキャッシュの生存時間が表示されますのでこの最小値よりも短い間隔でSQL学習スクリプトを実行します。
TIMESTAMP LAST_METRICS_UPDATE MINUTES
-------------------------- -------------------------- -----------
2023-03-27-08.30.17.648884 2023-03-25-16.59.40.187504 2370
2023-03-27-08.40.17.660143 2023-03-25-16.59.40.187504 2380
2023-03-27-08.50.17.716954 2023-03-25-16.59.40.187504 2390
2023-03-27-09.00.17.808559 2023-03-27-06.46.40.225944 133
2023-03-27-09.10.17.962319 2023-03-27-08.01.43.610033 68
2023-03-27-09.20.18.047991 2023-03-27-08.47.57.395576 32
2023-03-27-09.30.17.208402 2023-03-27-08.56.08.003888 34
2023-03-27-09.40.17.290989 2023-03-27-09.05.18.076547 34
2023-03-27-09.50.17.401778 2023-03-27-09.16.22.197491 33
2023-03-27-10.00.17.485806 2023-03-27-09.23.55.696520 36
2023-03-27-10.10.17.576362 2023-03-27-09.36.42.416790 33
2023-03-27-10.20.17.653890 2023-03-27-09.43.16.483966 37
2023-03-27-10.30.17.718682 2023-03-27-09.53.47.433389 36
2023-03-27-10.40.17.748468 2023-03-27-10.01.00.213119 39
2023-03-27-10.50.17.902828 2023-03-27-10.08.27.485670 41
2023-03-27-11.00.17.982862 2023-03-27-10.22.32.553582 37
まとめ
REOPT ONCEの最初の値に外れ値がセットされたときにスロークエリーになる対策として最適値でアクセスプランを作成してパッケージキャッシュに常駐させるという方法を説明しました。パッケージキャッシュの生存時間に注意しなければなりませんがプログラム修正を伴う方法に比べ、スピード感のある対策かと思います。
参考までにOracleのバインドピークには最初の値に外れ値がセットされる弱点への対応策があります。Oracle 11gより、Adaptive Cursor Sharingという機能でバインド変数の値が既存のアクセスプランでは不適切だと判断した場合、新たにアクセスプランを作成します。Db2もREOPT ONCEの機能を強化してもらえればと願います。