About MySQL database performance optimization

MySQL databases are IO-intensive programs. Like other databases, their main functions are data persistence and data management. This article focuses on optimizing MySQL database cache parameters such as query cache, table cache, log cache, index cache, innodb cache, insert cache, and connection parameters. Cache

MySQL databases are IO-intensive programs. Like other databases, their main functions are data persistence and data management. This article focuses on optimizing MySQL database cache parameters such as query cache, table cache, log cache, index cache, innodb cache, insert cache, and connection parameters. Cache

MySQL databases are IO-intensive programs. Like other databases, their main functions are data persistence and data management. This article focuses on optimizing MySQL database cache parameters such as query cache, table cache, log cache, index cache, innodb cache, insert cache, and connection parameters.

Cache Parameters

Here we will reference a sentence. the time consumed to read a data from the memory is in microseconds, And the read time from a general hard disk is in milliseconds. The difference between the two is three orders of magnitude. It can be seen that it is more direct to optimize the MySQL database and rationally allocate cache parameters.

Table Cache

Related parameters:Table_open_cache

Specify the table cache size. Earlier versions are table_cache. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. By checking the status value of the peak time, if open_tables is found to be table_open_cache and opened_tables is growing, you need to increase the value of this parameter. Note: you cannot set this parameter too large. If it is too large, it will cause insufficient file descriptors, unstable performance, or database connection failure. Recommended Value: 512

In most cases, the following values are suitable:

Open_tables / Opened_tables >= 0.85Open_tables / table_open_cache <= 0.95

Query Cache

Related parameters:Query_cache_size/query_cache_type

QC(Note: Query cache for short) is mainly used to cache the results set in MySQL, that is, the result set executed by an SQL statement. Therefore, it can only be used for select statements. If the QC function is enabled, after MySQL receives the select request, if the statement meetsQCMySQL will directly HASH the received select statement in string mode based on the hash algorithm, and thenQCIf it is already in the cache, the select request will directly return the data, thus omitting all the subsequent steps (such as SQL Syntax Parsing, optimizer optimization and storage engine request data), greatly improving performance.

Of course, QCThere is also a fatal defect, that is, when the data in the table changes, all QCThe cache is invalid. Therefore, when data changes frequently QCInstead, the loss is worth the candle.

The use of QC requires the combination of multiple parameters. The most important among them is query_cache_size and query_cache_type. The former sets the memory size of the cache record set, and the latter sets the scenario where QC is used.

From past experiences, for medium-sized websites, the query_cache_size setting is enough to reach 256 MB. Of course, you can also adjust it by calculating the QC hit rate.

Qcache_hits / (Qcache_hits + Qcache_inserts)  * 100%

Query_cache_type has three options: 0 (OFF, not usedQC), 1 (ON, QC is used by default), 2 (DEMAND, not used by default)QC).

Why is "default" added "? MySQL also supports dynamic use of the cache SQL syntax, as follows:

# Force SELECT SQL _CACHE id FROM table # force SELECT SQL _NO_CACHE id FROM table not to be cached

Log Cache

Related parameters:Binlog_cache_size

It is a memory cache designed by MySQL to cache binlog data in a short period of time in an environment that enables the binary log record function.

If there are no large transactions in the database, writing is not very frequent, 2 MB ~ 4 MB is a suitable choice. However, if the database has many large transactions and writes frequently, you can increase the number of transactions. You can also use binlog_cache_use and binlog_cache_disk_use to analyze whether the configured binlog_cache_size is sufficient and whether a large number of binlog_cache files are cached due to insufficient memory size.

Index Cache

Related parameters:Key_buffer_size

This is a parameter that has the greatest impact on the performance of the MyISAM table. It is used to set the memory size of the index file used to cache the MyISAM storage engine. If there is enough memory, the cache area size can be set to the total size of all indexes in the MyISAM table, that is, the total size of all *. MYI files in the data directory.

Note: Because the MyISAM engine only caches index blocks into the memory, it does not cache table database blocks. Therefore, to query SQL statements, make sure that all the filtering conditions are in the index as much as possible, so that the index cache can be used to improve the query efficiency.

