SHOW THREADS

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

SHOW THREADS is an SQL statement that lists all threads information about what they are doing at the moment.

It returns a table with following columns:

  • Tid: id assigned to the thread by the kernel
  • Name: thread name, you can also see this name in top, htop, ps and other unix tools to monitor thread's statistics
  • Proto: connection protocol, possible values are sphinx, mysql, http, ssl, compressed and replication or combination (e.g. http,ssl or compressed,mysql)
  • State: thread state, possible values are handshake, net_read, net_write, query, net_idle
  • Host: ip:port of the client
  • ConnID: connection id (starting from 0)
  • Time: current job's duration (in seconds, with microsecond precision) or uptime of the thread in case of format=all when the thread is idling
  • Work time: uptime of the thread
  • Work time CPU: effective CPU time (requires --cpustats)
  • Jobs done: how many jobs this thread has completed
  • Last job took: last job's duration
  • In idle: whether the thread is idling now or when it was last idling
  • Info: information about the query, can have multiple queries in case the query is against a distributed index or a real-time index
‹›
  • SQL
  • HTTP
  • PHP
  • Python
  • javascript
  • Java
📋
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

Info column shows:

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

Maximum width of the Info column can be limited by specifying the columns=N option (notice the second row in the example table).

By default, queries are shown in their original format. When the format=sphinxql option is specified, the queries will be shown in SQL format regardless of protocol through which they were executed.

format=all displays all threads, otherwise idling and system threads are hidden (e.g. those busy with OPTIMIZE)

‹›
  • SQL
  • HTTP
  • PHP
  • Python
  • javascript
  • Java
📋
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 WARNINGS

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1\. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1\. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW VARIABLES

SHOW [{GLOBAL | SESSION}] VARIABLES LIKE 'pattern'

It returns the current values of a few server-wide variables. Also, support for GLOBAL and SESSION clauses was added.

mysql> SHOW GLOBAL VARIABLES;
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| autocommit               | 1         |
| collation_connection     | libc_ci   |
| query_log_format         | sphinxql  |
| log_level                | info      |
| max_allowed_packet       | 134217728 |
| character_set_client     | utf8      |
| character_set_connection | utf8      |
| grouping_in_utc          | 0         |
| last_insert_id           | 123, 200  |
+--------------------------+-----------+
9 rows in set (0.00 sec)
mysql> show variables like '%log%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_log_format | sphinxql |
| log_level        | info     |
+------------------+----------+
2 rows in set (0.00 sec)
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | 0     |
+---------------+-------+
1 row in set (0.00 sec)