‍

In this series, the author will collate and summarize all the new features interpretations of the MySQL 8.0 version so far. They will be classified according to functionality, performance, security, optimizer and other aspects, and will be explained in multiple articles in the future.

The first part mainly discusses what functional enhancements MySQL 8.0 has. The second part introduces what progress MySQL 8.0 has made in terms of performance. This article is the third part, introducing the changes brought by MySQL 8.0 in aspects such as security and the optimizer.

Security

1. Deadlock detection

After version 5.0.3, a new dynamic variable innodb_deadlock_detect can be used to disable deadlock detection. On high-concurrency systems, when multiple threads are waiting for the same lock, deadlock detection can slow down. Sometimes, disabling deadlock detection and relying on the innodb_lock_wait_timeout setting to roll back transactions when a deadlock occurs may be more effective.

2.Default password authentication plugin

In version 8.0.4 of MySQL, the default identity authentication plugin has been changed. The old mysql_native_password plugin is changed to the new caching_sha2_password and is used as the default identity authentication mechanism. At the same time, the corresponding libmysqlclient on the client side also defaults to using the new authentication plugin.

3. User password enhancement

3.1 Password reuse policy

## Detection of historical password repetition times: The new password cannot be the same as the latest five passwords.
password_history = 5 ; 

## Time interval: The new password cannot be the same as the passwords used within the past 90 days.
password_reuse_interval = 90 ; 

3.2 Verification strategy required for changing passwords

When changing a password, the current password must be entered. This increases user security.

## Default is off; when set to on, changing a password requires the user to provide the current password (when enabled, changing a password requires verification of the old password, except for the root user).
password_require_current = on

3.3 Double passwords

Compared to a user having only one password, the greatest advantage is that changing the password will not cause the application to be unavailable. Then the application can automatically use the secondary password (the secondary password remains the same as the current password) to connect to the database. This ensures the uninterrupted operation of the business. Changing the password will not cause the application to be unavailable; the application can automatically use the secondary password to connect to the database.

4. Role function

A MySQL role is a collection of specified permissions. Like a user account, a role can have permissions that can be granted and revoked.User accounts can be granted roles, granting the account the permissions associated with each role.This facilitates user permission management and maintenance. It is a good solution for multiple users to use the same set of permissions.

Permissions –> Roles –> Users.

5. Redo log & Undo log encryption

Add the following two parameters to control the encryption of redo and undo logs.

innodb_redo_log_encrypt
innodb_undo_log_encrypt

Optimizer

1. Cost Model improvement

The optimizer can sense whether a page exists in the buffer pool. In fact, version 5.7 has already opened an interface, but it does not count pages in memory and always returns 1.0.

2. Scaling Read/Write Workloads

The version 8.0 handles read-write and high-write loads just right. In the case of concentrated read-write loads, we observed that with four concurrent users, for high loads, there is a twofold performance improvement compared to version 5.7. We significantly improved the performance in read-only scenarios on 5.7, while 8.0 significantly improved the scalability of read-write loads. This has improved the utilization rate of hardware performance for MySQL. The improvement is based on redesigning InnoDB's method of writing redo logs. Compared to users' threads competing to write their data changes before, in the new redo log solution, now redo logs are handled by dedicated threads for writing and flushing caches. User threads no longer hold locks related to redo writing, and the entire redo processing process is time-driven.

The version 8.0 allows full use of storage devices. For example, when using Intel Optane flash drives, we can obtain 1 million sampled QPS in IO-sensitive loads (here, IO-sensitive means not in IBP and must be obtained from secondary storage devices). This change is due to getting rid of the contention of the global lock file_system_mutex.

3. Better Performance upon High Contention Loads (“hot rows”)

The version 8.0 significantly improves performance under high contention loads. High contention loads usually occur when many transactions compete for the lock of the same row of data, resulting in the generation of transaction waiting queues. In actual scenarios, the load is not stable and may explode within a specific period of time (the 80/20 rule). The version 8.0 handles short-term burst loads better in terms of the number of transactions processed per second (in other words, latency) and 95% latency. For end users, it is reflected in better utilization of hardware resources (efficiency). Because the system needs to use hardware performance as much as possible to provide a higher average load.

Others

1. Support online modification of global parameters and persistence

By adding the PERSIST keyword, the modified parameters can be persisted in the new configuration file (mysqld-auto.cnf). When MySQL is restarted, the latest configuration parameters can be obtained from this configuration file.

The system will generate the mysqld-auto.cnf file in the data directory. The content of this file is stored in JSON format. When both my.cnf and mysqld-auto.cnf exist at the same time, the latter has a higher priority.

For example:

1SET PERSIST max_connections = 1000;
2SET @@PERSIST.max_connections = 1000;

This SET syntax enables you to make configuration changes at runtime that also persist after the server is restarted. Like SET GLOBAL, SET PERSIST sets the runtime value of a global variable but also writes the variable setting to the mysqld-auto.cnf file (replacing any existing variable settings if present).

2.The expiration time of binlog logs is accurate to seconds

Previously it was in days, and the parameter name has changed. Before version 8.0, the expiration time setting of binlog logs was always set by the parameter expire_logs_days. In version 8.0, MySQL uses the parameter binlog_expire_logs_seconds by default.

3. Automatic undo space recycling

In version 8.0.2, the default value of the parameter innodb_undo_log_truncate changes from OFF to ON, and the automatic recycling of undo log table space is enabled by default.

In version 8.0.2, the default value of the parameter innodb_undo_tablespaces is 2. When one undo table space is recycled, there is another one providing normal service.

The parameter innodb_max_undo_log_size defines the maximum value for undo table space recycling. When the undo table space exceeds this value, the table space is marked as recyclable.

4. Geographic Information System (GIS)

Version 8.0 provides support for terrain, which includes support for data source information of spatial reference systems, SRS-aware spatial data types, spatial indexes, and spatial functions. In short, version 8.0 can understand latitude and longitude information on the earth's surface and can calculate the distance between any two points on the earth in any of the supported 5000 spatial reference systems.

Note: Before upgrading, be sure to verify whether the JDBC driver matches and whether it needs to be upgraded along with the upgrade.

5. Parameter Switch Table

1select @@optimizer_switch \G
2
3mysql> select @@optimizer_switch \G
4*************************** 1. row ***************************
5@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
6
7## session
8set session optimizer_switch="use_invisible_indexes=off";  
9set session optimizer_switch="use_invisible_indexes=on";  
10
11## global
12set global optimizer_switch="use_invisible_indexes=off";  
13set global optimizer_switch="use_invisible_indexes=on"; 

‍

The above is the entire content of "The Most Comprehensive Interpretation of New Features in MySQL 8.0". With the release of MySQL 8.4 LTS and 9.0, we will bring more interpretations of new MySQL features in the future.