mysql问题定位_MySQL中IO问题定位

快来打我* 2023-01-12 01:56 163阅读 0赞

在前面讲过在linux下定位磁盘IO的一个命令：iostat

其实还有一个查看linux下磁盘IO读写速度命令：iotop

查看iotop -help，有哪些用法

\# iotop -help

Usage:/usr/sbin/iotop \[OPTIONS\]

DISK READ and DISK WRITE are the block I/O bandwidth used during the sampling

period. SWAPIN and IO are the percentages oftimethe thread spent respectivelywhile swapping in and waiting on I/O more generally. PRIO is the I/O priority atwhichthe thread is running (set using the ionice command).

Controls: left and right arrows to change the sorting column, r to invert the

sorting order, o to toggle the--only option, p to toggle the --processes

option, a to toggle the--accumulated option, i to change I/O priority, q to

quit, any other key to force a refresh.

Options:--version show program's version number and exit

\-h, --help show this help message and exit-o, --only only show processes or threads actually doing I/O-b, --batch non-interactive mode-n NUM, --iter=NUM number of iterations before ending \[infinite\]-d SEC, --delay=SEC delay between iterations \[1second\]-p PID, --pid=PID processes/threads to monitor \[all\]-u USER, --user=USER users to monitor \[all\]-P, --processes only show processes, not all threads-a, --accumulated show accumulated I/O instead of bandwidth-k, --kilobytes use kilobytes instead of a human friendly unit-t, --time add a timestamp on each line (implies --batch)-q, --quiet suppress some lines of header (implies --batch)

在linux(我的是centos)用iotop命令出现下面的内容：

![424cac6adcd10c7ce0d12b91279024dd.png][]

现在高版本的mysql(5.6.40或以上)中有一个很好用的系统数据库performance\_schema，然后里面有一张表 threads，

记录了线程的各种信息，上面等iotop的第一个参数TID，就是mysql的一个线程，如果我们看到DISK\_READ,

DISK\_WRITE 读写的数据很大， 那么我们就可以找出这个线程的TID，然后到threads里面去查看哪个sql执行的

比较慢

以前的方法是去查看 slow log 慢日志

mysql> select \* from performance\_schema.threads where thread\_id=29\\G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*THREAD\_ID:29NAME: thread/sql/one\_connection

TYPE: FOREGROUND

PROCESSLIST\_ID:10PROCESSLIST\_USER: root

PROCESSLIST\_HOST:192.168.0.100PROCESSLIST\_DB: performance\_schema

PROCESSLIST\_COMMAND: Sleep

PROCESSLIST\_TIME:21PROCESSLIST\_STATE: NULL

PROCESSLIST\_INFO: NULL

PARENT\_THREAD\_ID:1ROLE: NULL

INSTRUMENTED: YES1 row in set (0.00 sec)

PROCESSLIST\_INFO 这个参数就是显示慢SQL信息

[424cac6adcd10c7ce0d12b91279024dd.png]: /images/20221119/75202c4ef98549c3a74943680c460db0.png