Insight SQL Testing を触ってみた（第三回）

こんにちは、石原です。

Insight SQL Testing についてご紹介する内容の第三弾になります。

Insight SQL Testingはインサイトテクノロジー社が提供している製品になります。

Insight SQL Testing - 株式会社インサイトテクノロジー

データベース移行及びバージョンアップ向けSQLテストソフトウェア。異種データベース間でSQLのテストができる唯一の製品。

本内容をご確認いただく前に第一、二弾の内容をご参照ください。

■第一弾

Insight SQL Testing を触ってみた（第一回）

Insight SQL Testingを実際に触った内容を記事にしています。今後データベースのバージョンアップや移行を計画されており、それに伴う工数や懸念をお持ちの方々に是非知ってほしい製品になります。今回はMySQLを対象にして全体的な大まかな設定の流れや結果について概要レベルでまとめたものとなります。

■第二弾

Insight SQL Testing を触ってみた（第二回）

Insight SQL Testingを実際に触った内容を記事にしています。今後データベースのバージョンアップや移行を計画されており、それに伴う工数や懸念をお持ちの方々に是非知ってほしい製品になります。今回はISTの導入についてまとめたものとなります。

今回は以下の2についてまとめてみました。

①実際にInsight SQL Testing を用いた場合の実行結果
②実際にInsight SQL Testing を使ってみての注意点

①実際にInsight SQL Testing を用いた場合の実行結果

Insight SQL Testingは第一弾で触れたように最終的に得られる結果より一括で問題点が洗い出せるため、
そこまでたどり着くと非常に優れた製品です。

簡易的ではありますが最終的なイメージとしてどのような結果が得られるのかご説明します。

Insight SQL Testing の最終結果はアセスメントとしてまとめられ、以下の項目に振り分けられます。

①ターゲットDBでのみ失敗

②テスト用ソースDBでのみ失敗

③両DBで失敗

④結果が相違

⑤性能が劣化

⑥成功

ここに関して以前よりも詳しくまとめていきます。

サンプルデータ

MySQLの一般ログとして以下を用います。

Time	Id	Command	Argument
2024-12-18T05:29:45.214633Z	1627	Connect	XXXXX@10.10.90.120 on using SSL/TLS
2024-12-18T05:30:02.050688Z	1627	Init DB	test
2024-12-18T05:30:18.768317Z	1627	Query	insert into test123 values(2,’BOSS’)
2024-12-18T05:30:18.868317Z	1627	Query	insert into target1 values(1,’BOSS’)
2024-12-18T05:30:19.768317Z	1627	Query	insert into test1 values(1,’BOSS’)
2024-12-18T05:30:20.768317Z	1627	Query	insert into test124 values(1,’BOSS’)
2024-12-18T05:31:07.904233Z	1627	Query	select * from test.test123
2024-12-18T05:31:17.904233Z	1627	Query	select * from test.emp
2024-12-18T05:32:07.904233Z	1627	Query	SELECT count(*) FROM users u JOIN orders o ON u.user_id = o.user_id WHERE o.order_amount > 50 or o.order_amount like ‘%11%’

また比較する環境も以下のように設定しておきます。

テーブル名	テスト用ソースDB（現環境のイメージ）	ターゲットDB（新環境のイメージ）
test123	あり※レコード1件	あり※レコード2件
target1	なし	あり
test1	あり	なし
test124	なし	なし
emp	あり※レコードの内容は共に一緒

上記を踏まえてアセスメントの結果を見ていきます。

サマリーの画面になりますが以下のように分類されました。

アセスメントの結果のまとめ

上記の画像でも確認できるようにアセスメント実行時に「エラーメッセージ」として2件確認できます。

最初の「1146」のエラー側を確認すると、test1 の INSERT文でエラーになっています。
これは想定どおり、テスト用ソースDB側にしか存在しない表のため、「ターゲットDB」側でエラーとして計上されています。

また「1136」のエラー側をみると、 test124 でエラーとなっています。
これはテスト用ソースDBおよびターゲットDBのどちらにも存在しない表です。
なお、上記との差としてステータスのマークに違いがみられます。

次に「エラープログラム」側をみてみます。

こちらには2件計上されています。

ここでは例として test123 側に注目します。
test123 のSELECTでエラーとなっていますが、元々この表のデータ量が異なっている状態でした。

