Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use pt-query-digest tool for everyone, Xiaobian thinks it is quite practical, so share it with you as a reference, I hope you can gain something after reading this article.

Slow log analysis:

tail -n200000 slow.log.bak | pt-query-digest >slave.txt

Report slow queries for the last half hour:

pt-query-digest --report --since 1800s slow.log

Report a slow query for a time period, and the top 20 statement:

pt-query-digest --report --since '2014-12-30 14:50:00' --until '2014-12-30 14:59:59' --limit 20 --outliers slow.log

Report slow queries with only select statements:

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log

Report slow queries for a user:

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log

Report all full table scans or full join slow queries:

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log

# 7.8s user time, 110ms system time, 22.48M rss, 177.75M vsz

pt-query-digest User time and system time consumed, rss memory, virtual memory

# Current date: Thu Jan 1 11:00:16 2015

# Hostname: machine1

# Files: slow.log

# Overall: 437 total, 28 unique, 0.76 QPS, 0.64x concurrency _____________

Total number of statements, number of unique statements, qps, concurrency

# Time range: 2014-12-30 14:50:01 to 14:59:33

time range

# Attribute total min max avg 95% stddev median

# ============ ======= ======= ======= ======= ======= ======= =======

Total Min Max Average Percentage

# Exec time 368s 88us 126s 842ms 34ms 10s 1ms

statement execution time

# Lock time 78ms 39us 2ms 179us 445us 189us 98us

lock time

# Rows sent 2.91k 0 95 6.82 65.89 17.33 0.99

Number of rows transferred to client

# Rows examine 159.23M 0 52.94M 373.11k 1.53k 4.24M 4.96

Number of rows scanned

# Query size 471.95k 90 4.14k 1.08k 3.19k 1.33k 258.32

query statement size

# Profile

# Rank Query ID Response time Calls R/Call V/M Item

# ==== ================== ============== ===== ======== ===== ============

# 1 0xE2D730FF50E6F592 364.5363 99.1% 3 121.5121 0.17 SELECT * FROM TEST

rank: sentence ranking, default according to response time, can be modified by--order by

query id: hash value of query statement

Response time: Response time

calls: Number of times the query statement is executed

r/call: Average response time per execution

V/M: deviation index, execution time varies greatly.

# MISC 0xMISC 3.2034 0.9% 434 0.0074 0.0

# Query 1: 0.14 QPS, 16.57x concurrency, ID 0xE2D730FF50E6F592 at byte 14050954

QPS: queries per second

concurrency: number of concurrency

ID: query statement hash value

byte: The location of the query statement in the slow log file

# This item is included in the report because it matches --limit.

# Scores: V/M = 0.17

# Time range: 2014-12-30 14:52:41 to 14:53:03

# Attribute pct total min max avg 95% stddev median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count 0 3

number of executions

# Exec time 99 365s 116s 126s 122s 124s 5s 118s

# Lock time 1 1ms 365us 463us 401us 445us 41us 366us

# Rows sent 0 0 0 0 0 0 0 0

# Rows examine 99 158.83M 52.94M 52.94M 52.94M 52.94M 0 52.94M

# Query size 0 1.32k 450 450 450 450 0 450

# String:

# Databases test

# Hosts 10.1.1.44

Client machine name

# Users testuser

The user name for executing the statement

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s

# 10s+ ################################################################

Distribution of execution time

# Tables

# SHOW TABLE STATUS FROM `test` LIKE 'TEST'\G

table statistics

# SHOW CREATE TABLE `test`.` TEST`\G

# EXPLAIN /*! 50100 PARTITIONS*/

select * from test\G

query execution plan

About "how to use pt-query-digest tool" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.