写点什么

Percona Toolkit 神器全攻略(监控类)

作者:GreatSQL
  • 2024-07-03
    福建
  • 本文字数:6956 字

    阅读完需:约 23 分钟

Percona Toolkit 神器全攻略(监控类)

Percona Toolkit 神器全攻略(监控类)

Percona Toolkit 神器全攻略系列共八篇,前文回顾:


全文约定:$为命令提示符、greatsql>为 GreatSQL 数据库提示符。在后续阅读中,依据此约定进行理解与操作

监控类

在 Percona Toolkit 中监控类共有以下工具


  • pt-deadlock-logger:提取和记录 MySQL/GreatSQL 死锁

  • pt-fk-error-logger:提取和记录外键信息

  • pt-mext:并行查看 status 样本信息

  • pt-query-digest:分析查询日志,并产生报告

  • pt-mongodb-summary:收集有关 MongoDB 集群的信息

  • pt-pg-summary:收集有关 PostgreSQL 集群的信息

pt-deadlock-logger

概要

提取和记录 MySQL/GreatSQL 死锁


用法


  • pt-deadlock-logger [OPTIONS] DSN


记录 MySQL/GreatSQL 死锁的信息。信息打印到 STDOUT ,也可以通过指定 --dest 保存到表中。除非指定 --run-time--iterations ,否则该工具将永远运行

选项

该工具所有选项如下


最佳实践

如果想存储 pt-deadlock-logger 提取的有关死锁的所有信息,建议使用以下表结构:


# 可以根据--columns的字段进行调整CREATE TABLE deadlocks (  server char(20) NOT NULL,  ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  thread int unsigned NOT NULL,  txn_id bigint unsigned NOT NULL,  txn_time smallint unsigned NOT NULL,  user char(16) NOT NULL,  hostname char(20) NOT NULL,  ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL  db char(64) NOT NULL,  tbl char(64) NOT NULL,  idx char(64) NOT NULL,  lock_type char(16) NOT NULL,  lock_mode char(1) NOT NULL,  wait_hold char(1) NOT NULL,  victim tinyint unsigned NOT NULL,  query text NOT NULL,  PRIMARY KEY  (server,ts,thread)) ENGINE=InnoDB;
复制代码


  • server:发生死锁的(源)服务器

  • ts:上次检测到死锁的日期和时间

  • thread:GreatSQL 线程编号,和SHOW FULL PROCESSLIST中的 ID 一致

  • txn_id:InnoDB 事务 ID

  • txn_time:发生死锁时事务处于活动状态的时间

  • user:连接的数据库用户名

  • hostname:连接的主机

  • ip:连接的 IP 地址。如果指定--numeric-ip,则将转换为无符号整数

  • db:发生死锁的库

  • tbl:发生死锁的表

  • idx:发生死锁的索引

  • lock_type:导致死锁的锁上持有的事务的类型

  • lock_mode:导致死锁的锁的锁定模式

  • wait_hold:事务是在等待锁还是持有锁

  • victim:事务是否被选为死可回滚的事务并进行回滚

  • query:导致死锁的查询


首先创建上方提供的deadlocks表,也可以在命令中加入--create-dest-table自动创建表


