SHOW THREADS

SHOW THREADS [ OPTION columns=width[,format=sphinxql][,format=all] ]

SHOW THREADS is an SQL statement that displays information about all threads and their current activities.

The resulting table contains the following columns:

  • Tid: ID assigned to the thread by the kernel
  • Name: Thread name, also visible in top, htop, ps, and other Unix tools for monitoring thread statistics
  • Proto: Connection protocol; possible values include sphinx, mysql, http, ssl, compressed, replication, or a combination (e.g., http,ssl or compressed,mysql)
  • State: Thread state; possible values are handshake, net_read, net_write, query, net_idle
  • Host: Client's ip:port
  • ConnID: Connection ID (starting from 0)
  • Time: Current job's duration (in seconds, with microsecond precision) or thread uptime when using format=all and the thread is idling
  • Work time: Thread uptime
  • Work time CPU: Effective CPU time (requires --cpustats)
  • Jobs done: Number of jobs completed by this thread
  • Last job took: Duration of the last job
  • In idle: Whether the thread is currently idling or when it last idled
  • Info: Information about the query, which may include multiple queries if the query targets a distributed table or a real-time table
‹›
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
SHOW THREADS;
‹›
Response
*************************** 1. row ***************************
           Tid: 31797
          Name: work_3
         Proto: mysql
         State: query
          Host: 127.0.0.1:43388
        ConnID: 4931
          Time: 0.000903
     Work time: 2s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 2066
 Last job took: 930us
       In idle: No (working)
          Info: insert into t values(0,'abc'),(0,'def')
*************************** 2. row ***************************
           Tid: 31799
          Name: work_5
         Proto: mysql
         State: query
          Host: 127.0.0.1:43390
        ConnID: 4932
          Time: 0.000042
     Work time: 2s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 2072
 Last job took: 66us
       In idle: No (working)
          Info: show threads

The Info column displays:

  • Raw text of queries executed via the Manticore SQL interface
  • Full text syntax, comments, and data size for queries run through the internal Manticore binary protocol (e.g., from a remote Manticore instance)

You can limit the maximum width of the Info column by specifying the columns=N option (note the second row in the example table).

By default, queries are displayed in their original format. However, when the format=sphinxql option is used, queries will be shown in SQL format, regardless of the protocol used for execution.

Using format=all will show all threads, while idling and system threads are hidden without this option (e.g., those busy with OPTIMIZE).

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
SHOW THREADS OPTION columns=30\G
‹›
Response
mysql> show threads option columns=30\G
*************************** 1. row ***************************
           Tid: 9156
          Name: work_2
         Proto: mysql
         State: query
          Host: 127.0.0.1:53298
        ConnID: 20112
          Time: 0.002291
     Work time: 12s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 8497
 Last job took: 2ms
       In idle: No (working)
          Info: insert into t values(0,'abc'),
*************************** 2. row ***************************
           Tid: 9159
          Name: work_5
         Proto: mysql
         State: query
          Host: 127.0.0.1:57698
        ConnID: 8196
          Time: 0.000042
     Work time: 11s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 8547
 Last job took: 78us
       In idle: No (working)
          Info: show threads option columns=30
2 rows in set (0.00 sec)

SHOW QUERIES

SHOW QUERIES

SHOW QUERIES returns information about all currently running queries. The output is a table with the following structure:

  • id: Query ID that can be used in KILL to terminate the query
  • query: Query statement or a portion of it
  • protocol: Connection protocol, with possible values being sphinx, mysql, http, ssl, compressed, replication, or a combination (e.g., http,ssl or compressed,mysql)
  • host: Client's ip:port
‹›
  • SQL
SQL
📋
mysql> SHOW QUERIES;
‹›
Response
+------+--------------+----------+-----------------+
| id   | query        | protocol | host            |
+--------------------------+-----------------------+
|    6 | select       | http     | 127.0.0.1:41128 |
|    4 | show queries | mysql    | 127.0.0.1:56672 |
+------+--------------+----------+-----------------+
2 rows in set (0.61 sec)

Refer to SHOW THREADS if you'd like to gain insight from the perspective of the threads themselves.

KILL

KILL <query id>

KILL terminates the execution of a query by its ID, which you can find in SHOW QUERIES.

‹›
  • SQL
SQL
📋
mysql> KILL 4;
Query OK, 1 row affected (0.00 sec)