MySQL database server core parameters and recommended configuration _ MySQL

Detailed explanation and recommendation of MySQL database server core parameters bitsCN.com

【Guide]

The MySQL manual also provides server-side parameters and parameter value descriptions. Currently, we need to pay attention to the server-side parameters that need to be modified or affect performance, it takes a lot of time to explain its usage and recommend how to configure parameter values. (Note: it is always written intermittently and may be incorrect ), first published to the website in the form of an article. it is determined that there is no problem in the future. we will provide a PPT download through Sina Weibo's Micro disk for your convenience of learning and reference.

LLower_case_table_names

Linux or Unix-like platforms are case sensitive to file names, that is, they are case sensitive to object names such as databases, tables, and stored procedures. to reduce developers' development costs, we recommend that you set this parameter so that the object names are automatically converted to lowercase letters;

LMax_connect_errors

The default value of max_connect_errors is 10, that is, the mysqld thread has not been restarted. a physical server can no longer connect to the mysqld service as long as the connection exception is interrupted for more than 10 times, therefore, we recommend that you set this value to at least. if the total number of abnormal interruptions exceeds the value set by the parameter, you can run the command flush hosts or restart the mysqld service;

LInteractive_timeout and wait_timeout

U interactive_timeout

The interaction activity is forcibly disabled by the server, and the waiting time is measured in seconds;

U wait_timeout

This parameter is valid only for connections established based on TCP/IP or Socket communication protocol, unit: seconds;

U recommended settings

Interactive_timeout = 172800

Wait_timeout = 172800

LTransaction-isolation and binlog-format

UTransaction-isolation

Available values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ,

SERIALIZABLE, the default value is: REPEATABLE-READ, the transaction isolation level settings are different, the binary log register

For more information, see the article on how to interpret the isolation level and log registration mode selection skills of MySQL transactions;

UBinlog-format

Replication mode. values: STATEMENT, ROW, and MIXED (note:. * only command-line replication is supported ),

5. 1. * Default version setting: MIXED;

URecommended configuration

① Read-only business application scenarios

Transaction-isolation = read-committed

Binlog-format = mixed#5.1 .*Version,5. 0 .*Can only be setStatement

① Non-read-only business application scenarios

Transaction-isolation = repeatabled-read

Binlog-format = mixed#5.1 .*Version,5. 0 .*Can only be setStatement

L event_scheduler

Transaction scheduling is disabled by default. we recommend that you do not compile the source code or disable it in the actual production environment. you can enable it temporarily when necessary. run the following command: set global event_scheduler = 1;

L skip_external_locking

External locks, that is, the locks implemented by the operating system, are only valid for the MyISAM engine and are prone to deadlocks. Therefore, all locks are disabled;

L innodb_adaptive_hash_index

Based on the data access frequency, the InnoDB engine gradually slows down the table data to the memory. if a table has a large amount of data cached in the memory, it uses a hash index (note: hash Index) is more efficient. InnoDB has a Hash Index mechanism to monitor data access. a Hash Index can be created and maintained automatically to provide access efficiency and reduce memory usage;

L innodb_max_dirty_pages_pct

The InnoDB main thread directly updates the data in Innodb_buffer_pool_size and waits for related penalty events instead of flushing back to the disk in real time, the maximum percentage of data in the cache space that is not flushed back to the disk in real time. The small scale setting helps reduce the recovery time when the mysqld service encounters a problem. The disadvantage is that more physical I/O is required, therefore, we must make a compromise based on the business characteristics and tolerable scope. generally, we recommend that you set the range to 5% ~ 90%. like our SNS game industry, we have made a huge number of achievements, and set it to 20% based on various factors;

L innodb_commit_concurrency

Meaning: The number of threads allowed to submit InnoDB transactions at the same time. the default value is 0 and the value range is 0-1000.

0-any number of transactions can be committed at the same time;

N> 0-N transactions can be committed at the same time point;

Note:

① When mysqld provides services, the innodb_commit_concurrency value cannot be changed from 0 to non-0, or the non-0 value cannot be changed to 0;

② When mysqld is provided, change N> 0 of innodb_commit_concurrency to M and M> 0;

L innodb_concurrency_tickets

Meaning:

The number of threads that can access InnoDB engine data at the same time. the default value is 500, and the range is 1-4294967295.

Note: When the number of threads that access the InnoDB engine data reaches the set threshold, the threads will be placed in the queue, waiting for other threads to release ticket.

Suggestion:

Max_connections, the maximum number of threads in the MySQL database service, is generally set in the range of-. innodb_concurrency_tickets is usually enough by default, combined with the maximum transaction concurrency possible for the actual business.

L innodb_fast_shutdown and innodb_force_recovery

Innodb_fast_shutdown:

Description: Set the innodb engine shutdown mode. the default value is 1. The innodb engine is disabled normally;

0-before the mysqld service is disabled, you must first perform full data cleaning and insert buffer merging operations. if the data is dirty

This process may take several minutes or longer due to many factors such as server performance;

1-the mysqld service is disabled normally, and no other operations are performed on The innodb engine;

2-if mysqld crashes, immediately fl transaction logs to the disk and kill the mysqld service cold; no commit

The transaction will be lost, but the transaction will be rolled back and restored when the mysqld service is started again;

Innodb_force_recovery:

Meaning:

After the mysqld service crashes, the InnoDB engine performs the rollback mode. the default value is 0 and the value can be set to 0 ~ 6;

Tip:

We recommend that you set innodb_force_recovery to be greater than 0 only when you need to back up data from a database in the wrong state. If this parameter is used as a security option, you can set the value of this parameter to be greater than 0 to prevent data changes in the InnoDB engine. the Function of setting different values is as follows:

