Comparison of parameters and TempTable behavior between Amazon Aurora Version 2 (MySQL 5.7) and Version 3 (MySQL 8.0).
CyberAgent Group SRE Advent Calendar 2023This is the article for day 10.
This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
overviewCluster parameter groupThe default values of parameters have changed between versions 5.7 (v2) and 8.0 (v3).Parameter names that existed in 5.7(v2) but disappeared in 8.0(v3)Parameter names that exist only in 8.0(v3)Parameters to pay particular attention toThis is an example of a default value change in MySQL 8.0, although it is not included in the Aurora parameter group diff table.In conclusion
overview
This document compares the default cluster parameter groups applied in Version 2 and Version 3, summarizing any changes in default values or additions/deletions of settings.
The parameter names are sorted alphabetically.
Furthermore, we explain TempTable, a parameter whose behavior has changed since MySQL 8.0, and which requires particular attention.
These official AWS documents are very helpful when upgrading Aurora MySQL.
Cluster parameter group
The default values of parameters have changed between versions 5.7 (v2) and 8.0 (v3).
| Parameter name | 5.7(v2) Default values | 8.0(v3) Default value | supplementary explanation |
|---|---|---|---|
| aurora_binlog_replication_max_yield_seconds | 0 | - | Replication optimization settings used up to Aurora 2.09 |
| aurora_parallel_query | OFF | - | Aurora parallel query availability |
| innodb_sync_spin_loops | - | 30 | The number of times a thread waits for the InnoDB mutual exclusive lock to be released before being interrupted. |
| read_only | {TrueIfReplica} | 0 | Makes it read-only. This parameter does not apply to users with CONNECTION_ADMIN privileges starting with v3. (Including the master user) |
| server_audit_logging | 0 | - | Audit log availability |
| thread_handling | multiple-connections-per-thread | thread-pools | [Cannot be changed] Aurora connection thread settings |
Parameter names that existed in 5.7(v2) but disappeared in 8.0(v3)
| Parameter name | supplementary explanation |
|---|---|
| aurora_binlog_read_buffer_size | Replication optimization settings used in Aurora MySQL versions prior to 2.09 |
| aurora_binlog_use_large_read_buffer | Replication optimization settings used in Aurora MySQL versions prior to 2.09 |
| aurora_disable_hash_join | Disable hash join optimization in Aurora MySQL 2.09 and later. |
| aurora_enable_repl_bin_log_filtering | Automatically reduce the network bandwidth for replication messages. |
| aurora_enable_replica_log_compression | Replica log compression function |
| aurora_fwd_master_idle_timeout | Rename to aurora_fwd_writer_idle_timeout |
| aurora_fwd_master_max_connections_pct | Rename to aurora_fwd_writer_max_connections_pct |
| aurora_lab_mode | Lab Mode was only available in v2. It was removed in v3 because it was replaced by a built-in MySQL 8.0 feature. |
| aurora_load_from_s3_role | Loading text files from S3 into Aurora. v3 uses aws_default_s3_role. |
| aurora_mask_password_hashes_type | |
| aurora_max_alter_table_log_entries | |
| aurora_select_into_s3_role | Loading text files from S3 into Aurora. v3 uses aws_default_s3_role. |
| binlog_gtid_simple_recovery | Control how the binary log file is repeatedly used when GTID is looked up after MySQL is restarted. |
| innodb_checksums | [No changes allowed] |
| innodb_cmp_per_index_enabled | Enable index-specific compression statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. |
| 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 | MySQL 5.5 and earlier binary log format (ROW format) |
| log_builtin_as_identified_by_password | |
| log_slave_updates | rename |
| log_slow_slave_statements | rename |
| log_warnings | |
| master-info-repository | rename |
| master_verify_checksum | rename |
| max_length_for_sort_data | |
| max_tmp_tables | |
| metadata_locks_cache_size | |
| old_passwords | |
| query_cache_limit | Query cache related |
| query_cache_min_res_unit | Query cache related |
| query_cache_size | Query cache related |
| query_cache_type | Query cache related |
| query_cache_wlock_invalidate | Query cache related |
| relay_log_info_repository | |
| secure_auth | |
| server_audit_query_log_limit | |
| show_compatibility_56 | |
| slave_checkpoint_group | rename |
| slave_checkpoint_period | rename |
| slave_parallel_type | rename |
| slave_parallel_workers | rename |
| slave_pending_jobs_size_max | rename |
| slave_rows_search_algorithms | rename |
| slave-skip-errors | rename |
| slave_sql_verify_checksum | rename |
| slave_type_conversions | rename |
| sync_frm | |
| sync_master_info | rename |
| sync_relay_log | |
| temp-pool | |
| tx_isolation | rename |
Parameter names that exist only in 8.0(v3)
| Parameter name | default | supplementary explanation |
|---|---|---|
| activate_all_roles_on_login | 0 | An option to automatically enable all roles assigned to a user upon login. |
| authentication_kerberos_caseins_cmp | 0 | |
| aurora_enable_staggered_replica_restart | - | Unused |
| aurora_enhanced_binlog | - | Extended binary logpresence or absence |
| aurora_fwd_writer_idle_timeout | 60 | Rename from aurora_fwd_master_idle_timeout |
| aurora_fwd_writer_max_connections_pct | 10 | Renamed from aurora_fwd_master_max_connections_pct |
| aurora_jemalloc_background_thread | - | Options to control the behavior of Jemalloc (internal memory allocator), added in Aurora MySQL 3.05.0. |
| aurora_jemalloc_dirty_decay_ms | - | Options to control the behavior of Jemalloc (internal memory allocator), added in Aurora MySQL 3.05.0. |
| aurora_jemalloc_tcache_enabled | - | Options to control the behavior of Jemalloc (internal memory allocator), added in Aurora MySQL 3.05.0. |
| aurora_replica_read_consistency | - | When a client performs a write operation on a read replica, the reader instance forwards the write operation to the writer instance. |
| aurora_tmptable_enable_per_table_limit | - | New temporary table operation control |
| aurora_use_vector_instructions | 3.05.0 and above is Yes | Added in Aurora MySQL 3.05.0. Aurora MySQL uses optimized vector processing instructions to improve performance for I/O-intensive workloads. |
| binlog-do-db | - | Duplicate changes to the specified binary log table. |
| binlog_group_commit_sync_delay | - | Multithreaded replication relationship |
| binlog_group_commit_sync_no_delay_count | - | Multithreaded replication relationship |
| binlog-ignore-db | - | Do not duplicate changes to the specified binary log table. |
| binlog_replication_globaldb | 1 | To enable extended binary logging, set this parameter to 0. |
| binlog_row_metadata | - | Amount of table metadata added to the binary log |
| binlog_row_value_options | - | Setting it to PARTIAL_JSON allows you to use a space-efficient binary log format for updates that modify only small parts of a JSON document. |
| binlog_transaction_compression | - | Enables compression of transactions written to the binary log file on the server. OFF is the default setting. |
| binlog_transaction_compression_level_zstd | - | Transaction compression level |
| binlog_transaction_dependency_tracking | - | Specify the source of the dependency information that the source logs in the binary log, which helps multithreaded replication determine which transactions can be executed in parallel. |
| binlog_backup | 1 | To enable extended binary logging, set this parameter to 0. |
| cte_max_recursion_depth | - | Enforces a limit on the number of recursion levels for CTEs (Common Table Expressions). Default is 1000. |
| default_authentication_plugin | - | [Cannot be changed] Authentication plugin selection |
| explain_format | - | Added in Aurora MySQL 3.05.0.EXPLAINThe default output format used by |
| generated_random_password_length | 20 | Maximum number of characters for a random password |
| histogram_generation_max_mem_size | - | Maximum amount of memory available for generating histogram statistics |
| information_schema_stats_expiry | - | Defines the period before cached statistics expire. The default is 86400 seconds (24 hours). |
| innodb_aurora_max_partitions_for_range | 0 | This can improve the performance of calculating the number of rows in a partitioned table. |
| innodb_spin_wait_pause_multiplier | - | The multiplier used to determine the number of PAUSE instructions in a spin-wait loop that occurs when a thread is waiting to acquire a mutex or rw-lock. |
| innodb_trx_commit_allow_data_loss | 0 | |
| internal_tmp_mem_storage_engine | TempTable | This controls which in-memory storage engine is used for the internal temporary table. Allowed values are:And |
| log_replica_updates | 1 | [Cannot be changed] Rename from log_slave_updates |
| log_slow_extra | - | Output additional fields to the slow log file. |
| log_slow_replica_statements | - | Rename from log_slow_slave_statements |
| mandatory_roles | - | You can specify a role as required. The server will treat the required role as if it were granted to all users. |
| partial_revokes | 0 | [Cannot be changed] Controls whether permission restrictions can be set for an account. |
| performance_schema_max_digest_sample_age | - | Affects statement sampling in the `events_statements_summary_by_digest` table. Default is 60. |
| regexp_stack_limit | - | and the maximum available memory (bytes) of the internal stack used for regular expression matching operations performed by similar functions. |
| regexp_time_limit | - | and time limits for regular expression matching operations performed by similar functions |
| relay-log-space-limit | - | Sets the upper limit in bytes on the total size of all relay logs on the replica. A value of 0 means "no limit". The default is 0. |
| replica_checkpoint_group | - | Renamed from slave_checkpoint_group |
| replica_checkpoint_period | - | Rename from slave_checkpoint_period |
| replica_parallel_type | - | Rename from slave_parallel_type |
| replica_parallel_workers | - | Renamed from slave_parallel_workers 、that is the default |
| replica_pending_jobs_size_max | - | Rename from slave_pending_jobs_size_max |
| replica_preserve_commit_order | - | Rename from slave_preserve_commit_order |
| replica_sql_verify_checksum | - | Rename from slave_sql_verify_checksum |
| replica_transaction_retries | - | Rename from slave_transaction_retries |
| replica_type_conversions | - | Rename from slave_type_conversions |
| replicate-do-db | - | Replication filter |
| replicate-do-table | - | Replication filter |
| replicate-ignore-db | - | Replication filter |
| replicate-ignore-table | - | Replication filter |
| replicate-wild-do-table | - | Replication filter |
| replicate-wild-ignore-table | - | Replication filter |
| rpl_read_size | - | Control the minimum amount of data read from binary log files and relay log files in bytes. |
| schema_definition_cache | - | Limitation on the number of schema definition objects (both used and unused) that can be held in the dictionary object cache. |
| show_gipk_in_create_table_and_information_schema | - | Whether to display the generated invisible primary key in the output of the SHOW statement and in the information schema table. Default: ON |
| skip-replica-start | 1 | [No changes allowed] |
| source_verify_checksum | - | Rename from master_verify_checksum |
| sql_generate_invisible_primary_key | - | A feature that adds a hidden column to a table created without explicitly specifying a primary key. Default setting: OFF. |
| ssl_session_cache_mode | - | Controls whether to enable server-side session caching in memory and server-side session ticket generation. Default: ON |
| ssl_session_cache_timeout | - | When establishing a new encrypted connection to the server, if previous session data is available,Set the period during which previous sessions are allowed to be reused. |
| stored_program_definition_cache | - | The dictionary object cache has an upper limit on the number of saved program-defined objects that can be held. |
| tablespace_definition_cache | - | Defines the upper limit on the number of tablespace definition objects that can be held in the dictionary object cache. |
| temptable_max_mmap | 1073741824 | The maximum amount of memory that the TempTable storage engine is allowed to allocate from memory-mapped temporary files before it begins storing data to InnoDB internal temporary tables on disk. |
| 1073741824 | TempTable defines the maximum amount of memory the storage engine can occupy before it begins saving data to disk. | |
| temptable_use_mmap | 1 | Deprecated parameters |
| tls_ciphersuites | - | For encrypted connections using TLSv1.3, the cipher suites allowed by the server. |
| transaction_isolation | - | Transaction isolation level. Replaces tx_isolation. |
| windowing_use_high_precision | - | Calculate window operations without sacrificing accuracy (default: ON) |
| xa_detach_on_prepare | - | All XA transactions are disconnected from the connection (session). Default setting: ON |
Parameters to pay particular attention to
- aurora_tmptable_enable_per_table_limit
- Controls the size limit per table for temporary tables in internal memory. Default: OFF
- When you turn it ONWhen TempTable reaches its size limit, writers convert the in-memory temporary table to InnoDB, while readers will encounter an error.
- temptable_max_ram
- Maximum value of the common memory pool for temporary tables
16MB
- temptable_max_mmap
- Maximum storage limit for temporary tables
1GB- Pay attention to the local storage capacity per instance size.
- https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Performance.html#AuroraMySQL.Managing.TempStorage
- An example of instance size and local storage size
- t3.medium : 32GiB
- db.r6g.large : 32GiB
- db.r6g.xlarge : 80GiB
- db.r6g.2xlarge : 160GiB
These parameters require an understanding of how the new temporary table will behave.
For more details, please refer to the official article linked above, but I will give a brief explanation.
Starting with Aurora MySQL Version 3 (MySQL 8.0), the default storage engine for temporary tables has been changed to the TempTable storage engine.
TempTable creates a temporary table in memory, but if the size exceeds the set value,Save the overflowed data to storage.。
First, it writes to local storage, and if that also overflows, it writes to the shared cluster volume.
but,Aurora's leader instance cannot write to shared cluster volumes, so queries will fail once local storage is full.This is the result.
A relevant example is the case study of MIXI Corporation.
This is an example of a default value change in MySQL 8.0, although it is not included in the Aurora parameter group diff table.
| Parameter name | 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 | |
In conclusion
We want to thoroughly investigate the differences in each parameter before proceeding with the migration.
SRG is looking for new team members.
If you are interested, please contact us here.