MySQL tuning of server parameters for head-to-toe optimization

When it comes to MySQL tuning, there are a lot of points that we can do, so combing, some tuning strategy, today just summarizes the tuning of server parameters

Actually speaking, the parameter tuning, my understanding is nothing more than two points:

• If it is InnoDB database, innodb_buffer_pool_size to open as large as possible, I usually open the memory of about 80%
• If it is a MyISAM database, the key_buffer_size is as large as possible.　　　　

I think this is very important for the two parameters, below is the key introduction below, the role of these two parameters:

innodb_buffer_pool_size:

　 This parameter is used to cache data indexes and data blocks of data, simply speaking, when we operate the InnoDB database to obtain data, will be in this area of memory block to go, get the data.

Innodb_buffer_pool_size sets the size of the memory area block of the INNODB storage engine, which is directly related to INNODB's storage engine performance, so if we have large enough memory, we should set as much memory as possible,

This will send as much data and indexes as possible to the memory data block.

We can calculate cache hit ratio by (innodb_buffer_pool_read_requests–innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests * 100% , and according to the hit rate

To adjust the innodb_buffer_pool_size parameter size to optimize

　　Key_buffer_size:

　　This parameter is used to cache the index of the data, and does not cache the data block, that is, we should be as much as possible to the SQL filter in the index, thereby increasing the cache hit ratio.

This parameter is also directly related to the performance of MyISAM, so we should also allocate as much memory as possible to this parameter. In order to improve performance as high as possible.

　　The following parameters are some of the common parameters of MySQL, which can be referenced below:

Query_cache_size/query_cache_type:

Query cache acts on the entire MySQL Instance, primarily to cache ResultSet in MySQL, which is a result set executed by an SQL statement, so only for SELECT statements. When we opened the

Query cache feature, after MySQL receives a request to a SELECT statement, if the statement satisfies the query cache requirement (no explicit description is not allowed for query cache, or if you have explicitly declared that you need to use query

Cache), MySQL will hash the received SELECT statement as a string directly based on a pre-set hash algorithm, and then go directly to query cache to find out if it is already cached. In other words, if you are already in slow

Save, the Select request returns data directly, omitting all subsequent steps (such as parsing of SQL statements, optimizer optimizations, and requesting data from the storage engine), which greatly improves performance.

Of course, the query cache also has a fatal flaw, that is, when there is any change in the data of a table, it will cause all the SELECT statements referencing the table to fail in cached data in Query cache. So, when

When our data changes very frequently, using the query Cache may not be worth the candle.

The use of Query cache requires multiple parameter mates, the most critical of which is query_cache_size and Query_cache_type, which sets the memory size used to cache ResultSet, which is set in what scenario

Use the query Cache. In the past experience, if the MySQL database is not used to cache basic unchanging data, Query_cache_size General 256MB is a more appropriate size. Of course, this can be calculated by

Query Cache Hit Rate (qcache_hits/(qcache_hits+qcache_inserts) *100)) to adjust. The Query_cache_type can be set to 0 (OFF), 1 (on), or 2 (Demond), each of which represents the end

Do not use query cache at all, except explicitly require that all select other than query cache (using Sql_no_cache) use query cache, only the display requires the use of Querycache (Sql_cache).

Binlog_cache_size (Global)

The Binlog cache is used in environments where the binary log (Binlog) logging feature is turned on, and is a memory area that MySQL has designed to improve the efficiency of Binlog recording for a short period of time to temporarily cache Binlog data.

In general, if there are no large transactions in our database, and the writes are not particularly frequent, 2MB~4MB is a suitable choice. But if our database large transaction, write volume is large, can be adjusted with the appropriate height

Binlog_cache_size. At the same time, we can analyze whether the set of binlog_cache_size is sufficient by Binlog_cache_use and binlog_cache_disk_use, whether there is a large amount of binlog_cache due to memory

Not large enough to use temporary files (binlog_cache_disk_use) to cache the

　　　　

　　　　Innodb_additional_mem_pool_size (Global)

