Comparison of Amazon Aurora Version 2 (MySQL 5.7) and Version 3 (MySQL 8.0) parameters and TempTable behavior
CyberAgent Group SRE Advent Calendar 2023This is the 10th article.
This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
overviewCluster Parameter GroupParameter default values changed between 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 special attention toAn example of a default value that was changed in MySQL 8.0 but is not included in the Aurora parameter group difference tableConclusion
overview
We compared the default cluster parameter groups applied in Version 2 and Version 3 and summarized the changes in default values and the addition or deletion of configuration items.
Each parameter name is sorted alphabetically.
We also explain TempTable, a parameter that requires special attention since its behavior changed in MySQL 8.0.
These official AWS documents are very helpful for upgrading Aurora MySQL.
Cluster Parameter Group
Parameter default values changed between 5.7(v2) and 8.0(v3)
Parameter Name | 5.7(v2) Default Value | 8.0(v3) Default Value | supplementary explanation |
---|---|---|---|
aurora_binlog_replication_max_yield_seconds | 0 | - | Replication optimization settings used prior to Aurora 2.09 |
aurora_parallel_query | OFF | - | Aurora Parallel Query |
innodb_sync_spin_loops | - | 30 | The number of times a thread waits for the InnoDB mutex to be released before the thread is interrupted. |
read_only | {TrueIfReplica} | 0 | Read-only. This parameter does not apply to users with CONNECTION_ADMIN privileges from v3. (including master users) |
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 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 the original MySQL8.0 function. |
aurora_load_from_s3_role | Loading from a text file in S3 to Aurora. v3 uses aws_default_s3_role |
aurora_mask_password_hashes_type | |
aurora_max_alter_table_log_entries | |
aurora_select_into_s3_role | Loading from a text file in S3 to Aurora. v3 uses aws_default_s3_role |
binlog_gtid_simple_recovery | Controls how binary log files are cycled while searching for GTIDs when MySQL is restarted. |
innodb_checksums | [Cannot be changed] |
innodb_cmp_per_index_enabled | Enables compression-related statistics per index 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 | Binary log ROW format for MySQL 5.5 and earlier |
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 Notes |
query_cache_min_res_unit | Query Cache Notes |
query_cache_size | Query Cache Notes |
query_cache_type | Query Cache Notes |
query_cache_wlock_invalidate | Query Cache Notes |
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 | Option to automatically activate all assigned ROLEs when a user logs in |
authentication_kerberos_caseins_cmp | 0 | |
aurora_enable_staggered_replica_restart | - | Unused |
aurora_enhanced_binlog | - | Extended Binary LoggingPresence or absence |
aurora_fwd_writer_idle_timeout | 60 | Renaming from aurora_fwd_master_idle_timeout |
aurora_fwd_writer_max_connections_pct | 10 | Rename from aurora_fwd_master_max_connections_pct |
aurora_jemalloc_background_thread | - | Options added in Aurora MySQL 3.05.0 to control the behavior of Jemalloc (internal memory allocator) |
aurora_jemalloc_dirty_decay_ms | - | Options added in Aurora MySQL 3.05.0 to control the behavior of Jemalloc (internal memory allocator) |
aurora_jemalloc_tcache_enabled | - | Options added in Aurora MySQL 3.05.0 to control the behavior of Jemalloc (internal memory allocator) |
aurora_replica_read_consistency | - | When a client issues a write operation to a read replica, the reader instance forwards the write operation to the writer instance. |
aurora_tmptable_enable_per_table_limit | - | New Temporary Table Behavior Controls |
aurora_use_vector_instructions | 3.05.0 and above: 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 | - | Replicates changes to a specified binary log table |
binlog_group_commit_sync_delay | - | Multi-threaded Replication Relationships |
binlog_group_commit_sync_no_delay_count | - | Multi-threaded Replication Relationships |
binlog-ignore-db | - | Do not replicate changes to the specified binary log table |
binlog_replication_globaldb | 1 | To turn on extended binary logging, set this parameter to 0. |
binlog_row_metadata | - | The amount of table metadata added to the binary log |
binlog_row_value_options | - | Set to PARTIAL_JSON to use a space-efficient binary log format for updates that modify only a small portion of a JSON document. |
binlog_transaction_compression | - | Enables compression of transactions written to the binary log files on the server. OFF is the default. |
binlog_transaction_compression_level_zstd | - | Transaction Compression Level |
binlog_transaction_dependency_tracking | - | Specifies the source of dependency information that the source records in the binary log to help multi-threaded replicas determine which transactions can be executed in parallel. |
binlog_backup | 1 | To turn on extended binary logging, set this parameter to 0. |
cte_max_recursion_depth | - | Enforces a limit on the number of recursion levels in a CTE (Common Table Expression), 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 length of random password |
histogram_generation_max_mem_size | - | The maximum amount of memory that can be used to generate histogram statistics |
information_schema_stats_expiry | - | Defines the time period after which cached statistics expire, default is 86400 seconds (24 hours) |
innodb_aurora_max_partitions_for_range | 0 | It can improve performance for calculating row counts in partitioned tables. |
innodb_spin_wait_pause_multiplier | - | Multiplier value used to determine the number of PAUSE instructions in a spin-wait loop that occurs when a thread waits to acquire a mutex or rw-lock. |
innodb_trx_commit_allow_data_loss | 0 | |
internal_tmp_mem_storage_engine | TempTable | Controls which in-memory storage engine is used for internal temporary tables. Allowed values areAnd |
log_replica_updates | 1 | [Cannot be changed] Rename from log_slave_updates |
log_slow_extra | - | Add additional fields to slow logs in FILE output |
log_slow_replica_statements | - | Renamed from log_slow_slave_statements |
mandatory_roles | - | You can specify a role as mandatory; the server will treat mandatory roles as granted to all users. |
partial_revokes | 0 | [Cannot be changed] Controls whether permission restrictions can be set for the account |
performance_schema_max_digest_sample_age | - | Affects statement sampling in the events_statements_summary_by_digest table. Default is 60. |
regexp_stack_limit | - | The maximum available memory (in bytes) for the internal stack used by regular expression matching operations performed by and similar functions. |
regexp_time_limit | - | and similar functions |
relay-log-space-limit | - | Sets an upper limit on the total size of all relay logs on a replica, in bytes. A value of 0 means "no limit". The default is 0. |
replica_checkpoint_group | - | Rename from slave_checkpoint_group |
replica_checkpoint_period | - | Rename from slave_checkpoint_period |
replica_parallel_type | - | Rename from slave_parallel_type |
replica_parallel_workers | - | Rename from slave_parallel_workers 、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 | - | Renamed from slave_sql_verify_checksum |
replica_transaction_retries | - | Renamed from slave_transaction_retries |
replica_type_conversions | - | Rename from slave_type_conversions |
replicate-do-db | - | Replication Filters |
replicate-do-table | - | Replication Filters |
replicate-ignore-db | - | Replication Filters |
replicate-ignore-table | - | Replication Filters |
replicate-wild-do-table | - | Replication Filters |
replicate-wild-ignore-table | - | Replication Filters |
rpl_read_size | - | Controls the minimum amount of data, in bytes, that is read from the binary log and relay log files. |
schema_definition_cache | - | Limit on the number of schema definition objects (both dirty and unused) that can be held in the dictionary object cache |
show_gipk_in_create_table_and_information_schema | - | Whether or not generated invisible primary keys should be displayed in the output of SHOW statements and in information schema tables. Default is ON. |
skip-replica-start | 1 | [Cannot be changed] |
source_verify_checksum | - | Rename from master_verify_checksum |
sql_generate_invisible_primary_key | - | Ability to add hidden columns to tables created without explicitly specifying a primary key. Default is OFF. |
ssl_session_cache_mode | - | Controls whether server-side in-memory session caching and server-generated session tickets are enabled. Default: ON |
ssl_session_cache_timeout | - | When establishing a new encrypted connection to a server if previous session data is availableSets the period during which a previous session is allowed to be reused. |
stored_program_definition_cache | - | Defines an upper limit on the number of saved program-defined objects that can be held in the dictionary object cache. |
tablespace_definition_cache | - | Defines the upper limit for 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 a memory-mapped temporary file before it starts storing data to InnoDB internal temporary tables on disk. |
1073741824 | Defines the maximum amount of memory that the TempTable storage engine can occupy before it starts persisting data to disk. | |
temptable_use_mmap | 1 | Deprecated parameters |
tls_ciphersuites | - | Cipher suites allowed by the server for encrypted connections using TLSv1.3 |
transaction_isolation | - | Transaction isolation level, replacing tx_isolation |
windowing_use_high_precision | - | Calculate window operations without losing precision. Default is ON. |
xa_detach_on_prepare | - | All XA transactions are detached from the connection (session) Default: ON |
Parameters to pay special attention to
- aurora_tmptable_enable_per_table_limit
- Controls per-table size limit for internal in-memory temporary tables. Default is OFF.
- When turned ONWhen the TempTable reaches this size, the writer will convert the in-memory temporary table to InnoDB, and the reader will get an error.
- temptable_max_ram
- Maximum common memory pool size for temporary tables
16MB
- temptable_max_mmap
- Maximum storage for temporary tables
1GB
- Pay attention to the local storage capacity for each 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 understanding of the new temporary table behavior.
For more details, please refer to the official article above, but I will give a brief explanation.
Starting with Aurora MySQL Version 3 (MySQL 8.0), the default storage engine for temporary tables is 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.。
It writes to the local storage first, and if that overflows it writes to the shared cluster volume.
but,Aurora reader instances cannot write to the shared cluster volume, so queries will fail when their local storage is full.It will be.

A related case study on this is that of MIXI, Inc.
An example of a default value that was changed in MySQL 8.0 but is not included in the Aurora parameter group difference 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 | |
Conclusion
I would like to thoroughly investigate the differences in each parameter before making the transition.
SRG is looking for people to work with us. If you are interested, please contact us here.