もちろん、どのようなデータの差異が生じたのか確認することも可能です。

アセスメントのページの「性能が劣化したSQL」をクリックすると、そのSQLを確認することができます。

この EMP 表はテスト用ソースDBおよびターゲットDBの両方に存在し、データの内容も一致もしたが、実行速度が
「テスト用ソースDB 」より「ターゲットDB」より遅いとこちらに計上されます。
(事前の設定によって容認できる遅延具合を設定できるので、その場合はその条件から外れた場合になります。)
ちなみに結果が異なる場合や成功した場合においても、同様に実行時間を確認することができます。

最後にアセスメントのページの「テスト用ソースDBにて失敗」をクリックすると、そのSQLを確認することができます。
ターゲット側で失敗した場合と同様に、2件計上されています。
target1はターゲット側には存在しますが、テスト用ソースDBには存在しないため、こちらに表記されます。
勿論、test124も存在しないのでターゲット側でも表示されたようにこちらにも計上される形となります。

このようにアセスメントの結果より何が問題であるか、うまくSQLが実行できても新しい環境において、
どれだけ遅延を引き起こすのかをまとめて確認することができます。

さて、このアセスメントにたどり着くまでには色々と注意するべき点があります。
それを次にまとめていきます。

②実際にInsight SQL Testing を使ってみての注意点

ここからはアセスメントにたどり着くまでに検証などを通じで確認できた注意点を記載していきます。

「評価SQLセット」注意点

Insight SQL Testing を使用してみて一番重要と感じたことは「評価SQLセット」のフェーズでした。
今回はMySQLの一般ログから直接Insight SQL Testingに認識させましたが、ここで気を付けなければいけないのが
一般ログに書かれている情報が全部 Insight SQL Testing に登録されたのかという点です。
以下は読み取らせた結果で、特にエラーも出ていないので問題なくすべての情報を読み取ったように見えますが、
実際は内部で認識できずに読み飛ばしている情報があったりします。
そのため、一般ログに書かれたデータ量と読み取られたデータ量が一致するのか確認した方が安全です。

MySQLの一般ログ観点になりますが、検証を行ったうえで Insight SQL Testing に読み込まれる条件についてまとめたので記載します。

「評価SQLセット」Insight SQL Testing に読み込まれる条件

まず条件の前に前提知識について触れます。
以下はMySQLの一般ログの内容の抜粋となりますが、処理のまとまりとしてはプロセスID単位となります。
プロセスIDは以下の中の赤文字の数字になります。
この数字が一致していれば同じセッション内で行われた処理であると考えられます。
一般ログはすべてのセッションの更新情報等を書き込むため、さまざまなプロセスIDのレコードが入り混じっています。

～一般ログの出力例～
2024-12-06T05:16:08.701213Z 1531 Query show databases

読み込まれる条件（１）

以下を全て満たす必要があります。

・Init DB の出力があること

・Query の出力があること

「Init DB test」とはコマンドでは「use test」と打った場合に一般ログに表示させる記載内容です。
つまり、MySQL内でデータベースの切り替えを行った場合の出力となります。

～一般ログの出力例～
2024-12-06T05:16:08.700088Z 1531 Init DB test
2024-12-06T05:16:08.701213Z 1531 Query show databases

注意点として、確かにこの出力であれば Insight SQL Testing に読み取られはしますが、
Connect情報が無いとどのユーザーから実行した処理であるか判断付かなくなります。
対処としてアセスメントの前にそのような紐づかないユーザーの処理に対して一律意図したユーザー名に
置き換えることが可能ですが、基本的には一般ログにMySQLに接続される処理を含むようにしてもらうことが重要です。

～一般ログの出力例～
2024-12-18T05:29:45.214633Z 1627 Connect ishihara@XXX.XXX.XXX.XXX on using SSL/TLS
2024-12-18T05:30:25.186372Z 1627 Init DB test
2024-12-18T05:30:58.220105Z 1627 Query insert into minori1 values(3,’CCC’)
2024-12-18T05:31:25.186372Z 1628 Init DB test
2024-12-18T05:32:58.220105Z 1628 Query insert into minori1 values(4,’DDD’)

読み込まれる条件（2）

(1)以外に以下のパターンであっても認識させることができます。

