データベースのバージョンアップの不安を解消。Insight SQL Testingを活用した検証手順

はじめに

こんにちは、初めまして。今年3月に入社し、タイミーで Platform Engineer をしている小河原(@kgwryk28)です。

現在、タイミーのシステムで利用しているメインのデータベース（Aurora MySQL）のバージョンアップにおける検証を進めています。

データベースのアップグレードで気になるのは、「今まで動いていたSQLが新バージョンでもそのまま動くのか」「アップグレードによって遅くなるクエリはないのか」という点です。

アップグレード後に互換性の問題や性能劣化が本番で発覚すると、影響は計り知れません。そのため、バージョンアップ範囲内の各バージョンの変更内容（changelog）を調べるだけでは不十分だと感じました。

そこで今回は、Insight SQL Testing を使って検証を実施した際の工夫（期間の絞り込み・重複排除・結果のトリアージ）や検証手順を中心に共有したいと思います。

Insight SQL Testing とは？

「Insight SQL Testing」は、株式会社インサイトテクノロジーが提供する、データベース移行やバージョンアップ時の SQL テストを自動化・効率化するソフトウェアです。

移行元のデータベースで実行されていたSQLを、移行先のデータベースでも実際に使えるか検証できます。そのため、異種間の移行やバージョンアップ時に、SQLの互換性やパフォーマンスを確認できます。

実環境の SQL クエリを使用することで、本番環境に近い網羅性で「動くか／遅くならないか」を検証できます。

仕組みとしては以下のようになっています。

移行元のデータベース（ソース DB）と移行先のデータベース（ターゲット DB）の 2 つの DB を検証用に作成しておき、それぞれに対して同じクエリを実行します。

今回はデータベースのバージョンアップを行うため、ソース DB を現状の本番環境と同一のバージョンである Aurora MySQL 3.04.2（MySQL 8.0.28 相当）、ターゲット DB を移行先の Aurora MySQL 3.10.3（MySQL 8.0.42 相当）で作成します。

クエリの実行結果（成功／失敗・返ってきた値・実行時間）の突き合わせにより、バージョン間での互換性（片方の DB でだけ失敗するクエリ、結果が食い違うクエリ）、性能劣化（移行先のデータベースだけパフォーマンスが劣化するクエリ）を洗い出すことができます。

なお、入力となる実環境の SQL クエリには、元々 S3 に保存している監査用の Audit Log を使用しました。監査ログのフォーマットはInsight SQL Testingが要求するフォーマットと異なるため、作業用EC2インスタンスであらかじめ変換してから取り込みました。

検証作業の流れ

今回の検証は、おおまかに次の流れで進めました。

検証環境を構築
検証用の SQL セットを準備
Insight SQL Testing による検査（アセスメント）を実施
検査結果を元に SQL の互換性、パフォーマンス劣化クエリの抽出
検査結果を元に評価レポートの作成

検証上の課題

検証にあたって直面した課題が、検証対象期間での全量テストは現実的に間に合わないという問題でした。

当初、検査対象の期間は 1ヶ月を想定していました。これは、週次・月次で実行される定期バッチがあるため、クエリの網羅性を担保するためです。

実際に確認してみたところ、以下のことがわかりました。

1 日あたり約 1 億行のクエリが流れている
1 時間分の量を流すだけでも 24 時間かかる。このペースで 1ヶ月分（約 31 億行）を全量テストすると、約 2 年かかる計算になる

当然これでは時間がかかり過ぎてしまいます。そのため、アセスメントの実行時間を短縮するための対策が必須でした。

SQL 互換性検査とパフォーマンス検査を分けて考える

まず、SQL 互換性検査とパフォーマンス検査を分けて実施する方針にしました。目的が違うので、必要なクエリの範囲や確認方法も変わるからです。

SQL 互換性検査 の目的: アップグレード後の構文エラーの検出
パフォーマンス検査 の目的: アップグレード後に性能が劣化するクエリがないか確認したい

データベースに対するアクセス特性による網羅性

タイミーのシステムでは、データベースに対するアクセス特性を大きく以下の 2 つに分類することができます。

