mysqlsla是hackmysql.com推出的一款MySQL的日志分析工具,可以分析mysql的慢查询日志、分析慢查询非常好用,能针对库分析慢查询语句的执行频率、扫描的数据量、消耗时间等,而且分析出来以后还有语句范例,这是mysqldba必不可少的工具之一.接下来就来详细介绍一下mysqlsla慢查询日志分析工具的安装使用方法.
- 1. 下载mysqlsla慢查询分析工具
地址:http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz # cd /usr/local/src/tarbag/ # wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
- 2、mysqlsla慢查询分析工具安装
# tar -xzvf mysqlsla-2.03.tar.gz # cd mysqlsla-2.03 # perl Makefile.PL // 需要安装perl语言 # make # make install
- 3. 查看帮助
mysqlsla慢查询分析工具与其他软件不同,看他的帮助只能使用man mysqlsla,不能使用mysqlsla --help或者mysqlsla -h文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
- 4. mysqlsla慢查询分析工具命令
# Basic operation: parse a MySQL slow or general log mysqlsla --log-type slow LOG mysqlsla --log-type general LOG # Parse output from mysqlbinlog # mysqlsla cannot directly parse binary logs mysqlbinlog LOG │ mysqlsla --log-type binary - # Parse a microslow patched slow log mysqlsla --log-type msl LOG # Replay a replay file mysqlsla --replay FILE # Parse a user-defined log specify its format mysqlsla --log-type udl --udl-format FILE # Let mysqlsla automatically determine the log type mysqlsla LOG
一般来说分析mysql慢查询只用到第一个命令,mysqlsla --log-type slow LOG文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
- 4、mysqlsla慢查询分析工具使用
# mysqlsla --log-type slow mysql-slow.log //mysql-slow.log 为对应的mysql慢查询日志.文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
Report for slow logs: mysql-slow.log
4.65k queries total, 8 unique
Sorted by 't_sum'
Grand Totals: Time 146.91k s, Lock 5.37k s, Rows sent 25.74k, Rows Examined 5.17G文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
______________________________________________________________________ 001 ___
Count : 3.13k (67.35%)
Time : 101904.863582 s total, 32.536674 s avg, 10.005459 s to 122.343236 s max (69.36%)
95% of Time : 87652.235055 s total, 29.462936 s avg, 10.005459 s to 78.079492 s max
Lock Time (s) : 3120.503518 s total, 996.329 ms avg, 49 o 52.777983 s max (58.06%)
95% of Lock : 296.754387 s total, 99.749 ms avg, 49 o 5.702285 s max
Rows sent : 1 avg, 1 to 1 max (12.17%)
Rows examined : 1.15M avg, 875.88k to 1.17M max (69.80%)
Database : test_ttlsa_com
Users :
test_ttlsa_com@ 192.168.1.24 : 82.50% (2584) of query, 82.13% (3819) of all users
test_ttlsa_com@ 192.168.1.20 : 17.50% (548) of query, 17.78% (827) of all users文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
Query abstract:
SET timestamp=N; SELECT * FROM tbl_number WHERE projectid = N AND gettime = N ORDER BY gettime DESC, id ASC LIMIT N;文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
Query sample:
SET timestamp=1374055035;
SELECT * FROM `tbl_number` WHERE `projectId` = 1644 AND `getTime` = 0 ORDER BY `getTime` DESC, `id` ASC LIMIT 1;文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
一般会显示前10条慢查询,如上结果是其中的一条,可以看出这个报表非常的直观,这就是我为什么选用mysqlsla慢查询分析工具的原因.文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
分析说明:
1. Count : 3.13k (67.35%)
本条SQL占所有mysql慢查询中67%,一共出现了3130条.显得非常多了文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
2. Time
一共耗费了101904秒,平均每条就消耗了29条,最小的10秒,最大的78秒.可以看出这个语句非常慢,忘告诉大家,我这边慢查询设置的是10秒,一般情况是设置为1-2秒文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
3. Row sent
查询返回1条,如果返回的条数非常多,那注意看下sql是不是未加limit.文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
4. Query abstract
SQL模型文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
5. Query sample
SQL样例文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
6. Users
哪些用户做了这个查询,占比是多少.文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
转载请注明出处:mysqlsla慢查询分析工具安装使用方法 - https://www.ttlsa.com/html/1673.html文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqlsla-mysql-slow-log-query-tools/
评论