pt-query-digest(percona 툴킷)

pt-query-digest는 로그, 프로세스 목록, tcpdump를 통해 MySQL 쿼리 관련 정보를 분석할 수 있습니다. 기본 구문은 다음과 같습니다.

pt-query-digest [OPTIONS] [FILES] [DSN]

pt-query-digest는 MySQL 쿼리를 분석하는 간단한 도구입니다. 이보다 더 사용하기 쉬운 도구는 없습니다. MySQL 느린 로그, 일반 LOG 및 바이너리 로그 쿼리를 분석할 수 있습니다. (바이너리 로그는 먼저 mysqlbinlog 도구를 통해 텍스트로 변환되어야 합니다.) 또한 tcpdump의 SHOW PROCESSLIST 및 MySQL 프로토콜 데이터와도 작동합니다. 기본적으로 도구는 어떤 쿼리가 가장 느린지 보고하므로 최적화하는 것이 가장 중요합니다. --group-by, --filter 및 --embedded-attributes와 같은 매개변수를 사용하여 보다 사용자 정의된 보고서를 생성할 수 있습니다.

pt-query-digest에는 주로 다음 기능이 있습니다.

(1) Slow.log를 사용하여 통계 정보 생성:

pt-query-digest slow.log

(2) 프로세스 목록에서 보고서 분석 및 생성:

pt-query-digest --processlist h=host1

(3) tcppdump 패킷 캡처를 통해 느린 쿼리 분석:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

(4) 다른 호스트에 대한 느린 로그 쿼리 분석:

pt-query-digest --review h=host2 --no-report slow.log

살펴보겠습니다. 주요 매개변수:

--유형의 기본값은 Slowlog이며, 매개변수 값은 binlog, genlog, Slowlog, tcpdump, rawlog 등으로 설정할 수 있습니다.

--processlist processlist를 통해 MySQL의 전체 로그 쿼리를 분석합니다.

--create-review-table --review 매개변수를 사용하여 분석 결과를 테이블에 출력할 때 테이블이 없으면 자동으로 생성됩니다.

--create-history-table --history 매개변수를 사용하여 분석 결과를 테이블로 출력할 때 테이블이 없으면 자동으로 생성됩니다.

--필터링은 지정된 문자열에 따라 입력 느린 쿼리를 일치시키고 필터링한 다음 분석합니다.

-limit는 출력 결과의 백분율 또는 수를 제한합니다. 기본값은 20이며, 이는 가장 느린 20개의 명령문이 출력됨을 의미합니다. .전체 응답 시간에 따라 50%를 큰 것부터 작은 것 순으로 정렬하고, 전체가 50%에 도달하면 출력을 차단합니다.

--host MySQL 서버 주소

--user mysql 사용자 이름

--password mysql 사용자 비밀번호

--history 분석 결과를 테이블에 저장하면 다음번에 --history를 사용합니다. 동일한 문장이 존재하고 쿼리의 시간 간격이 기록 테이블의 시간 간격과 다른 경우 동일한 CHECKSUM을 쿼리하여 특정 유형의 쿼리에 대한 기록 변경 사항을 비교할 수 있습니다.

--review 분석 결과를 테이블에 저장합니다. 이 분석은 쿼리 조건만 매개변수화합니다. 쿼리 유형 중 하나는 비교적 간단합니다. 다음에 --review를 사용할 때 동일한 구문 분석이 존재하면 데이터 테이블에 기록되지 않습니다.

--출력 분석 결과 출력 유형, 값은 보고서(표준 분석 보고서), Slowlog(Mysql 느린 로그), json, json-anon일 수 있으며 일반적으로 쉽게 읽을 수 있도록 보고서를 사용합니다.

--since는 분석이 시작된 이후의 시간입니다. 값은 "yyyy-mm-dd [hh:mm:ss]" 형식으로 지정된 시점일 수 있습니다. 간단한 시간 값: s(초), h(시간), m(분), d(일), 예를 들어 12h는 통계가 12시간 전에 시작되었음을 의미합니다.

--마감일까지 --since와 결합하면 일정 기간 내에 느린 쿼리를 분석할 수 있습니다.

기본 출력 보고서 관련 정보를 살펴보겠습니다.

(1) 데이터 통계 정보

# 2291.9s user time, 6.4s system time, 41.68M rss, 193.36M vsz
# Current date: Mon Jun 19 11:19:51 2017# Hostname: mxqmongodb2
# Files: /home/mysql/db3306/log/slowlog_343306.log
# Overall: 6.72M total, 140 unique, 16.12 QPS, 0.69x concurrency _________
# Time range: 2017-06-13T14:34:41 to 2017-06-18T10:22:04# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 287519s 1us 20s 43ms 148ms 339ms 214us
# Lock time 151259s 0 20s 23ms 144us 319ms 47us
# Rows sent 5.40M 0 1000 0.84 0.99 6.58 0.99# Rows examine 388.33M 0 3.72k 60.59 5.75 388.16 0.99# Query size 692.26M 6 799 108.02 202.40 69.96 80.10

