20250710-dbtech-showcase-C7.pdf

Transcript

1. Aurora Audit Log 深掘り実践 ~ Insight SQL Testing を通じた運用改善への道 ~ KINTO テクノロジーズ株式会社 粟田 啓介
2. ©KINTO Corporation. All rights reserved. 2 自己紹介 mysql > SELECT * FROM me \G *************** 1. row *************** name: 粟田 啓介 nickname: あわっち X(twitter): @_awache company: KINTO テクノロジーズ株式会社 role: DBRE/SRE MGR favorite: MySQL 1 rows in set (0.00 sec)
3. ©KINTO Corporation. All rights reserved. 3 KINTO テクノロジーズ株式会社 (KTC) について 2021年04月設立 2019年01月設立
4. ©KINTO Corporation. All rights reserved. 4 KTC における DBRE / SRE の立ち位置 エンジニア組織： 約 30グループ 社内のエンジニアの数： 約 380名 アプリケーション開発組織 • KINTO サービス開発 • グローバル ID 基盤開発 • バックオフィスシステム開発 • モバイルアプリ開発, etc. 横断組織 (プラットフォーム開発部) • QA • クラウドインフラ • DBRE / SRE • Platform Engineering • MSP (24*365 保守運用) プラットフォーム開発部 QA クラウドインフラ DBRE / SRE MSP アプリケーション 開発組織 アプリケーション 開発組織 アプリケーション 開発組織 アプリケーション 開発組織 アプリケーション 開発組織 ・・・ Platform Engineering
5. ©KINTO Corporation. All rights reserved. 5 お品書き 1 Aurora バージョンアップ対応 2 Insights SQL Testing の活用 3 4 Insight SQL Testing を使用するための 準備 5 DBRE としての Audit Log の新たな活用 Aurora Audit Log を分解してみる
6. ©KINTO Corporation. All rights reserved. 6 Aurora 3.0 バージョンアップ対応 1
7. ©KINTO Corporation. All rights reserved. 7 Aurora 3.0 バージョンアップ対応 2024年 10月末で Aurora 2.0系は標準サポートが終了(*) ◼ KTC でも Aurora のバージョンアップを実行 ◼ 期日までにほとんどの Aurora バージョンアップを完了 ◼ 検証環境を含めると 150 DB クラスター程度 ◼ バージョンアップを支えた DBRE Platform ◼ DB Catalog ◼ データベースに関わるドキュメントを自動生成 ◼ データマイグレーションツール (*) https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.20Updates.html
8. ©KINTO Corporation. All rights reserved. 8 Aurora 3.0 バージョンアップ対応を支えた DBRE Platform DB Catalog ◼ データベースの「今」の情報を「正しく」知ることがバージョンアップには重要 ◼ ER 図 ◼ DDL 情報 ◼ データサイズ ◼ DB 設定パラメータ
9. ©KINTO Corporation. All rights reserved. 9 Aurora 3.0 バージョンアップ対応を支えた DBRE Platform information_schema から様々な情報を取得し可視化 ◼ データベース推奨設定 ◼ メタデータ情報から直した方がいい設定をレコメンド ◼ いくつかのガイドラインに合わせて検知 ◼ 内部的には mysqlsh の upgrade_checker を元にカスタマイズ
10. ©KINTO Corporation. All rights reserved. 10 Aurora 3.0 バージョンアップに必要なレポートの作成 あくまでも主体はプロダクト ◼ DBRE は移行がスムーズに行われるように裏側のサポートに徹している ◼ 実際の移行に関しては DBRE が開発した移行ツールを利用して実施 実際のマイグレーション手順 バージョンアップに必要なレポートの作成
11. ©KINTO Corporation. All rights reserved. 11 Insight SQL Testing の活用 2
12. ©KINTO Corporation. All rights reserved. 12 Insight SQL Testing の活用 インサイトテクノロジーが提供している移行アセスメントツール ◼ Audit Log を吸い出してそれをリプレイすることでさまざまな情報を可視化 ◼ バージョンアップ前後で失敗するクエリ ◼ パフォーマンス劣化が発生するクエリ ◼ 結果 が違ってくるクエリ ◼ そこそこライセンス料がかかるので本当に必要なプロダクトにのみ適用
13. ©KINTO Corporation. All rights reserved. 13 Insight SQL Testing の活用 Insight SQL Testing によって得られた効果 ◼ およそ 3.3億行のクエリをリプレイし、対応しなければいけない観点を絞ることができた ◼ 全てのクエリを一つづつ対応するのは非現実的 ◼ Insight SQL Testing によって 3.3億行をリプレイし対応すべきクエリを約 486万クエリに絞込み ◼ 両 DB で失敗したものを見ることで、移行ツールの漏れを知ることができた ◼ このタイミングで COLLATION の変更を行なっていたが、独自 function に気が回ってなかった ことで大量のエラーを検知 ◼ 実際の移行作業をより安全に実行可能な状態になった
14. ©KINTO Corporation. All rights reserved. 14 Insight SQL Testing の活用 対応しなければいけないクエリが 486万クエリあることも事実 ◼ 減ったとはいえこれでも現実的ではない ◼ クエリのノーマライズ処理をするスクリプトを作成し、それによって 3,300クエリ程度まで縮小 ◼ 条件値（リテラル値）や IN 句の内容が異なるクエリを同一視 ◼ クエリのコメントなど、解析対象として不要な部分を無視 ◼ SQL構文のばらつき（大文字・小文字、ホワイトスペースの違いなど）を統一 ◼ 参考: クエリのノーマライズ処理を実装してみた Aurora3系で のみ失敗 両DBで失敗 結果が相違 性能劣化 成功 合計 全体件数 1,586,332 1,717,814 1,457,426 95,627 324,339,799 329,196,998 ユニーク 205 416 2,398 309 -- 3,328
15. ©KINTO Corporation. All rights reserved. 15 Insight SQL Testing の活用 KTC DBRE のスタンス ◼ ベースは内製開発を軸としているが必要に応じて正しく投資を行う ◼ 内製開発にこだわるわけではない ◼ 自分たちが成し遂げたいことに対してどれだけ素早く到達できるかを見定めて実行することが重要
16. ©KINTO Corporation. All rights reserved. 16 Aurora Audit Log を分解してみる 3
17. ©KINTO Corporation. All rights reserved. 17 Aurora Audit Log とは データベースに対する操作やアクセスに関する情報を記録する仕組み ◼ Aurora MySQL では MariaDB Audit Plugin をベースにしている ◼ ソースコードも公開されている (https://github.com/aws/audit-plugin-for-mysql) ◼ メリット ◼ Aurora 標準機能として追加コストなしで利用可能 ◼ AWS Console や IaC による簡易な有効化 ◼ セキュリティ監査（SOC2 / PCI DSS など）への対応手段 ◼ 接続・クエリ・テーブル操作など幅広い監査対象 ◼ デメリット ◼ クエリ全文がそのまま記録されることによる個人情報漏洩リスク ◼ CloudWatch Logs での1イベント1MB制限による切り捨て発生 ◼ ログボリューム増大によるコスト・パフォーマンス影響
18. ©KINTO Corporation. All rights reserved. 18 Aurora Audit Log の出力項目 Amazon Aurora MySQL DB クラスターでのアドバンストな監査の使用 - Amazon Aurora フィールド 説明 timestamp 記録されたイベントの UNIX タイムスタンプ (マイクロ秒の精度)。 serverhost イベントが記録されているインスタンスの名前。 username ユーザーの接続されたユーザー名。 host ユーザーの接続元のホスト。 connectionid 記録されたオペレーションの接続 ID 番号。 queryid クエリ ID 番号。リレーショナルテーブルイベントと関連するクエリの検索に使用できます。 TABLE イベントの場合、複数の行が追加されます。 オペレーション 記録されたアクションの種類。指定できる値は CONNECT、QUERY、READ、WRITE、 CREATE、ALTER、RENAME、DROP です。 データベース USE コマンドにより設定されたアクティブなデータベース。 オブジェクト QUERY イベントの場合、この値は、データベースが実行したクエリを示します。TABLE イベントの場合、テーブル名を示します。 retcode 記録されたオペレーションのリターンコード。
19. ©KINTO Corporation. All rights reserved. 19 実際の Aurora Audit Log の出力項目 timestamp serverhost username host connectionid queryid オペレーション データベース オブジェクト retcode
20. ©KINTO Corporation. All rights reserved. 20 Aurora Audit Log を読み解いてみる ◼ この Audit Log から分かること ◼ 該当時間帯のセッションの個数 (7662 ~ 7665) ◼ セッション単位の接続時間 ◼ 接続時のユーザー、接続元 IP アドレス ◼ 接続データベース ◼ 実際に実行されたクエリ ◼ クエリそのものの成功失敗 etc.
21. ©KINTO Corporation. All rights reserved. 21 Insight SQL Testing を使用するための 準備 4
22. ©KINTO Corporation. All rights reserved. 22 Insight SQL Testing を使用するためには Audit Log をパースしなければならない 最初の鬼門は Audit Log をどうにかして Export すること ◼ CloudWatch Logs からログをダウンロードする方法 方法 特徴 メリット デメリット コンソール操作 GUI から期間を選択してログ をエクスポート • 誰でも簡単に操作できる • ログ量が多いと非現実的 • スクロールでデータを読み込む 必要がある AWS CLI get-log-events ログストリーム単位で順次ロ グを取得 • 自動化可能 • データが時系列順で取得され る • フィルタ不要ならシンプル • 最大1MB or 10,000件/回の制限 • トークンを使ったページネー ションが面倒 • ストリーム単位でループ処理必 要 AWS CLI create-export-task 指定期間のログをS3へ一括出 力（非同期） • 日付指定で一括出力可能 • 大量データでも安定 • S3連携で後処理しやすい • 同時に1つまでという実行制限 あり 今回は dbtech showcase なので詳細は一旦割愛します 採 用
23. ©KINTO Corporation. All rights reserved. 23 Insight SQL Testing を使用するためには Audit Log をパースしなければならない Insight SQL Testing には Audit Log を直接取り込む機能はなかった ◼ インサイトテクノロジーさんは Audit Log を取り込めるフォーマットに変換するツールを提供 ◼ マイニングサーチ出力CSV形式変換ツール ◼ https://github.com/takumats/mysql-audit-log-to-mining-search- csv/blob/main/convert_myaudit_to_mscsv.py • ログの中から CONNECT / DISCONNECT を探してセッションの開始・終了時刻を記録 • CONNECT / DISCONNECT がない場合は、最初と最後の QUERY のタイムスタンプを使って 代用 • ログの各行から QUERY の行だけを対象に処理 • 出力CSVにはSQL実行時刻・ユーザー・DB名などの情報を整形して書き出す
24. ©KINTO Corporation. All rights reserved. 24 Insight SQL Testing を使用するためには Audit Log をパースしなければならない マイニングサーチ出力変換ツールを実行した結果 ◼ Audit Log を変換し、セッション開始時間、クエリ開始時間の順番に CSV 出力 ◼ このファイルを取り込むことで Insight SQL Testing を実行することができる
25. Audit Log もここまで分かりやすくなると 別の活用ができると思いません？
26. ©KINTO Corporation. All rights reserved. 26 DBRE としての Audit Log の新たな活用 5
27. ©KINTO Corporation. All rights reserved. 27 Audit Log を自分たちが扱いやすいようにパースする仕組みを開発 Audit Log の export → パースまでをコマンド化 ◼ 結果を MySQL に保存してしまうことでさまざまな分析が可能に クエリそのものは保存せず hash 値を保存
28. ©KINTO Corporation. All rights reserved. 28 Audit Log を自分たちが扱いやすいようにパースする仕組みを開発 分析ケース ◼ 該当の時間帯にいくつのセッションが開始されたか ◼ 該当の時間帯にユーザー単位でどのユーザーが何回セッションを開始したか mysql> SELECT COUNT(*) FROM sqlset_sessions WHERE sqlset_id = 1; +----------+ | COUNT(*) | +----------+ | 21570 | +----------+ 1 row in set (0.00 sec) mysql> SELECT CONCAT(LEFT(dbuser_name, 0), REPEAT('*', CHAR_LENGTH(dbuser_name) -0)) dbuser_name, COUNT(dbuser_name) FROM sqlset_sessions WHERE sqlset_id = 1 GROUP BY dbuser_name; +-----------------------+--------------------+ | dbuser_name | COUNT(dbuser_name) | +-----------------------+--------------------+ | ************** | 352 | | ********* | 560 | | ************** | 1427 | | **** | 974 | | **** | 77 | | ********** | 15290 | | *************** | 194 | | *************** | 74 | | **** | 17 | | ********************* | 6 | | ***** | 2597 | | ************ | 2 | +-----------------------+--------------------+ 12 rows in set (0.03 sec)
29. ©KINTO Corporation. All rights reserved. 29 Audit Log を自分たちが扱いやすいようにパースする仕組みを開発 分析ケース ◼ 最も長くセッションを保持していたリストは？ mysql> SELECT -> sqlset_session_id, -> sqlset_id, -> query_session_id, -> CONCAT(LEFT(dbuser_name, 0), REPEAT('*', CHAR_LENGTH(dbuser_name) -0)) dbuser_name, -> CONCAT(LEFT(target_dbname, 0), REPEAT('*', CHAR_LENGTH(target_dbname) -0)) target_dbname, -> session_start_datetime, -> session_end_datetime, -> ROUND(TIMESTAMPDIFF(MICROSECOND, session_start_datetime, session_end_datetime) / 1000000.0, 6) AS session_duration_sec -> FROM -> sqlset_sessions -> ORDER BY -> session_duration_sec DESC -> LIMIT 5; +-------------------+-----------+------------------+----------------+-------------------+----------------------------+----------------------------+----------------------+ | sqlset_session_id | sqlset_id | query_session_id | dbuser_name | target_dbname | session_start_datetime | session_end_datetime | session_duration_sec | +-------------------+-----------+------------------+----------------+-------------------+----------------------------+----------------------------+----------------------+ | 11390 | 1 | 14295 | ************** | | 2025-07-09 09:00:20.363211 | 2025-07-10 08:36:10.082707 | 84949.7195 | | 5861 | 1 | 12633 | ********** | ********** | 2025-07-09 12:35:26.147334 | 2025-07-10 08:26:26.769400 | 71460.6221 | | 12111 | 1 | 13008 | ********** | ********** | 2025-07-09 12:45:12.608671 | 2025-07-10 08:36:10.075717 | 71457.4670 | | 10969 | 1 | 13010 | ********** | ********** | 2025-07-09 12:45:12.839912 | 2025-07-10 08:36:07.641749 | 71454.8018 | | 20385 | 1 | 12550 | ************** | ***************** | 2025-07-09 12:34:04.142573 | 2025-07-10 08:24:41.351471 | 71437.2089 | +-------------------+-----------+------------------+----------------+-------------------+----------------------------+----------------------------+----------------------+ 5 rows in set (0.02 sec)
30. ©KINTO Corporation. All rights reserved. 30 Audit Log を自分たちが扱いやすいようにパースする仕組みを開発 分析ケース ◼ 特定のユーザーで同じ種類のクエリが何回叩かれたか知りたい ◼ (実際のクエリそのものは別の方法で追いかけることは可能) mysql> SELECT -> sqlset_queries.sqlhash, -> COUNT(*) AS query_count -> FROM -> sqlset_sessions, -> sqlset_queries -> WHERE -> sqlset_sessions.sqlset_session_id = sqlset_queries.sqlset_session_id AND -> sqlset_sessions.dbuser_name = 'admin' -> GROUP BY -> sqlset_queries.sqlhash -> ORDER BY -> query_count DESC -> LIMIT 5; +------------------------------------------------------------------+-------------+ | sqlhash | query_count | +------------------------------------------------------------------+-------------+ | 5a9597e184799ed89de32b7f0b12dcace4a319d43ccbc9dc479db01d9532a872 | 103 | | 45aaead70c54b885f887c5012ce030dee39486dd2491a0a38de305d43894ef00 | 102 | | 5cba1aa85b0d75d8cdc13c5ce53908f6050e9a499529eaaf73b1482f5fda76fa | 102 | | 56fe10fdfd093a46fe9b2d65ff03a270d83c866d2f07f4d46526074194378a2c | 100 | | 99b9a6a1db1f204596048d5be192b9800ef8468876136ef997a7f0e7abd014a7 | 100 | +------------------------------------------------------------------+-------------+ 5 rows in set (0.02 sec)
31. これまで設定だけして活用していなかった Audit Log も ただの監査だけでなく色々な用途に使えそうな気がしてきたので 今後の KTC DBRE の活動でアウトプットしていきたいと思います
32. ©KINTO Corporation. All rights reserved. 32 まとめ 6
33. ©KINTO Corporation. All rights reserved. 33 Aurora バージョンアップ対応 ◼ DBRE Platform を軸に着実に対応を実施 ◼ ER図、DDL、パラメータなどを稼働している環境から取得することで正確な情報を取得 ◼ mysqlsh upgrade_checker をベースに独自ルールで推奨設定を含めレポーティング ◼ 作業主体はプロダクト、DBRE はあくまでも支援に徹する
34. ©KINTO Corporation. All rights reserved. 34 Insight SQL Testing の活用 ◼ より安心で安全なバージョンアップを支援するために導入 ◼ 3.3億のクエリをリプレイし、実際に対応するべきポイントを絞り込み ◼ クエリをノーマライズする仕組みを開発してプロダクトの移行負荷を軽減 ◼ 必要な箇所に正しくライセンス投資、アジリティとコストのバランスを重視した活動
35. ©KINTO Corporation. All rights reserved. 35 Audit Log の活用 ◼ マイニングサーチの仕組みを理解する中で自分たちの運用に活用できないか模索中 ◼ Audit Log を適切にパースし、MySQL に格納することでセッションやクエリの分析が可能に ◼ 今後は単なる監査ログではなく、運用インサイトの源泉として活用していきたい
36. Thank you！