Aurora3のTempTableの落とし穴とその回避策
-- Views
November 21, 24
スライド概要
Aurora3で発生する、TempTableのエラーについて細かく解説し、どう設定すればいいのかを説明します
株式会社Finatext サーバーサイドエンジニア AWS/Go/Terraform
関連スライド
各ページのテキスト
Jr.Champions第8回目勉強会 Amazon Aurora MySQL 3のTempTableの落とし穴 2024-11-21 株式会社Finatext 松崎稔矢 @tm8619_pro © 2024 Finatext Holdings Ltd.
自己紹介 松崎稔矢 Toshiya Matsuzaki 株式会社Finatext 職種: バックエンドエンジニア チームリーダー 趣味: ボウリング ど /ゲーム /ダーツ /ポーカー /乗馬 /お酒 な 技術: Go/AWS/Terraform 昔競プロ経験あり 好きなAWSサービス: AWS RDS(Aurora) 何食べたらこんな設計思いつくんだ… © 2024 Finatext Holdings Ltd. 1
イントロダクション 2024/10/31に Amazon Aurora 2のEOLがありました 対応に追われた方も多かったのでは…？ © 2024 Finatext Holdings Ltd. 2
イントロダクション Aurora3で大きい選択クエリを発行する際に 起きたエラーについて深堀りしていきます ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full 具体的にはこいつです↑ © 2024 Finatext Holdings Ltd. 3
検知した背景memo ● EmbulkというETLツールを使用してGoogle Cloud BigQueryへDBのデータを連携している。 その際、Viewテーブル的に少々大きめのクエリを発行していたが、エラーが出てしまった ● embulkのエラー ○ ○ Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,800,083 milliseconds ago. The last packet sent successfully to the server was 1,848,883 milliseconds ago. ● これより少し小さいクエリは1分掛からず返ってきていたのに突然30分返ってこなくなった ● 流石におかしいと思い、Aurora側のエラーログとスロークエリログを見ると以下のエラー を発見する ○ Query_time: 48.381259 Lock_time: 0.000056 Rows_sent: 0 Rows_examined: 25024439 ○ 2024-01-17T04:01:09.506156Z 5742740 [ERROR] [MY-013132] [Server] ○ The table '/rdsdbdata/tmp/#sql4d76_57a094_1' is full! (handler.cc:4380) ● Rows_sentが0なこと、tmpテーブルっぽいものがfullで有ることが分かり、どうもDB側の エラーが原因であると判断し、調べていくとパラメータチューニングをしないといけないこ とに行き着いた。 © 2024 Finatext Holdings Ltd. 4
検知した背景memo ● EmbulkというETLツールを使用してGoogle Cloud BigQueryへDBのデータを連携している。 その際、Viewテーブル的に少々大きめのクエリを発行していたが、エラーが出てしまった ● embulkのエラー ○ ○ Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,800,083 milliseconds ago. The last packet sent successfully to the server was 1,848,883 milliseconds ago. ● これより少し小さいクエリは1分掛からず返ってきていたのに突然30分返ってこなくなった ● 流石におかしいと思い、Aurora側のエラーログとスロークエリログを見ると以下のエラー を発見する ○ Query_time: 48.381259 Lock_time: 0.000056 Rows_sent: 0 Rows_examined: 25024439 ○ 2024-01-17T04:01:09.506156Z 5742740 [ERROR] [MY-013132] [Server] ○ The table '/rdsdbdata/tmp/#sql4d76_57a094_1' is full! (handler.cc:4380) ● Rows_sentが0なこと、tmpテーブルっぽいものがfullで有ることが分かり、どうもDB側の エラーが原因であると判断し、調べていくとパラメータチューニングをしないといけないこ とに行き着いた。 © 2024 Finatext Holdings Ltd. 5
イントロダクション 今日の目的 Aurora MySQL 3でのTempTableの 注意すべき仕様を把握・理解し 課題に直面した際自力で対応出来るようになる © 2024 Finatext Holdings Ltd. 6
アジェンダ ●MySQL TempTableについておさらい ●MySQL3 のパラメータの解説 ●Aurora3 特有の注意点 ●対応方法 © 2024 Finatext Holdings Ltd. 7
アジェンダ ●MySQL TempTableについておさらい ●MySQL3 のパラメータの解説 ●Aurora3 特有の注意点 ●対応方法 © 2024 Finatext Holdings Ltd. 8
MySQL TempTableについておさらい TempTableとは クエリを処理するために、一時的に作成するテーブルのこと ざっくりのイメージは、Excelで集計シート作るような感覚 自分で作成することも出来るが、基本的にはEXPLAIN句で using temporaryが出るクエリで作成される © 2024 Finatext Holdings Ltd. 9
MySQL TempTableについておさらい TempTableとは クエリを処理するために、一時的に作成するテーブルのこと ざっくりのイメージは、Excelで集計シート作るような感覚 自分で作成することも出来るが、基本的にはEXPLAIN句で using temporaryが出るクエリで作成される ここ！ © 2024 Finatext Holdings Ltd. 10
MySQL TempTableについておさらい TempTableの種類 ● ディスクに作る ○ 操作に時間がかかる ○ 大量のデータを載せられる ● メモリに作る ○ メモリに乗るので、高速な操作が可能 © 2024 Finatext Holdings Ltd. 11
MySQL TempTableについておさらい TempTableの種類 メモリに作る ● MEMORY Engine ○ MySQL5.7以前からある ○ インメモリのみ使用する ● TempTable Engine ○ MySQL8系から新設 Aurora3からのデフォルト ○ メモリマップトファイル(memory mapped files)を使う ■ ディスクにファイルを作るが、操作をメモリ上でやる © 2024 Finatext Holdings Ltd. 12
アジェンダ ●MySQL TempTableについておさらい ●MySQL3 のパラメータの解説 ●Aurora3 特有の注意点 ●対応方法 © 2024 Finatext Holdings Ltd. 13
MySQL3 のパラメータの解説 一時テーブル関連のパラメータ ● tmp_table_size ○ 個別のtemp tableのサイズ上限 ○ default 16MB (16GBメモリ以上のインスタンスは1GB) ● temptable_max_ram ○ 全体のtemp tableのサイズ上限で、メモリにいくらまで 乗せるか ○ Default 1GB ● temptable_max_mmap ○ temptable_max_ramを超えた際、メモリマップトファ イルを使ってディスクに保存する最大サイズの制限 ○ Default 1GB ● temptable_use_mmap ○ メモリマップトファイルの生成をするかどうか ○ MySQL8.0.26でdeprecated （temptable_max_mmapに統合） © 2024 Finatext Holdings Ltd. 14
MySQL3 のパラメータの解説 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram メモリ使用サイズを超えな ければメモリだけで終了 超えていたら後続へ （メモリマップトファイ ル） ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 15
MySQL3 のパラメータの解説 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap メモリマップトファイルで 収まればそこで終了 超えていたらディスクへ ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 16
MySQL3 のパラメータの解説 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap ディスクにTempTableを 作成し、クエリを完了 ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 17
アジェンダ ●MySQL TempTableについておさらい ●MySQL3 のパラメータの解説 ●Aurora3 特有の注意点 ●対応方法 © 2024 Finatext Holdings Ltd. 18
Aurora3 特有の注意点 Auroraの保存領域の前提 ● インスタンスメモリ ○ FreeableMemoryで見れる部分 ○ 各インスタンスのメモリ ● インスタンスストレージ ○ FreeLocalStorageで見れる部分 ○ 各インスタンスが持つストレージ ● クラスタボリューム ○ AuroraVolumeBytesLeftTotalで見れる部分 ○ クラスタに紐づき、DBのデータを格納している ○ Writerインスタンスのみ書き込み権限がある https://pages.awscloud.com/rs/112-TZM© 2024 Finatext Holdings Ltd. 766/images/01_Amazon%20Aurora%20%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%81%E3%83%A3%E6%A6%82%E8%A6%81.pdf 19
Aurora3 特有の注意点 Auroraの保存領域の前提 ● インスタンスメモリ ○ FreeableMemoryで見れる部分 ○ 各インスタンスのメモリ ● インスタンスストレージ ○ FreeLocalStorageで見れる部分 ○ 各インスタンスが持つストレージ ● クラスタボリューム ○ AuroraVolumeBytesLeftTotalで見れる部分 ○ クラスタに紐づき、DBのデータを格納している ○ Writerインスタンスのみ書き込み権限がある https://pages.awscloud.com/rs/112-TZM© 2024 Finatext Holdings Ltd. 766/images/01_Amazon%20Aurora%20%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%81%E3%83%A3%E6%A6%82%E8%A6%81.pdf 20
Aurora3 特有の注意点 具体的なメトリクス ※2024-01-30時点でdb.r6g.largeのもの メモリ(6GB) インスタンスストレージ (32GB) © 2024 Finatext Holdings Ltd. クラスタボリューム (130TB) 21
Aurora3 特有の注意点 Aurora2->3での変更点 ● TempTableがローカルストレージを使用するか ○ Aurora2ではReader Instanceでディスクに書く際に使用 ○ Aurora3ではメモリマップトファイルの作成で使用 ● デフォルトのメモリストレージエンジン ○ Aurora2ではMEMORY ○ Aurora3ではTempTable ■ ファイルを作ってメモリにマッピングするエンジン https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.CompareMySQL57.html © 2024 Finatext Holdings Ltd. 22
Aurora3 特有の注意点 Aurora3特有の仕様 ● クラスタボリュームに対し ○ ReaderInstanceが書き込めない ○ ＝ディスクに書き込む一時テーブルが作成出来ない つまり先ほどのフロー図が一部変化 © 2024 Finatext Holdings Ltd. 23
Aurora3 特有の注意点 Aurora 3 Reader Instanceでの挙動 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram メモリ使用サイズを超えな ければメモリだけで終了 超えていたら後続へ （メモリマップトファイ ル） ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 24
Aurora3 特有の注意点 Aurora 3 Reader Instanceでの挙動 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap メモリマップトファイルで 収まればそこで終了 超えていたらディスクへ ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 25
MySQL3 のパラメータの解説 Aurora 3 Reader Instanceでの挙動 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap ディスクにTempTable を作成... ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 26
MySQL3 のパラメータの解説 Aurora 3 Reader Instanceでの挙動 ● tmp_table_size ○ temp tableのサイズ上限 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限 ● temptable_max_mmap ディスクにTempTable を作成... することが出来ない！ ○ temptable_max_ramを超えた際、 メモリマップトファイルを使ってディス クに保存する最大サイズの制限 エラー ● temptable_use_mmap ○ メモリマップトファイルの生成をするか どうか © 2024 Finatext Holdings Ltd. 27
Aurora3 特有の注意点 つまり挙動としては以下の変化が起きた ● 変化していないポイント ○ メモリに保存する制限 ■ tmp_table_size ■ temptable_max_ram ■ max_heap_table_size ● Aurora2 ○ ディスクとしてインスタンスストレージを使っていた ○ Max 32GB(r6g.largeの場合)までTempTableを作成できていた ● Aurora3 ○ メモリマップトファイルでdefault 1GBまでインスタンスストレージを使用 ○ Max 1GBまでしかTempTableを使用できなくなった © 2024 Finatext Holdings Ltd. 28
Aurora3 特有の注意点 つまり挙動としては以下の変化が起きた ● 変化していないポイント ○ メモリに保存する制限 ■ tmp_table_size ■ temptable_max_ram ■ max_heap_table_size ● Aurora2 ○ ディスクとしてインスタンスストレージを使っていた ○ Max 32GB(r6g.largeの場合)までTempTableを作成できていた ● Aurora3 ○ メモリマップトファイルでdefault 1GBまでインスタンスストレージを使用 ○ Max 1GBまでしかTempTableを使用できなくなった © 2024 Finatext Holdings Ltd. 29
アジェンダ ●MySQL TempTableについておさらい ●MySQLのパラメータの解説 ●Aurora3 特有の注意点 ●対応方法 © 2024 Finatext Holdings Ltd. 30
対応方法 パラメータを確認 ● tmp_table_size ○ 個別のtemp tableのサイズ上限 ○ default 16MB (16GBメモリ以上のインスタンスは1GB) ● temptable_max_ram ○ 全体のtemp tableのサイズ上限で、メモリにいくらまで乗せるか ○ Default 1GB ● temptable_max_mmap ○ temptable_max_ramを超えた際、メモリマップトファイルを使ってディスクに保 存する最大サイズの制限 ○ Default 1GB ● temptable_use_mmap © 2024 Finatext Holdings Ltd. 31
対応方法 具体的なメトリクス ※2024-01-30時点でdb.r6g.largeのもの メモリ(6GB) インスタンスストレージ (32GB) © 2024 Finatext Holdings Ltd. クラスタボリューム (130TB) 32
対応方法 改めてパラメータを確認 ● tmp_table_size ○ 個別のtemp tableのサイズ上限 ○ default 16MB (16GBメモリ以上のインスタンスは1GB) ● temptable_max_ram ○ 全体のtemp tableのサイズ上限で、メモリにいくらまで乗せるか ○ Default 1GB ● temptable_max_mmap ○ temptable_max_ramを超えた際、メモリマップトファイルを使ってディスクに保 存する最大サイズの制限 ○ Default 1GB ● temptable_use_mmap © 2024 Finatext Holdings Ltd. 33
対応方法 改めてパラメータを確認 ● tmp_table_size ○ 個別のtemp tableのサイズ上限 ○ default 16MB → 1GBに メモリで収められる範囲が向上 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限で、メモリにいくらまで乗せるか ○ Default 1GB ● temptable_max_mmap ○ temptable_max_ramを超えた際、メモリマップトファイルを使ってディスクに保 存する最大サイズの制限 ○ Default 1GB ● temptable_use_mmap © 2024 Finatext Holdings Ltd. 34
対応方法 改めてパラメータを確認 ● tmp_table_size ○ 個別のtemp tableのサイズ上限 ○ default 16MB → 1GBに メモリで収められる範囲が向上 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限で、メモリにいくらまで乗せるか ○ Default 1GB → 変更なし 6GB空いているので、3GBくらいまでは行けたかも ● temptable_max_mmap ○ temptable_max_ramを超えた際、メモリマップトファイルを使ってディスクに保 存する最大サイズの制限 ○ Default 1GB ● temptable_use_mmap © 2024 Finatext Holdings Ltd. 35
対応方法 改めてパラメータを確認 ● tmp_table_size ○ 個別のtemp tableのサイズ上限 ○ default 16MB → 1GBに メモリで収められる範囲が向上 ● temptable_max_ram ○ 全体のtemp tableのサイズ上限で、メモリにいくらまで乗せるか ○ Default 1GB → 変更なし 6GB空いているので、3GBくらいまでは行けたかも ● temptable_max_mmap ○ temptable_max_ramを超えた際、メモリマップトファイルを使ってディスクに保 存する最大サイズの制限 ○ Default 1GB → 10GBに ■ メモリマップトファイル領域増加 ■ インスタンス領域は32GB空いているのでもっと行ける想定 ● temptable_use_mmap © 2024 Finatext Holdings Ltd. 36
アウトロダクション 結論 伝えたいこと① ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full ↑を見たら、 一時テーブルの仕様を思い出してください © 2024 Finatext Holdings Ltd. 37
アウトロダクション 結論 伝えたいこと② Aurora3を使ってサービスをリリースする前に 一時テーブル周りの設定を確認しておくこと！ ただしメトリクス等を見て適切な数値にしないと メモリ枯渇等で障害になる可能性があります © 2024 Finatext Holdings Ltd. 38