greatsql> CREATE TABLE deadlocks (......中间省略Query OK, 0 rows affected (0.06 sec)
复制代码


将 host1 主机产生的死锁信息保存在 host2 主机 test_db 库下面的 deadlocks 表中


$ pt-deadlock-logger h=localhost,P=3306,u=root,p='' --dest h=localhost,P=3307,u=root,p='',D=test_db,t=deadlocks
复制代码


因为没有指定--run-time所以该工具会一直在当前窗口运行,如果要转到后台运行可以使用--daemonize


人为制造一个死锁



查看deadlocks


+-----------+---------------------+--------+--------+----------+------+-----------+----+---------+-----+---------+-----------+-----------+-----------+--------+--------------------------------------------+| server    | ts                  | thread | txn_id | txn_time | user | hostname  | ip | db      | tbl | idx     | lock_type | lock_mode | wait_hold | victim | query                                      |+-----------+---------------------+--------+--------+----------+------+-----------+----+---------+-----+---------+-----------+-----------+-----------+--------+--------------------------------------------+| localhost | 2024-03-20 15:12:51 |   1216 |      0 |        8 | root | localhost |    | test_db | t1  | PRIMARY | RECORD    | X         | w         |      1 | UPDATE t1 SET c2 = 'GreatSQL' WHERE id = 1 || localhost | 2024-03-20 15:12:51 |   1230 |      0 |       11 | root | localhost |    | test_db | t1  | PRIMARY | RECORD    | X         | w         |      0 | UPDATE t1 SET c2 = 'greatsql' WHERE id = 2 |+-----------+---------------------+--------+--------+----------+------+-----------+----+---------+-----+---------+-----------+-----------+-----------+--------+--------------------------------------------+2 rows in set (0.00 sec)
复制代码


deadlocks表中记录了锁的细节、类型、SQL 语句,比起直接看SHOW ENGINE INNODB STATUS方便

pt-fk-error-logger

概要

pt-fk-error-logger 工具的作用和 pt-deadlock-logger 差不多,pt-fk-error-logger 是记录 MySQL/GreatSQL 外键错误信息。


用法


  • pt-fk-error-logger [OPTIONS] [DSN]


记录有关给定 DSN 上的外键错误的信息。信息打印到 STDOUT ,也可以通过指定 --dest 保存到表中。除非指定 --run-time--iterations ,否则该工具将永远运行。

选项

该工具所有选项如下


最佳实践

如果想存储 pt-fk-error-logger 可以提取的有关死锁的所有信息,建议使用以下表结构:


CREATE TABLE foreign_key_errors (  ts datetime NOT NULL,  error text NOT NULL,  PRIMARY KEY (ts));
复制代码


  • ts:记录时间

  • error:错误描述


将 host1 主机产生的违反外键约束信息保存在 host2 主机 test_db 库下面的 foreign_key_errors 表中


$ pt-fk-error-logger h=localhost,P=3306,u=root,p='',S=/data/GreatSQL01/mysql.sock --dest h=localhost,P=3307,u=root,p='',S=/data/GreatSQL02/mysql.sock,D=test_db,t=foreign_key_errors
复制代码


人为创建违反索引约束


# 建t_fk1表CREATE TABLE `t_fk1` (    `id` int unsigned NOT NULL AUTO_INCREMENT,    `k` int unsigned NOT NULL DEFAULT '0',    `c` char(20) NOT NULL DEFAULT '',    `pad` char(20) NOT NULL DEFAULT '',    PRIMARY KEY (`id`),    KEY `k_2` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 建t_fk2表CREATE TABLE `t_fk2` ( `id1` int unsigned NOT NULL AUTO_INCREMENT, `id2` int unsigned NOT NULL, PRIMARY KEY (`id1`), KEY `id2` (`id2`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码


往 t_fk1 表插入数据


greatsql> insert into t_fk1 values(1,1,'a','a');greatsql> insert into t_fk1 values(2,2,'b','b');greatsql> insert into t_fk1 values(3,3,'c','c');
复制代码


往 t_fk2 表插入数据


greatsql> insert into t_fk2 values(5,5);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`t_fk2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)
复制代码


查看foreign_key_errors


greatsql> select * from foreign_key_errors\G*************************** 1. row ***************************   ts: 2024-03-20 16:21:51error: 140628737369792 Transaction:TRANSACTION 21974, ACTIVE 0 sec insertingmysql tables in use 1, locked 13 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1MySQL thread id 1235, OS thread handle 140628737369792, query id 90865 localhost root updateinsert into t_fk2 values(5,5)Foreign key constraint fails for table `test_db`.`t_fk2`:,  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`) ON DELETE RESTRICT ON UPDATE CASCADETrying to add in child table, in index id2 tuple:DATA TUPLE: 2 fields; 0: len 4; hex 00000005; asc     ;; 1: len 4; hex 00000005; asc     ;;
But in parent table `test_db`.`t1`, in index PRIMARY,the closest match we can find is record:PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 00000006; asc ;; 1: len 6; hex 000000004339; asc C9;; 2: len 7; hex 82000003cb0110; asc ;; 3: len 4; hex 000f5bcd; asc [ ;; 4: len 17; hex 3139323639362e36393136393235323433; asc 192696.6916925243;;
1 row in set (0.00 sec)
复制代码


该表中很清晰的记录了在t_fk2表的id2字段中尝试插入值5,但是根据外键约束t2_ibfk_1,这个值必须在t1表的id字段中存在。

pt-mext

概要

并排查看 MySQL/GreatSQL SHOW GLOBAL STATUS 的例子


用法


  • pt-mext [OPTIONS] -- COMMAND

选项

该工具所有选项如下


最佳实践

$ pt-mext -r -- mysqladmin ext -i10 -c3
Aborted_clients 84 0Aborted_connects 18 0Acl_cache_items_count 0 0Binlog_cache_disk_use 15 0Binlog_cache_use 118 0······下方省略
复制代码


  • -i10:采集间隔

  • -c5:采集次数

  • -r:相对的


上述命令中会有三次迭代,但只会输出第一次的结果,第二次和第一次相差的结果。意味着这会详细的列出每个变量在这一阶段的一个初始值(第一列)以及每两个采样点的差异值。


上面例子中Aborted_clients中的 84 是采样的初始值,后面的 0 是每两个采样点的差异值。

pt-query-digest

概要

pt-query-digest 是用于分析 MySQL/GreatSQL 慢查询的一个工具,它可以分析 Binlog、General log、Slowlog,也可以通过 SHOWPROCESSLIST 或者通过 tcpdump 抓取的 MySQL/GreatSQL 协议数据来进行分析。可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。


用法


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

选项

该工具所有选项如下


最佳实践

直接分析慢查询日志

$ pt-query-digest ./slow.log
复制代码


第一部分


# 用户时间,系统时间,物理内存占用大小,虚拟内存占用大小170ms user time, 0 system time, 29.88M rss, 38.17M vsz# 执行工具的时间Current date: Thu Mar 21 10:13:18 2024# 主机名Hostname: myarch# 被分析的文件名字Files: ./slow.log# 语句总数量,唯一的语句数量,QPS,并发数Overall: 119 total, 18 unique, 0.00 QPS, 0.00x concurrency _____________# 日志记录时间范围Time range: 2024-03-08T09:52:08 to 2024-03-20T14:37:23# 属性              总计     最小     最大     平均     95%    标准   中位数Attribute          total     min     max     avg     95%  stddev  median============     ======= ======= ======= ======= ======= ======= =======Exec time           122s   189us     44s      1s      1s      6s   384usLock time          489us       0   198us     4us     6us    17us     1usRows sent          1.10M       0 535.35k   9.44k   1.26k  68.78k   97.36Rows examine      97.56M     102  35.09M 839.55k 961.27k   4.59M   97.36Rows affecte           0       0       0       0       0       0       0Bytes sent       285.10M      56 202.68M   2.40M   9.76M  18.45M   5.45kQuery size        15.50k      30     250  133.39  202.40   52.84  143.84......下方省略
复制代码


  • unique:唯一查询数量,即对查询条件进行参数化以后,总共有多少个不同的查询

  • 95%:把所有值从小到大排列,位置位于 95%的那个数

  • median:中位数,把所有值从小到大排列,位置位于中间那个数


如果没有命令 hostname 可能会导致报错error: Can't exec "hostname"此时下载inetutils即可,因本文使用 arch 系统,该系统比较干净,所以导致报错。


第二部分


ProfileRank Query ID                            Response time Calls R/Call  V/M   Item==== =================================== ============= ===== ======= ====  ========   1 0x4029831C8032DEE4724E42576E2C52A6  83.1656 68.2%     2 41.5828  0.37 SELECT tpch.lineitem   2 0x6472467F1FD96D847221959F021B8110  22.8429 18.7%     1 22.8429  0.00 SELECT xxl_job_log   3 0x34BC467D466B794E79C020BEF3BFFE95   6.3289  5.2%     7  0.9041  1.17 SELECT test_index   4 0x14810CF629251E9A8950ED961EA04448   4.3492  3.6%     6  0.7249  0.06 SELECT test_db.xxl_job_log
复制代码


这部分对查询进行参数化并分组,然后对各类查询的执行情况进行分析,结果按总执行时长,从大到小排序。


  • Response:总响应时间

  • time:该查询在本次分析中总的时间占比

  • Calls:执行次数,即本次分析总共有多少条这种类型的查询语句

  • R/Call:平均每次执行的响应时间

  • V/M:响应时间 Variance-to-mean 的比率

  • Item:查询对象


第三部分


此部分列出了第一个查询的详细统计结果,列出了执行次数、最大、最小、平均、95%、标准、中位数的统计


Query 1: 0.02 QPS, 0.92x concurrency, ID 0x4029831C8032DEE4724E42576E2C52A6 at byte 1789This item is included in the report because it matches --limit.Scores: V/M = 0.37Time range: 2024-03-08T09:53:37 to 2024-03-08T09:55:07Attribute    pct   total     min     max     avg     95%  stddev  median============ === ======= ======= ======= ======= ======= ======= =======Count          1       2Exec time     68     83s     39s     44s     42s     44s      4s     42sLock time      2    11us     5us     6us     5us     6us       0     5usRows sent      0     273     133     140  136.50     140    4.95  136.50Rows examine  71  70.19M  35.09M  35.09M  35.09M  35.09M       0  35.09MRows affecte   0       0       0       0       0       0       0       0Bytes sent     0  38.41k  18.81k  19.60k  19.20k  19.60k  567.10  19.20kQuery size     0      98      49      49      49      49       0      49String:Databases    test_dbEnd          2024-03-08... (1/50%), 2024-03-08... (1/50%)Hosts        localhostStart        2024-03-08... (1/50%), 2024-03-08... (1/50%)Users        rootQuery_time distribution  1us 10us100us  1ms 10ms100ms   1s 10s+  ################################################################Tables   SHOW TABLE STATUS FROM `tpch` LIKE 'lineitem'\G   SHOW CREATE TABLE `tpch`.`lineitem`\GEXPLAIN /*!50100 PARTITIONS*/select * from tpch.lineitem where l_suppkey=23045\G
复制代码


  • Exec time:表示查询的执行时间

  • Lock time:表示查询在等待锁的时间

  • Rows sent:表示查询返回的行数

  • Rows examined:表示查询扫描的行数

  • Rows affected:表示查询影响的行数

  • Bytes sent:表示查询发送的字节数

  • Query size:表示查询的大小

  • Query_time distribution:查询时间的分布,可以看到这个 SQL 查询执行时间都是 10 秒以上

  • Tables:该 SQL 查询涉及的表

  • EXPLAIN:查询的 SQL 语句

分析指定时间内的查询

分析 12 小时内的查询


$ pt-query-digest --since=12h ./slow.log
复制代码


分析指定时间段内的查询


$ pt-query-digest slow.log --since '2024-03-19 00:00:00' --until '2024-03-21 23:59:59'
复制代码

分析指含有查询语句的慢查询

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

分析指定用户的查询

修改m/^root/i'中的 root 换成对应用户即可


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

分析其他日志

分析 binlog


分析前要先解析


$ mysqlbinlog binlog.000023 > binlog.000023.sql$ pt-query-digest  --type=binlog  binlog.000023.sql > binlog_analysis.log
复制代码


分析 general log


$ pt-query-digest  --type=genlog  general.log > general_analysis.log
复制代码

查询结果存储到表

把查询保存到 query_review 表或 query_review_history 表,先来查看下 query_review 表结构


CREATE TABLE IF NOT EXISTS query_review (   checksum     CHAR(32) NOT NULL PRIMARY KEY,   fingerprint  TEXT NOT NULL,   sample       TEXT NOT NULL,   first_seen   DATETIME,   last_seen    DATETIME,   reviewed_by  VARCHAR(20),   reviewed_on  DATETIME,   comments     TEXT)
复制代码


把查询保存到 query_review 表,使用--create-review-table会自动创建


$ pt-query-digest --user=root,-password='' --review h=localhost,D=test_db,t=query_review --create-review-table slow.log
复制代码

分析 tcpdump 抓取的数据

$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > GreatSQL.tcp.txt$ pt-query-digest --type tcpdump GreatSQL.tcp.txt> tcp_analysis.log
复制代码


如果没有 tcpdump,请手动安装


本文完 :) 下章节将介绍 Percona Toolkit 神器全攻略(系统类)


发布于: 刚刚阅读数: 5
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
Percona Toolkit 神器全攻略(监控类)_GreatSQL_InfoQ写作社区