027、优化工具的使用

转载

mb5fe559619e363 2021-07-30 14:59:00

捕获慢sql的终极必杀招：

使用工具pt-query-digest

创建示例表：

mysql> use test;
Database changed
mysql> create table su(
    ->   id int unsigned not null auto_increment,
    ->   c1 int not null default '0',
    ->   c2 int not null default '0',
    ->   c3 int not null default '0',
    ->   c4 int not null default '0',
    ->   c5 timestamp not null,
    ->   c6 varchar(200) not null default '',
    ->   primary key(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

括入测式数据：

mysql> delimiter $$
mysql> drop procedure if exists `insert_su` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure `insert_su`(in row_num int)
    -> begin
    ->   declare i int default 0;
    ->   while i < row_num do
    ->     insert into su(c1,c2,c3,c4,c5,c6) values(floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('su',floor(rand()*20)));
    ->     set i = i+1;
    ->   end while;
    -> end$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call insert_su(100000);
Query OK, 1 row affected (20 min 6.79 sec)

执行一些操作：

mysql> select * from su;
100000 rows in set (0.33 sec)
mysql> select count(*) from su;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)
mysql> update su set c4=11;
Query OK, 100000 rows affected (0.69 sec)

查看帮助：

[root@localhost ~]# pt-query-digest --help
[root@localhost ~]# pt-query-digest --since 24h /u01/data/mysql/slow.log > /u01/1.log

查看日志：

[root@localhost ~]# vi /u01/1.log
# 290ms user time, 10ms system time, 26.28M rss, 241.42M vsz
# Current date: Tue May 18 19:30:07 2021
# Hostname: localhost.localdomain
# Files: /u01/data/mysql/slow.log
# Overall: 4 total, 4 unique, 0.02 QPS, 5.14x concurrency ________________
# Time range: 2021-05-18 19:19:33 to 19:23:28
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1208s    66ms   1207s    302s   1172s    507s    586s
# Lock time           13ms    78us    13ms     3ms    13ms     6ms     7ms
# Rows sent         97.66k       0  97.66k  24.41k  97.04k  42.02k  48.52k
# Rows examine     287.52k       0  97.66k  71.88k  97.04k  40.88k  97.04k
# Query size            80      16      23      20   22.53    2.83   21.99

# Profile
# Rank Query ID                            Response time   Calls R/Call
# ==== =================================== =============== ===== =========
#    1 0x39509314429901ED040ADBE3A1EED0A2  1206.7834 99.9%     1 1206.7834  0.00 CALL insert_su
# MISC 0xMISC                                 1.0886  0.1%     3    0.3629   0.0 <3 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x39509314429901ED040ADBE3A1EED0A2 at byte 2400
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-05-18 19:20:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25       1
# Exec time     99   1207s   1207s   1207s   1207s   1207s       0   1207s
# Lock time      0    95us    95us    95us    95us    95us       0    95us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    27      22      22      22      22      22       0      22
# String:
# Databases    test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
call insert_su(100000)\G