mysql慢sql获取 - 墨天轮文档

mysql慢sql获取

zyt

572

2页

1次

2020-10-29

5墨值下载

1.配置 yum 源

cd /etc/yum.repos.d/

[r610]

name=CentOS-$releasever - Media

baseurl=xxxx

gpgcheck=0

enabled=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

2.安装包

yum install /tmp/percona-toolkit-3.0.4-1.el6.x86_64.rpm

3.查看

SHOW VARIABLES LIKE '%query%';

ls -lrt /picclife/log/mysql-slow.log

4.获取报告

pt-query-digest --since=12h /picclife/data/mysql5719/mysql-slow.log >

/tmp/`hostname`_slow_20191108.log

pt-query-digest /picclife/log/mysql-slow.log --since '2020-10-28 02:30:00'

--until '2020-10-28 05:00:00' > /tmp/`hostname`_slow_201201028.log

pt-query-digest /picclife/log/mysql-slow.log --since '2020-10-28 07:00:00'

--until '2020-10-28 09:00:00' > /tmp/`hostname`_slow_2012010280709.log

# 415.1s user time, 37.4s system time, 32.26M rss, 226.66M vsz

# Current date: Thu Oct 29 10:18:41 2020

# Hostname: qdmy-pzdb01

# Files: /picclife/log/mysql-slow.log

# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________

# Time range: all events occurred at 2020-10-28T02:38:50

# Attribute total min max avg 95% stddev median

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

# Exec time 1s 1s 1s 1s 1s 0 1s

# Lock time 1ms 1ms 1ms 1ms 1ms 0 1ms

# Rows sent 45 45 45 45 45 0 45

# Rows examine 7.37k 7.37k 7.37k 7.37k 7.37k 0 7.37k

# Query size 2.79k 2.79k 2.79k 2.79k 2.79k 0 2.79k

# Profile

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

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

# 1 0x43276AF4C630EC90BDC0EAEA6883C087 1.3228 100.0% 1 1.3228 0.00

SELECT t_mcde_dividend_record

# Query 1: 0 QPS, 0x concurrency, ID 0x43276AF4C630EC90BDC0EAEA6883C087 at byte

1717251290

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

# Scores: V/M = 0.00

# Time range: all events occurred at 2020-10-28T02:38:50

# Attribute pct total min max avg 95% stddev median

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

# Count 100 1

# Exec time 100 1s 1s 1s 1s 1s 0 1s

# Lock time 100 1ms 1ms 1ms 1ms 1ms 0 1ms

# Rows sent 100 45 45 45 45 45 0 45

# Rows examine 100 7.37k 7.37k 7.37k 7.37k 7.37k 0 7.37k

# Query size 100 2.79k 2.79k 2.79k 2.79k 2.79k 0 2.79k

# String:

# Hosts 10.111.251.34

# Users picc_mcdeNg_pro

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s ################################################################

# 10s+

# Tables

# SHOW TABLE STATUS LIKE 't_mcde_dividend_record'\G

# SHOW CREATE TABLE `t_mcde_dividend_record`\G

# EXPLAIN /*!50100 PARTITIONS*/

-- 分红失败总数

SELECT r.`POLICY_CODE`,r.`AMOUNT`,r.`TRANS_TIME`,r.`ERROR_INFO` FROM

t_mcde_dividend_record r

WHERE (r.MCDE_CHECK_STATUS IN(0) OR (r.MCDE_CHECK_STATUS =1 AND

r.ANT_CHECK_STATUS NOT IN(1))) AND r.`TYPE` ='1'

AND r.TRANS_TIME>='2020-03-06' AND r.`POLICY_CODE` NOT

IN('704414759521158','708999659484158','702399239193158','705335957206158','7073

72378222158','707420579458158','705513324391158','700725545912158','705580768715

158','705614728950158','705638145402158','705651366823158','705653935922158','70

7612725357158','707681174161158','703573614214158','705782595642158','7090691364

88158','705786369477158','705789694096158'/*... omitted 120 items ...*/)

ORDER BY r.`TRANS_TIME` ASC LIMIT 0, 1000\G

参考

https://www.cnblogs.com/luyucheng/p/6265873.html

1.配置 yum 源

cd /etc/yum.repos.d/

[r610]

name=CentOS-$releasever - Media

baseurl=xxxx

gpgcheck=0

enabled=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

2.安装包

yum install /tmp/percona-toolkit-3.0.4-1.el6.x86_64.rpm

3.查看

SHOW VARIABLES LIKE '%query%';

ls -lrt /picclife/log/mysql-slow.log

4.获取报告

pt-query-digest --since=12h /picclife/data/mysql5719/mysql-slow.log >

/tmp/`hostname`_slow_20191108.log

pt-query-digest /picclife/log/mysql-slow.log --since '2020-10-28 02:30:00'

--until '2020-10-28 05:00:00' > /tmp/`hostname`_slow_201201028.log

pt-query-digest /picclife/log/mysql-slow.log --since '2020-10-28 07:00:00'

--until '2020-10-28 09:00:00' > /tmp/`hostname`_slow_2012010280709.log

# 415.1s user time, 37.4s system time, 32.26M rss, 226.66M vsz

# Current date: Thu Oct 29 10:18:41 2020

# Hostname: qdmy-pzdb01

# Files: /picclife/log/mysql-slow.log

# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________

# Time range: all events occurred at 2020-10-28T02:38:50

# Attribute total min max avg 95% stddev median

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

# Exec time 1s 1s 1s 1s 1s 0 1s

# Lock time 1ms 1ms 1ms 1ms 1ms 0 1ms

# Rows sent 45 45 45 45 45 0 45

# Rows examine 7.37k 7.37k 7.37k 7.37k 7.37k 0 7.37k

# Query size 2.79k 2.79k 2.79k 2.79k 2.79k 0 2.79k

# Profile

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

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

# 1 0x43276AF4C630EC90BDC0EAEA6883C087 1.3228 100.0% 1 1.3228 0.00

SELECT t_mcde_dividend_record

# Query 1: 0 QPS, 0x concurrency, ID 0x43276AF4C630EC90BDC0EAEA6883C087 at byte

1717251290

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

# Scores: V/M = 0.00

# Time range: all events occurred at 2020-10-28T02:38:50

# Attribute pct total min max avg 95% stddev median

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

# Count 100 1

# Exec time 100 1s 1s 1s 1s 1s 0 1s

# Lock time 100 1ms 1ms 1ms 1ms 1ms 0 1ms

# Rows sent 100 45 45 45 45 45 0 45

# Rows examine 100 7.37k 7.37k 7.37k 7.37k 7.37k 0 7.37k

# Query size 100 2.79k 2.79k 2.79k 2.79k 2.79k 0 2.79k

# String:

# Hosts 10.111.251.34

# Users picc_mcdeNg_pro

of 2

5墨值下载

mysql

关注

评论