pt-query-digest是最应该掌握的一个工具。它可以分析MySQL的各种日志,如慢查询日志、generel日志,也可以分析show processlist的输出。配合tcpdump我们还可以对线上数据库流量进行采样,实时监控数据库流量,及时发现性能问题。
基本用法如下,
pt-query-digest /path/to/slow.log > /path/to/keep/report_file
如果你有大量的数据库节点,可以考虑把pt-query-digest的分析报告写入数据库,方便检索和绘图。
输出的报表类似如下,以下截取了报告的部分内容,
# Overall: 565 total, 22 unique, 0.00 QPS, 0.00x concurrency _____________
# Time range: 2012-09-22 18:33:43 to 2012-10-16 10:45:31
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1233s 503ms 15s 2s 7s 2s 1s
# Lock time 53ms 31us 145us 94us 119us 17us 93us
# Rows sent 1.67k 0 20 3.02 9.83 4.12 0.99
# Rows examine 616.77M 72.90k 12.03M 1.09M 6.61M 2.02M 245.21k
# Query size 139.49k 25 381 252.81 346.17 70.94 234.30
# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M Item
# ==== ================== ============== ===== ======= ==== ===== ========
# 1 0xBE5D289C750F172A 308.6929 25.0% 40 7.7173 0.00 0.08 SELECT ccc tbl_eee tbl_ddd bbb
# 2 0x5C898C5E065DD204 149.4144 12.1% 105 1.4230 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 3 0x6F05415421300718 136.7381 11.1% 97 1.4097 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 4 0x2E9AE41A4D2149A1 123.0681 10.0% 22 5.5940 0.00 0.02 SELECT ccc tbl_eee tbl_ddd bbb
# 5 0xAFF556BC27138443 121.9603 9.9% 73 1.6707 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 6 0xD07F224EF598BD9A 105.0456 8.5% 16 6.5653 0.00 0.23 SELECT ccc tbl_eee tbl_ddd bbb
# 7 0xC22F9709F846BB4E 99.1936 8.0% 73 1.3588 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 8 0x4CAD792BF4A54CE9 53.7477 4.4% 4 13.4369 0.00 0.17 SELECT tbl_fff tbl_eee tbl_ddd
# 9 0x347319A37AC29893 39.1390 3.2% 69 0.5672 1.00 0.00 SELECT tbl_fff pt_game_base_score
# 10 0x7EF77B274F1C37D3 27.2826 2.2% 4 6.8207 0.00 0.00 SELECT ccc tbl_eee tbl_ddd bbb
# 11 0x8383B2CB219358F3 16.7553 1.4% 18 0.9308 0.97 0.00 SELECT tbl_iii tbl_hhh tbl_eee
# MISC 0xMISC 51.5793 4.2% 44 1.1723 NS 0.0 <11 ITEMS>
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0xBE5D289C750F172A at byte 120071
# This item is included in the report because it matches –limit.
# Scores: Apdex = 0.00 [1.0]*, V/M = 0.08
# Query_time sparkline: | ^_|
# Time range: 2012-09-25 11:01:09 to 2012-10-16 10:14:31
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 40
# Exec time 25 309s 7s 10s 8s 9s 802ms 7s
# Lock time 6 4ms 59us 110us 89us 103us [……]
Read more