・Connect 時に on の次にDB名がある

・Query の出力があること

Connect は MySQL へ接続した際の出力であり、その際に事前にアクセスするDB名も一緒に指定してあった場合になります。

～一般ログの出力例～
2024-12-18T05:29:45.214633Z 1627 Connect ishihara@XXXX.XXX.XXX.XXX on test using SSL/TLS
2024-12-18T05:30:58.220105Z 1627 Query show databases

こちらも注意点として例えば、接続時にDB名を指定せず行い、「SELECT * FROM TEST.EMP;」などを実行した場合、
これは条件から外れるため無視されてしまいます。

～一般ログの出力例～
2024-12-18T05:29:45.214633Z 1627 Connect ishihara@XXX.XXX.XXX.XXX on using SSL/TLS
2024-12-18T05:30:58.220105Z 1627 Query select * from test.emp
2024-12-18T05:31:25.186372Z 1627 Init DB test
2024-12-18T05:32:58.220105Z 1627 Query insert into minori1 values(4,’DDD’)

「評価SQLセット」Insight SQL Testing は Init DB の情報をみない

「ターゲットDB」の設定時に「データベース名」を一緒に登録します。
ここから分かるように、基本 Insight SQL Testing は一つのデータベースに対して処理を行う場合に用いる製品です。

例えば以下の場合はデータベース名が「test」となっています。
「Init DB xxx」と記載された一般ログを読み込んだ評価SQLセットと、このターゲットDBを使用してアセスメントを行うと、
内部で「Init DB xxx」から「Init DB test」として変換され実行されてしまいます。

裏技として一般ログに対して以下のように変更することで複数DBに対して対応させることができます。
つまり、コマンド単位として Query を選び、そのコマンドとして「USE ＜データベース名＞」を記載します。

～一般ログの出力例～
2024-12-18T05:29:45.214633Z 1627 Connect ishihara@XXX.XXX.XXX.XXX on test using SSL/TLS
2024-12-18T05:30:25.186372Z 1627 Init DB exam
2024-12-18T05:31:58.220105Z 1627 Query insert into emp2 values(3,’BBB’)

↓

～一般ログの出力例～
2024-12-18T05:29:45.214633Z 1627 Connect ishihara@XXX.XXX.XXX.XXX on test using SSL/TLS
2024-12-18T05:30:25.186372Z 1627 Query use exam
2024-12-18T05:31:58.220105Z 1627 Query insert into emp2 values(3,’BBB’)

なお、こちらを用いる場合、「直列実行」をアセスメント時に選択しておくことをお勧めします。

「評価SQLセット」一般ログのローテーションの問題

上で少し触れましたが、一般ログはローテーションにより別のログに切り替わる影響で Connect 情報が失われることがあります。
その影響により実行ユーザー情報が失われたり、Connect情報にDB名も含む場合はそのセッション情報が全て読み込まれずに
無視されることもあります。

解決策として手動制御とはなりますが、事前にMySQL側で「show processlist;」の情報を取得して後で一般ログに反映させる方法です。
例えば、1:00～3:00に動く夜間処理について Insight SQL Testing で解析したい場合、1:00 前に
「show processlist;」の情報を取得しておきます。

～ MySQL の出力例 ～
mysql> show processlist;
+------+----------+--------------------+------+---------+------+----------+------------------+
| Id   | User     | Host               | db   | Command | Time | State    | Info             |
+------+----------+--------------------+------+---------+------+----------+------------------+
|    5 | rdsadmin | localhost          | NULL | Sleep   | 14   | NULL     | NULL             |
| 2200 | test     | XX.XX.XX.XX:51200  | abc  | Query   | 0    | NULL     | NULL             |
| 2203 | admin    | XX.XX.XX.XX:51208  | NULL | Query   | 0    | starting | show processlist |
+------+----------+--------------------+------+---------+------+----------+------------------+

※rdsadminユーザーは、AWSが管理する内部ユーザーであり通常のユーザーが意図的に接続することはできないため無視可能

上記の場合、以下のように変換して読み込む一般ログの頭に記載することで Connect 情報を補うことができます。

