Optimize MySQL server based on status information

There are a lot of articles on the internet to teach how to configure the MySQL server, but given the different server hardware configuration, the specific application of the differences, the practice of those articles can only be used as a preliminary setting reference, we need to configure according to their own situation optimization, good practice is the MySQL server stable run for some time Optimized according to the status of the server.

mysql> show global status;

Can list the MySQL server running various status values, in addition, query the MySQL server configuration information statement:

mysql> show variables;

One, 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 turned on in the configuration, and the execution time is more than 2 seconds. The system shows that there are 4,148 slow queries, you can analyze the slow query log, find the problem of the SQL statements, slow query time should not be set too long, otherwise meaningless, preferably within 5 seconds, if you need microsecond level of other slow query, You can consider a patch for MySQL: Http://www.percona.com/docs/wiki/release:start, remember to find the corresponding version.

Open a slow query log may have a little impact on system performance, if your MySQL is the master-from structure, you can consider to open one of the slow query log from the server, so that you can monitor the slow query, the system can have little impact on.

Second, the number of connections

Often meet the "Mysql:error 1040:too many connections" situation, one is that the traffic is really high, MySQL server can not resist, this time to consider increasing the spread from the server read pressure, the other is the MySQL configuration file in the Max_ Connections value 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 the maximum number of connections to the server response is queried:

mysql> show global status like 'Max_used_connections';

MySQL server in the past the maximum number of connections is 245, did not reach the maximum number of server connections 256, should not appear 1040 errors, the more ideal setting is:

Max_used_connections / max_connections * 100% ≈ 85%

The maximum number of connections to the upper limit of 85% of the number of connections, if the ratio is found below 10%, MySQL server connection to the upper limit set too high.