0-close and start normally, and do not perform any forced recovery operations;

1-skip the error page to continue running the mysqld service. Skip the error index record and storage page and try to use

SELECT * inot outfile '../filename' FROM tablename; Method to complete data backup;

2-stop the InnoDB main thread from running. If the mysqld service crashes during cleaning, data recovery is blocked;

3-no transaction rollback is performed during recovery;

4-prevents the merge operation of the INSERT buffer. No merge operation is performed to prevent the mysqld service from crashing. Not calculated

Table Statistics

5-when the mysqld service is started, the rollback log is not checked: the InnoDB engine treats every uncertain transaction like committing

The same as transactions;

6-transaction log rollback and recovery operations are not performed;

Recommended parameter combination configuration:

Innodb_fast_shutdown = 1

# If the machine room has good conditions, you can set it to 0 (dual power supply, UPS, RAID card battery and power supply system stability)

Innodb_force_recovery = 0

# When a problem occurs, you must set the value based on the cause and severity of the error and perform subsequent operations on the data.

L innodb_additional_mem_pool_size

Meaning: a piece of memory is used to cache data dictionary information and internal data structures of the InnoDB engine (for example, adaptive HASH index structure );

Default value: build-in; default value: 1 M; default value: 8 M;

Tip: If the number of table objects on the mysqld service is large, the InnoDB engine has a large amount of data, and the innodb_buffer_pool_size value is set to large, the innodb_additional_mem_pool_size value should be adjusted as appropriate. If there is insufficient memory in the cache area, the system will apply for memory configuration splitting directly from the operating system, and the warning information will be written to the MySQL error log file;

L innodb_buffer_pool_size

Meaning: a piece of memory is used to cache data and indexes of InnoDB engine tables;

Default value: the historical default value is 8 M. The default value of the current version is 128 M;

Maximum parameter value: limited by the CPU architecture, 32-bit or 64-bit is supported, and 32-bit or 64-bit is also limited by the operating system;

Tip:

The value of innodb_buffer_pool_size is set appropriately, which will save the physical IO for accessing data in table objects. In the official manual, we recommend that you set a dedicated database server to 80% of the total physical memory. However, we recommend that you check the total physical memory of the physical server and consider the following: whether to use only InnoDB engine, memory occupied by mysqld internal management, maximum number of thread connections, temporary tables, and other factors. The official 80% value is used as a reference, for example, you can easily decide (premise: physical servers are dedicated to the mysqld service and only use the InnoDB engine. assume that the data volume is much larger than the physical memory ):

1). memory configuration: 24 GB, innodb_buffer_pool_size = 18 GB

1). memory configuration: 32 GB, innodb_buffer_pool_size = 24G

Which of the following conditions can be considered to reduce the value of innodb_buffer_pool_size:

1) physical memory competition may result in paging of the operating system;

2) InnoDB pre-allocates additional memory to the buffer zone and structure management. when the total allocated memory volume exceeds 10% of the innodb_buffer_pool_size value;

3). the address space must be consecutive. in windows, there is a serious problem. DLL needs to be loaded in a specific address space;

4). the time consumed to initialize the buffer is proportional to the buffer size. Official data Linux X86 64-bit system initialization innodb_buffer_pool_size = 10G takes about 6 seconds;

L innodb_flush_log_at_trx_commit AND sync_binlog

Innodb_flush_log_at_trx_commit = N:

N = 0-every second, write the data in the transaction log cache area to the log file, and refresh the data in the log file to the disk;

N = 1-when each transaction is committed, the transaction log is written from the cache to the log file, and the data of the log file is refreshed to the disk;

N = 2-when a transaction is committed, the transaction log data is written from the cache to the log file. the log file is refreshed every second, but not necessarily to the disk, it depends on the scheduling of the operating system;

Sync_binlog = N:

N> 0-after n SQL statements or N transactions are written to the binary log file, the data in the binary log file is refreshed to the disk;

N = 0-the operating system determines not to actively refresh the data in the binary log file to the disk;

Recommended configuration combination:

N =-suitable for high data security requirements, and the disk I/O write capability is sufficient to support businesses, such as the recharge consumption system;

N =-suitable for high data security requirements. the disk I/O write capability supports no surplus of business and allows the standby database to lag behind or not replicate;

N = 2, 0 or 2, m (0

N =-limited disk I/O write capability, no replication or a slight long replication latency is allowed, for example, log registration;

L innodb_file_per_table

Enabling a single tablespace reduces the maintenance cost of the shared tablespace and reduces the pressure on the release of idle disk space. In addition, performance can be improved in the case of large data volumes. Therefore, we recommend that you useIndependent tablespace ReplaceShared tablespaceMethod;

L key_buffer_size

Key_buffer_size can only cache index data of MyISAM or MyISAM engines, while innodb_buffer_pool_size can not only cache index data, but also cache metadata. However, for database systems that only use the InnoDB engine, the value of this parameter cannot be too small, because the temporary table may use this key cache space. The index cache is recommended to be 64 MB;

L query_cache_type and query_cache_size

N query_cache_type = N

N = 0 -- disable the query cache function;

N = 1 -- enable the message sending cache function to cache all query result sets that meet the requirements, except SELECT SQL _NO_CACHE... and the result set that does not meet the query cache settings;

N = 2 -- only cache SELECT SQL _CACHE... The query result set of the clause, except for the result set that does not conform to the query cache settings;

N query_cache_size

What makes the query cache settings reasonable? It must be considered from at least four dimensions:

① Query the performance impact of the cache area on DDL and DML statements;

② Query the internal maintenance cost of the cache area;

③ Comprehensively consider the hit rate and memory usage of the query cache area

④ Business type

BitsCN.com