～一般ログの出力例～
※2024-12-18 01:00 を想定
2024-12-18T01:00:00.000000Z 5 Connect rdsadmin@localhost on using Socket
2024-12-18T01:00:00.000000Z 2200 Connect test@XX.XX.XX.XX on abc using SSL/TLS
2024-12-18T01:00:00.000000Z 2203 Connect admin@XX.XX.XX.XX on using SSL/TLS

「アセスメント」ロールバックについて

アセスメントを実行し、一般ログの内容を「テスト用ソースDB 」と「ターゲットDB」上で実行しますが、
その実施した内容がアセスメント終了時に「コミット」されるのか「ロールバック」されるのかを
選べるようにアセスメントの設定時に「DBへのデータ反映」にて選択することができます。
一度アセスメントしても、またテストをするために前回実行した内容を排除するために
「ロールバック」を選択することが望ましいですが、DBの仕様によってはコミット/ロールバックが効かない場合があります。

例えば以下の一般ログの場合、100のセッションは中でDDL文が実行されています。
このようなセッションの処理はロールバックされません。
なお、200の場合はロールバックされます。このように実行処理によってロールバックの有無がわかれてしまいます。
確実に前回の処理内容を残したくないのであれば、AWSであれば事前にスナップショットを取得し、
アセスメント毎にスナップショットから環境を復旧させて利用すると確実です。

～一般ログの出力例～
2024-12-18T01:00:01.000000Z 100 Init DB　test
2024-12-18T01:00:02.000000Z 100 Query drop table emp
2024-12-18T01:00:03.000000Z 100 Query create table emp (no int,name varchar(10))
2024-12-18T01:00:04.000000Z 100 Query insert into emp values(1,’AAA’)
2024-12-18T01:00:05.000000Z 200 Init DB　test
2024-12-18T01:00:06.000000Z 200 Query insert into abc values(1,’AAA’)

「アセスメント」ターゲットDBにアクセス権のないユーザー処理

先にお話ししたようにInsight SQL Testing は「ターゲットDB」で設定したDBに対して一般ログに
記載された内容を実行しようとします。
その動作から場合によっては、ターゲットDBに設定したDBに対して接続できないユーザーも出てくることが考えられます。
では、「USE ＜データベース名＞」として置き換えればよいと考えるかもしれませんが、
必ず最初に接続するべきDBはターゲットDBに設定したDBとなってしまうため、解決になりません。

ちなみにアセスメント時、ユーザー情報より接続テストをすることができますが以下のように失敗します。
この時点で失敗するため、アセスメントを実行しても一連の処理は全て失敗します。

よって、考えられる回避方法は事前にターゲットDBに指定するDBに対して関連ユーザーに権限を与えておくことになります。

その他の注意事項

基本的には上記が躓きやすい箇所になりますが、他の注意事項も以下に記載します。

移行元のDBがAWSの場合、内部で rdsadminユーザーの接続や処理内容が思っている以上に存在します。
アセスメントの段階で実行しないユーザーを省くこともできますが、評価SQLセットの段階で余計な
情報を読むことになるため、事前に省いておくことが有効です。
「評価SQLセット作成方法」において「DBのログから作成」以外である「Amazon RDSから作成」もありますが、
上記のように一般ログに対してチューニングすることができるので「DBのログから作成」がお勧めです。
Insight SQL Testing に認識できるログの量は1000万以内に抑えるようにすることが重要です。

検証結果のまとめ

検証結果から以下の方法を用いることがよいと考えられます。

Insight SQL Testing用にDBを用意し、既存ユーザーは全てそのDBに対して接続権限を与える
Init DB の処理はすべて Query USE に変更する※アセスメント時に「直列実行」を選択しておく
Connect 処理には DB 名もセットで記載する
Connect 処理に記載する DB名はターゲットDB名にする
既存のConnectでDB名がある場合、DB名をターゲット名に変更し、その後 Query USE で別途1行追記する
rdsadminユーザーの処理は可能であれば、取り込む前に排除する

まとめ

今回は Insight SQL Testing の良い点と検証からわかった注意点をまとめてみました。
アセスメントまで辿り着けばそこから得られる情報は非常に有効です。
ただし、意図した処理を全て含むためには今回触れたように手を加える必要があります。
それでもこの製品を使用することで総合的には問題の洗い出しに対して工数を大きく削減することが期待できます。

もし使用する場合は本内容を参考にしていただけると幸いです。