mysql 5.7中的threads
2018-02-27 14:57 abce 阅读(1227) 评论(0) 编辑 收藏 举报1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | >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
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）