위에 포함된 정보는 호스트 이름 호스트 이름, 전체 전체 쿼리, 고유 개별 쿼리, 분석입니다. time period 시간 범위, 속성 부분은 세 번째 부분과 동일하므로 최상의 분석을 위해 넣습니다

(2) 느린 쿼리 SQL 통계 결과 및 오버헤드 통계

# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================= ======= ====== ===== =========# 1 0x255C57D761A899A9 146053.6926 50.8% 75972 1.9225 2.93 UPDATE warehouse
# 2 0x813031B8BBC3B329 94038.9621 32.7% 242741 0.3874 0.23 COMMIT
# 3 0xA0352AA54FDD5DF2 10125.5055 3.5% 75892 0.1334 0.43 UPDATE order_line
# 4 0xE5E8C12332AD11C5 5660.5113 2.0% 75977 0.0745 0.83 SELECT district
# 5 0xBD195A4F9D50914F 3634.6219 1.3% 757760 0.0048 1.01 SELECT stock
# 6 0xF078A9E73D7A8520 3431.3527 1.2% 75874 0.0452 0.81 UPDATE district
# 7 0x9577D48F480A1260 2307.4342 0.8% 50255 0.0459 1.25 SELECT customer
# 8 0xFFDA79BA14F0A223 2158.4731 0.8% 75977 0.0284 0.54 SELECT customer warehouse
# 9 0x5E61FF668A8E8456 1838.4440 0.6% 1507614 0.0012 0.74 SELECT stock
# 10 0x10BEBFE721A275F6 1671.8274 0.6% 757751 0.0022 0.52 INSERT order_line
# 11 0x8B2716B5B486F6AA 1658.5984 0.6% 75871 0.0219 0.75 INSERT history
# 12 0xBF40A4C7016F2BAE 1504.7939 0.5% 758569 0.0020 0.77 SELECT item
# 13 0x37AEB73B59EFC119 1470.5951 0.5% 2838 0.5182 0.27 INSERT SELECT tpcc._stock_new tpcc.stock
# 15 0x26C4F579BF19956D 1030.4416 0.4% 1982 0.5199 0.28 INSERT SELECT tpcc.__stock_new tpcc.stock
# 22 0xD80B7970DBF2419C 493.0831 0.2% 947 0.5207 0.28 INSERT SELECT tpcc.__stock_new tpcc.stock
# 23 0xDE7EA4E363CAD006 488.2134 0.2% 943 0.5177 0.25 INSERT SELECT tpcc.__stock_new tpcc.stock
# 25 0x985B012461683472 470.6418 0.2% 907 0.5189 0.25 INSERT SELECT tpcc.__stock_new tpcc.stock
# MISC 0xMISC 9482.0467 3.3% 2182254 0.0043 0.0 <123 ITEMS>

정보에는 응답: 총계가 포함됩니다. 응답 시간, 시간 : 이 분석에서 해당 쿼리의 총 시간 비율입니다. 호출: 실행 횟수, 즉 이 분석에서 해당 유형의 쿼리 문의 총 개수입니다. R/Call: 실행당 평균 응답 시간입니다. 항목: SQL 연산 테이블.

(3) 세 번째 부분은 각 SQL의 상세 정보

# Query 1: 1.14 QPS, 2.19x concurrency, ID 0x255C57D761A899A9 at byte 1782619576# This item is included in the report because it matches --limit.
# Scores: V/M = 2.93# Time range: 2017-06-13T14:34:42 to 2017-06-14T09:05:56# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======# Count 1 75972# Exec time 50 146054s 160us 20s 2s 7s 2s 1s
# Lock time 94 142872s 39us 20s 2s 7s 2s 992ms
# Rows sent 0 0 0 0 0 0 0 0# Rows examine 0 74.19k 1 1 1 1 0 1# Query size 0 4.05M 53 57 55.88 56.92 0.82 54.21# String:
# Hosts 127.0.0.1# Users root
# Query_time distribution
# 1us
# 10us
# 100us ######################
# 1ms ##
# 10ms ###
# 100ms ##################################
# 1s ################################################################
# 10s+ ##
# Tables
# SHOW TABLE STATUS LIKE 'warehouse'\G
# SHOW CREATE TABLE `warehouse`\G
UPDATE warehouse SET w_ytd = w_ytd + 3651 WHERE w_id = 4\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/select w_ytd = w_ytd + 3651 from warehouse where w_id = 4\G

Query 1은 비용 측면에서 첫 번째 행이 테이블의 열 헤더입니다. Percent는 전체 분석 실행에 대한 합계의 백분율이고 total은 지정된 지표의 실제 값입니다. 예를 들어, 이 경우 쿼리가 75972번 실행되었음을 알 수 있는데, 이는 파일에 있는 전체 쿼리의 50%에 해당합니다. 최소, 최대 및 평균 열은 설명이 필요하지 않습니다. 95번째 백분위수 열은 95번째 백분위수를 표시하며, 값의 95%가 해당 값보다 작거나 같습니다. 표준 편차는 값이 얼마나 밀접하게 그룹화되어 있는지 보여줍니다. 표준 편차와 중앙값은 95번째 백분위수부터 계산되며 가장 큰 값과 가장 작은 값은 삭제됩니다.

