mysql5.7 New Parameter configuration explanation

1:binlog_group_commit_sync_delay

Global dynamic variable, unit subtle, default 0, Range: 0~1000000 (1 seconds).

Indicates how much time to wait for a delay after Binlog commits to sync to disk, default 0, no delay. Setting the delay allows multiple transactions to be committed at one time, increasing the number of concurrent commits and efficiencies of the Binlog group, and increasing the throughput of the slave.

2:binlog_group_commit_sync_no_delay_count

Global dynamic variable, number of units, default 0, Range: 0~1000000.

Represents the maximum number of transactions waiting for a deferred commit, which is synchronized directly to disk if the time of the above parameter is not reached, but the number of transactions is reached. If the binlog_group_commit_sync_delay is not turned on, the parameter is not turned on.

3:check_proxy_users

Global dynamic variable, closed by default.

Indicates whether the function of the delegate user is turned on, which is described later in security.

4:default_authentication_plugin--Safety

Global variables, default mysql_native_password, Optional: Sha256_password

Represents the user's default authentication method, which is encryption, which is described later in security.

5:default_password_lifetime--Safety

Global dynamic variable, default 0, Range: 0~65535, per day.

Indicates the expiration time of the user's password, which is turned off by default. A setting of 365 indicates that the user password expires after one year.

6:disabled_storage_engines

global variable, default NULL.

Disables the creation of the specified storage engine, such as: disabled_storage_engines= "myisam,federated" If the server starts--bootstrap,--initialize,-- Initialize-insecure,--skip-grant-tables parameter, Disabled_storage_engines does not work.

7:gtid_executed_compression_period--Performance

Global dynamic variable, default 1000, range: 0~4294967295

Represents the number of transactions per execution, the mysql.gtid_executed table is compressed, and the default value is 1000.

8:have_statement_timeout--Performance

global variable, default Yes

Indicates whether the statement execution Timeout feature is available and is turned on by default.

9:innodb_adaptive_hash_index_parts--Performance

Global variables, default 8, range 1~512.

Indicates that the adaptive hash index begins to support partitioning, avoiding the lock contention problem caused by a previous single index, which is divided by default to 8. The maximum setting is 512.

10:innodb_buffer_pool_chunk_size--Performance

global variable, default 128M, range: 1m~innodb_buffer_pool_size/innodb_buffer_pool_instances

In order to support the online buffer pool resize, the introduction of the concept of chunk, each chunk default is 128M, when we modify the buffer pool online, the chunk as the unit to grow or shrink. InnoDB requires buffer pool size to be a multiple of innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances, and if not, it will be adjusted appropriately innodb_buffer_ Pool_size, to meet the requirements, the actual allocation of buffer pool may appear larger than the size specified in the configuration file.

11:innodb_buffer_pool_dump_pct

Global dynamic variable, 5.7.7 starts with a default of 25 (percent), before 100 (percent). Range: 1~100 (%)

Indicates the percentage of control backup buffer pool, more about the BP preheating can read this article.

12:innodb_default_row_format--Performance

Global dynamic variable. 5.7.9 after the default dynamic, before the default compact. Optional values are: DYNAMIC, COMPACT, redundant. Compressed is not supported by the system tablespace, but you can create a normal table. You can view the row format of a table by Show table status like ' TB '. Indicates that the default row_format is specified, and the various line formats for InnoDB can be viewed in this article.

13:innodb_fill_factor

Global dynamic variable. Default 100, Range: 10~100

Represents the percentage of its table space during each b-tree page sort index build, leaving the remaining space for future exponential growth. That is, when the page fills the proportions to the extent that it is considered full. Set to 100, leaving 1/16 of the space within the page for future growth. Just like InnoDB a 16K page, up to 15K, the remaining 1K is used for subsequent table index maintenance. For leaves and non-leaf nodes effective, the inclusion of text, blog overflow travel invalid.

14:innodb_flush_sync--Performance

Global dynamic variable, default to On

Checkpoint is not affected by innodb_io_capacity.

15:innodb_log_checksums--Performance

Global dynamic variable, default on.

Indicates that each block of redo log needs to be prefixed with a checksum check bit before writing the redo log to the file to prevent the apply from damaging redo log. Using crc-32c to check the redo log instead of the lower efficiency innodb_log_checksum_algorithm.

