Optimize the MySQL server based on the status information

There are many articles on the Internet that teach you how to configure a MySQL server. However, considering the differences in server hardware configurations and specific applications, the practices in these articles can only be used as a reference for preliminary setup, we need to optimize the configuration according to our own situation. The good practice is that the MySQL server runs stably for a period of time and is optimized according to the "status" of the server.

There are many articles on the Internet that teach you how to configure a MySQL server. However, considering the differences in server hardware configurations and specific applications, the practices in these articles can only be used as a reference for preliminary setup, we need to optimize the configuration according to our own situation. The good practice is that the MySQL server runs stably for a period of time and is optimized according to the "status" of the server.

Mysql> show global status;

You can list the running status values of the MySQL server. In addition, the statement for querying the configuration information of the MySQL server is as follows:

Mysql> show variables;

1. Slow Query

Mysql> show variables like '% slow % ';
+ ------ + --- +
| Variable_name | Value |
+ ------ + --- +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
+ ------ + --- +

Mysql> show global status like '% slow % ';
+ ------- + --- +
| Variable_name | Value |
+ ------- + --- +
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+ ------- + --- +

Slow query is enabled in the configuration. If the execution time exceeds 2 seconds, the query is slow. The system displays 4148 slow queries. You can analyze the slow query logs, find out the problematic SQL statement. The slow query time should not be too long. Otherwise, it is of little significance. It is best to set it to within 5 seconds. If you need a slow query in microseconds, you can patch MySQL :, remember to find the corresponding version.

Opening the slow query log may have a slight impact on the system. If your MySQL is a master-slave structure, you can consider opening the slow query log of one of the slave servers, in this way, you can monitor slow queries with little impact on system performance.

Ii. Number of connections

We often encounter "MySQL: ERROR 1040: Too connections". One is that the access traffic is indeed high and the MySQL server cannot resist it. In this case, we need to consider increasing the load on distributed reads from the server, the value of max_connections In the MySQL configuration file is too small:

Mysql> show variables like 'max _ connections ';
+ ------ + --- +
| Variable_name | Value |
+ ------ + --- +
| Max_connections | 256 |
+ ------ + --- +

The maximum number of connections on this MySQL server is 256, and then query the maximum number of connections on the server response:

Mysql> show global status like 'max _ used_connections ';
+ -------- + --- +
| Variable_name | Value |
+ -------- + --- +
| Max_used_connections | 245 |
+ -------- + --- +

In the past, the maximum number of connections on the MySQL server was 245, but the maximum number of connections on the server was 256. There should be no 1040 error. The ideal setting is:

Max_used_connections/max_connections * 100% ≈ 85%.

The maximum number of connections accounts for about 85% of the maximum number of connections. If the percentage is less than 10%, the maximum number of connections of the MySQL server is too high.

3. Key_buffer_size

Key_buffer_size is a parameter that has the greatest impact on the performance of the MyISAM table. The following configuration of a storage engine server with MyISAM as the main storage engine:

Mysql> show variables like 'key _ buffer_size ';
+ ------ + ---- +
| Variable_name | Value |
+ ------ + ---- +
| Key_buffer_size | 536870912 |
+ ------ + ---- +

Allocated MB of memory to key_buffer_size. Let's look at the usage of key_buffer_size:

Mysql> show global status like 'key _ read % ';
+ -------- + ----- +
| Variable_name | Value |
+ -------- + ----- +
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+ -------- + ----- +

There are a total of 27813678764 index read requests, 6798830 of which are not found in the memory to directly read the index from the hard disk, calculate the probability that the index does not hit the cache:

Key_cache_miss_rate = Key_reads/Key_read_requests * 100%

For example, the key_cache_miss_rate of the above data is 0.0244%, and only one direct read hard disk is available for 4000 index read requests, which is already very BT, key_cache_miss_rate is good at lower than 0.1% (each 1000 requests have a direct read hard disk). If key_cache_miss_rate is lower than 0.01%, excessive key_buffer_size allocation can be minimized.

The MySQL server also provides the key_blocks _ * parameter:

Mysql> show global status like 'key _ blocks_u % ';
+ -------- + ----- +
| Variable_name | Value |
+ -------- + ----- +
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543
+ -------- + ----- +

Key_blocks_unused indicates the number of unused cache clusters (blocks), and Key_blocks_used indicates the maximum number of blocks used. For example, on this server, all the caches are used, or key_buffer_size is increased, either it is a transitional index, and the cache is full. Ideal settings:

Key_blocks_used/(Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

Iv. Temporary table

Mysql> show global status like 'created _ tmp % ';
+ --------- + --- +
| Variable_name | Value |
+ --------- + --- +
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1, 1771587 |
+ --------- + --- +

Created_tmp_tables is added each time you create a temporary table. If you create a temporary table on the disk, Created_tmp_disk_tables is added. Created_tmp_files indicates the number of temporary file files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables/Created_tmp_tables * 100% <= 25%

For example, the above server Created_tmp_disk_tables/Created_tmp_tables * 100% = 1.20% should be quite good. Let's take a look at the configuration of temporary tables on the MySQL server:

Mysql> show variables where Variable_name in ('tmp _ table_size ', 'max _ heap_table_size ');
+ ------- + ---- +
| Variable_name | Value |
+ ------- + ---- +
| Max_heap_table_size | 268435456 |
| Tmp_table_size | 536870912 |
+ ------- + ---- +

Only temporary tables smaller than MB can be fully stored in the memory. If the memory limit is exceeded, the temporary hard disk table will be used.

V. Open Table

Mysql> show global status like 'open % tables % ';
+ ----- + --- +
| Variable_name | Value |
+ ----- + --- +
| Open_tables | 919 |
| Opened_table | 1951 |
+ ----- + --- +

Open_tables indicates the number of opened tables, and Opened_tables indicates the number of opened tables. If the number of Opened_tables is too large, the value of table_cache (table_cache after 5.1.3 is called table_open_cache) in the configuration may be too small, let's query the server table_cache value:

Mysql> show variables like 'table _ cache ';
+ ----- + --- +
| Variable_name | Value |
+ ----- + --- +
| Table_cache | 2048 |
+ ----- + --- +

The appropriate values are:

Open_tables/Opened_tables * 100%> = 85%
Open_tables/table_cache * 100% <= 95%

Vi. Process usage

Mysql> show global status like 'thread % ';
+ ------- + --- +
| Variable_name | Value |
+ ------- + --- +
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+ ------- + --- +

If we set thread_cache_size In the MySQL server configuration file, after the client is disconnected, the thread on which the server processes this customer will be cached in response to the next customer rather than destroyed (provided that the number of caches has not reached the upper limit ). Threads_created indicates the number of created threads. If the value of Threads_created is too large, it indicates that the MySQL server has been creating threads, which is also resource-consuming. You can increase the value of thread_cache_size in the configuration file as appropriate, query the server thread_cache_size Configuration:

Mysql> show variables like 'thread _ cache_size ';
+ ------- + --- +
| Variable_name | Value |
+ ------- + --- +
| Thread_cache_size | 64 |
+ ------- + --- +

The server in the example is healthy.

VII. query cache)

Mysql> show global status like 'qcache % ';
+ --------- + ---- +
| Variable_name | Value |
+ --------- + ---- +
| Qcache_free_blocks | 1, 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 1, 111212 |
+ --------- + ---- +

Explanation of cache variable query in MySQL:

Qcache_free_blocks: Number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block.
Qcache_free_memory: idle memory in the cache.
Qcache_hits: increases when a query hits the cache.
Qcache_inserts: It increases every time a query is inserted. By dividing the number of hits by the number of inserts, This is the ratio of no hits.
Qcache_lowmem_prunes: the cache has insufficient memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is)
Qcache_not_cached: the number of queries that are not suitable for caching. It is generally because these queries are not SELECT statements or use functions such as now.
Qcache_queries_in_cache: number of queries (and responses) cached currently.
Qcache_total_blocks: Number of cached blocks.

Let's query the server's query_cache Configuration:

Mysql> show variables like 'query _ cache % ';
+ ---------- + ---- +
| Variable_name | Value |
+ ---------- + ---- +
| Query_cache_limit | 2097152 |
| Query_cache_min_res_unit | 4096 |
| Query_cache_size | 203423744 |
| Query_cache_type | ON |
| Query_cache_wlock_invalidate | OFF |
+ ---------- + ---- +

Description of each field:

Query_cache_limit: queries exceeding this size will not be cached.
Query_cache_min_res_unit: Minimum cache block size
Query_cache_size: query the cache size
Query_cache_type: cache type, which determines the type of queries to be cached. In this example, select SQL _no_cache query is not cached.
Query_cache_wlock_invalidate: when another client is performing a write operation on the MyISAM table, if the query is in the query cache, whether to return the cache result or wait until the write operation is complete and then read the table to obtain the result.

