Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the new configuration parameters of MySQL8.0 are, which the editor thinks is very practical, so I share it with you for reference. I hope you can get something after reading this article.

MySQL8.0 adds or improves features such as data dictionary, atomic DDL, security and account, resource management, InnoDB enhancement, character set support, JSON enhancement, field type support, optimizer, general expression, window function, regular expression support, internal temporary table, log, backup lock and so on. MySQL itself is a database with more and more complex configuration. So which parameter items are added in the new version to control the use of these new features? Next, we analyze the new configuration items one by one.

Activate_all_roles_on_login

This parameter was introduced in version 8.0.2 and is a dynamically adjustable global-level parameter with a default value of OFF. This parameter is used to control whether granted roles are activated when the account logs in, if ON, the granted roles are activated, and when set to OFF, user roles can only be explicitly activated through SET DEFAULT ROLE. The activate_all_roles_on_login setting takes effect only when the account logs in or starts executing the stored procedure. If you want to change the role of session, you need to execute the SET ROLE statement.

Binlog_expire_logs_seconds

The parameter, introduced in version 8.0.1, is a global-level parameter that can be adjusted dynamically. The default value before 8.0.4 is 2592000 after 8.0.11, that is, 30 days. In previous versions of binlog, the automatic cleanup time is measured in parameters expire_logs_days, that is, days. If the current two parameters coexist and there is a non-0 parameter, the binlog automatic cleanup time is taken as the non-0 parameter. If both are non-0 values, the expire_logs_days parameter setting is ignored with binlog_expire_logs_seconds as the binlog cleanup time.

Binlog_row_metadata

This parameter was introduced in version 8.0.1 and is a global-level parameter that can be adjusted dynamically. The default value is MINIMAL and can be set to FULL. This parameter is used to control the amount of metadata in the table in binlog in row format, record symbol marks, column character sets and space types when set to MINMAL, and record all metadata of the table, such as column names, enumerations or collections, primary key information, etc., when set to FULL.

Binlog_row_value_options

This parameter was introduced in version 8.0.3 and can be adjusted dynamically at the global level. The default value is''and can be set to PARTIAL_JSON.

When set to PARTIAL_JSON and the binlog format is in ROW or MIXED mode, when using JSON_SET (), JSON_REPLACE (), and JSON_REMOVE () to operate on the columns of JSON, binlog records only the updated portion, rather than the changes to the entire JSON, thereby reducing the binlog size. However, when changes to the JSON document require more space than the current document, or when the SERVER cannot be partially updated, binlog still records the entire JSON document.

Binlog_transaction_dependency_history_size

This parameter, introduced in version 8.0.1, is a dynamically adjustable global-level parameter with a default value of 25000 and can be set to any integer between 0 and 1000000. 8.0 when replicating in parallel based on WriteSet, WriteSet is an hash array, and the binlog_transaction_dependency_history_size value is the maximum value of this hash array.

Binlog_transaction_dependency_tracking

This parameter was introduced in version 8.0.1 and is an global-level enumeration type parameter that can be adjusted dynamically. The default value is COMMIT_ORDER, or it can be set to WRITESET, WRITESET_SESSION. This parameter is used by the master library to determine the dependency mode of multithreaded replication between transactions in the slave library.

COMMIT_ORDERE: parallelized according to the commit timestamp of the main database transaction, i.e. 5.7GroupCommit

WRITESET: parallel according to WriteSet, as long as it is not in the same queue

WRITESET_SESSION: parallel according to WriteSet, but transactions of the same session are not parallel.

Caching_sha2_password_auto_generate_rsa_keys

Introduced in version 8.0.4, it is a global-level parameter that cannot be dynamically adjusted, and the default value is ON. When OpenSSL is compiled into MySQL, it is determined whether to automatically generate public and private key files at startup.

Caching_sha2_password_private_key_path

As above, it is used to specify the private key file path and file name, which is stored under the data file path when set to a relative path.

Caching_sha2_password_public_key_path

As above, it is used to store the path and name of the public key file.

Cte_max_recursion_depth

Introduced in version 8.0.3, it is an integer type parameter at global and session level that can be adjusted dynamically. The default value is 1000, which can be set to any integer between 0 and 4294967295. This parameter is used to set the maximum recursive depth of a common table expression. If the recursion depth exceeds this value during the execution of SQL, the execution will be interrupted.

Default_collation_for_utf8mb4

Introduced in version 8.0.11, it is an enumerated type parameter at the global and session level that can be adjusted dynamically. This parameter value is passed from the master library to the slave library and is mainly used for master-slave synchronization or when synchronizing data from pre-8.0 to 8.0 in MGR to maintain a consistent collation between the master and slave to process the data.

Histogram_generation_max_mem_size

Introduced in version 8.0.2, it is a parameter at global and session level that can be set dynamically. The default value is 20000000, which can be set to any value between 1000000 and 18446744073709551615 (4294967295 for 32-bit system). When creating a histogram, the data of the table needs to be read into memory, but when the table is too large, a large amount of data may be read into memory. To avoid this risk, the server layer calculates according to the histogram_generation_max_mem_size setting, calculates how many rows are read into memory, and samples.

Information_schema_stats_expiry

The parameters, introduced by version 8.03, are global and session-level parameters that can be set dynamically. The default value is 86400, which can be set to hot any integer between 0 and 315360000.

There are columns in the Information_schema library that provide statistics for the table, which are updated as the contents of the table change. By default, MySQL retrieves mysql.index_stats and mysql.table_stats dictionary tables for table information retrieval, which is much more efficient than retrieving relevant information from the storage engine layer. If the required table statistics are not cached or have expired, the relevant statistics are obtained from the storage engine and cached in the mysql.index_stats and mysql.table_stats tables, which are used by subsequent queries before the table statistics expire. Each session can set information_schema_stats_expiry independently. The default value is 86400 seconds, that is, 24 hours. This value can be set to a maximum of one year. If you want to update the statistics of a table, execute ANALYZE TABLE. If you want to get the latest statistics for each query, set this value to 0. Each time, MySQL skips mysql.index_stats and mysql.table_stats dictionary tables to obtain statistics directly through the storage engine.

Innodb_dedicated_server

The parameter, introduced in version 8.03, is a global-level parameter that can only be set at startup. When this parameter is set to ON, MySQL sets innodb_buffer_pool_size, innodb_log_file_size, and innodb_flush_method parameters based on the amount of memory detected. With this parameter, we no longer have to write scripts to modify these three parameters of the configuration file according to memory size, and operation and maintenance automation saves another step. It is recommended to set to OFF when server MySQL shares server memory with other applications. So what is the specific setting strategy of MySQL?

Innodb_buffer_pool_size:

Detected Server Memory

Buffer Pool Size

< 1G

128MiB (the innodb_buffer_pool_size default)

4G

Detected server memory * 0.75

Innodb_log_file_size:

Detected Server Memory

Log File Size

< 1GB

48MiB (the innodb_log_file_size default)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.