如何在MySQL中查询OS线程id(LWP)?

  1. 云栖社区>
  2. 博客>
  3. 正文

如何在MySQL中查询OS线程id(LWP)?

小麦苗 2017-08-27 12:05:07 浏览1222
展开阅读全文
如何在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

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';
+-----------+--------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-----------------+--------------+
| 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        小麦苗的微店

.............................................................................................................................................

img_e3029f287d989cd04bd75432ecc1c172.png
DBA笔试面试讲解群1
DBA笔试面试讲解群2
欢迎与我联系



网友评论

登录后评论
0/500
评论