Installing the Mysqlsla Performance analysis tool

Turn on MySQL slow query log

Vi/etc/my.cnf

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服务

Installing Mysqlsla

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

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

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

CD mysqlsla-2.03 #进入安装目录

Perl makefile.pl #配置

Make #编译

Make install #安装

, 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

Installing the Mysqlsla Performance analysis tool