nagios监控到bbs的数据库,有几千条的慢查询纪录,而且在不断的上涨
Uptime: 2665801 Threads: 9 Questions: 1483924522 Slow queries: 3833 Opens: 764180 Flush tables: 1 Open tables: 512 Queries per second avg: 556.652
所以需要开启慢查询的日志,查看一下到底是哪些sql语句引起的
一般是在/etc/f添加配置文件,然后重启数据库
log-slow-queries=/web/var/localhost-slow.log
long_query_time=2
log-queries-not-using-indexes
因为是线上的数据库,不能够随便重启,幸好mysql从5.1.6版本开始,general query log 和slow query log支持写到文件或者数据库表两种方式,并且日志的开启,输出方式的修改,都可以在global级别动态修改
mysql> show variables like '%slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /web/var/localhost-slow.log |
+---------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql>
mysql> set global log_slow_queries=ON ;
Query OK, 0 rows affected, 1 warning (0.46 sec)
mysql> show variables like '%slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /web/var/localhost-slow.log |
+---------------------+-----------------------------+
4 rows in set (0.00 sec)
开启了之后,就可以通过分析日志查看是哪些sql语句有问题,再进行优化