slow

105次阅读

共计 3488 个字符，预计需要花费 9 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 slow_log 查看工具怎么用，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

pt-query-digest
yum install perl-IO-Socket-SSL
rpm -ivh percona-toolkit-2.2.9-1.noarch.rpm

执行
[root@localhost mysql]# pt-query-digest mysql-slow.log

# 770ms user time, 140ms system time, 31.97M rss, 221.28M vsz

用户时间，系统时间，物理内存占用大小，虚拟内存占用大小

# Current date: Wed Sep 7 16:53:57 2016
# Hostname: localhost.localdomain
# Files: mysql-slow.log
# Overall: 154 total, 61 unique, 0.00 QPS, 0.00x concurrency _____________

sql 总数量，唯一语句数量，QPS，并发数

# Time range: 2015-05-06 15:00:48 to 2016-09-06 17:38:29
# Attribute total min max avg 95% stddev median

属性总计最小最大平均 95% 标准差中位数

# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 8216s 10s 1377s 53s 159s 139s 19s
# Lock time 25s 0 23s 164ms 65ms 2s 185us 锁占用时间
# Rows sent 33.38M 0 3.46M 221.94k 1.86M 652.95k 0.99 发送到客户端
# Rows examine 100.90M 0 3.46M 670.94k 1.86M 833.96k 257.78k Row 扫描
# Query size 992.64k 6 955.28k 6.45k 592.07 73.52k 65.89 查询大小
# Profile
# Rank Query ID Response time Calls R/Call V/M Item

排名语句的 ID 响应时间查询执行次数平均响应时间响应时间 Variance-to-mean 的比率查询语句一部分

# ==== ================== =============== ===== ========= ===== ==========
# 1 0xC81C455C1E9447FB 1426.6461 17.4% 2 713.3230 12… SELECT dtbm.bus_detail
# 2 0x813031B8BBC3B329 1379.1050 16.8% 31 44.4873 29.16 COMMIT
# 3 0x67A347A2812914DF 1373.7824 16.7% 22 62.4447 89.27 SELECT bus_log
# 4 0xFD74C492E522AFEE 1014.0772 12.3% 1 1014.0772 0.00 SELECT bus_detail
# 5 0x5AF43D27D48CAAEA 507.6413 6.2% 18 28.2023 7.48 SELECT vote_timer_stats
# 6 0xF1E653C67605EF34 334.0602 4.1% 8 41.7575 27.54 INSERT vote_timer_stats
# 7 0x9685CBA24DA36B98 195.7007 2.4% 1 195.7007 0.00 SELECT jslt?
# 8 0x012092C92E2E8911 169.6964 2.1% 1 169.6964 0.00 SELECT jslt?
# 9 0x1D3699BBA7B0BAF8 165.5122 2.0% 1 165.5122 0.00 SELECT jslt?
# 10 0x1089E98FF0D37139 163.4763 2.0% 1 163.4763 0.00 SELECT jslt?
# 11 0xFD0B3ADDAD1B7133 162.9314 2.0% 1 162.9314 0.00 SELECT bus_detail
# 12 0x9C6BA968B6CCA955 108.2027 1.3% 6 18.0338 3.41 SELECT jslt?
# 13 0x7C9FA49760CB1B7D 84.9871 1.0% 6 14.1645 0.06 SELECT INFORMATION_SCHEMA.TABLES
# 14 0x4252FC2C0B3A0603 80.0699 1.0% 1 80.0699 0.00 SELECT yd_mcp_rdo_order bus_detail customer
# 15 0x5CBA2034458B5BC9 59.3694 0.7% 2 29.6847 0.00 SHOW DATABASES
# 16 0x7D6AE5DB33F47D2F 58.2366 0.7% 3 19.4122 0.07 SELECT jslt?
# 17 0xF7ED71B4D0D8BE1A 54.0662 0.7% 1 54.0662 0.00 SELECT jslt?
# 18 0x0C190526F0AC214C 52.3847 0.6% 2 26.1923 3.71 SELECT jslt?
# 19 0xEF8B0940922A6042 51.2116 0.6% 3 17.0705 0.06 SELECT jslt?
# 20 0x3C332EAC8DB54FD3 45.6802 0.6% 1 45.6802 0.00 SELECT information_schema.KEY_COLUMN_USAGE
# MISC 0xMISC 729.2764 8.9% 42 17.3637 0.0 41 items=

每条查询语句的分析
# Query 1: 0.00 QPS, 0.99x concurrency, ID 0xC81C455C1E9447FB at byte 1042056
# This item is included in the report because it matches –limit.
# Scores: V/M = 1233.79
# Time range: 2016-08-03 17:58:09 to 18:22:05
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 2
# Exec time 17 1427s 50s 1377s 713s 1377s 938s 713s
# Lock time 0 364us 177us 187us 182us 187us 7us 182us
# Rows sent 0 83 0 83 41.50 83 58.69 41.50
# Rows examine 2 2.38M 469.63k 1.92M 1.19M 1.92M 1.04M 1.19M
# Query size 0 140 70 70 70 70 0 70
# String:
# Databases dtbm
# Hosts 30.97.8.103
# Users root
# Query_time distribution 查询的执行时间分布情况图
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `dtbm` LIKE bus_detail \G
# SHOW CREATE TABLE `dtbm`.`bus_detail`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM dtbm.bus_detail order by create_time desc
LIMIT 0, 50000\G