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 kernelName
: Thread name, also visible intop
,htop
,ps
, and other Unix tools for monitoring thread statisticsProto
: Connection protocol; possible values includesphinx
,mysql
,http
,ssl
,compressed
,replication
, or a combination (e.g.,http,ssl
orcompressed,mysql
)State
: Thread state; possible values arehandshake
,net_read
,net_write
,query
,net_idle
Host
: Client'sip:port
ConnID
: Connection ID (starting from 0)Time
: Current job's duration (in seconds, with microsecond precision) or thread uptime when usingformat=all
and the thread is idlingWork time
: Thread uptimeWork time CPU
: Effective CPU time (requires--cpustats
)Jobs done
: Number of jobs completed by this threadLast job took
: Duration of the last jobIn idle
: Whether the thread is currently idling or when it last idledInfo
: 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;
*************************** 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
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
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 queryquery
: Query statement or a portion of itprotocol
: Connection protocol, with possible values beingsphinx
,mysql
,http
,ssl
,compressed
,replication
, or a combination (e.g.,http,ssl
orcompressed,mysql
)host
: Client'sip:port
- SQL
mysql> SHOW QUERIES;
+------+--------------+----------+-----------------+
| 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 <query id>
KILL
terminates the execution of a query by its ID, which you can find in SHOW QUERIES.
- SQL
mysql> KILL 4;
Query OK, 1 row affected (0.00 sec)