mysql slow sql 配置_mysql-普通查询(General Query)慢查询(Slow Query)相关日志配置

配置

配置方法一: 服务启动时

# vi /etc/my.cnf

...

log-output=TABLE,FILE

general-log=1

slow-query-log=1

# systemctl restart mysqld

log-output默认是FILE,还有个值是NONE,就不输出日志了.我这里演示的是表和日志文件都输出.

配置方法二: 运行时

mysql> set global log_output="table", global general_log=on, global slow_query_log=on;

查看

查看相关变量:

mysql> show variables like 'general%';

+------------------+--------------------------+

| Variable_name | Value |

+------------------+--------------------------+

| general_log | ON |

| general_log_file | /var/lib/mysql/srv00.log |

+------------------+--------------------------+

2 rows in set (0.00 sec)

mysql> show variables like 'slow%';

+---------------------+-------------------------------+

| Variable_name | Value |

+---------------------+-------------------------------+

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /var/lib/mysql/srv00-slow.log |

+---------------------+-------------------------------+

3 rows in set (0.00 sec)

相关日志文件:

general_log_file和slow_query_log_file变量指示的文件,可以按需要进行修改

日志相关的表:

mysql> select * from mysql.general_log;

mysql> select * from mysql.slow_log;

维护

日志文件

mysql> set global general_log=off;

mysql> \! mv /var/lib/mysql/srv00.log /var/lib/mysql/srv00.log.bak

mysql> set global general_log=on;

或者:

mv /var/lib/mysql/srv00.log /var/lib/mysql/srv00.log.bak

mv /var/lib/mysql/srv00-slow.log /var/lib/mysql/srv00-slow.log.bak

mysqladmin flush-logs

修改日志表原理也是一样,先暂停,维护表,再启用

slow query log相关参数

mysql> show variables like 'long%';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.01 sec)

超过这个秒数的慢查询才记录

mysql> show variables like 'min%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| min_examined_row_limit | 0 |

+------------------------+-------+

1 row in set (0.00 sec)

返回记录数超过才记录

mysql> show variables like 'log_slow_admin%';

+---------------------------+-------+

| Variable_name | Value |

+---------------------------+-------+

| log_slow_admin_statements | OFF |

+---------------------------+-------+

1 row in set (0.00 sec)

是否记录管理类型的sql, 包括:ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE.

mysql> show variables like '%not_using_indexes';

+----------------------------------------+-------+

| Variable_name | Value |

+----------------------------------------+-------+

| log_queries_not_using_indexes | OFF |

| log_throttle_queries_not_using_indexes | 0 |

+----------------------------------------+-------+

2 rows in set (0.01 sec)

没有使用索引的sql是否要记录,如果开启会产生很多记录,log_throttle_queries_not_using_indexes设置每分钟在此范围内只记录一次.

slow query log 分析

使用工具mysqldumpslow

熟悉oracle的可以认为mysqldumpslow是oracle的tkprof

总结

当然开启都会对服务器资源消耗.只在需要的时候开启,不用的时候关掉.

//END