慢查询与pt-query-digest使用

如果你发现系统中mysql的cpu占用很高，有可能是系统上有慢查询，通过mysql的慢查询确认嫌疑sql非常有必要

怎么查慢查询?

– 查看慢查询配置
show VARIABLES LIKE ‘slow%’;

slow_launch_time    2
slow_query_log    ON
slow_query_log_file     /alidata/server/mysql/data/1.log

– 多长时间的会被记录为慢查询
SHOW VARIABLES LIKE ‘long_query_time’;

– 设置慢查询的参数
set global slow_query_log = ON;
set global long_query_time = 5;
set global slow_query_log_file = ‘/alidata/server/mysql/data/1.log’;

找到慢查询的 服务器路径，
比如：
tail -11f /alidata/server/mysql/data/1.log

# Time: 170217 22:25:46
# User@Host: root[root] @  [223.73.113.210]
# Query_time: 9.048088  Lock_time: 0.000093 Rows_sent: 8964  Rows_examined: 2809648
SET timestamp=1487341546;
SELECT * FROM mtest where major_name='商务英语';
# Time: 170217 22:28:25
# User@Host: root[root] @  [223.73.113.210]
# Query_time: 8.462414  Lock_time: 0.000102 Rows_sent: 8964  Rows_examined: 2809648
SET timestamp=1487341705;
SELECT * FROM mtest where major_name='商务英语';
# Time: 170217 22:31:44
# User@Host: root[root] @  [223.73.113.210]
# Query_time: 9.137539  Lock_time: 0.000080 Rows_sent: 8964  Rows_examined: 2809648
SET timestamp=1487341904;
SELECT * FROM mtest where major_name='商务英语';

针对 查出来的慢查询进行优化配置，比如 缺少索引了

tp-query-digest 怎么安装?

tp-query-digest 是 Percona Toolkit 的其中一个工具,官网在https://www.percona.com/downloads/percona-toolkit/

这边服务器是ubuntu ，下载deb包安装
sudo dpkg -i percona-toolkit.deb，有不少依赖，一个个搞定

perl 要5.20,但服务器是 5.14……..
更新perl,试了2个多钟还是搞不定，最后，下载了低版本Percona Toolkit, percona-toolkit_2.0.3_all.deb

用tp-query-digest 怎么使用?

pt-query-digest /alidata/server/mysql/data/1.log

# 250ms user time, 20ms system time, 21.62M rss, 66.34M vsz
# Current date: Sat Feb 18 17:30:59 2017
# Hostname: iZ28gy61tjeZ
# Files: /alidata/server/mysql/data/1.log
# Overall: 102 total, 1 unique, 0.09 QPS, 0.84x concurrency ______________
# Time range: 2017-02-18 14:28:23 to 14:46:50
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           925s      9s     14s      9s      9s   784ms      8s
# Lock time            9ms    70us   130us    84us   103us    11us    80us
# Rows sent        892.53k   8.38k   8.75k   8.75k   8.46k   40.66   8.46k
# Rows examine     273.25M   2.62M   2.68M   2.68M   2.62M    0.04   2.62M
# Query size         5.08k      51      51      51      51       0      51

# Profile
# Rank Query ID           Response time   Calls R/Call Apdx V/M   Item
# ==== ================== =============== ===== ====== ==== ===== ========
#    1 0x07B4534FA3E77537 925.4291 100.0%   102 9.0728 0.00  0.07 SELECT mtest

# Query 1: 0.09 QPS, 0.84x concurrency, ID 0x07B4534FA3E77537 at byte 21684
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.00 [1.0], V/M = 0.07
# Query_time sparkline: |      ^_|
# Time range: 2017-02-18 14:28:23 to 14:46:50
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100     102
# Exec time    100    925s      9s     14s      9s      9s   784ms      8s
# Lock time    100     9ms    70us   130us    84us   103us    11us    80us
# Rows sent    100 892.53k   8.38k   8.75k   8.75k   8.46k   40.66   8.46k
# Rows examine 100 273.25M   2.62M   2.68M   2.68M   2.62M    0.04   2.62M
# Query size   100   5.08k      51      51      51      51       0      51
# String:
# Databases    channel
# Hosts
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  ##
# Tables
#    SHOW TABLE STATUS FROM `channel` LIKE 'mtest'\G
#    SHOW CREATE TABLE `channel`.`mtest`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM mtest where major_name='商务英语'\G

后记

上面是工具分析的结果，可以做个参考,个人觉得 慢查询本身的日志已经能提供很多信息了，