16:innodb_log_write_ahead_size:--Performance

Global dynamic variable, default 8192, which is 8 K, range: 512bytes~innodb_page_size, in bytes.

Represents the block size before redo log is written. InnoDB writes the Ib_logfile file as a block of 512 bytes, but the file system generally takes 4096 bytes as a block unit. If the log file block that is about to be written is not in the OS cache, you need to read the corresponding 4096-byte block into memory, modify the 512 bytes in it, and then write the block back to disk. This parameter solves this problem, when the offset of the currently written file cannot be divisible by the value, then 0 is required, and a portion of the data is written. This way, when the data being written is aligned with the disk block size, you can write the disk directly without read-modify-write the three steps.

17:innodb_max_undo_log_size--Performance

Global dynamic variable, default 1073741824byte is 1024M, range: 10m~2**64-1byte

Represents the maximum value of the limit undo log, and if Innodb_undo_log_truncate is enabled, Undo is marked as truncate if the threshold is exceeded. You can turn on the online shrink undo log, but you need to turn on the parameters innodb_undo_tablespaces and innodb_undo_directory. For more information on the log, see this article.

18:innodb_page_cleaners--Performance

Global variables, 5.7.7 before default 1,5.7.8 after default 4, range: 1~64

Indicates the number of threads that write BP dirty pages, 5.6.2 begins to stand out from the master thread, and 5.7.4 starts to support multi-threaded flush. This value must be less than or equal to innodb_buffer_pool_instances.

19:innodb_purge_rseg_truncate_frequency--Performance

Global dynamic variable, default 128, Range: 1~128

Represents the speed at which the purge rollback segment is controlled, and the undo table space cannot be reclaimed when there are active segments on it. This parameter is set to purge a rollback segment after each execution of the purge number of times.

20:innodb_temp_data_file_path

Global variables, default ibtmp1:12m:autoextend. Generate a file with a ibtmp1 size of 12M in the data directory.

Represents the path, file name, and size of the specified InnoDB temporary tablespace file. All non-compressed temporary tables use the same temporary tablespace, and the compressed temporary table uses a separate data file for each table in the directory specified by Tmpdir. can be passed information_schema. Innodb_temp_table_info See more information.

21:innodb_undo_log_truncate--Performance

Global dynamic variable, default off.

Indicates whether online shrink undo log is turned on. When undo log exceeds the size defined by innodb_max_undo_log_size, Undo is marked as truncate. Only the separation that has undo log turned on is valid, not the undo log for the shared table space. That is, you need to set parameters Innodb_undo_tablespaces (>=2) and Innodb_undo_logs (>=35) and Innodb_undo_directory

22:internal_tmp_disk_storage_engine

Global dynamic variable, default InnoDB

The storage engine that represents the internal disk staging table, which is innodb by default after 5.7.6, and the optional values are: InnoDB, MyISAM.

23:log_builtin_as_identified_by_password

Global dynamic variable, default off

24:log_error_verbosity--Performance

Global dynamic variable, default 3, Range:

Information that represents error logging, 1: Only error information is logged, 2: Error and warnings information is logged, 3: error, warnings, and plain notes information are logged.

25:log_statements_unsafe_for_binlog--Performance

Global dynamic variable, default on.

Indicates whether error code ' 1592′ information is logged in the error log. Error code ' 1592′:unsafe statement written to the binary log using statement format

26:log_syslog--Performance

Global dynamic variable, default off

Indicates whether the error log is logged to Syslog (/var/log/syslog).

27:log_syslog_facility

Global dynamic variable, default daemon.

Indicates that the device error number is written to the syslog, provided the Log_syslog parameter is turned on.

28:log_syslog_include_pid

Global dynamic variable, default on.

Indicates whether the ID of the service process is written to the syslog, provided the Log_syslog parameter is turned on.

29:log_syslog_tag

Global dynamic variable, default empty.

Indicates that the label is added to the server identifier and written to the syslog in the error log

30:log_timestamps

Global dynamic variable, default UTC, Optional value: UTC, SYSTEM

Indicates that this variable controls when log messages are written to the file, including error logs, normal logs, and slow query logs.

31:max_execution_time

Global \ Session dynamic variable, default 0.

Represents the time-out period for executing a SELECT statement, default 0: does not time out.

