MySQL slow query analysis under Linux Mysqlsla installation using

 

Description

Operating system: CentOS 5.X 64-bit

MySQL version: mysql-5.5.35

MySQL configuration file:/etc/my.cnf

MySQL Database storage directory:/data/mysql

to achieve the purpose: turn on the MySQL Slow query log feature, install the use of MySQL slow query analysis Mysqlsla

Specific operation:

First, turn on the MySQL slow query function

Mysql-u root-p #进入MySQL控制台

Show variables like '%slow% '; #查看MySQL慢查询是否开启

Set global slow_query_log=on; #开启MySQL慢查询功能

Show variables like "Long_query_time"; #查看MySQL慢查询时间设置, default 10 seconds

Set global long_query_time=5; #修改为记录5秒内的查询

Select Sleep (6); #测试MySQL慢查询

Show variables like "%slow%"; #查看MySQL慢查询日志路径

Show global status like '%slow% '; #查看MySQL慢查询状态

System operation and maintenance www.osyunwei.com warm reminder: qihang01 original Content © Copyright, reproduced please indicate the source and the original chain

Exit #退出MySQL控制台

Cat/data/mysql/127-slow.log #查看MySQL慢查询日志是否被记录

Note: You can also turn on MySQL slow query by modifying the MySQL configuration file parameters.

VI/ETC/MY.CNF #编辑, add the following code in the [Mysqld] Segment

Slow-query-log = on #开启MySQL慢查询功能

Slow_query_log_file =/data/mysql/127-slow.log #设置MySQL慢查询日志路径

Long_query_time = 5 #修改为记录5秒内的查询, this parameter is not set by default for 10 seconds in the record query

Log-queries-not-using-indexes = on #记录未使用索引的查询

: wq! #保存退出

Service mysqld Restart #重启MySQL服务

Second, the installation uses the MySQL slow query analysis tool Mysqlsla

1, installation Mysqlsla

Yum install wget perl perl-dbi perl-dbd-mysql MySQL #执行此命令安装依赖包

CD/USR/LOCAL/SRC #进入软件包存放目录

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz #下载mysqlsla

Tar zxvf mysqlsla-2.03.tar.gz #解压

CD mysqlsla-2.03 #进入安装目录

System operation and maintenance www.osyunwei.com warm reminder: qihang01 original Content © Copyright, reproduced please indicate the source and the original chain

Perl makefile.pl #配置

Make #编译

Make install #安装

2. Using Mysqlsla to analyze the MySQL slow query log

#查询记录最多的20个sql语句, and write it into the select.log.

Mysqlsla-lt Slow--sort t_sum--top 20/data/mysql/127-slow.log >/tmp/select.log

#统计慢查询文件为 slow Query SQL for all select/data/mysql/127-slow.log, and displays the longest execution time of 100 SQL and writes to Sql_select.log

Mysqlsla-lt slow-sf "+select"-top 100/data/mysql/127-slow.log >/tmp/sql_select.log

#统计慢查询文件为/data/mysql/127-slow.log Database for all MyData Select and update slow query SQL, and queries up to 100 SQL, and write to Sql_num.sql

Mysqlsla-lt slow-sf "+select,update"-top 100-sort c_sum-db mydata/data/mysql/127-slow.log >/tmp/sql_num.log

At this point, Linux under MYSQ

MySQL slow query analysis under Linux Mysqlsla installation using