Zenn
🦔

MySQLパフォーマンス向上のためのプロビジョニングIOPSの活用とその結果

2024/05/29に公開
MySQL
RDS
dbmigration
tech

概要

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 (io1)
ストレージ 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)
<schema> 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: ステートメントの統計情報を確認します。

Discussion