MySQL slow query analysis under Linux Mysqlsla installation using

Source: Internet
Author: User
Tags mysql slow query log

 

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.