This parameter we usually adjust may not be too much, many people have used the default value, probably many people are not too familiar with the role of this parameter. Innodb_additional_mem_pool_size Set up InnoDB storage

The engine holds the data dictionary information and the memory space of some internal data structures, so when we have a very large number of database objects in MySQL instance, we need to adjust the size of this parameter to ensure that all the data

Can be stored in memory to improve access efficiency.

It's easy to know if this parameter size is enough, because when it's too small, MySQL logs the Warning information into the database's error log, and you know it's time to adjust the parameter size.

　　　　

　　　　Innodb_log_buffer_size (Global)

This is the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing transaction logs, in order to improve performance, the information is written to INNOFB log Buffer first,

The log is written to a file (or to a disk) when the appropriate conditions set by the Innodb_flush_log_trx_commit parameter are met (or the log buffer is full). Can be done by innodb_log_buffer_size

parameter to set the maximum memory space that it can use.

Note: The Innodb_flush_log_trx_commit parameter has a very critical impact on the write performance of InnoDB log. This parameter can be set to 0,1,2, as explained below:

The data in the 0:log buffer is written to log file at a frequency of one second, and the file system-to-disk synchronization is performed, but the commit of each transaction does not trigger any flush of log buffer to log file or

File system-to-disk refresh operations;

1: The data in log buffer will be written to log file each time the transaction commits, and the file system to disk synchronization will also be triggered;

2: Transaction commit triggers a flush of log buffer to log file, but does not trigger a disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.

In addition, the MySQL documentation mentions that the mechanism of synchronizing each second in these settings may not completely ensure that a very accurate synchronization occurs every second and depends on the process scheduling problem. In fact, can InnoDB really

Being satisfied with the value set by this parameter is normal Recovery is also limited by the file system under different OS and the disk itself, there may be times when you do not actually complete the disk synchronization will tell Mysqld already

The disk synchronization is complete.

The following are the settings for some of the commonly used parameters reproduced:

• Query_cache_type: If you use the InnoDB storage engine all, it is recommended to 0, if you use the MyISAM storage engine, 2 is recommended, and you explicitly control whether Gquery cache is used in SQL statements
• Query_cache_size: According to the hit Ratio (qcache_hits/(qcache_hits+qcache_inserts) *100) to adjust, generally not recommended too large, 256MB may have been almost, Large configuration-type static data can be adjusted to the appropriate size
• Binlog_cache_size: The general Environment 2MB~4MB is a suitable choice, the transaction is large and writes frequently the database environment can be suitably large, but does not recommend more than 32MB
• Key_buffer_size: If you do not use the MyISAM storage engine, 16MB is sufficient to cache some system table information and so on. If you use the MyISAM storage engine, put all the indexes into memory whenever possible, and simply say, "Bigger is better."
• Bulk_insert_buffer_size: If you frequently need to use the BULK INSERT special statement (described above) to insert the data, you can adjust the parameter to 16MB~32MB, do not recommend to continue to increase, someone 8MB
• Innodb_buffer_pool_size: If you do not use the InnoDB storage engine, you can not adjust this parameter, if necessary, in memory, if possible to store all the InnoDB data files as in memory, but also the "bigger the better"
• Innodb_additional_mem_pool_size: General database recommended adjustment to 8MB~16MB, if the table is particularly large, can be adjusted to 32MB, you can determine whether you need to increase the information in error log
• Innodb_log_buffer_size: The default is 1MB, as the system with frequent systems can be increased appropriately to 4MB~8MB. Of course, as described above, this parameter is actually related to the additional flush parameter. Generally not recommended more than 32MB
• innodb_max_dirty_pages_pct: According to previous experience, restarting the recovered data if it is more than 1GB, the boot speed will be relatively slow, almost unacceptable, so it is less than 1gb/innodb_buffer_pool_size (GB) * 100 this value. Of course, if you can tolerate a long boot time and want to minimize memory to disk flush, you can adjust this value to 90, but not more than 90

MySQL tuning of server parameters for head-to-toe optimization