Shulou(Shulou.com)05/31 Report--

This article is about how to optimize the MySQL server with status information. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are many articles on the Internet to teach how to configure the MySQL server, but considering the different hardware configuration of the server and the differences in specific applications, the practice of those articles can only be used as a preliminary setting reference. We need to optimize the configuration according to our own situation. The good practice is to run the MySQL server steadily for a period of time and optimize it according to the "status" of the server.

> show global status

You can list the various status values that the MySQL server is running. In addition, query the MySQL server configuration information statement:

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 opened in the configuration, the execution time is more than 2 seconds is the slow query, the system shows that there are 4148 slow queries, you can analyze the slow query log to find out the problematic SQL statements, slow query time should not be set too long, otherwise it is not meaningful, preferably within 5 seconds, if you need microsecond level slow query, you can consider making patches to MySQL: remember to find the corresponding version.

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

Second, the number of connections

We often encounter the situation of "MySQL: ERROR 1040: Too many connections". One is that the traffic is really high and the MySQL server cannot resist it. At this time, it is necessary to consider increasing the decentralized reading pressure from the server, and the other is that the max_connections value 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 for this MySQL server is 256, and then query the maximum number of connections that the server responded to:

Mysql > show global status like Max_used_connections

+-+ +

| | Variable_name | Value |

+-+ +

| | Max_used_connections | 245 |

+-+ +

The maximum number of connections to the MySQL server in the past is 245.It does not reach the server connection limit of 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 upper limit. If the proportion is found to be less than 10%, the maximum number of MySQL server connections is set too high.

III. Key_buffer_size

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

Mysql > show variables like key_buffer_size

+-+ +

| | Variable_name | Value |

+-+ +

| | key_buffer_size | 536870912 | |

+-+ +

After allocating 512MB memory to key_buffer_size, let's take a look at key_buffer_size usage:

Mysql > show global status like key_read%

+-+ +

| | Variable_name | Value |

+-+ +

| | Key_read_requests | 27813678764 |

| | Key_reads | 6798830 | |

+-+ +

There are 27813678764 index read requests, of which 6798830 are not found in memory to read the index directly from the hard disk. Calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%

For example, for the above data, there is only one direct read hard disk with a key_cache_miss_rate of 0.0244% and 4,000 index read requests, which is already very BT. The key_cache_miss_rate is good below 0.1% (there is one direct read hard disk for every 1000 requests). If the key_cache_miss_rate is less than 0.01%, the key_buffer_size allocation is too much and can be reduced appropriately.

The MySQL server also provides key_blocks_* parameters:

Mysql > show global status like key_blocks_u%

+-+ +

| | Variable_name | Value |

+-+ +

| | Key_blocks_unused | 0 | |

| | Key_blocks_used | 413543 | |

+-+ +

Key_blocks_unused represents the number of unused cache clusters (blocks), and Key_blocks_used indicates the maximum number of blocks ever used, such as this server, where all the cache is used, either adding key_buffer_size or transitional indexes, filling up the cache. Ideal settings:

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

IV. Temporary watch

Mysql > show global status like created_tmp%

+-+ +

| | Variable_name | Value |

+-+ +

| | Created_tmp_disk_tables | 21197 | |

| | Created_tmp_files | 58 | |

| | Created_tmp_tables | 1771587 | |

+-+ +

Each time a temporary table is created, Created_tmp_tables increases. If a temporary table is created on disk, Created_tmp_disk_tables also increases. Created_tmp_files represents the number of temporary files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% 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 below 256MB can be stored in memory, and hard disk temporary tables will be used if they are exceeded.

V. Open Table situation

Mysql > show global status like open%tables%

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 919 | |

| | Opened_tables | 1951 | |

+-+ +

Open_tables indicates the number of open tables, and Opened_tables indicates the number of opened tables. If the number of Opened_tables is too large, the value of table_cache (called table_open_cache after 5.1.3) 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 more appropriate values are:

Open_tables / Opened_tables * 100% > = 85%

Open_tables / table_cache * 100%

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.