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.
The default value of an InnoDB system variable has changed: 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 native password authentication revisions: 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.
Hash table optimization for set operations: 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.
Information Schema PROCESSLIST Table Usage:
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 Replication: 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.
group_replication_set_as_primary() function and DDL statements:
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.
FLUSH_PRIVILEGES privilege:
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.
Keyring migration: MySQL 8.4.0 Migration
- 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
