mysql开启全局日志，通过每秒sql语句执行次数分析系统性能瓶颈

打开mysql慢查询日志

show variables like '%slow%';
SET GLOBAL slow_query_log ='ON';
set global slow_launch_time =0.2; 重启生效 单位秒
FLUSH SLOW LOGS;

打开mysql全局日志

show variables like 'log_output';
SET GLOBAL slow_query_log ='on';

将日志输出到表，方便查询

show variables like 'log_output';
set global log_output='TABLE';

修改输出表类型为myisam提升查询性能

show create table mysql.slow_log;

SET GLOBAL slow_query_log ='off';
SET GLOBAL slow_query_log ='off';
alter table mysql.slow_log   engine=myisam;
alter table mysql.general_log   engine=myisam;

SET GLOBAL slow_query_log ='on';

SET GLOBAL general_log  ='on';

查询日志

select * from mysql.general_log

清理日志表
truncate table mysql.general_log

sql统计语句

select
event_time,substring(argument,1,35) as sqltxt,count(*) as cnt
from  mysql.general_log
where event_time>='2016-03-27 12:01:13'
group by substring(argument,1,35) ,event_time
order by event_time,sqltxt

© 2016, 新之助meow. 原创文章转载请注明： 转载自http://www.xinmeow.com