如何在MySQL中查詢OS執行緒id（LWP）？
如何在MySQL中查詢OS執行緒id（LWP）？
[root@LHRDB ~]# ps -Lf 16833
UID PID PPID LWP C NLWP STIME TTY STAT TIME CMD
mysql 16833 16666 16833 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16834 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16836 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16837 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16838 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16839 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16840 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16841 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16842 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16843 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16844 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16845 0 38 06:05 ? Sl 0:01 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16846 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16848 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16849 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16850 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16851 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16852 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16853 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16854 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16855 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16856 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16857 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16858 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16859 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16860 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 16861 0 38 06:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 18619 0 38 07:59 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19060 0 38 09:56 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19136 0 38 10:05 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19193 0 38 10:33 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19218 0 38 10:47 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19219 0 38 10:47 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19221 0 38 10:47 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19222 0 38 10:47 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19223 0 38 10:47 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19230 0 38 10:49 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
mysql 16833 16666 19231 0 38 10:49 ? Sl 0:00 /var/lib/mysql57/mysql5719/bin/mysqld --basedir=/var/lib/mysql57/mysql5719 --datadir=/var/lib/mysql57/mysql5719/data --plugin-dir=/var/lib/mysql57/m
-> a.NAME,
-> a.TYPE,
-> a.PROCESSLIST_ID,
-> a.PROCESSLIST_USER,
-> a.PROCESSLIST_HOST,
-> a.PROCESSLIST_DB,
-> a.PROCESSLIST_COMMAND,
-> a.PROCESSLIST_TIME,
-> a.CONNECTION_TYPE,
-> a.THREAD_OS_ID
-> FROM performance_schema.threads a
-> where a.TYPE='FOREGROUND';
+-----------+--------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+--------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-----------------+--------------+
| 27 | thread/sql/compress_gtid_table | FOREGROUND | 1 | NULL | NULL | NULL | Daemon | 154158 | NULL | 16861 |
| 32 | thread/sql/one_connection | FOREGROUND | 7 | root | localhost | NULL | Sleep | 1929 | Socket | 19060 |
| 33 | thread/sql/one_connection | FOREGROUND | 8 | root | 192.168.59.1 | NULL | Query | 0 | TCP/IP | 18619 |
| 35 | thread/sql/one_connection | FOREGROUND | 10 | root | 192.168.59.1 | NULL | Sleep | 4503 | TCP/IP | 19218 |
| 36 | thread/sql/one_connection | FOREGROUND | 11 | root | 192.168.59.1 | sys | Sleep | 3962 | TCP/IP | 19219 |
| 37 | thread/sql/one_connection | FOREGROUND | 12 | root | 192.168.59.1 | sys | Sleep | 4494 | TCP/IP | 19221 |
| 38 | thread/sql/one_connection | FOREGROUND | 13 | root | 192.168.59.1 | sys | Sleep | 4476 | TCP/IP | 19222 |
| 39 | thread/sql/one_connection | FOREGROUND | 14 | root | 192.168.59.1 | sys | Sleep | 4469 | TCP/IP | 19223 |
| 40 | thread/sql/one_connection | FOREGROUND | 15 | root | 192.168.59.1 | sys | Sleep | 4370 | TCP/IP | 19230 |
| 41 | thread/sql/one_connection | FOREGROUND | 16 | root | 192.168.59.1 | sys | Sleep | 4095 | TCP/IP | 19231 |
| 42 | thread/sql/one_connection | FOREGROUND | 17 | root | 192.168.59.1 | sys | Sleep | 4004 | TCP/IP | 19243 |
| 43 | thread/sql/one_connection | FOREGROUND | 18 | root | 192.168.59.1 | sys | Sleep | 3962 | TCP/IP | 19246 |
| 44 | thread/sql/one_connection | FOREGROUND | 19 | root | 192.168.59.1 | performance_schema | Sleep | 3914 | TCP/IP | 19247 |
| 45 | thread/sql/one_connection | FOREGROUND | 20 | root | 192.168.59.1 | performance_schema | Sleep | 3924 | TCP/IP | 19248 |
| 46 | thread/sql/one_connection | FOREGROUND | 21 | root | 192.168.59.1 | performance_schema | Sleep | 3921 | TCP/IP | 19249 |
| 47 | thread/sql/one_connection | FOREGROUND | 22 | root | 192.168.59.1 | performance_schema | Sleep | 3914 | TCP/IP | 19252 |
| 48 | thread/sql/one_connection | FOREGROUND | 23 | root | 192.168.59.1 | performance_schema | Sleep | 2237 | TCP/IP | 19293 |
| 49 | thread/sql/one_connection | FOREGROUND | 24 | root | 192.168.59.1 | performance_schema | Sleep | 2221 | TCP/IP | 19295 |
| 50 | thread/sql/one_connection | FOREGROUND | 25 | root | 192.168.59.1 | sys | Sleep | 20 | TCP/IP | 19304 |
| 51 | thread/sql/one_connection | FOREGROUND | 26 | root | 192.168.59.1 | sys | Sleep | 20 | TCP/IP | 19305 |
+-----------+--------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-----------------+--------------+
20 rows in set (0.00 sec)
mysql> SELECT a.THREAD_ID,
-> a.NAME,
-> a.TYPE,
-> a.PROCESSLIST_ID,
-> a.PROCESSLIST_USER,
-> a.PROCESSLIST_HOST,
-> a.PROCESSLIST_DB,
-> a.PROCESSLIST_COMMAND,
-> a.PROCESSLIST_TIME,
-> a.CONNECTION_TYPE,
-> a.THREAD_OS_ID
-> FROM performance_schema.threads a
-> where a.TYPE='FOREGROUND'
-> and a.THREAD_OS_ID=19231;
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-----------------+--------------+
| 41 | thread/sql/one_connection | FOREGROUND | 16 | root | 192.168.59.1 | sys | Sleep | 4143 | TCP/IP | 19231 |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-----------------+--------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.19 |
+-----------+
官網：https://dev.mysql.com/doc/refman/5.7/en/threads-table.html
從5.7開始加了THREAD_OS_ID列。
About Me
.............................................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群號：230161599（滿）、618766405
● 微信群：可加我微信，我拉大家進群，非誠勿擾
● 聯絡我請加QQ好友（646634621），註明新增緣由
● 於 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群1 小麥苗的DBA寶典QQ群2 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2144192/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 如何在Linux中執行MySQL/MariaDB查詢LinuxMySql
- 多執行緒查詢執行緒
- 多執行緒查詢,效率翻倍執行緒
- MySQL 查詢處理 SQL查詢執行順序MySql
- 如何在子執行緒中更新UI執行緒UI
- MySQL中in（獨立子查詢）的執行計劃MySql
- 如何在Django ORM中進行not查詢？DjangoORM
- 如何在MySQL中開啟慢查詢日誌？MySql
- Android《多執行緒-中》Android執行緒
- 如何查詢一個程式下面的執行緒數（程式和執行緒區別）執行緒
- MySQL 查詢常用操作(0) —— 查詢語句的執行順序MySql
- pgrep查詢正在執行的程式ID
- MySQL學習 - 查詢的執行過程MySql
- MySQL執行狀況查詢方式介紹MySql
- Android中的執行緒池Android執行緒
- Android中執行緒的使用Android執行緒
- Java執行緒：執行緒中斷Java執行緒
- oracle中執行os命令Oracle
- MySQL 查詢語句執行過程淺析MySql
- Android新執行緒中更新主執行緒UI中的View方法彙總Android執行緒UIView
- MySQL_殺mysql執行緒MySql執行緒
- MySQL 批量殺mysql執行緒MySql執行緒
- Android中的執行緒通訊Android執行緒
- Android JNI 中的執行緒操作Android執行緒
- Android多執行緒之執行緒池Android執行緒
- Android的執行緒和執行緒池Android執行緒
- 如何在word中進行查詢與替換 word文件中的替換與查詢功能
- 在KYLIN中執行查詢報錯
- oracle查詢sql執行耗時、執行時間、sql_idOracleSQL
- 從Java到JVM到OS執行緒睡眠JavaJVM執行緒
- 如何在 SOLIDWORKS Simulation 中查詢反作用力Solid
- 為何在查詢中索引未被使用 (文件 ID 1549181.1)索引
- ORACLE_DB2_SQL SERVER_MYSQL中執行os命令OracleDB2ServerMySql
- 如何在es中查詢null值Null
- 為何在查詢中索引未被使用索引
- 如何在Clob欄位中查詢
- oracle中執行os命令(轉)Oracle
- Java執行緒中斷與終止執行緒執行Java執行緒