Heim  >  Artikel  >  Datenbank  >  pt-query-digest解析MySQL Binlog日志文件

pt-query-digest解析MySQL Binlog日志文件

WBOY
WBOYOriginal
2016-06-07 17:33:111675Durchsuche

解析binlog工具百家争鸣,最常用的是mysqlbinlog,各有千秋,对于DBA,唯手熟尔罢了,本文仅介绍pt-query-digest其实也是能用来解

解析binlog工具百家争鸣,最常用的是mysqlbinlog,各有千秋,对于DBA,唯手熟尔罢了
然而,有工具的地方就有江湖,故本文无意争论工具属优属劣,,免得引起不成熟的争端

仅介绍pt-query-digest其实也是能用来解析Binlog,友好、可读性强、便于快速诊断故障

如果直接:
[root@ld88 mysqldata]# pt-query-digest --type binlog mysql-bin88.000189
将无法解析,需先做如下转换
[root@ld88 mysqldata]# mysqlbinlog mysql-bin88.000189 > mysql-bin88.000189.sql

看两个例子,以下为了排版,对输出做了大量裁减-_-!

㈠ 指定时间窗口

[root@ld88 mysqldata]# pt-query-digest --type binlog --since "2013-11-06 20:55:00" --until "2013-11-06 21:00:00" mysql-bin88.000189.sql
mysql-bin88.000189.sql:  17% 02:22 remain
mysql-bin88.000189.sql:  33% 01:58 remain
mysql-bin88.000189.sql:  47% 01:39 remain
mysql-bin88.000189.sql:  62% 01:12 remain
mysql-bin88.000189.sql:  83% 00:28 remain

# 160.9s user time, 8.8s system time, 23.62M rss, 150.04M vsz
# Current date: Thu Nov  7 15:37:19 2013
# Hostname: ld88
# Files: mysql-bin88.000189.sql
# Overall: 914 total, 31 unique, 3.06 QPS, 41.19kx concurrency ___________
# Time range: 2013-11-06 20:55:00 to 20:59:59
# Attribute          total    min    max    avg    95%  stddev  median
# ============    ======= ======= ======= ======= ======= ======= =======
# Exec time        12316866s  13349s  13639s  13476s  13443s    145s  13443s
# Query size      442.35k      6  35.33k  434.29 1012.63  1.88k  143.84
# error code            0      0      0      0      0      0      0

# Profile
# Rank Query ID          Response time      Calls R/Call    V/M  Item
# ==== ================== ================== ===== ========== ===== ======
#    1 0x972882477A1D4A3F 3739234.0000 30.4%  277 13499.0397  1.54 UPDATE tbBlogArticleStat?
#    2 0xF5B3ADEC45DB5266 1099848.0000  8.9%    82 13412.7805  1.39 UPDATE tbBlogArticleStat?
#    3 0xC05BF1F3A8344559 1099848.0000  8.9%    82 13412.7805  1.39 UPDATE tbBlogArticleChart
#    4 0xA85CE0CC3154666E 1024921.0000  8.3%    76 13485.8026  1.26 UPDATE tbBlogTag
#    5 0x7C12B8C66B369B73  822705.0000  6.7%    61 13486.9672  1.26 INSERT tbBlogTagArticle
#    6 0xE8059EB28F9F68AA  752337.0000  6.1%    56 13434.5893  1.39 INSERT tbBlogArticleVote
#    7 0x01FCF322381E8B7E  404606.0000  3.3%    30 13486.8667  1.28 INSERT tbBlogArticleDayClick
#    8 0xD5C00F71DB944F6C  404606.0000  3.3%    30 13486.8667  1.28 UPDATE tbBlogSubArticleStatist
#    9 0x6EFA3A2E4EC6B202  404606.0000  3.3%    30 13486.8667  1.28 UPDATE tbBlogMemberSort
#  10 0xE14AB2C787449950  404606.0000  3.3%    30 13486.8667  1.28 UPDATE tbBlogMemberStat
#  11 0x34764E44BE970CDD  404606.0000  3.3%    30 13486.8667  1.28 INSERT tbBlogArticleChart
#  12 0xCFEB2F244234CE05  404605.0000  3.3%    30 13486.8333  1.28 INSERT tbBlogArticleStat?
#  13 0xCDB381C90AF965D4  404604.0000  3.3%    30 13486.8000  1.28 INSERT tbBlogArticle?
#  14 0xF7D29C9021590977  162011.0000  1.3%    12 13500.9167  1.30 UPDATE tbBlogArticleChart
#  15 0xFBA7BD32B7694172  134777.0000  1.1%    10 13477.7000  1.23 INSERT tbBlogTag
#  16 0xB2317C2DE1E87251  108184.0000  0.9%    8 13523.0000  0.00 UPDATE tbBlogMemberStat
# MISC 0xMISC              540762.0000  4.4%    40 13519.0500  0.0

# Query 1: 0.93 QPS, 12.59kx concurrency, ID 0x972882477A1D4A3F at byte 218115626
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.54
# Time range: 2013-11-06 20:55:00 to 20:59:57
# Attribute    pct  total    min    max    avg    95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        30    277
# Exec time    30 3739234s  13351s  13639s  13499s  13443s    144s  13443s
# Query size    10  44.59k    144    201  164.84  192.76  17.53  166.51
# error code    0      0      0      0      0      0      0      0
# Query_time distribution
#  1us
#  10us
# 100us
#  1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS LIKE 'tbBlogArticleStat0022'\G
#    SHOW CREATE TABLE `tbBlogArticleStat0022`\G
update tbBlogArticleStat0022 set PrevArticleID='0',PrevArticleAppearTime='',NextArticleID='35810718',NextArticleAppearTime='2011-03-14 19:59:01' where ArticleID='35605800'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  PrevArticleID='0',PrevArticleAppearTime='',NextArticleID='35810718',NextArticleAppearTime='2011-03-14 19:59:01' from tbBlogArticleStat0022 where  ArticleID='35605800'\G

更多详情见请继续阅读下一页的精彩内容

相关阅读:

如何安全删除MySQL下的binlog日志

MySQL--binlog日志恢复数据

MySQL删除binlog日志及日志恢复数据的方法

MySQL binlog三种格式介绍及分析

MySQL 利用binlog增量备份+还原实例

MySQL删除binlog日志及日志恢复数据

MySQL binlog预分配的实现和性能

linux

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn