Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL読むよ
を読んで理解を整理したときのメモ
Aurora3に上げるとreaderの一時テーブルの扱いが変わるらしいというのを念頭において読んでるので、そのへんの内容を中心にメモってる
MySQLは一時テーブルへの過剰なメモリを回避するために、一定の制限値に達したらディスクストレージに一時データを退避する
このディスクストレージは通常のストレージとは異なる
InnoDB on-disk internal temporay table
8.0より前はこのストレージにMEMORY storage engineが使われていたが、8.0からはデフォルトでTempTable storage engineが使われる
TempTableによるメモリ管理について
すべてのセッションが使用するmemory poolを使用する
temptable_max_ramによって制限を管理可能
MEMORY storage engineはtmp_table_sizeとmax_heap_table_sizeによって、セッションのテーブルごとに管理されるのとは異なる
これ多分結構大きな変更で、これまでセッションごとにtmp_disk_tables作るかどうかのしきい値を持っていたのが、セッション全体で管理されるようになる
なのでより非決定的な動作になりそう (その時の全体の負荷状況とかによって動作が変わる)
独自のオーバーフローメカニズムを持つ
オーバーフロー時の挙動を以下の2つのどちらかに設定できる
memory_mapped temporary files
memory mapped fileはファイルとメモリ空間のマッピングを提供するので、読み書き操作の高速化に寄与する
InnoDB on-disk internal temporary tables
TempTable storageengine使っててもmempry mappedの設定入れてなかったり、memory mapped fileのlimitサイズに達したりするとこれまでと同様にInnoDB on-disk internalが使われると
各設定値
temptable_max_ram
TempTable ストレージ エンジンが使用できるメモリの最大量
temptable_max_mmap
TempTable ストレージ エンジンが使用できる、memoryにマッピングされた一時ファイルを保持するためのストレージの最大量 (コレを超えると、InnoDB on-disk internalが使われちゃう)
これブログに書いてある内容違いそう
Introduced in MySQL 8.0.23. Defines the maximum amount of memory the TempTable storage engine is permitted to allocate from memory-mapped files before MySQL starts using InnoDB on-disk internal temporary tables. The default setting is 1073741824 bytes (1GiB).
ディスクのサイズじゃなくて、memory-mapped fileから割当られるメモリのサイズなはず
各parameterの設定内容ごとの一時テーブルの作成フローは以下のような感じ
https://gyazo.com/69752b2e08dbef75acbcc71796941157
RDSはこれらの振る舞いに影響を与えるパラメータを改定してきているので、バージョン履歴を注意して見たほうがいいよ
Aurora MySQL3.0はMySQL8.0.23 CEと互換性がある
AuroraユーザはDB cluster parameter or DB parameter groupで上記のパラメータを設定することができる
ただし、Auroraのクラスターアーキテクチャの影響で、Auroraレプリカ(reader)の一時テーブルの動作は本家MySQLとは異なる
Auroraのクラスターは以下の2タイプのストレージを持つ
a cluster volume shared by all DB instancdes
local storage attached to each DB instance
AuroraのwriterインスタンスのTempTable storageエンジンは一時テーブルオーバーフロー時の設定が以下のようになっている
Memory-mapped temporary files get created on the local storage
memory mapped fileがwriterのローカルストレージ上に作成される
The InnoDB on-disk internal temporary table is stored in the shared cluster volume
InnoDB on-disk internal (MEMORY storage engineやmemory mapped fileの制限を超えたときに使われるストレージ)はクラスタ共有のvolumeに保存される
Aurroraレプリカ(reader)はクラスタ共有のvolumeへの書き込み権限を持たない
つまり、writerと異なりクラスタ共有のvolumeへnnoDB on-disk internalの内容を書き込むことができない
ローカル上のmemory mapped filesのみが一時領域として使われる
そのためreaderの一時テーブルの作成フローは以下のような感じになる
internal_tmp_mem_storage_engine, temptable_use_mmap,などのパラメータはそもそもreaderだと変更できない
temptable_max_mmapは0に設定することができない
0に設定すると勝手にデフォルトの1GBになる
これと、temptable_max_ramはメモリサイズを規定してるので、readerの場合大きめに取りたくなるが、大きく取りすぎるとメモリ不足になったりしそうなので注意が必要そう
これのリミットに達すると"table is full”のエラーメッセージがでる
https://gyazo.com/df2f1e3b972117c25eae8712399145f9
オーバーフローしたら、InnoDB on-disk internalにフォールバックする動きではなくてエラーになる
上記の記述した通り "table is full" のエラー
チューニングについて
このあたりの話はreaderを考慮したそこまで話はしてくれてなさげ
temptable_max_mmap
各インスタンスごと(reader, writerわけて)に一時テーブルのパラメータとかは変えられるけど、フェイルオーバーごに設定が不一致になってることによる問題を防ぐために、インスタンスタイプやパラメータはwriterとreaderで揃えておくのをおすすめするよ
table structure
character setによって一時テーブルの領域の使う量は異なるよ
MySQL8.0はデフォルトでutf8mb4を使うから意識しておいてね
これについては日本語圏はあんま選択しないと思う
RDSストレージタイプ
memory mapped fileへのI/Oの場合、サイズが増えてくると顕著にIOPS性能高いインスタンスのほうがパフォーマンスでる
on-disk internal temporary table使ってるとIOPS性能によるさはあんまなくなる
モニタリング
役立つ指標
FreeableMemory for both Amazon RDS for MySQL and Aurora MySQL
FreeStorageSpace for Amazon RDS for MySQL
FreeLocalStorage, VolumeBytesUsed, and AuroraVolumeBytesLeftTotal for Aurora MySQL
Performance Insights使ってると、TempTable ストレージ エンジンによって使用されるメモリとストレージを監視することもできるよ!!