mysql 慢查询排查:
1。查看服务器性能,cpu内存,磁盘情况等
2。查看mysql慢查询日志。
查看慢查询日志:
可看看mysql是否开启慢查询记录:
mysql> show variables like "slow_query%";
+---------------------+----------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/imp-WebService-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)
以上没有开启可以设置开启。以及修改log文件路径
mysql> set global slow_query_log='ON';
如果不想要文件的形式,修改到表记录日志,可修改
mysql> show variables like 'log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
log_output | TABLE | 默认为file
超过以下长时间查询的被记录下来,默认为10s
mysql> show variables like'long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
查看日志
mysql> use mysql
Database changed
mysql> select * from slow_log;