Comparison of Amazon Aurora Version 2 (MySQL 5.7) and Version 3 (MySQL 8.0) parameters and TempTable behavior


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.
 

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 Name5.7(v2) Default Value8.0(v3) Default Valuesupplementary explanation
aurora_binlog_replication_max_yield_seconds0-Replication optimization settings used prior to Aurora 2.09
aurora_parallel_queryOFF-Aurora Parallel Query
innodb_sync_spin_loops-30The number of times a thread waits for the InnoDB mutex to be released before the thread is interrupted.
read_only{TrueIfReplica}0Read-only. This parameter does not apply to users with CONNECTION_ADMIN privileges from v3. (including master users)
server_audit_logging0-Audit log availability
thread_handlingmultiple-connections-per-threadthread-pools[Cannot be changed] Aurora connection thread settings

Parameter names that existed in 5.7(v2) but disappeared in 8.0(v3)

Parameter Namesupplementary explanation
aurora_binlog_read_buffer_sizeReplication optimization settings used in Aurora MySQL versions prior to 2.09
aurora_binlog_use_large_read_bufferReplication optimization settings used in Aurora MySQL versions prior to 2.09
aurora_disable_hash_joinDisable hash join optimization in Aurora MySQL 2.09 and later
aurora_enable_repl_bin_log_filteringAutomatically reduce network bandwidth for replication messages
aurora_enable_replica_log_compressionReplica log compression function
aurora_fwd_master_idle_timeoutRename to aurora_fwd_writer_idle_timeout
aurora_fwd_master_max_connections_pctRename to aurora_fwd_writer_max_connections_pct
aurora_lab_modeLab 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_roleLoading 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_roleLoading from a text file in S3 to Aurora. v3 uses aws_default_s3_role
binlog_gtid_simple_recoveryControls how binary log files are cycled while searching for GTIDs when MySQL is restarted.
innodb_checksums[Cannot be changed]
innodb_cmp_per_index_enabledEnables 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_eventsBinary log ROW format for MySQL 5.5 and earlier
log_builtin_as_identified_by_password
log_slave_updatesrename
log_slow_slave_statementsrename
log_warnings
master-info-repositoryrename
master_verify_checksumrename
max_length_for_sort_data
max_tmp_tables
metadata_locks_cache_size
old_passwords
query_cache_limitQuery Cache Notes
query_cache_min_res_unitQuery Cache Notes
query_cache_sizeQuery Cache Notes
query_cache_typeQuery Cache Notes
query_cache_wlock_invalidateQuery Cache Notes
relay_log_info_repository
secure_auth
server_audit_query_log_limit
show_compatibility_56
slave_checkpoint_grouprename
slave_checkpoint_periodrename
slave_parallel_typerename
slave_parallel_workersrename
slave_pending_jobs_size_maxrename
slave_rows_search_algorithmsrename
slave-skip-errorsrename
slave_sql_verify_checksumrename
slave_type_conversionsrename
sync_frm
sync_master_inforename
sync_relay_log
temp-pool
tx_isolationrename

Parameter names that exist only in 8.0(v3)

Parameter Namedefaultsupplementary explanation
activate_all_roles_on_login0Option to automatically activate all assigned ROLEs when a user logs in
authentication_kerberos_caseins_cmp0
aurora_enable_staggered_replica_restart-Unused
aurora_enhanced_binlog-Extended Binary LoggingPresence or absence
aurora_fwd_writer_idle_timeout60Renaming from aurora_fwd_master_idle_timeout
aurora_fwd_writer_max_connections_pct10Rename 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_instructions3.05.0 and above: YesAdded 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_globaldb1To 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_backup1To 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_length20Maximum 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_range0It 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_loss0
internal_tmp_mem_storage_engineTempTableControls which in-memory storage engine is used for internal temporary tables. Allowed values areAnd
log_replica_updates1[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_revokes0[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-start1[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_mmap1073741824The 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.
1073741824Defines the maximum amount of memory that the TempTable storage engine can occupy before it starts persisting data to disk.
temptable_use_mmap1Deprecated 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
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.
Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL より引用
Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQLQuoted from
 
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 NameMySQL5.7MySQL8.0
innodb_autoinc_lock_mode1
character_set_serverlatin1
character_set_databaselatin1
collation_serverlatin1_swedish_ci
collation_databaselatin1_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.