일반적인 사용법을 살펴보겠습니다.

1: 느린 로그 분석

기본 보고서

[root@mxqmongodb2 bin]# ./pt-query-digest /home/mysql/db3306/log/slowlog_343306.log >/home/sa/slowlog_343306.log

시간별로 분류되며 일반적으로 하루의 느린 로그를 분석합니다.

[root@mxqmongodb2 bin]# ./pt-query-digest --since=24h /home/mysql/db3306/log/slowlog_343306.log >/home/sa/slowlog_343306_24.log

而且我们可以设置过滤条天通过--filter参数，更好生成我们想要的报表。

例如只查询select：--filter '$event->{arg} =~ m/^select/i'，只查询某个用户：--filter '($event->{user} || "") =~ m/^dba/i' ，全表扫描等：--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' 

2：保存分析结果到表文件：

[root@mxqmongodb2 bin]# ./pt-query-digest --user=root --password=123456 --port=3306 --review h=172.16.16.35,D=test,t=query_report /home/mysql/db3306/log/slowlog_343306.log

 

看一下结果样式

mysql> select * from query_report limit 1\G*************************** 1. row ***************************checksum: 1206612749604517366fingerprint: insert into order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) values(?+)
sample: INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3730, 6, 10, 1, 6657, 10, 8, 62.41910171508789, 'N3F5fAhga7U51tlXr8AEgZdi')
first_seen: 2017-06-13 14:34:42last_seen: 2017-06-14 09:05:54reviewed_by: NULL
reviewed_on: NULL
comments: NULL1 row in set (0.00 sec)

 

3：分析binlog（要先使用mysqlbinlog将binlog转换）

[root@mxqmongodb2 log]# mysqlbinlog mysql-bin.000012 >/home/sa/mysql-bin_000012.log
[root@mxqmongodb2 bin]# ./pt-query-digest --type=binlog /home/sa/mysql-bin_000012.log >/home/sa/mysql-bin_000012_report.log

 

这个测试的时候还是有点小迷茫的，因为打印的结果并不是我要的，难道是因为我的binlog格式是ROW？保留下来，后面在测试。

4：分析general log

加上--type=genlog 即可，没有验证。。。。。。

5：tcpdump抓包分析

我们先要开启压力测试：

[root@mxqmongodb2 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P3306 -d tpcc -u root -p123456 -w 10 -c 10 -r 10 -l 3000

 

连续测试三十分钟，提供我们的抓取数据：

[root@mxqmongodb2 log]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 10000 port 3306 >/home/sa/mysql.tcp.txt
[root@mxqmongodb2 bin]# ./pt-query-digest --type=tcpdump /home/sa/mysql.tcp.txt >/home/sa/mysql.tcp_repot.txt

 

看一下效果：

[root@mxqmongodb2 sa]# cat mysql.tcp_repot.txt
 
# 4.2s user time, 50ms system time, 27.65M rss, 179.15M vsz
# Current date: Tue Jun 20 17:08:40 2017# Hostname: mxqmongodb2
# Files: /home/sa/mysql.tcp.txt
# Overall: 155 total, 3 unique, 9.76 QPS, 4.52x concurrency ______________
# Time range: 2017-06-20 17:06:19.850032 to 17:06:35.731291# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 72s 63us 2s 463ms 1s 352ms 393ms
# Rows affecte 25 0 15 0.16 0.99 1.18 0# Query size 956 6 30 6.17 5.75 1.85 5.75# Warning coun 1 0 1 0.01 0 0.08 0
 # Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== =========# 1 0x813031B8BBC3B329 69.9077 97.4% 153 0.4569 0.25 COMMIT
# MISC 0xMISC 1.8904 2.6% 2 0.9452 0.0 <2 ITEMS>
 # Query 1: 9.63 QPS, 4.40x concurrency, ID 0x813031B8BBC3B329 at byte 10100332# This item is included in the report because it matches --limit.
# Scores: V/M = 0.25# Time range: 2017-06-20 17:06:19.850032 to 17:06:35.731291# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======# Count 98 153# Exec time 97 70s 63us 2s 457ms 1s 336ms 393ms
# Rows affecte 100 25 0 15 0.16 0.99 1.19 0# Query size 96 918 6 6 6 6 0 6# Warning coun 100 1 0 1 0.01 0 0.08 0# String:
# Hosts 127.0.0.1# Query_time distribution
# 1us
# 10us #
# 100us ####
# 1ms #
# 10ms #
# 100ms ################################################################
# 1s ##########
# 10s+commit\G

 

위 내용은 pt-query-digest(percona 툴킷)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!