MySQL 8.4 Features introduce significant improvements in database performance, security, replication, and administration compared to MySQL 8.0. The latest release enhances InnoDB configuration, authentication, replication, optimizer performance, monitoring, and database management tools. This guide explores the most important MySQL 8.4 Features that every DBA and developer should understand before upgrading production environments.
MySQL 8.0, a reliable base in database management, has been continuously updated with new functionalities and enhancements. This blog explores the performance, security, user-friendly tools, and New features in MySQL 8.4, providing insights for both experienced database administrators and developers to maximize the capabilities of this transformative database management tool.
Following are the new features of Mysql 8.4 from Mysql 8.0.
MySQL 8.4 Features for InnoDB:
The standard value of an InnoDB system variable has changed. MySQL 8.4.0 modified the default settings for a number of server system variables associated with the InnoDB storage engine, as indicated in the table below:
MySQL 8.4 Authentication Features:
MySQL has added a new feature called “start_server” which allows users to start a server without the need for the deprecated mysql_native_password authentication plugin.
MySQL 8.4 Performance Features:
MySQL 8.2 Performance Improvements
- Introduces new hash table optimization for EXCEPT and INTERSECT statements.
- Controlled by the hash_set_operations optimizer switch.
- Disables optimization to revert to old temporary table optimization.
- Adjusts memory allocation for optimization by changing set_operations_buffer_size server system variable.
- Raising buffer size improves execution speeds of some statements using these operations.
MySQL 8.4 Monitoring Features:
MySQL Versions and PROCESSLIST Table Usage
- Information_SCHEMA.PROCESSLIST table deprecated in MySQL 8.0.35 and 8.2.0.
- Two system status variables: Deprecated_use_i_s_processlist_count and Deprecated_use_i_s_processlist_last_timestamp.
- Deprecated_use_i_s_processlist_count records query references since server start.
- Deprecated_use_i_s_processlist_last_timestamp records latest table consultation.
The default value of an InnoDB system variable is changed: MySQL 8.4.0 introduced changes to default values for server system variables related to the InnoDB storage engine, as illustrated in the table.
| InnoDB System Variable Name | Previous Default Value (MySQL 8.0) | New Default Value (MySQL 8.4) |
| innodb_buffer_pool_in_core_file | ON | OFF if MADV_DONTDUMP is supported, otherwise ON |
| innodb_change_buffering | all | none |
| innodb-dedicated-server | OFF | If ON[a], the value of innodb_flush_method is not altered as in MySQL 8.0, however the computation of innodb_redo_log_capacity is switched from memory-based to CPU-based. |
| innodb_adaptive_hash_index | ON | OFF |
| innodb_adaptive_hash_index | ON | OFF |
| innodb_doublewrite_files | innodb_buffer_pool_instances * 2 | 2 |
| innodb_doublewrite_pages | innodb_write_io_threads, which meant a default of 4 | 128 |
| innodb_flush_method on Linux | fsync | O_DIRECT if supported, otherwise fsync |
| temptable_max_mmap | 1073741824 (1 GiB) | 0, which means OFF |
| temptable_use_mmap[b] | ON | OFF |
| temptable_use_mmap[b] | ON | OFF |
| innodb_buffer_pool_instances | 8 (or 1 if innodb_buffer_pool_size < 1 GiB) | The calculated value of innodb_buffer_pool_size, based on the input data, is 1 GiB, indicating the minimum value in the range of 1 to 64. |
| innodb_change_buffering | all | none |
| innodb_io_capacity | 200 | 10000 |
| innodb_io_capacity_max | 2 * innodb_io_capacity, with a minimum default value of 2000 | 2 * innodb_io_capacity |
| innodb_log_buffer_size | 16777216 (16 MiB) | 67108864 (64 MiB) |
| innodb_numa_interleave | OFF | ON |
| innodb_page_cleaners | 4 | innodb_buffer_pool_instances |
| innodb_parallel_read_threads | 4 | available logical processors / 8, with a minimum default value of 4 |
| innodb_purge_threads | 4 | 1 if available logical processors is <= 16, otherwise 4 |
| innodb_read_io_threads | 4 | available logical processors / 2, with a minimum default value of 4 |
| innodb_use_fdatasync | OFF | ON |
| temptable_max_ram | 1073741824 (1 GiB) | 3% of total memory, with a default value within a range of 1-4 GiB |
| temptable_max_mmap | 1073741824 (1 GiB) | 0, which means OFF |
| temptable_use_mmap[b] | ON | OFF |
MySQL 8.4 Replication Features:
SOURCE_RETRY_COUNT Change: The CHANGE REPLICATION SOURCE TO statement has modified the default value for the SOURCE_RETRY_COUNT option to 10, indicating that the replica waits 60 seconds between reconnection attempts and rejoins at this rate for 10 minutes.
MySQL Replication: Tagged GTIDs: MySQL Replication and Group Replication have improved the structure of global transaction identifiers (GIDs) to identify transaction groups. The updated GTID format is UUID:TAG:NUMBER, with TAG being a string of up to 8 characters. This tag is effective for all transactions initiated in the current session and is applied at commit time for such transactions or Group Replication at certification time. The original UUID:NUMBER format for GTIDs remains supported without changes. A new TRANSACTION_GTID_TAG privilege is introduced, allowing administrators to limit the usage of SET @gtid_next=AUTOMATIC:TAG or UUID:TAG:NUMBER to a specified group of MySQL users or roles.
Replication terminology and compatibility with past versions:
MySQL Dump Release Overview
- Includes the –output-as-version option for MySQL 8.2 or greater servers.
- Allows building a dump compatible with previous MySQL versions.
- Query retrieves the server’s version and applies the latest replication statements and variable names.
- Output compatible with MySQL servers running versions 8.0.23 to 8.1.0, inclusive.
- Output compatible with MySQL servers operating versions before 8.0.23.
The MySQL version number used in version-specific comments allows a major version of one or two digits, allowing the overall version to be five or six digits long.
MySQL 8.4 Group Replication Features:
The group_replication_set_as_primary() method waits for active DDL commands, such as ALTER TABLE, to complete before selecting a new primary.
DDL and DCL statement monitoring for group_replication_set_as_primary():
group_replication_set_as_primary() also waits for the next couple of statements to finish before electing a new primary.
ALTER DATABASE, ALTER FUNCTION, ALTER INSTANCE, ALTER PROCEDURE, ALTER SERVER, ALTER TABLESPACE, ALTER USER, ALTER VIEW, CREATE DATABASE, CREATE FUNCTION, CREATE PROCEDURE, CREATE ROLE, CREATE SERVER, CREATE SPATIAL REFERENCE SYSTEM, CREATE TABLESPACE, CREATE TRIGGER, CREATE USER, CREATE VIEW, DROP DATABASE, DROP FUNCTION, DROP PROCEDURE, DROP ROLE, DROP SERVER, DROP SPATIAL REFERENCE SYSTEM, DROP TABLESPACE, DROP TRIGGER, DROP USER, DROP VIEW, GRANT, RENAME TABLE, REVOKE etc.
MySQL 8.4 Security Features:
MySQL 8.4.0 Introduces FLUSH PRIVILEGES Privilege
- Exclusively applicable to FLUSH PRIVILEGES statements.
- Supports RELOAD privilege for backward compatibility.
- Upgrade checks if users have FLUSH_PRIVILEGES privilege.
- Users with RELOAD privilege granted new privilege.
- Downgrade to a version without FLUSH_PRIVILEGES privilege prevents execution of FLUSH PRIVILEGES commands.
MySQL 8.4 Keyring Migration Features:
- Supports transition from keyring component to plugin.
- Uses –keyring-migration-from-component server option.
- Specifies source component and target plugin.
MySQL 8.0, a database management system, is continuously improving through continual upgrades, addressing both performance and security concerns. Its user-friendly tools and novel features in versions like MySQL 8.4 ensure that administrators can fully realize its potential. Future releases will explore more aspects, offering even greater advancements in database administration.
Credit: https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html
