Use status information to optimize the MySQL server (1)

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 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: http://www.percona.com/docs/wiki/release:start.

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%