The configuration of query_cache_min_res_unit is a double-edged sword. The default value is 4 kb. Setting a large value is good for big data queries. However, if all your queries are small data queries, this can easily cause memory fragmentation and waste.

Query cache fragmentation rate = Qcache_free_blocks/Qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use flush query cache to sort out the CACHE fragmentation, or try to reduce query_cache_min_res_unit, if your QUERY is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size * 100%

If the query cache utilization is below 25%, the query_cache_size setting is too large and can be appropriately reduced. If the query cache utilization is above 80% and Qcache_lowmem_prunes> 50, the query_cache_size may be small, or too many fragments.

Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits * 100%

The cache fragmentation rate of the sample server is 20.46%, the query cache utilization is 62.26%, the query cache hit rate is 1.94%, And the hit rate is very low. It is possible that write operations are frequent and there may be some fragments.

8. Sorting usage

Mysql> show global status like 'sort % ';
+ ------- + ---- +
| Variable_name | Value |
+ ------- + ---- +
| Sort_merge_passes | 29 |
| Sort_range | 37432840 |
| Sort_rows | 9178691532 |
| Sort_scan | 1860569 |
+ ------- + ---- +

Sort_merge_passes consists of two steps. MySQL first tries to sort data in the memory. The memory size is determined by the system variable Sort_buffer_size. If the memory size is insufficient, all the records will be read to the memory, mySQL stores the sorting results in the memory to a temporary file. After MySQL finds all the records, it sorts the records in the temporary file. Sort_merge_passes will be added when sorting again. In fact, MySQL uses another temporary file to store the result of re-sorting. Therefore, we usually see that the Sort_merge_passes increase by twice the number of created temporary files. Because temporary files are used, the speed may be slow. Increasing Sort_buffer_size will reduce the number of times Sort_merge_passes and temporary files are created. However, blindly increasing Sort_buffer_size does not necessarily increase the speed. See How fast can you sort data with MySQL? (Reference, looks like a wall)

In addition, adding the value of read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) also has some advantages for sorting operations. See:

9. Number of opened files (open_files)

Mysql> show global status like 'open _ files ';
+ ----- + --- +
| Variable_name | Value |
+ ----- + --- +
| Open_files | 1410 |
+ ----- + --- +

Mysql> show variables like 'open _ files_limit ';
+ ------ + --- +
| Variable_name | Value |
+ ------ + --- +
| Open_files_limit | 4590 |
+ ------ + --- +

Suitable settings: Open_files/open_files_limit * 100% <= 75%

10. Table locks

Mysql> show global status like 'table _ locks % ';
+ -------- + ---- +
| Variable_name | Value |
+ -------- + ---- +
| Table_locks_immediate | 490206328 |
| Maid | 2084912 |
+ -------- + ---- +

Table_locks_immediate indicates the number of table locks to be released immediately, Table_locks_waited indicates the number of table locks to wait. If Table_locks_immediate/Table_locks_waited> 5000, InnoDB engine is recommended because InnoDB is a row lock and MyISAM, innoDB provides better performance for applications with high concurrent writes. In the example, the server Table_locks_immediate/Table_locks_waited = 235, and MyISAM is enough.

11. Table Scanning

Mysql> show global status like 'handler _ read % ';
+ -------- + ----- +
| Variable_name | Value |
+ -------- + ----- +
| Handler_read_first | 5803750 |
| Handler_read_key | 6049319850
| Handler_read_next | 94440908210 |
| Handler_read_prev | 34822001724 |
| Handler_read_rnd| 405482605 |
| Handler_read_rnd_next | 1, 18912877839 |
+ -------- + ----- +

For explanations of each field, see the number of query requests completed by the server:

Mysql> show global status like 'com _ select ';
+ ----- + ---- +
| Variable_name | Value |
+ ----- + ---- +
| Com_selected | 222693559 |
+ ----- + ---- +

Calculate the table scan rate:

Table scan rate = Handler_read_rnd_next/Com_select

If the scanning rate of a table exceeds 4000, too many table scans are performed. It is very likely that the index has not been created. Increasing the value of read_buffer_size may be advantageous, but it is best not to exceed 8 MB.

Refer to the following webpage:

1. http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.htm
2. http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
3. http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html
4. The specific value of the http://www.day32.com/MySQL/tuning-primer.sh mainly refer to this tool