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

HeatWavejp Meetup #13 祝2周年 HeatWave の 〇〇 やってみた!LT大会!! HeatWave MySQL の Autopilot Indexing を試してみた！ 日時： 2025/4/17(木) 担当： 株式会社スマートスタイル 藤本 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved.

自己紹介 藤本 正比古 株式会社 スマートスタイル DB＆クラウド事業部データベース部エンジニア 来月からは、ビジネスディベロップメント部 プリンシパルアーキテクト 【経歴】 • プログラマ(C,Java,色々) 5年 • Oracle DB/WebLogic(Fusion Middleware)デリバリ・サポート・DBA 7年 • 2019年 スマートスタイルに中途入社 (6年目) 【これまでのスマートスタイルでの業務範囲】 • MySQL/MariaDB/Percona 全般 【趣味】 卓球 (右ペン表ソフト速攻) • 振り返るとオンプレ多め • Orchestrator, ProxySQL など割と得意 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 2

Autopilot Indexing ◼ 使用条件 最低5クエリ必要 • 1種類×5回 • 5種類×各1回づつ • – HeatWave MySQL 9.0.0 以上 – HeatWave クラスタ有効 – InnoDB ストレージエンジンテーブル • • アドバイザ標準出力 sys.autopilot_index_advisor_report テーブル 1. パフォーマンス向上が見込めるインデックスの作成提案 • 影響を受けるクエリ(ダイジェスト)も表示 2. 不要インデックス(未使用、重複)の削除提案 3. インデックスの追加・削除によるディスク容量の推定サイズ増減 4. 各DDLの推定実行時間 メンテナン ス実施 クエリ実行 レコメン デーション の確認 統計情報の 蓄積 アドバイザ 実行 • 2025/4/17 sys.autopilot_index_advisor プロシージャ • Machine Learning Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. • • performance_schema information_schema 3

Autopilot Indexing ◼ How-To など細かいことは… Tech ブログ記事にしました！ スマートスタイル TECH BLOG | HeatWave MySQL の Autopilot Indexing について https://blog.s-style.co.jp/2025/04/14078/ 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 4

• https://blog.s-style.co.jp/2025/04/14078/

ベンチマークツールで検証 ◼ Oracle CloudWorld 2023 [Automatic Indexing of MySQL Database with Machine Learning] より引用 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 5

ベンチマークツールで検証 ◼ 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. 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. $ java -jar benchbase.jar ¥ -b seats ¥ -c config/mysql/sample_seats_config.xml ¥ --create=true ¥ --load=true ¥ --execute=true 6

• https://github.com/cmu-db/benchbase
• https://db.cs.cmu.edu/projects/benchbase/
• https://github.com/cmu-db/benchbase/wiki/Seats

ベンチマークツールで検証：アドバイザ実行結果 ◼ 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 | | | +---------------------------------------------------------+ (つづく) 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 7

ベンチマークツールで検証：アドバイザ実行結果 ◼ 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 | | | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ (つづく) 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 8

ベンチマークツールで検証：アドバイザ実行結果
◼ 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; |
|
|
+-------------------------------------------------------------------------------------------------------------------------+

(つづく)

2025/4/17

Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved.

9

ベンチマークツールで検証：アドバイザ実行結果
◼ seats / Scale factor : 7
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` );
|
+-------------------------------------------------------------------------------------------------------------------------------+

2025/4/17

Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved.

10

ベンチマークツールで検証：アドバイザ実行結果
◼ seats / Scale factor : 7
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"
}

2025/4/17

Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved.

11

ベンチマークツールで検証 ◼ インデックス作成前後のクエリ実行結果の比較 セカンダリ インデックス 追加による改善 クエリ実行時間(秒) インデックス 作成所要時間(秒) 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 クエリ 2025/4/17 改善前 （平均） Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 改善後 アドバイザ 予想 実際 12

ベンチマークツールで検証：性能比較 ◼ seats / Scale factor : 7 Before After Eff. 22455 35493 158% 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% Measured Requests Latency Distribution (microseconds) 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 13

インデックスメンテナンス方式・方法の比較 ◼ MySQL Community / Enterprise 追加インデックスの検討 手段・ツール データソース・対象など 人力 • • • クエリとその実行計画 テーブル・インデックス定義 performance_schema 統計情報 • • performance_schema.table_io_waits_summary_by_index_u sage (インデックスごとのテーブル I/O 待機) information_schema.STATISTICS • • • • mysql スキーマ以外 主キー以外 統計情報上のインデックス使用回数が0回 非ユニークキー • • スロークエリログ EXPLAIN(実行計画) • • information_schema.STATISTICS から重複する冗長イン デックスをリスト 主キーも対象 • SHOW CREATE TABLE sys.schema_unused_indexes 不使用インデックスの確認 pt-index-usage 重複インデックスの確認 sys.schema_redundant_indexes pt-duplicate-key-checker 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 14

インデックスメンテナンス方式・方法の比較 ◼ 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 は スロークエリログ出力不可) 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 15

さいごに ◼ 惜しむらくは… 1. MySQL バージョン 9.0.0 から使用可能、ということ 2. HeatWeve クラスタが必要 • MDS (InnoDB)のみでも使いたい！ 3. プリペアードステートメントが分析対象外 • AP・フレームワークによっては常用・多用ありますよね • events_statements_summary_by_digest ではなくて prepared_statements_instances のほうに載る(そ して揮発する)からか • 実行クエリをどこか(なにか)でキャプチャしてから改めて流し込むとか（と、言うは易し） 4. 公式ドキュメントにまだ明記されていない分析対象外パターンがある模様 • 今回検証していて見つけたものだと、NOT EXISTS を使ったクエリが対象外になった • 実行クエリの種類が多すぎると確認が大変… 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 16

さいごに ◼ HeatWave MySQL を利用するためのモチベーションのひとつに繋がったらいいですね – 決定的ではないけど、HeatWave MySQL でしか使えない便利な機能があるよ！ というイメージで捉えてもらえれば ◼ HeatWave MySQL でのデータベース 運用の自動化 (Autopilot) の発展に今後も期待してます！ 2025/4/17 Copyright © 2025. SmartStyle Co.,Ltd . All Rights Reserved. 17

ご清聴ありがとうございました MySQL & OCI に関するすべてを、私たちにおまかせ下さい。 Database & Cloud Technology Company [お問い合わせ] Tel：0120-429-223 Mail：[email protected] www.s-style.co.jp