[HeatWavejpMeetup#13] HeatWave の Autopilot Indexing を試してみた！ [藤本 正比古 氏 (スマートスタイル)]

【講演内容】
HeatWave MySQL の Autopilot Indexing を試してみた！

　- Autopilot Indexing とは？
　- ベンチマークツールで検証
　 - アドバイザ実行結果
　 - 性能比較
　- インデックスメンテナンス方式・方法の比較

【発表者】
株式会社スマートスタイル　
データベース&クラウド事業部 データベース部
藤本 正比古 氏

【イベント情報】
HeatWavejp Meetup #13
https://heatwavejp.connpass.com/event/349599/

Transcript

1. HeatWavejp Meetup #13 祝2周年 HeatWave の 〇〇 やってみた!LT大会!! HeatWave MySQL の Autopilot Indexing を試してみた！ 日時： 2025/4/17(木) 担当： 株式会社スマートスタイル 藤本 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved.
2. 自己紹介 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 2 藤本 正比古 株式会社 スマートスタイル DB＆クラウド事業部データベース部エンジニア 来月からは、ビジネスディベロップメント部 プリンシパルアーキテクト 【経歴】 • プログラマ(C,Java,色々) 5年 • Oracle DB/WebLogic(Fusion Middleware)デリバリ・サポート・DBA 7年 • 2019年 スマートスタイルに中途入社 (6年目) 【これまでのスマートスタイルでの業務範囲】 • MySQL/MariaDB/Percona 全般 • 振り返るとオンプレ多め • Orchestrator, ProxySQL など割と得意 【趣味】 卓球 (右ペン表ソフト速攻)
3. Autopilot Indexing 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 3 クエリ実行 統計情報の 蓄積 アドバイザ 実行 レコメン デーション の確認 メンテナン ス実施 ◼ 使用条件 – HeatWave MySQL 9.0.0 以上 – HeatWave クラスタ有効 – InnoDB ストレージエンジンテーブル • 最低5クエリ必要 • 1種類×5回 • 5種類×各1回づつ • performance_schema • information_schema • sys.autopilot_index_advisor プロシージャ • Machine Learning • アドバイザ標準出力 • sys.autopilot_index_advisor_report テーブル 1. パフォーマンス向上が見込めるインデックスの作成提案 • 影響を受けるクエリ(ダイジェスト)も表示 2. 不要インデックス(未使用、重複)の削除提案 3. インデックスの追加・削除によるディスク容量の推定サイズ増減 4. 各DDLの推定実行時間
4. Autopilot Indexing 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 4 ◼ How-To など細かいことは… Tech ブログ記事にしました！ スマートスタイル TECH BLOG | HeatWave MySQL の Autopilot Indexing について https://blog.s-style.co.jp/2025/04/14078/
5. ベンチマークツールで検証 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 5 ◼ Oracle CloudWorld 2023 [Automatic Indexing of MySQL Database with Machine Learning] より引用
6. ベンチマークツールで検証 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 6 ◼ BenchBase (Multi-DBMS SQL Benchmarking Framework via JDBC) https://github.com/cmu-db/benchbase https://db.cs.cmu.edu/projects/benchbase/ currently supported: PostgreSQL, MySQL, MariaDB, SQLite, Cockroach DB, Apache Phoenix, Spanner Stonebraker Electronic Airline Ticketing System (SEATS) フライト検索・オンライン予約の航空券発券システムを模した ベンチマーク https://github.com/cmu-db/benchbase/wiki/Seats > Approximately 60% of the transactions are read-only (e.g., customers searching for open seats), while the other 40% involve creating, updating, and deleting reservation records. > Thus, many of its transactions use secondary indexes or foreign-key joins to find the primary key of a customer’s reservation record. For example, customers may access the system using various credentials, including their frequent flyer number, their customer account number, or their login name. $ java -jar benchbase.jar ¥ -b seats ¥ -c config/mysql/sample_seats_config.xml ¥ --create=true ¥ --load=true ¥ --execute=true
7. ベンチマークツールで検証：アドバイザ実行結果 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 7 ◼ seats / Scale factor : 7 +----------------------------+ | INITIALIZING INDEX ADVISOR | +----------------------------+ | Version: 1.14 | | | | Output Mode: normal | | Target Schemas: 1 | | | +----------------------------+ +---------------------------------------------------------+ | ANALYZING DATA | +---------------------------------------------------------+ | Total 10 table(s) for 1 schema(s) | | | | Total Data size: 1.76 GiB | | Total Index size: 1.03 GiB | | | | SCHEMA TABLE COLUMN | | NAME COUNT COUNT | | ------ ----- ------ | | `bb_seats` 10 189 | | | +---------------------------------------------------------+ (つづく)
8. ベンチマークツールで検証：アドバイザ実行結果 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 8 ◼ seats / Scale factor : 7 +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | INDEX SUGGESTIONS | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Total Index suggestions: 2 | | Statements analyzed: 94218 | | | | SUGGEST TABLE INDEXED REASON ESTIMATED ESTIMATED ESTIMATED | | ACTION NAME COLUMNS SUGGESTED PERF IMPACT FOOTPRINT CREATE TIME | | ------- ----- ------- --------- ----------- --------- ----------- | | CREATE `bb_seats`.`flight` `f_arrive_ap_id`, `f_depart_ap_i ... Missing Index HIGH + 74.54 MiB 8 s | | CREATE `bb_seats`.`airport_distance` `d_ap_id0`, `d_distance` Missing Index LOW + 1.27 MiB 134 ms | | | | Expected performance benefit after applying all Index suggestions: 38.4% | | Expected storage footprint after applying all Index suggestions: + 75.81 MiB | | 0 bytes freed up by dropping indexes. | | 75.81 MiB required for creating indexes. | | NOTE: Indexes will be stored efficiently at time of creation. | | To accommodate efficient future inserts, size may double. | | Expected time for applying all Index creation suggestions: 8 s | | | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ (つづく)
9. ベンチマークツールで検証：アドバイザ実行結果 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 9 ◼ seats / Scale factor : 7 +----------------------------------------------------------------------------------------------------------------+ | SCRIPT GENERATION | +----------------------------------------------------------------------------------------------------------------+ | Script generated for applying suggestions for 0 drop indexes and 2 create indexes. | | | | Retrieve script containing 2 generated DDL commands using the query below: | | SELECT log->>"$.sql" AS "SQL Script" FROM sys.autopilot_index_advisor_report WHERE type = "sql" ORDER BY id; | | | | Caution: Executing the generated script will alter the indexes in schema | | | +----------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------+ | EXPLANATIONS | +-------------------------------------------------------------------------------------------------------------------------+ | Retrieve explanations for create index recommendations using the query below: | | SELECT JSON_PRETTY(log) AS "Explanations" FROM sys.autopilot_index_advisor_report WHERE type = "explain" ORDER BY id; | | | +-------------------------------------------------------------------------------------------------------------------------+ (つづく)
10. ベンチマークツールで検証：アドバイザ実行結果 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 10 mysql> SELECT log->>"$.sql" AS "SQL Script" FROM sys.autopilot_index_advisor_report WHERE type = "sql" ORDER BY id; +-------------------------------------------------------------------------------------------------------------------------------+ | SQL Script | +-------------------------------------------------------------------------------------------------------------------------------+ | CREATE INDEX `autoidx_tab5461_col5_col3_col4` ON `bb_seats`.`flight` ( `f_arrive_ap_id`, `f_depart_ap_id`, `f_depart_time` ); | | CREATE INDEX `autoidx_tab5457_col1_col3` ON `bb_seats`.`airport_distance` ( `d_ap_id0`, `d_distance` ); | +-------------------------------------------------------------------------------------------------------------------------------+ ◼ seats / Scale factor : 7
11. ベンチマークツールで検証：アドバイザ実行結果 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 11 mysql> SELECT JSON_PRETTY(log) AS "Explanations" FROM sys.autopilot_index_advisor_report WHERE type = "explain" ORDER BY id¥G *************************** 1. row *************************** Explanations: { "SQL": "CREATE INDEX `autoidx_tab5461_col5_col3_col4` ON `bb_seats`.`flight` ( `f_arrive_ap_id`, `f_depart_ap_id`, `f_depart_time` );", "explanation": [ { "reason": "Secondary Index", "query_text": "SELECT `F_ID` , `F_AL_ID` , `F_SEATS_LEFT` , `F_DEPART_AP_ID` , `F_DEPART_TIME` , `F_ARRIVE_AP_ID` , `F_ARRIVE_TIME` , `AL_NAME` , `AL_IATTR00` , `AL_IATTR01` FROM `flight` , `airline` WHERE `F_DEPART_AP_ID` = ? AND `F_DEPART_TIME` >= ? AND `F_DEPART_TIME` <= ? AND `F_AL_ID` = `AL_ID` AND `F_ARRIVE_AP_ID` IN (...)", "estimated_gain": "10.0x" } ], "est_create_time": "7.82 s" } *************************** 2. row *************************** Explanations: { "SQL": "CREATE INDEX `autoidx_tab5457_col1_col3` ON `bb_seats`.`airport_distance` ( `d_ap_id0`, `d_distance` );", "explanation": [ { "reason": "Covering Index", "query_text": "SELECT * FROM `airport_distance` WHERE `D_AP_ID0` = ? AND `D_DISTANCE` <= ? ORDER BY `D_DISTANCE` ASC", "estimated_gain": "10.0x" } ], "est_create_time": "133.00 ms" } ◼ seats / Scale factor : 7
12. ベンチマークツールで検証 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 12 ◼ インデックス作成前後のクエリ実行結果の比較 クエリ ESTIMATED PERF IMPACT 原因 セカンダリ インデックス 追加による改善 クエリ実行時間(秒) インデックス 作成所要時間(秒) 改善前 （平均） 改善後 アドバイザ 予想 実際 No.1 HIGH index_merge で Using intersect ICPの最適化 0.0225 0.0008 7.82 4.1066 No.2 LOW 主キー検索だが order by のソートコ スト インデックスで ソート不要に 0.0006 0.0006 0.133 0.1371
13. ベンチマークツールで検証：性能比較 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 13 Before After Eff. Measured Requests 22455 35493 158% Latency Distribution (microseconds) 95th Percentile 11371 3323 29% Maximum 46247 23744 51% Median 2153 1936 90% Minimum 96 194 202% 25th Percentile 476 475 100% 90th Percentile 4340 3045 70% 99th Percentile 16710 4446 27% 75th Percentile 2852 2619 92% Average 2668 1686 63% Throughput (requests/second) 374.2495406 591.5498396 158% Goodput (requests/second) 373.9328743 591.4998396 158% ◼ seats / Scale factor : 7
14. インデックスメンテナンス方式・方法の比較 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 14 手段・ツール データソース・対象など 追加インデックスの検討 人力 • クエリとその実行計画 • テーブル・インデックス定義 • performance_schema 統計情報 不使用インデックスの確認 sys.schema_unused_indexes • performance_schema.table_io_waits_summary_by_index_u sage (インデックスごとのテーブル I/O 待機) • information_schema.STATISTICS • mysql スキーマ以外 • 主キー以外 • 統計情報上のインデックス使用回数が0回 • 非ユニークキー pt-index-usage • スロークエリログ • EXPLAIN(実行計画) 重複インデックスの確認 sys.schema_redundant_indexes • information_schema.STATISTICS から重複する冗長イン デックスをリスト • 主キーも対象 pt-duplicate-key-checker • SHOW CREATE TABLE ◼ MySQL Community / Enterprise
15. インデックスメンテナンス方式・方法の比較 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 15 ◼ HeatWave MySQL (w/ HeatWave Cluster) 手段・ツール データソース・対象など 追加インデックスの検討 Autopilot Indexing • 指定されたスキーマに対して、以下の情報を収集し Autopilot Indexing 用の内部一時テーブルに格納 • information_schema.tables, columns, statistics, innodb_tablespaces, innodb_tables, key_column_usage • performance_schema.events_statements_summary_by_ digest • 分析対象クエリの EXPLAIN FORMAT=JSON • EXPLAIN をパース • 内部一時テーブルの情報を元に機械学習し、 レコメンデーションを作成 • 主キーは対象外 不使用インデックスの確認 重複インデックスの確認 ※前ページ記載のビュー・ツールも使用可能 (ただし pt-index-usage はインプットにスロークエリログが必要だが HeatWave MySQL は スロークエリログ出力不可) ✓ 定量的な評価、想定見積りを提示して くれる ✓ 統計情報の蓄積により提案内容の精度 は向上？
16. さいごに 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 16 ◼ 惜しむらくは… 1. MySQL バージョン 9.0.0 から使用可能、ということ 2. HeatWeve クラスタが必要 • MDS (InnoDB)のみでも使いたい！ 3. プリペアードステートメントが分析対象外 • AP・フレームワークによっては常用・多用ありますよね • events_statements_summary_by_digest ではなくて prepared_statements_instances のほうに載る(そ して揮発する)からか • 実行クエリをどこか(なにか)でキャプチャしてから改めて流し込むとか（と、言うは易し） 4. 公式ドキュメントにまだ明記されていない分析対象外パターンがある模様 • 今回検証していて見つけたものだと、NOT EXISTS を使ったクエリが対象外になった • 実行クエリの種類が多すぎると確認が大変…
17. さいごに 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 17 ◼ HeatWave MySQL を利用するためのモチベーションのひとつに繋がったらいいですね – 決定的ではないけど、HeatWave MySQL でしか使えない便利な機能があるよ！ というイメージで捉えてもらえれば ◼ HeatWave MySQL でのデータベース 運用の自動化 (Autopilot) の発展に今後も期待してます！
18. ご清聴ありがとうございました MySQL & OCI に関するすべてを、私たちにおまかせ下さい。 Database & Cloud Technology Company [お問い合わせ] Tel：0120-429-223 Mail：sales@s-style.co.jp www.s-style.co.jp