>desc threads;
+---------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| THREAD_ID | bigint(20) unsigned | NO | | NULL | |#MySQL内部线程ID
| NAME | varchar(128) | NO | | NULL | |
| TYPE | varchar(10) | NO | | NULL | |
| PROCESSLIST_ID | bigint(20) unsigned | YES | | NULL | |#show processlist看到的id的值，也对应select connection_id()的值
| PROCESSLIST_USER | varchar(32) | YES | | NULL | |
| PROCESSLIST_HOST | varchar(60) | YES | | NULL | |
| PROCESSLIST_DB | varchar(64) | YES | | NULL | |
| PROCESSLIST_COMMAND | varchar(16) | YES | | NULL | |
| PROCESSLIST_TIME | bigint(20) | YES | | NULL | |
| PROCESSLIST_STATE | varchar(64) | YES | | NULL | |
| PROCESSLIST_INFO | longtext | YES | | NULL | |
| PARENT_THREAD_ID | bigint(20) unsigned | YES | | NULL | |
| ROLE | varchar(64) | YES | | NULL | |
| INSTRUMENTED | enum('YES','NO') | NO | | NULL | |
| HISTORY | enum('YES','NO') | NO | | NULL | |
| CONNECTION_TYPE | varchar(16) | YES | | NULL | |
| THREAD_OS_ID | bigint(20) unsigned | YES | | NULL | |#线程对应的操作系统层的线程号；即iotop -u mysql中tid
+---------------------+---------------------+------+-----+---------+-------+
>select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 5837643 |
+-----------------+
1 row in set (0.00 sec)
>select * from threads;
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+---------------------------------------------------------------+-----------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+---------------------------------------------------------------+-----------------------+------------------+------+--------------+---------+-----------------+--------------+
| 1 | thread/sql/main | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 26598253 | NULL | NULL | NULL | NULL | YES | YES | NULL | 5928 |
| 2 | thread/sql/thread_timer_notifier | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | YES | YES | NULL | 5929 |
| 3 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5930 |
| 4 | thread/innodb/io_log_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5931 |
| 5 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5932 |
| 6 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5933 |
| 7 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5934 |
| 8 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5935 |
| 9 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5936 |
| 10 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5937 |
| 11 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5938 |
| 12 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5939 |
| 13 | thread/innodb/page_cleaner_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5940 |
| 15 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5942 |
| 16 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5944 |
| 17 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5943 |
| 18 | thread/innodb/srv_master_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5945 |
| 19 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5947 |
| 20 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5946 |
| 21 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5949 |
| 22 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5948 |
| 23 | thread/innodb/buf_dump_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5950 |
| 24 | thread/innodb/dict_stats_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 5951 |
| 25 | thread/sql/signal_handler | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | YES | YES | NULL | 5954 |
| 26 | thread/sql/compress_gtid_table | FOREGROUND | 1 | NULL | NULL | NULL | Daemon | 26598253 | Suspending | NULL | 1 | NULL | YES | YES | NULL | 5955 |
| 5836384 | thread/sql/one_connection | FOREGROUND | 5836359 | uc | 192.168.1.61 | uter | Sleep | 1 | NULL | NULL | NULL | NULL | YES | YES | TCP/IP | 10576 |
| 4538657 | thread/sql/one_connection | FOREGROUND | 4538632 | mrepl | 192.168.1.81 | NULL | Binlog Dump | 5829470 | Master has sent all binlog to slave; waiting for more updates | NULL | NULL | NULL | YES | YES | TCP/IP | 28283 |
| 5837668 | thread/sql/one_connection | FOREGROUND | 5837643 | root | localhost | performance_schema | Query | 0 | Sending data | select * from threads | NULL | NULL | YES | YES | Socket | 30372 |
| 5837782 | thread/sql/one_connection | FOREGROUND | 5837757 | rms | 192.168.1.58 | rms | Sleep | 296 | NULL | NULL | NULL | NULL | YES | YES | TCP/IP | 28679 |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+---------------------------------------------------------------+-----------------------+------------------+------+--------------+---------+-----------------+--------------+
通过threads表中的信息，结合iotop -u mysql 的输出，就可以知道某个线程的io使用情况
5.6中没有线程对应的THREAD_OS_ID的时候，可以使用show engine innodb status
支付宝扫一扫
微信扫一扫
.png)
.png)
.png)
