Amazon Aurora Version2(MySQL5.7)とVersion3(MySQL8.0)のパラメータの比較とTempTableの挙動について
CyberAgent Group SRE Advent Calendar 2023の10日目の記事です。
技術本部 サービスリライアビリティグループ(SRG)の鬼海(@fat47)です。
#SRG(Service Reliability Group)は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
なにかの役に立てば幸いです。
概要クラスタパラメータグループ5.7(v2)と8.0(v3)でパラメータのデフォルト値が変更になったもの5.7(v2) には存在したが 8.0(v3)で消えたパラメータ名8.0(v3) にだけ存在するパラメータ名特に気をつけたいパラメータAuroraパラメータグループの差分表にはないがMySQL8.0でデフォルト値が変更された一例終わりに
概要
Version2とVersion3で適用されるデフォルトのクラスタパラメータグループを比較し、デフォルト値が変わったものや設定項目が追加・削除されたものをまとめました。
それぞれのパラメータ名はアルファベット順にソートしてあります。
また、特に気をつけるべきパラメータとしてMySQL8.0から挙動の変わったTempTableについて解説しています。
Aurora MySQLのアップグレードについてはAWS公式のこれらのドキュメントが非常に参考になります。
クラスタパラメータグループ
5.7(v2)と8.0(v3)でパラメータのデフォルト値が変更になったもの
パラメータ名 | 5.7(v2)デフォルト値 | 8.0(v3)デフォルト値 | 補足説明 |
---|---|---|---|
aurora_binlog_replication_max_yield_seconds | 0 | - | Aurora 2.09未満まで利用されていたレプリケーション最適化設定 |
aurora_parallel_query | OFF | - | Auroraパラレルクエリ有無 |
innodb_sync_spin_loops | - | 30 | スレッドが中断される前に、InnoDB相互排他ロックが開放されるまでスレッドが待機する回数 |
read_only | {TrueIfReplica} | 0 | 読み取りのみにする。v3からCONNECTION_ADMIN 権限を持つユーザーにこのパラメータは適用されない。(マスターユーザー含む) |
server_audit_logging | 0 | - | 監査ログ有無 |
thread_handling | multiple-connections-per-thread | thread-pools | 【変更不可】Auroraの接続スレッドの設定 |
5.7(v2) には存在したが 8.0(v3)で消えたパラメータ名
パラメータ名 | 補足説明 |
---|---|
aurora_binlog_read_buffer_size | Aurora MySQL 2.09未満まで利用されていたレプリケーション最適化設定 |
aurora_binlog_use_large_read_buffer | Aurora MySQL 2.09未満まで利用されていたレプリケーション最適化設定 |
aurora_disable_hash_join | Aurora MySQL 2.09 以降でハッシュ結合最適化を無効設定 |
aurora_enable_repl_bin_log_filtering | レプリケーションメッセージのネットワーク帯域幅を自動的に縮小 |
aurora_enable_replica_log_compression | レプリカログ圧縮機能 |
aurora_fwd_master_idle_timeout | aurora_fwd_writer_idle_timeoutにリネーム |
aurora_fwd_master_max_connections_pct | aurora_fwd_writer_max_connections_pctにリネーム |
aurora_lab_mode | v2にだけあったラボモード。v3ではMySQL8.0純正の機能に置き換わるので削除された |
aurora_load_from_s3_role | S3のテキストファイルからAuroraへのロード。v3ではaws_default_s3_roleを使用 |
aurora_mask_password_hashes_type | |
aurora_max_alter_table_log_entries | |
aurora_select_into_s3_role | S3のテキストファイルからAuroraへのロード。v3ではaws_default_s3_roleを使用 |
binlog_gtid_simple_recovery | MySQLが再起動された時に、GTIDを検索する最中にどのようにバイナリログファイルが繰返し利用されるかを制御する |
innodb_checksums | 【変更不可】 |
innodb_cmp_per_index_enabled | INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX テーブルでインデックスごとの圧縮関連の統計を有効 |
innodb_compression_failure_threshold_pct | |
innodb_compression_level | |
innodb_compression_pad_pct_max | |
innodb_file_format | |
innodb_large_prefix | |
innodb_random_read_ahead | |
innodb_read_ahead_threshold | |
innodb_support_xa | |
internal_tmp_disk_storage_engine | |
log_bin_use_v1_row_events | MySQL5.5以前のバイナリログROW形式 |
log_builtin_as_identified_by_password | |
log_slave_updates | リネーム |
log_slow_slave_statements | リネーム |
log_warnings | |
master-info-repository | リネーム |
master_verify_checksum | リネーム |
max_length_for_sort_data | |
max_tmp_tables | |
metadata_locks_cache_size | |
old_passwords | |
query_cache_limit | クエリキャッシュ関連 |
query_cache_min_res_unit | クエリキャッシュ関連 |
query_cache_size | クエリキャッシュ関連 |
query_cache_type | クエリキャッシュ関連 |
query_cache_wlock_invalidate | クエリキャッシュ関連 |
relay_log_info_repository | |
secure_auth | |
server_audit_query_log_limit | |
show_compatibility_56 | |
slave_checkpoint_group | リネーム |
slave_checkpoint_period | リネーム |
slave_parallel_type | リネーム |
slave_parallel_workers | リネーム |
slave_pending_jobs_size_max | リネーム |
slave_rows_search_algorithms | リネーム |
slave-skip-errors | リネーム |
slave_sql_verify_checksum | リネーム |
slave_type_conversions | リネーム |
sync_frm | |
sync_master_info | リネーム |
sync_relay_log | |
temp-pool | |
tx_isolation | リネーム |
8.0(v3) にだけ存在するパラメータ名
パラメータ名 | default | 補足説明 |
---|---|---|
activate_all_roles_on_login | 0 | ユーザーログイン時に付与されているROLEをすべて自動で有効にするオプション |
authentication_kerberos_caseins_cmp | 0 | |
aurora_enable_staggered_replica_restart | - | 使用されていない |
aurora_enhanced_binlog | - | 拡張バイナリログの有無 |
aurora_fwd_writer_idle_timeout | 60 | aurora_fwd_master_idle_timeoutからのリネーム |
aurora_fwd_writer_max_connections_pct | 10 | aurora_fwd_master_max_connections_pctからのリネーム |
aurora_jemalloc_background_thread | - | Aurora MySQL3.05.0で追加されたJemalloc(内部メモリアロケータ)の動作を制御するオプション |
aurora_jemalloc_dirty_decay_ms | - | Aurora MySQL3.05.0で追加されたJemalloc(内部メモリアロケータ)の動作を制御するオプション |
aurora_jemalloc_tcache_enabled | - | Aurora MySQL3.05.0で追加されたJemalloc(内部メモリアロケータ)の動作を制御するオプション |
aurora_replica_read_consistency | - | クライアントからリードレプリカに対して書き込み操作を行った場合に、リーダーインスタンスがライターインスタンスに書き込み操作を転送する |
aurora_tmptable_enable_per_table_limit | - | 新しい一時テーブルの動作制御 |
aurora_use_vector_instructions | 3.05.0以上はYes | Aurora MySQL3.05.0で追加された。Aurora MySQL は最適化されたベクトル処理命令を使用して、I/O 負荷の高いワークロードのパフォーマンスを向上 |
binlog-do-db | - | 指定されたバイナリログテーブルに変更を複製 |
binlog_group_commit_sync_delay | - | マルチスレッドレプリケーション関係 |
binlog_group_commit_sync_no_delay_count | - | マルチスレッドレプリケーション関係 |
binlog-ignore-db | - | 指定されたバイナリログテーブルに変更を複製しない |
binlog_replication_globaldb | 1 | 拡張バイナリログをオンにするには、このパラメータを0にする |
binlog_row_metadata | - | バイナリログに追加されるテーブルメタデータの量 |
binlog_row_value_options | - | PARTIAL_JSON に設定すると、JSON ドキュメントの小さい部分のみを変更する更新に領域効率のよいバイナリログ形式を使用できるようになる |
binlog_transaction_compression | - | サーバー上のバイナリログファイルに書き込まれるトランザクションの圧縮を有効にします。 OFF がデフォルト |
binlog_transaction_compression_level_zstd | - | トランザクションの圧縮レベル |
binlog_transaction_dependency_tracking | - | マルチスレッドレプリが並列で実行できるトランザクションを判断するのに役立つように、バイナリログにソースが記録する依存性情報のソースを指定 |
binlog_backup | 1 | 拡張バイナリログをオンにするには、このパラメータを0にする |
cte_max_recursion_depth | - | CTE (共通テーブル式)の再帰レベル数に制限を強制。デフォルト1000 |
default_authentication_plugin | - | 【変更不可】認証プラグインの選択 |
explain_format | - | Aurora MySQL3.05.0で追加された。EXPLAIN が使用するデフォルトの出力形式 |
generated_random_password_length | 20 | ランダムパスワードの最大文字数 |
histogram_generation_max_mem_size | - | ヒストグラム統計の生成に使用できるメモリの最大量 |
information_schema_stats_expiry | - | キャッシュされた統計が期限切れになるまでの期間を定義します。デフォルトは 86400 秒 (24 時間) |
innodb_aurora_max_partitions_for_range | 0 | パーティション分割テーブルの行数計算のパフォーマンスを向上させることができる。 |
innodb_spin_wait_pause_multiplier | - | スレッドが mutex または rw-lock の取得を待機するときに発生するスピン待機ループ内の PAUSE 命令の数を決定するために使用される乗数値 |
innodb_trx_commit_allow_data_loss | 0 | |
internal_tmp_mem_storage_engine | TempTable | 内部一時テーブルにどのインメモリ ストレージ エンジンを使用するかを制御します。許可される値は とです |
log_replica_updates | 1 | 【変更不可】log_slave_updatesからリネーム |
log_slow_extra | - | スローログに追加のフィールドをFILE出力 |
log_slow_replica_statements | - | log_slow_slave_statementsからリネーム |
mandatory_roles | - | ロールを必須として指定できます。 サーバーは必須ロールをすべてのユーザーに付与されたものとして処理する |
partial_revokes | 0 | 【変更不可】アカウントに権限制限を設定できるかどうかを制御 |
performance_schema_max_digest_sample_age | - | events_statements_summary_by_digest テーブルのステートメントサンプリングに影響。デフォ60 |
regexp_stack_limit | - | および同様の関数によって実行される正規表現照合操作に使用される内部スタックの最大使用可能メモリー (バイト) |
regexp_time_limit | - | および同様の関数によって実行される正規表現照合操作の時間制限 |
relay-log-space-limit | - | レプリカ上のすべてのリレーログの合計サイズの上限をバイト単位で設定します。 値 0 は 「制限なし」。デフォルトは0 |
replica_checkpoint_group | - | slave_checkpoint_groupからリネーム |
replica_checkpoint_period | - | slave_checkpoint_periodからリネーム |
replica_parallel_type | - | slave_parallel_typeからリネーム |
replica_parallel_workers | - | slave_parallel_workersからリネーム 、 がデフォルト |
replica_pending_jobs_size_max | - | slave_pending_jobs_size_maxからリネーム |
replica_preserve_commit_order | - | slave_preserve_commit_orderからリネーム |
replica_sql_verify_checksum | - | slave_sql_verify_checksumからリネーム |
replica_transaction_retries | - | slave_transaction_retriesからリネーム |
replica_type_conversions | - | slave_type_conversionsからリネーム |
replicate-do-db | - | レプリケーションフィルター |
replicate-do-table | - | レプリケーションフィルター |
replicate-ignore-db | - | レプリケーションフィルター |
replicate-ignore-table | - | レプリケーションフィルター |
replicate-wild-do-table | - | レプリケーションフィルター |
replicate-wild-ignore-table | - | レプリケーションフィルター |
rpl_read_size | - | バイナリ ログ ファイルおよびリレー ログ ファイルから読み取られるデータの最小量をバイト単位で制御 |
schema_definition_cache | - | ディクショナリオブジェクトキャッシュに保持できるスキーマ定義オブジェクト (使用済と未使用の両方) の数の制限 |
show_gipk_in_create_table_and_information_schema | - | 生成された不可視の主キーをSHOW文の出力やインフォメーション・スキーマ・テーブルで表示するかどうか。デフォON |
skip-replica-start | 1 | 【変更不可】 |
source_verify_checksum | - | master_verify_checksumからリネーム |
sql_generate_invisible_primary_key | - | 明示的に主キーを指定せずに作成したテーブルに対して、非表示のカラムを追加する機能。デフォルトOFF |
ssl_session_cache_mode | - | サーバー側のメモリ内のセッション キャッシュとサーバーによるセッション チケットの生成を有効にするかどうかを制御。デフォルトON |
ssl_session_cache_timeout | - | 以前のセッション データが利用可能な場合に、サーバーへの新しい暗号化された接続を確立するときに 以前のセッションの再利用が許可される期間を設定 |
stored_program_definition_cache | - | ディクショナリ・オブジェクト・キャッシュに保持できる、保存されたプログラム定義オブジェクトの数の上限を定 |
tablespace_definition_cache | - | ディクショナリ・オブジェクト・キャッシュに保持できる表空間定義オブジェクトの数の上限を定義 |
temptable_max_mmap | 1073741824 | TempTable ストレージ エンジンがディスク上の InnoDB 内部テンポラリ テーブルへのデータ格納を開始する前に、メモリ マップされたテンポラリ ファイルから割り当てることを許可されるメモリの最大量 |
1073741824 | TempTable ストレージ・エンジンがディスクへのデータ保存を開始するまでに占有できるメモリの最大量を定義 | |
temptable_use_mmap | 1 | 非推奨パラメータ |
tls_ciphersuites | - | TLSv1.3を使用する暗号化接続で、サーバが許可する暗号スイート |
transaction_isolation | - | トランザクション分離レベル。tx_isolationから置き換え |
windowing_use_high_precision | - | 精度を落とさずにウィンドウ操作を計算 デフォルトON |
xa_detach_on_prepare | - | すべてのXAトランザクションが接続(セッション)から切り離される(切断される)デフォルトON |
特に気をつけたいパラメータ
- aurora_tmptable_enable_per_table_limit
- 内部メモリ内一時テーブルのテーブルごとのサイズ制限の制御。デフォルトOFF
- ONにするとのサイズにTempTableが達したらライターの場合はメモリ内一時テーブルをInnoDBに変換し、リーダーではエラーになる。
- temptable_max_ram
- 一時テーブルの共通メモリプールの最大値
- デフォルトは16 GB以上のメモリを持つ DB インスタンスは 、 16GB未満の DBインスタンスでは
- セッションレベルのメモリ消費に影響
- temptable_max_mmap
- 一時テーブルのストレージ保存の最大値
- デフォルトはインスタンスのサイズに関係なく
- インスタンスサイズごとのローカルストレージの容量に注意
- https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Performance.html#AuroraMySQL.Managing.TempStorage
- インスタンスサイズとローカルストレージサイズの一例
- t3.medium : 32GiB
- db.r6g.large : 32GiB
- db.r6g.xlarge : 80GiB
- db.r6g.2xlarge : 160GiB
これらのパラメータでは新しい一時テーブルの挙動を理解しておく必要があります。
詳細は上記の公式記事を参照いただいたほうがよいですが、簡単に説明をします。
Aurora MySQL Version3(MySQL8.0)から一時テーブルのデフォルトがTempTableストレージエンジンになりました。
TempTableは一時テーブルをメモリー上に作成しますが、サイズが設定値を超えた場合、オーバーフローしたデータをストレージに保存します。
まずローカルストレージに書き込み、そこも溢れたら共有クラスタボリュームに書き込みます。
しかし、Auroraのリーダーインスタンスは共有クラスター・ボリュームに書き込みができないため、ローカルストレージが溢れた時点でクエリがエラーとなります。
これらに関連する資料として株式会社MIXI様の事例があります。
Auroraパラメータグループの差分表にはないがMySQL8.0でデフォルト値が変更された一例
パラメータ名 | MySQL5.7 | MySQL8.0 |
---|---|---|
innodb_autoinc_lock_mode | 1 | |
character_set_server | latin1 | |
character_set_database | latin1 | |
collation_server | latin1_swedish_ci | |
collation_database | latin1_swedish_ci | |
終わりに
各パラメータの差分をしっかり調査して移行に望みたいですね。
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。