Calculate the probability of index cache miss:

Key_reads / Key_read_requests * 100%

Insert Cache

Related Parameters: bulk_insert_buffer_size

The MyISAM engine is used to cache data written in the temporary cache when data is inserted in batches. When we use the following data writing statements, we will use this memory area to cache batch structured data to help batch write data files. The default value is 8 Mb. We recommend that you do not exceed 32 MB.

Insert... Select... Insert... Values (...), (...), (...),... Load data infile... Into... /* Non-empty table */

InnoDB Cache

Related parameters:Innodb_buffer_pool_size/innodb_additional_mem_pool_size

Innodb_buffer_pool_sizeThe parameter is the most critical parameter that affects the performance of the InnoDB Storage engine. It is similar to the key_buffer_size parameter of the MyISAM storage engine, it may be more like db_cache_size of Oracle. To put it simply, when operating an InnoDB table, all the data returned or any index block used in the data removal process will involve this memory area.

Innodb_buffer_pool_sizeThe parameter sets the size of the maximum memory area required by the InnoDB Storage engine, which is directly related to the performance of the InnoDB Storage engine. Therefore, if there is enough memory, increase the value of this parameter as much as possible, store as many InnoDB indexes and data as possible in the cache.

Of course, you can calculate the cache hit rate and adjust the value of this parameter based on the hit rate:

 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 

Innodb_additional_mem_pool_sizeThe parameter is used to set the data directory information stored in InnoDB and the memory pool size of other internal data structures. As the number of database objects increases, you need to adjust the size of this parameter to ensure that all data can be stored in the memory to improve access efficiency. The size of this parameter is relatively stable and there is no need to reserve a very large value. If the InnoDB engine uses up the memory in this pool, the InnoDB engine will apply for memory from the operating system and write warning information to the MySQL error log. The default value is 1 MB. When the error log contains related warning information, the parameter size should be appropriately increased.

Innodb_log_buffer_sizeThe parameter is the buffer used by the transaction logs of the InnoDB Storage engine. Similar to Binlog Buffer, InnoDB writes information to Innofb Log Buffer to improve performance when writing transaction logs, when the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log will be written to a file (or synchronized to the disk. You can use the innodb_log_buffer_size parameter to set the maximum memory space that can be used.

Innodb_flush_log_trx_commitParameters have a critical impact on the log Writing Performance of the InnoDB engine. This parameter can be set to 0, 1, 2, as follows:

0: Data in the log buffer is written to the log file at a frequency every second, and the file system is synchronized to the disk at the same time, however, the commit of each transaction does not trigger any refresh from log buffer to log file or from the file system to disk;

1: Data in the log buffer will be written to the log file during each transaction commit, and synchronization from the file system to the disk will also be triggered;

2: transaction commit triggers refresh from log buffer to log file, but does not trigger Disk file System to disk synchronization. In addition, a file system is synchronized to the disk every second.

In addition, the MySQL documentation also mentions that the mechanism for synchronizing these settings once per second may not completely ensure that synchronization will occur every second very accurately, but also depends on the process scheduling problem. In fact, whether InnoDB can really meet the meaning of the value set by this parameter indicates that normal Recovery is still restricted by file systems and disks in different operating systems, sometimes, if the Disk Synchronization is not completed, mysqld is also notified that the disk synchronization has been completed.

Innodb_max_dirty_pages_pctThe parameter is used to control the Dirty pages in the InnoDB Buffer Pool without writing data files) ratio (dirty data that has been repaired but has not been written into the data file from the memory ). The larger the proportion value, the write operations from memory to disk will be relatively reduced, so the disk IO for write operations can be reduced to a certain extent. However, if the ratio is too large, it may take a long time to restart after the database crashes (Crash), because a large amount of transaction data needs to be recovered from the log file and written into the data file. At the same time, a large proportion value may also cause excessive flush operations after reaching the upper limit of the proportion setting, resulting in high performance fluctuations. If this parameter is set too large, the MySQL startup time will be too long and the shutdown time will be too long.

Connection Parameters

MySQL database operations are based on MySQL database connections. Therefore, improving MySQLl's connection processing capability is also an important way to improve MySQL performance.

