概要
最近、最新のアクションカメラを購入してYoutube用に動画編集してるCTO室のKazです 今回、MySQLのマイグレーション時、Index有効クエリを高速化するため、ボトルネックを特定してそれを解消しました。
結果
Index有効化において
適切なメモリ量を確保し、MySQL Workbenchのパフォーマンスレポートを確認することで、システムのボトルネックを特定しました。その結果、プロビジョニングIOPSを利用することにより、特に時間を要していたテーブルのインデックス有効化の時間を大幅に短縮し、システム全体の効率化を図ることができました。
インデックス有効化の実行時間の比較
| 項目 | 時間 |
|---|---|
| プロビジョニングIOPS利用前 | 約178.94分 |
| プロビジョニングIOPS利用後 | 約74.3分 |
| 時間の短縮量 | 約104.64分 |
| 短縮率 | 約58.5% |
Import先DB
| 項目 | 詳細 |
|---|---|
| インスタンスクラス | db.m7g.2xlarge |
| vCPU | 8 |
| RAM | 32GB |
| ストレージタイプ | 汎用 SSD (gp2) |
| ストレージ | 198 GiB （最大IOPSは9900） |
| プロビジョンド IOPS | 5000 (測定値の最大値近くを設定) |
プロビジョンド IOPS：5000
設定可能最大値の9900IOPSでIndex無効時に計測した実際のIOPSはそこに到達していなかった。理由はいくつか考えられるが、75％以上も時間短縮できているのでアプリが持つボトルネックはこれ以上は掘り下げないことにしました。（Index有効にした後はio1 ボリュームから gp2 ボリュームへ変更します）
インスタンスクラス：db.m7g.2xlarge - 総合的に考えると、最低でも24 GB以上のメモリが必要でした。
MySQLのメモリ使用状況を確認
MySQLのメモリ使用状況を確認
SHOW ENGINE INNODB STATUS;
SHOW ENGINE INNODB STATUS コマンドの出力の一部
---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2745958400 Dictionary memory allocated 2122468 Buffer pool size 163840 Free buffers 8205 Database pages 155340 Old database pages 57179 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3056426, not young 184889454 0.00 youngs/s, 0.00 non-youngs/s Pages read 12983565, created 620638, written 37391870 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 155340, unzip_LRU len: 0 I/O sum[96]:cur[0], unzip sum[0]:cur[0]
Total large memory allocated: 2745958400 bytes (約2.56 GB) Dictionary memory allocated: 2122468 bytes (約2 MB) Buffer pool size: 163840 pages (1ページ = 16 KB, 約2.56 GB) Free buffers: 8205 pages Database pages: 155340 pages Old database pages: 57179 pages Modified db pages: 0 Pending reads: 0 Pending writes: LRU 0, flush list 0, single page 0
SHOW ENGINE INNODB STATUSからメモリ使用量の計算
- バッファプール全体のメモリ量: Total large memory allocated 約2.56 GB
- ディクショナリメモリを引く: Dictionary memory allocated 約2.12 MB
- バッファプールが使用しているメモリ量: 約2.56 GB - 約2.12 MB = 約2.56 GB （innodb_buffer_pool_sizeは{DBInstanceClassMemory*3/4}と推奨の値になっているので一致）
メモリ使用量の見積もり
SELECT table_schema AS 'Database', ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'Data Size (MB)', ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'Index Size (MB)', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)' FROM information_schema.tables WHERE table_schema = '<schema>' GROUP BY table_schema;
| Database | Data Size (MB) | Index Size (MB) | Total Size (MB) |
|---|---|---|---|
| |
5746.02 | 13400.30 | 19146.31 |
データベースの現状 データサイズ: 約5.75 GB インデックスサイズ: 約13.4 GB 合計サイズ: 約19.15 GB
InnoDBバッファプールのサイズ 現在のバッファプールサイズ: 約2.56 GB（163,840ページ、1ページ16 KB）
現在のメモリ使用状況 バッファプールが使用されているメモリ量: 約2.37 GB
必要なメモリ量の見積もり 基本的には、データサイズとインデックスサイズの合計量に加え、ある程度のバッファやシステムのオーバーヘッドを考慮してメモリを割り当てることが推奨されます。
以下に、計算手順を示します：
データとインデックスの合計サイズ 約19.15 GB
バッファプールのサイズ 一般的には、データとインデックスの合計サイズの80%～100%程度のバッファプールサイズを設定 19.15 GBの80%は約15.32 GB
システムのオーバーヘッド MySQLサーバやOS自体が必要とするメモリも考慮する必要があるので通常、4 GB程度とする
推奨メモリ量 総合的に考えると、データベースサーバに最低でも24 GB以上のメモリが必要。 最小メモリ量: 約24 GB（15.32 GBのバッファプール + 4 GBのオーバーヘッド + 追加の余裕） 推奨メモリ量: 32 GB（19.15 GBのバッファプール + 4 GBのオーバーヘッド + 追加の余裕）
Export
ストアドプロシージャとストアドファンクションを除外せず、データベース全体の完全なバックアップを作成する
データベースマイグレーション時にストアドプロシージャやストアドファンクションを含めた完全なバックアップを作成することは、アプリケーションの機能維持、データ整合性の確保において重要です。
index無効化
export時はIndexサイズが大きいテーブルにおいてIndex無効化しておく。インデックスサイズが大きいものを無効化するというアプローチは合理的だが無効化によってデータの整合性が失われるリスクに注意する。
index無効化対象テーブル
| Table | Table Size (MB) | Data Size (MB) | ndex Size (MB) |
|---|---|---|---|
| Table_A | 16891.77 | 4311.00 | 12580.77 |
| Table_B | 998.81 | 424.94 | 573.88 |
| Table_C | 328.33 | 155.67 | 172.66 |
| Table_D | 136.25 | 118.67 | 17.58 |
テーブルが持つインデックスサイズを知る(top10)
SELECT table_schema AS `Database`, table_name AS `Table`, ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Index Size (MB)` FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') GROUP BY table_schema, table_name ORDER BY `Index Size (MB)` DESC LIMIT 10;
テーブルが持つインデックスサイズを知る
SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '<database_name>' AND TABLE_NAME = 'Table_A' ORDER BY INDEX_NAME, SEQ_IN_INDEX;
Index無効化
use <database_name>; ALTER TABLE Table_A DROP INDEX <index_name>;
Export command
mysqldump --host=<hostname> --port=3306 --default-character-set=utf8 --user=<username> --password --protocol=tcp --routines <schema> > C:\Users\Administrator\Documents\dumps\<schema>_full_backup.sql
--routines
ストアドプロシージャとファンクションのエクスポート
このオプションを使用することで、MySQLデータベースからストアドプロシージャとストアドファンクションを含めたエクスポートが可能です。
所要時間
- 約120分
Import
Import先DB
- 指定したschemaがDBに存在しない場合はエラーになるのでschemaは事前に作っておく
CREATE SCHEMA `<schema>` ;
mysql --host=<hostname> --port=3306 --default-character-set=utf8 --user=<username> --password --protocol=tcp <schema> < C:\Users\Administrator\Documents\dumps\<schema>_full_backup.sql
所要時間
- 約60分
Index有効化
インデックス有効化とプロビジョニングIOPSの関係
データベースのインデックスを有効化する際に、プロビジョニングIOPSを利用していなかった場合、クエリの実行に長時間が必要です。これは、インデックスの作成や更新には多くのディスクI/Oが必要となり、ディスクアクセスがボトルネックになるためです。
具体的には、以下のような状況が発生しました。 状況: インデックスの有効化を行うクエリの実行中、ディスクI/Oの負荷が高まり、IOPS（Input/Output Operations Per Second）の制限に達しました。(gp2のバースト性能の上限は、3,000 IOPS) 結果、クエリの実行時間が長くなり、データベースの応答性が低下しました。
ボトルネック解消の打ち手
- プロビジョニングIOPSを使用したEBSに変更することで、以下の利点が期待できます。
- 高いI/Oキャパシティ: プロビジョニングIOPS EBSボリュームは、予測可能かつ高いI/Oパフォーマンスを提供します。これにより、データベースの書き込み操作が迅速に行われ、ボトルネックが解消される可能性が高くなります。
- 低レイテンシ:プロビジョニングIOPSは低レイテンシを提供するため、データの読み書き速度が向上し、インポート時間が短縮されます。
- 一般的な用途でOKなio1を選択する
index有効化実行
- 無効化しているテーブルに対してIndexを有効化します。
use <schema>; ALTER TABLE <table_name> ADD INDEX <index_name> (colum1, colum2, colum3, colum4);
インデックス有効化の時間と効果
プロビジョニングIOPSを利用することで、一番時間を要していたテーブルのインデックス有効化の時間が大幅に短縮され、効率化が図られました。
| 項目 | 時間 |
|---|---|
| 実行時間 | 約63分 |
| 時間の短縮量 | 約47.4分 |
| 短縮率 | 約75.3% |
05:35:56 ALTER TABLE <table_name> ADD INDEX <index_name> (column1, column2, column3, column4) 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 933.094 sec
| 項目 | プロビジョニングIOPS利用前 | プロビジョニングIOPS利用後 |
|---|---|---|
| インデックス有効化にかかる時間 | 約63分 | 約15.6分 |
| 時間の短縮量 | - | 約47.4分 |
| 短縮率 | - | 約75.3% |
| 実行前、後の効果 | - | 大幅に短縮され、効率化 |
補足
これらのパラメータを設定することで、接続が長時間アイドル状態になるのを防ぎ、サーバのリソースを効果的に管理することができます。
RDS
- timeoutしないようにしておく
| パラメータ | 説明 | 設定値 |
|---|---|---|
interactive_timeout |
このパラメータは、MySQLサーバがインタラクティブクライアント（通常はMySQLコンソールなどの手動入力が行われるセッション）に対してどれくらいの時間アイドル状態を許可するかを指定します。このタイムアウト時間が経過すると、サーバは接続を閉じます。 | 3600秒 |
wait_timeout |
このパラメータは、MySQLサーバが非インタラクティブクライアント（アプリケーションやスクリプトなどの自動接続）に対してどれくらいの時間アイドル状態を許可するかを指定します。このタイムアウト時間が経過すると、サーバは接続を閉じます。 | 28800秒 |
MySQL Workbench
- timeoutしないようにしておく
ボトルネック調査で推奨されるレポート
Hot Spots for I/O: Top File I/O Activity Report: どのファイルが最もI/Oを引き起こしているかを確認します。 Top I/O by File by Time: 時間ごとのファイルI/Oを確認します。 Top I/O by Event Category: イベントカテゴリ別のI/Oを確認します。 Top I/O by Time by Event Categories: イベントカテゴリごとの時間別I/Oを確認します。 Top I/O Time by User/Thread: ユーザーやスレッドごとのI/O時間を確認します。
High Cost SQL Statements: Statements in Highest 5 Percent by Runtime: 実行時間が最も長い上位5%のSQLステートメントを確認します。 With Sorting: ソートを伴うクエリを確認します。 Full Table Scans: フルテーブルスキャンを行っているクエリを確認します。
Database Schema Statistics: Schema Index Statistics: インデックスの統計情報を確認します。 Schema Table Statistics: テーブルの統計情報を確認します。 Tables with Full Table Scans: フルテーブルスキャンを行っているテーブルを確認します。 Unused Indexes: 使用されていないインデックスを確認します。
Wait Event Times (Expert): Global Waits by Time: 全体の待機時間を確認します。 Waits by User by Time: ユーザーごとの待機時間を確認します。 Wait Classes by Time: 待機クラスごとの待機時間を確認します。 Wait Classes by Average Time: 平均待機時間ごとの待機クラスを確認します。
InnoDB Statistics: InnoDB Buffer Stats by Schema: スキーマごとのInnoDBバッファの統計を確認します。 InnoDB Buffer Stats by Table: テーブルごとのInnoDBバッファの統計を確認します。
User Resource Use: Overview: ユーザーごとのリソース使用状況を確認します。 I/O Statistics: I/O統計情報を確認します。 Statement Statistics: ステートメントの統計情報を確認します。
