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

This article will explain in detail how to use the slow_log viewing tool. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Pt-query-digest

Yum install perl-IO-Socket-SSL

Rpm-ivh percona-toolkit-2.2.9-1.noarch.rpm

Execution

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

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

User time, system time, physical memory footprint, virtual memory footprint

# 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 _

Total number of sql, number of unique statements, QPS, concurrency

# Time range: 2015-05-06 15:00:48 to 2016-09-06 17:38:29

# Attribute total min max avg 95% stddev median

Attribute total minimum maximum average 95% median standard deviation

# = =

# Exec time 8216s 10s 1377s 53s 159s 139s 19s

# Lock time 25s 023s 164ms 65ms 2s 185us lock occupancy time

# Rows sent 33.38M 0 3.46M 221.94k 1.86M 652.95k 0.99 send to the client

# Rows examine 100.90M 03.46M 670.94k 1.86M 833.96k 257.78k Row scan

# Query size 992.64k 6 955.28k 6.45k 592.07 73.52k 65.89 query size

# Profile

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

ID response time of ranking statement average response time of query execution average response time part of the ratio query statement of 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 SELECT jslt?

# 10 0x1089E98FF0D37139 163.4763 2.0% 1 163.4763 SELECT jslt?

# 11 0xFD0B3ADDAD1B7133 162.9314 2.0% 1 162.9314 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 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

Analysis of each query statement

# 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: VBG 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 0 70

# String:

# Databases dtbm

# Hosts 30.97.8.103

# Users root

# Chart of execution time distribution of Query_time distribution query

# 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

This is the end of this article on "how to use the slow_log viewing tool". I hope the above content can be of some help to you, 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.