Number of connections

Related parameters:Max_connections/back_log

Max_connectionsThe parameter sets the maximum number of connections to MySQL, that is, the number of customers that can be connected simultaneously. If the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections. However, the larger the number of connections, MySQL will provide a connection buffer for each connection, and the higher the memory overhead, the more memory the server consumes, which may affect the server performance, therefore, you must adjust the value based on the server configuration, and do not blindly increase the value. The default value is 100.

It is recommended to calculate the connection handling status when MySQL is busy. The value ranges from 50% ~ 80%

max_used_connections / max_connections * 100%

Back_logThe parameter sets the number of connections that MySQL can store temporarily. It works when MySQL receives many connection requests in a short time. If the number of connections in MySQL reaches max_connections, the new request will be stored in the stack to wait for a connection to release the resource. The number of stacks is back_log, if the number of connections waiting exceeds back_log, connection resources are not granted. Setting back_log higher than your operating system is invalid. The default value is 50.

Connection timeout

Related parameters:Wait_timeout/interactive_timeout

The number of seconds before the server closes the connection. The maximum number of connections supported by MySQL is limited, because the creation of each connection consumes memory. Therefore, we hope that MySQL will disconnect and release the occupied memory after processing the corresponding operations. If your MySQL Server has a large number of idle connections, they will not only consume the memory in vain, but if the connections continue to accumulate and continue to open, it will eventually reach the maximum number of connections of the MySQL Server, this will report the 'too has connections' error. The value of wait_timeout should be determined based on the system running condition. After the system runs for a period of time, you can run the show processlist command to view the connection status of the current system. If a large number of connection processes in sleep status are found, this parameter is set too large, you can make some adjustments. Recommended 120 ~ 300

Connection check

Related parameters:Skip-name-resolve

The skip-name-resolve parameter is used to disable reverse DNS resolution. MySQL enables reverse DNS resolution by default. When a new connection comes, MySQL will resolve the DNS of the connected host, which affects the connection speed. There is also a price to use this parameter, that is, if the IP address is used for each connection, localhost cannot be used and changed to 127.0.0.1.

Recommended Configuration value

We recommend that you analyze and adjust the values in the production environment.

MySQL Configuration	Recommended value description
Table_open_cache	If the value is too large, the system may be unstable or the database connection may fail. We recommend that you set the value to 512
Query_cache_type	If the MyISAM engine is not used, 0 is recommended; otherwise, 1 is recommended. If writing is too frequent, 2 is recommended.
Query_cache_size	Adjust the hit rate based on actual conditions. It does not need to be too large. 256 MB is recommended.
Binlog_cache_size	2 MB ~ recommended ~ 4 MB, large transactions and frequent writes can be appropriately increased, but not more than 32 MB
Key_buffer_size	If MyISAM is used, increase the value as much as possible when the memory permits. The reference value is 512 MB.
Bulk_insert_buffer_size	If you need to insert data in batches frequently, increase the data size to 32 MB.
Innodb_buffer_pool_size	If InnoDB is used, you can set the value 50% ~ 80% memory
Innodb_log_buffer_size	The default value is 1 MB. The system with frequent database operations can be increased to 4 MB ~ 16 MB
Innodb_max_dirty_pages_pct	The larger the value, the longer the database start time and close time, you can adjust it to 90
Max_connections	If the value is too large, the performance will be affected. The default value is 100. The recommended value is 128 ~ 512
Back_log	The default value is 50. We recommend that you set the value to 128 ~ 512
Wait_timeout	Modify interactive_timeout at the same time. The default value is 28800 (8 hours). The recommended value is 120 ~ 300

Possible MySQL commands:

# View the current MySQL running status value mysql> show global status like 'thread _ % '; # view the current MySQL configuration information mysql> show global variables like' % binlog % '; # modify MySQL configuration information in real time mysql> set global max_connections = 512;

Reference

Http://blog.csdn.net/mycwq/article/details/16370525
Http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter
Http://database.51cto.com/art/201010/229939.htm
Http://jackyrong.iteye.com/blog/781859
Http://www.bootf.com/523.html