Clean Blog

海阔凭鱼跃,天高任鸟飞

Mysql调优

参考:https://gist.github.com/jhjguxin/6266380

查看MySQL服务器运行的各种状态值

mysql> show global status;

查看MySQL服务器配置信息

mysql> show variables;

slow log ==&&== general log

mysql> show variables like "slow%";
+---------------------+--------------------------------------------------------+
| Variable_name       | Value                                                  |
+---------------------+--------------------------------------------------------+
| slow_launch_time    | 2                                                      |
| slow_query_log      | OFF                                                    |
| slow_query_log_file | /usr/local/mysql/data/iZbp168cjq900vx3mo49rnZ-slow.log |
+---------------------+--------------------------------------------------------+
3 rows in set (0.03 sec)

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 2882  |
+---------------------+-------+
2 rows in set (0.06 sec)

mysql> show variables like 'general_%';
+------------------+---------------------------------------------------+
| Variable_name    | Value                                             |
+------------------+---------------------------------------------------+
| general_log      | OFF                                               |
| general_log_file | /usr/local/mysql/data/iZbp168cjq900vx3mo49rnZ.log |
+------------------+---------------------------------------------------+
2 rows in set (0.04 sec)

mysql> set global slow_query_log = On;
Query OK, 0 rows affected (0.03 sec)

mysql> 
慢sql可以定位一些有性能问题的sql, 但是 general log 会记录所有的sql,因此一般情况下general log 不会长期开启,仅在需要调查sql情况下开启排错。

连接数

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+

设置的最大连接数是500,而响应的连接数是498

max_used_connections / max_connections * 100% = 99.6% (理想值 ≈ 85%)

mysql线程池

进程使用情况
mysql> show status like 'threads_%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Threads_cached    | 0       |
| Threads_connected | 41      |
| Threads_created   | 1562605 |
| Threads_running   | 1       |
+-------------------+---------+
4 rows in set (0.07 sec)

mysql> show variables like "thread_cache_size";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     |
+-------------------+-------+
1 row in set (0.04 sec)

mysql> set global thread_cache_size = 20;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like "thread_cache_size";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 20    |
+-------------------+-------+
1 row in set (0.04 sec)

如果我们在MySQL服务器配置文件中设置了thread_cache_size,当客户端断开之后,服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。
Threads_created表示创建过的线程数,如果发现Threads_created值过大的话,表明 MySQL服务器一直在创建线程,这也是比较耗资源,可以适当增加配置文件中thread_cache_size值,查询服务器 thread_cache_size配置: