Aurora MySQLの一時テーブルを拡張してみた
はじめに
こんにちは、カスタマーサクセス部の奥井大和/やまとです。
今回は、Aurora MySQLの一時テーブルのtmp_table_sizeとmax_heap_table_sizeを拡張してみます。
これらを拡張すると、メモリ内でより大きな一時テーブルを使用できるようになります。
Amazon RDS for MySQL データベースの空きメモリが少ない場合のトラブルシューティング方法を教えてください。
なので、根本解決にはなりませんが「一時テーブル」の領域が不足した時の応急処置として使うことができるかもしれません。
さっそく、テストしてみます。
背景は、「"The table is full"エラーが発生。一時テーブルのメモリ制限に達しているため、パラメータ調整で対処する」といったところです。
tmp_table_sizeとは？
MEMORY ストレージエンジンによって作成される一時テーブルの最大サイズのことです。
Aurora MySQL バージョン 3 での新しい一時テーブルの動作
max_heap_table_sizeとは？
MEMORY テーブルの最大サイズのことです。
MySQL 8.0 リファレンスマニュアル / 代替ストレージエンジン / MEMORY ストレージエンジン
RDSの環境
- エンジンバージョン：
8.0.mysql_aurora.3.08.2 - DB インスタンスのパラメータグループ：
default.aurora-mysql8.0を使用 - DB クラスターのパラメータグループ：
default.aurora-mysql8.0を使用
作業前の注意点
1: 事前確認-現在の構成の把握
1.1 現在のパラメータグループの値を確認する
下記画像では、パラメータグループから対象の値を検索してみました。
- 入力
aws rds describe-db-parameters \
--db-parameter-group-name default.aurora-mysql8.0 \
--query 'Parameters[?ParameterName==`tmp_table_size`||ParameterName==`max_heap_table_size`]'
出力
[
{
"ParameterName": "max_heap_table_size",
"Description": "Maximum size to which MEMORY tables are allowed to grow.",
"Source": "engine-default",
"ApplyType": "dynamic",
"DataType": "integer",
"AllowedValues": "16384-1844674407370954752",
"IsModifiable": true,
"ApplyMethod": "pending-reboot"
},
{
"ParameterName": "tmp_table_size",
"Description": "Defines the maximum size of internal in-memory temporary tables created by the MEMORY storage engine and, as of Aurora 3.04, the TempTable storage engine when aurora_tmptable_enable_per_table_limit is ON.",
"Source": "engine-default",
"ApplyType": "dynamic",
"DataType": "integer",
"AllowedValues": "1024-18446744073709551615",
"IsModifiable": true,
"ApplyMethod": "pending-reboot"
}
]
【出力の解説】
tmp_table_size
- 設定可能範囲: 1,024バイト(1KB) ～ 18,446,744,073,709,551,615バイト(約18EB)
- 適用方法: pending-reboot（再起動が必要）
- Aurora 3.04以降: aurora_tmptable_enable_per_table_limitがONの場合、TempTableエンジンも制御
max_heap_table_size
- 設定可能範囲: 16,384バイト(16KB) ～ 1,844,674,407,370,954,752バイト(約1.8EB)
- 適用方法: pending-reboot（再起動が必要）
- デフォルト値: engine-default（通常16MB）
デフォルトでは、サイズは16MB程度に設定されています。
なお、MySQLがメモリ内に作成する一時テーブルの上限サイズは、tmp_table_sizeとmax_heap_table_sizeのうち、小さい方の値が適用されます。そのため、両方のパラメータを同じ値に設定するのが一般的です。
インメモリー一時テーブルの最大サイズは、tmp_table_size または max_heap_table_size のいずれか小さい方の値によって定義されます。
8.4.4 MySQL での内部一時テーブルの使用
2: カスタムDBパラメータグループの作成と変更
AWSでは、デフォルトのパラメータグループを直接編集することはできません。そのため、まずカスタムのパラメータグループを作成します。
2.1 パラメータグループファミリーを調べる
Auroraのエンジンバージョンに合ったファミリーを指定するため確認します。
- 入力
aws rds describe-db-engine-versions \
--engine aurora-mysql \
--query 'DBEngineVersions[?EngineVersion==`8.0.mysql_aurora.3.09.0`].DBParameterGroupFamily' \
--output text
出力結果にエンジンバージョンが表示されます。
2.2 カスタムDBパラメータグループを作成する
上記で調べたファミリー名を指定して、新しいグループを作成します。
- 入力
aws rds create-db-parameter-group \
--db-parameter-group-name "パラメータグループ名" \
--db-parameter-group-family "パラメータグループのファミリー" \
--description "説明"
今回はパラメータグループ名をaurora-test-mysql80-temp-tableにし、パラメータグループのファミリー（DBエンジンとバージョン）をaurora-mysql8.0にします。
例
aws rds create-db-parameter-group \
--db-parameter-group-name "aurora-test-mysql80-temp-table" \
--db-parameter-group-family "aurora-mysql8.0" \
--description "Temporary table optimization for cluster"
出力結果にDBParameterGroupの情報が表示されます。
2.3 パラメータを変更する
作成したカスタムグループのパラメータを、問題解決のために変更します。ここでは一時テーブルの上限サイズ（作業スペース）を16MBから256MBへと拡張します。アプリケーションが発行するクエリの複雑さや、インスタンスのメモリサイズに応じて調整してください。
# tmp_table_sizeを256MBに設定
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-test-mysql80-temp-table" \
--parameters "ParameterName=tmp_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot"
# max_heap_table_sizeを256MBに設定
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-test-mysql80-temp-table" \
--parameters "ParameterName=max_heap_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot"
※ApplyMethod=pending-rebootは、この変更を有効にするには再起動が必要であることを明示します。
--parameters オプションを複数指定することで、1つのコマンドにまとめることもできます。
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-test-mysql80-temp-table" \
--parameters \
"ParameterName=tmp_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot" \
"ParameterName=max_heap_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot"
3: DBインスタンスに適用
パラメータの準備ができたら、DBインスタンスに適用します。--no-apply-immediately オプションを指定することで、次のメンテナンスウィンドウまで適用（と再起動）を保留し、即時影響が出るのを防ぎます。
メンテナンスウィンドウになると、パラメータグループの適用とインスタンスの再起動が自動的に行われます。
- 入力
# RDSインスタンスへの適用
aws rds modify-db-instance \
--db-instance-identifier <DBインスタンスの識別子> \
--db-parameter-group-name "aurora-test-mysql80-temp-table" \
--no-apply-immediately
上記を入力すると、対象インスタンスの現在の状態が返ってきます。
4: 適用後の確認と監視
再起動後、設定が正しく反映されたかを確認します
4.1 パラメータ適用を確認する
MySQLクライアントでデータベースに接続し、以下のSQLを実行してグローバル変数が変更されていることを確認
- 入力
SELECT
@@tmp_table_size/1024/1024 as tmp_table_size_mb,
@@max_heap_table_size/1024/1024 as max_heap_table_size_mb,
@@internal_tmp_mem_storage_engine as storage_engine;
出力結果
+-------------------+------------------------+----------------+
| tmp_table_size_mb | max_heap_table_size_mb | storage_engine |
+-------------------+------------------------+----------------+
| 256.00000000 | 256.00000000 | TempTable |
+-------------------+------------------------+----------------+
1 row in set (0.000 sec)
万が一のために：ロールバック手順
もし予期せぬ問題が発生した場合、すぐに元の状態に戻せるようにしておくことが重要です。元のパラメータグループを即座に適用し、インスタンスを再起動します。
- 入力
aws rds modify-db-instance \
--db-instance-identifier <DBインスタンスの識別子> \
--db-parameter-group-name <元のパラメータグループの名前> \
--apply-immediately
# 即座にリブート
aws rds reboot-db-instance --db-instance-identifier <DBインスタンスの識別子>
まとめ
Aurora MySQLの一時テーブル領域不足に対してtmp_table_sizeとmax_heap_table_sizeを拡張することで、メモリ内での一時テーブル処理範囲が広がり、ディスクI/Oを削減して「The table is full」エラーを一時的に解消できます。
しかし、この対応はメモリ枯渇のリスクを伴う応急処置に過ぎず、データ量の増加に伴い問題が再発する可能性があります。
根本的な解決のためには、スロークエリログで問題のあるクエリを特定し、適切なインデックスの追加やJOIN条件の見直しなどのクエリ最適化を実施することが不可欠です。
パラメータ調整で緊急対応しつつ、並行して根本的な最適化を進めることが、安定したデータベース運用への道筋となります。
参考：公式ドキュメント
アノテーション株式会社について
アノテーション株式会社はクラスメソッドグループのオペレーション専門特化企業です。サポート・運用・開発保守・情シス・バックオフィスの専門チームが、最新 IT テクノロジー、高い技術力、蓄積されたノウハウをフル活用し、お客様の課題解決を行っています。当社は様々な職種でメンバーを募集しています。「オペレーション・エクセレンス」と「らしく働く、らしく生きる」を共に実現するカルチャー・しくみ・働き方にご興味がある方は、アノテーション株式会社 採用サイトをぜひご覧ください。