32:mysql_native_password_proxy_users

Global dynamic variable, default off.

Represents a plug-in that controls whether a proxy user's built-in authentication is supported, provided the check_proxy_users is turned on, 5.7.7 introduces the parameter.

33:ngram_token_size

global variable, default 2. Range: 1~10

Represents the length of the control InnoDB full-text search participle, see: InnoDB Full-text index: N-gram Parser

34:offline_mode--Performance

Global dynamic variable, default off.

Indicates that the server tier has been added to the offline mode (offline mode) for administrator upgrade maintenance work. Feature: When set to offline mode, the non-super account will be disconnected and error when the next request is made, the super account can remain connected and manage the database, and the repository copy thread will not be interrupted.

35:range_optimizer_max_mem_size--Performance

Global \ Session dynamic variable, default 8388608 is 8 m.

Represents limiting the maximum memory usage of the range optimizer. 0 means no limit.

36:rbr_exec_mode

Session dynamic variable, default STRICT, optional value: STRICT, idempotent

Represents the mode that controls the session thread: strict and idempotent. The idempotent mode ignores errors of Duplicate-key and No-key-found and is useful for replay in row mode where data is present, via Mysqlbinlog plus--idempotent parameters.

37:require_secure_transport--Safety

Global dynamic variable, default off.

Indicates whether a certain form of secure transport is required for the client to connect to the server. If enabled, the server only allows TCP/IP connections using SSL, or a connection file using a socket. The server rejects the insecure connection attempt, and the failure error er_secure_transport_required. This feature takes precedence over SSL requirements, and if you define a require SSL account, enable Require_secure_transport, the account cannot be connected using a UNIX socket file.

38:sha256_password_proxy_users--Safety

Global dynamic variable, default off.

Represents a plug-in that controls whether a proxy user's built-in authentication is supported, provided the check_proxy_users is turned on, 5.7.7 introduces the parameter.

39:show_compatibility_56

Global dynamic variable, 5.7.8 after default off.

mysql5.7.6 starting from the beginning of the information_schema.global_status has been discarded, for compatibility, at this time need to open show_compatibility_56, otherwise error:

ERROR 3167 (HY000): the ' information_schema. Global_status ' feature is disabled; See the documentation for ' show_compatibility_56 '

40:slave_parallel_type--Performance

5.7.2 start support, parallel replication mode. The default database, which represents the parallel replication at the library level; Logical_clock: Parallel replication based on group submissions. Optional values: DATABASE, Logical_clock

Represents the pattern of multi-threaded replication, and 5.6 begins to support library-based parallel replication, which is not effective for only one library. 5.7 Start support for group commit (Logical_clock)-based parallel replication to improve replication availability. For more information see: MySQL 5.7 Parallel replication implementation principle and tuning

41:slave_preserve_commit_order--Performance

Global dynamic variable, default 0, optional value 0, 1.

Indicates whether the order needs to be strictly persisted, and the default value of 0 indicates concurrent execution of the ignore order. For multithreaded slaves, the order in which transactions are performed on slave is strictly consistent with the order in relay log, and only when Slave_parallel_workers is turned on, Log_bin, log_slave_updates must be turned on, And the Slave_parallel_type value must be Logical_clock (the default is database), and if your transaction is often a cross-db operation, consider using this parameter to qualify the order. When this parameter is turned on, when any worker thread is required to execute a transaction, it can be executed and committed only after all of the previous transactions in the current transaction have been executed (executed by another worker thread).

42:super_read_only--Safety

Global dynamic variable, default off. The parameters supported after 5.7.8.

Before 5.7.8, the server turns on the read_only parameter, which means that only accounts with super privileges can update and modify tables. Users who are not super-privileged cannot be modified. After 5.7.8, the super_read_only parameter is turned on, and the account with super privileges cannot update and modify the table, and READ_ONLY will be invalid (controlled by super_read_only).

43:tls_version--Safety

Global variables, default tlsv1,tlsv1.1.

Represents a connection that allows an encrypted server, which is a comma-delimited list that contains one or more protocol names.

44:innodb_numa_interleave--Performance

Global variable, default off.

Represents the allocation policy for buffer pool memory in a interleave manner

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1931458

mysql5.7 New Parameter configuration explanation