常時アクセスが発生するもの。タイミーのワーカー様（アプリ利用者）・事業者様からの API 経由のデータベースアクセスや、CDC（変更データキャプチャ）によるレプリケーション接続（データ連携用）が該当
一定期間のみアクセスが発生するもの。日次・週次・月次の定期バッチジョブなど、システム内部で決められた日時に実行されるもの

「2. 一定期間のみアクセスが発生するもの」が動いている期間には、「1. 常時アクセスが発生するもの」も同時に発生します。そのため完全性は保証できないものの、一定の網羅性は確保できます。

SQL 互換性検査における重複排除による効率化

検査対象のクエリを削減する方法として、SQL パターンごとにクエリの重複排除を行う方法があります。 SQL の互換性検査に関しては、重複排除が効率化の鍵になります。同じパターンのクエリを何度もテストする必要はないからです。

重複排除の 3 つの設定

SQL Testing Manager の最新のバージョンでは、入力として実行する SQL（評価 SQL セット）を作成する際に、重複排除の挙動を 3 種類から選ぶことができます。

設定	重複排除	区別の仕方
しない	排除しない	すべてのクエリをそのまま対象にする
PI Hash	排除する	リテラルを除いた SQL で区別する
SQL ID	排除する	SQL 文の違いで区別する

実際に検証したところ、各設定ごとに重複排除の方法が異なることが確認できました。まず、以下のような SQL セットを準備します。

/* comment 1 */ SELECT * FROM test_table WHERE id = 1;
/* comment 2 */ SELECT * FROM test_table WHERE id = 1;
/* comment 3 */ SELECT * FROM test_table WHERE id = 2;

各重複排除モードで評価 SQL セットを作成した結果、行数は以下のようになりました。

重複排除モード	結果行数	区別の仕方	比較イメージ
しない	3	すべてのクエリをそのまま対象にする	-
SQL ID	3	コメントも含めた SQL 文の完全一致による重複排除が行われる	`/* comment 1 / SELECT FROM test_table WHERE id = 1;`
PI Hash	1	コメントの有無にかかわらず、リテラル値が異なる SQL のみ区別される	`SELECT * FROM test_table WHERE id = ?`

PI Hash モードではクエリが完全にパターン化されるため、上記の入力クエリはすべて同一のものとして扱われます。一方、SQL ID での重複排除はコメントも含めたクエリ文の完全一致による比較が行われます。

まとめると以下のようになります。

SQL ID: コメントも含めた SQL 文の完全一致で重複排除する。
- 同じクエリでもコメントが異なると別物として扱われ、重複排除されない。
PI Hash: リテラル値の違いで区別しない（コメントの有無は問わない）。
- 例えば WHERE id = 1 と WHERE id = 2 のように リテラルだけが違う SQL は同一として扱われる。

検証方法まとめ

これらを踏まえてまとめると、以下のように検証を行うことにしました。

項目	SQL 互換性検証	パフォーマンス検証
目的	構文エラーの検出	アップグレード後の SQL のパフォーマンス劣化可能性の検出
方針	PI Hash によるクエリ構文のパターン化が行われたクエリで構文エラーの確認を行う	利用されている SQL クエリの実行比較を行い、アップグレード前後でパフォーマンスの変化を確認する
利用する重複排除モード	PI Hash	SQL ID
テスト件数	約 31 万件	約 6700 万件
対象期間	過去 1ヶ月の SQL クエリ	全定期ジョブをカバーする最小のテスト期間（3 区間・合計 7 日間）

互換性検査（1ヶ月）が約 31 万件、性能検査（7 日間）が約 6700 万件と、期間が短い性能検査のほうが件数が多くなっています。これは、PI Hash がリテラル値まで畳んでクエリをパターン化するため件数が激減するのに対し、SQL ID はリテラル値の違いを区別するため件数が大きく残るためです。

なお、性能検査の対象期間は、定期バッチの実行タイミングを全てカバーできる最小の組み合わせを選んだ結果、3 区間・合計 7 日間となりました。

SQL IDの重複排除モードを利用するため、あらかじめ入力に使用するクエリからはコメントを除去しておき、SQLクエリに対する重複排除が行われるようにしておきます。また、パフォーマンス検査はバッチ期間に合わせて3期間に区切り、アセスメント実行期間を高速化するため、独立した3環境を用意して並列で実行しました。これらの対処により、テスト件数を大幅に削減できたことに加え、パフォーマンス検査は環境ごとに並列で実行したことで、元々約 2 年かかる想定だった検証作業を 5 日で完了することができました。

トリアージ方針

アセスメント（評価 SQL の実行）を実施すると、各クエリごとに「ソース DB／ターゲット DB でそれぞれどうだったか」を示す結果パターンが割り当てられます。これをもとに、次の方針で調査の要否を切り分けました。

各結果パターンの分類と、それぞれのトリアージ方針は以下のとおりです。

結果パターン	対応する検証	トリアージ方針
成功	SQL 互換性検査	問題なし
ターゲット DB のみで失敗	SQL 互換性検査	エラーコードを元に原因を調査し、SQL の互換性に起因するエラーでないことを確認
ソース DB のみで失敗	SQL 互換性検査	エラーコードを元に原因を調査し、SQL の互換性に起因するエラーでないことを確認
両 DB で失敗	SQL 互換性検査	アップグレードによる差分は発生していないが、エラーコードごとの集計結果により、SQL の互換性に起因するエラーでないか原因を調査
両 DB で成功したが結果が相違	SQL 互換性検査	結果が異なる原因が検証環境起因のエラーであるか調査
両 DB で成功したがターゲット DB で性能劣化	パフォーマンス検査	ターゲット DB での実行時間が 1 秒以上、かつソース DB での実行と比較して 2 倍以上かかったクエリを抽出して原因を調査

ポイントは以下の点です。

バージョン間で挙動が変わったクエリに調査リソースを集中させることで、膨大な結果の中から本当に見るべきものを絞り込む
「両 DB で成功したがターゲット DB で性能劣化」したものは、わずかな実行時間の差まで拾うとノイズが多くなるため、「遅くなった倍率（200% 以上）」かつ「絶対値としても 1 秒以上かかっている」という条件で、影響の大きいものだけを抽出する

調査結果

調査の結果、バージョンアップ前後で結果差分が発生していることが判明しました。 PI Hash による重複排除後、EXPLAIN を除いて結果差分が検出されたクエリパターンは16件でした。これらを調査したところ、いずれもアップグレード前後で行の順番が異なることによる差分であり、各行の内容は同一で、保存されているデータ自体に差分は発生していませんでした。

原因は主に二つありました。

ORDER BY で指定したカラムの値が同一になっている行が複数存在しており、同一の値に対する ORDER BY ... LIMIT の結果は非決定的であること
EXPLAIN 結果の比較により、内部的な結合アルゴリズムとして Nested Loop Join が選択されていたクエリが、バージョンアップ後では Hash Join が選択されたことによる結果順序の不定化

このうち、クライアント上で表示される結果順序が変わってしまうなど、実際のプロダクトに問題が発生するクエリについては、該当クエリに ORDER BY を追加する等の対応を入れました。保存されているデータ自体に差分は確認されなかったため、現時点ではこれらの対応により、アップグレードに伴う影響は許容範囲内であると判断しています。

まとめ

今回はマイナーバージョンアップ（MySQL 8.0.28 相当 → 8.0.42 相当）であり、MySQL 8.0.34 以降は bugfix のみのリリースの方針であるため、大量の差分が発生することはありませんでした。しかし、調査結果で検出された内部的な結合アルゴリズムの変化は、各バージョンの変更内容（changelog）の調査だけでは気づきにくい部分でもあるため、今回の検証で気づくことができたのは大きな収穫でした。

膨大なクエリを現実的な時間で検証するために効いたのは、次の 4 つの工夫です。

目的で検証を分ける: 網羅性が欲しい互換性検査と、負荷状況を見たい性能検査を切り離し、それぞれに最適な対象範囲を設定
期間を賢く絞る: 定期バッチのタイミングを考慮し、「全定期ジョブをカバーする最小の 7 日間」で性能検査の対象を設計
重複排除で件数を削る: PI Hash / SQL ID の挙動の違いを理解し、目的に合わせて使い分けることで、テスト件数を大幅に削減
効率的なトリアージ: アップグレードに起因する変化に絞り込み、重要な差分に調査リソースを割く

もし、今回の自分と同じようにデータベースのアップグレードを検討している方が、この記事が事前検証を行う上で何らかの参考になれば嬉しいです。

Timee Product Team Blog

タイミー開発者ブログ