SHOW THREADS

  1. 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 table or a real-time table
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. SHOW THREADS;
  1. POST /cli -d "SHOW THREADS"
  1. $client->nodes()->threads();
  1. utilsApi.sql('SHOW THREADS')
  1. res = await utilsApi.sql('SHOW THREADS');
  1. utilsApi.sql("SHOW THREADS");

Response

  1. *************************** 1. row ***************************
  2.            Tid: 31797
  3.           Name: work_3
  4.          Proto: mysql
  5.          State: query
  6.           Host: 127.0.0.1:43388
  7.         ConnID: 4931
  8.           Time: 0.000903
  9.      Work time: 2s
  10.  Work time CPU: 0us
  11. Thd efficiency: 0.00%
  12.      Jobs done: 2066
  13.  Last job took: 930us
  14.        In idle: No (working)
  15.           Info: insert into t values(0,'abc'),(0,'def')
  16. *************************** 2. row ***************************
  17.            Tid: 31799
  18.           Name: work_5
  19.          Proto: mysql
  20.          State: query
  21.           Host: 127.0.0.1:43390
  22.         ConnID: 4932
  23.           Time: 0.000042
  24.      Work time: 2s
  25.  Work time CPU: 0us
  26. Thd efficiency: 0.00%
  27.      Jobs done: 2072
  28.  Last job took: 66us
  29.        In idle: No (working)
  30.           Info: show threads
  1. {
  2.   "columns": [
  3.     {
  4.       "Tid": {
  5.         "type": "string"
  6.       }
  7.     },
  8.     {
  9.       "Name": {
  10.         "type": "string"
  11.       }
  12.     },
  13.     {
  14.       "Proto": {
  15.         "type": "string"
  16.       }
  17.     },
  18.     {
  19.       "State": {
  20.         "type": "string"
  21.       }
  22.     },
  23.     {
  24.       "Host": {
  25.         "type": "string"
  26.       }
  27.     },
  28.     {
  29.       "ConnID": {
  30.         "type": "string"
  31.       }
  32.     },
  33.     {
  34.       "Time": {
  35.         "type": "string"
  36.       }
  37.     },
  38.     {
  39.       "Work time": {
  40.         "type": "string"
  41.       }
  42.     },
  43.     {
  44.       "Work time CPU": {
  45.         "type": "string"
  46.       }
  47.     },
  48.     {
  49.       "Thd efficiency": {
  50.         "type": "string"
  51.       }
  52.     },
  53.     {
  54.       "Jobs done": {
  55.         "type": "string"
  56.       }
  57.     },
  58.     {
  59.       "Last job took": {
  60.         "type": "string"
  61.       }
  62.     },
  63.     {
  64.       "In idle": {
  65.         "type": "string"
  66.       }
  67.     },
  68.     {
  69.       "Info": {
  70.         "type": "string"
  71.       }
  72.     }
  73.   ],
  74.   "data": [
  75.     {
  76.       "Tid": 6844,
  77.       "Name": "work_3",
  78.       "Proto": "http",
  79.       "State": "query",
  80.       "Host": "127.0.0.1:51752",
  81.       "ConnID": 91,
  82.       "Time": 3245,
  83.       "Work time": "2h",
  84.       "Work time CPU": "0us",
  85.       "Thd efficiency": "0.00%",
  86.       "Jobs done": 1073587960,
  87.       "Last job took": "16ms",
  88.       "In idle": "No (working)",
  89.       "Info": "show_threads"
  90.     }
  91.   ],
  92.   "total": 0,
  93.   "error": "",
  94.   "warning": ""
  95. }
  1. Array
  2. (
  3.     [31796] => Array
  4.         (
  5.             [Name] => work_2
  6.             [Proto] => mysql
  7.             [State] => query
  8.             [Host] => 127.0.0.1:39320
  9.             [ConnID] => 2897
  10.             [Time] => 517
  11.             [Work time] => 1s
  12.             [Work time CPU] => 0us
  13.             [Thd efficiency] => 0.00%
  14.             [Jobs done] => 1228
  15.             [Last job took] => 526us
  16.             [In idle] => No (working)
  17.             [Info] => insert into t values(0,'abc'),(0,'def')
  18.         )
  20.     [31798] => Array
  21.         (
  22.             [Name] => work_4
  23.             [Proto] => http
  24.             [State] => query
  25.             [Host] => 127.0.0.1:37748
  26.             [ConnID] => 2898
  27.             [Time] => 38
  28.             [Work time] => 883ms
  29.             [Work time CPU] => 0us
  30.             [Thd efficiency] => 0.00%
  31.             [Jobs done] => 1237
  32.             [Last job took] => 76us
  33.             [In idle] => No (working)
  34.             [Info] => show_threads
  35.         )
  37. )
  1. {
  2.   u'columns': [
  3.     {
  4.       u'Tid': {
  5.         u'type': u'string'
  6.       }
  7.     },
  8.     {
  9.       u'Name': {
  10.         u'type': u'string'
  11.       }
  12.     },
  13.     {
  14.       u'Proto': {
  15.         u'type': u'string'
  16.       }
  17.     },
  18.     {
  19.       u'State': {
  20.         u'type': u'string'
  21.       }
  22.     },
  23.     {
  24.       u'Host': {
  25.         u'type': u'string'
  26.       }
  27.     },
  28.     {
  29.       u'ConnID': {
  30.         u'type': u'string'
  31.       }
  32.     },
  33.     {
  34.       u'Time': {
  35.         u'type': u'string'
  36.       }
  37.     },
  38.     {
  39.       u'Work time': {
  40.         u'type': u'string'
  41.       }
  42.     },
  43.     {
  44.       u'Work time CPU': {
  45.         u'type': u'string'
  46.       }
  47.     },
  48.     {
  49.       u'Thd efficiency': {
  50.         u'type': u'string'
  51.       }
  52.     },
  53.     {
  54.       u'Jobs done': {
  55.         u'type': u'string'
  56.       }
  57.     },
  58.     {
  59.       u'Last job took': {
  60.         u'type': u'string'
  61.       }
  62.     },
  63.     {
  64.       u'In idle': {
  65.         u'type': u'string'
  66.       }
  67.     },
  68.     {
  69.       u'Info': {
  70.         u'type': u'string'
  71.       }
  72.     }
  73.   ],
  74.   u'data': [
  75.     {
  76.       u'Tid': 6844,
  77.       u'Name': u'work_3',
  78.       u'Proto': u'http',
  79.       u'State': u'query',
  80.       u'Host': u'127.0.0.1:51752',
  81.       u'ConnID': 91,
  82.       u'Time': 3245,
  83.       u'Work time': u'2h',
  84.       u'Work time CPU': u'0us',
  85.       u'Thd efficiency': u'0.00%',
  86.       u'Jobs done': 1073587960,
  87.       u'Last job took': u'16ms',
  88.       u'In idle': u'No (working)',
  89.       u'Info': u'show_threads'
  90.     }
  91.   ],
  92.   u'total': 0,
  93.   u'error': u'',
  94.   u'warning': u''
  95. }
  1. {
  2.   "columns": [
  3.     {
  4.       "Tid": {
  5.         "type": "string"
  6.       }
  7.     },
  8.     {
  9.       "Name": {
  10.         "type": "string"
  11.       }
  12.     },
  13.     {
  14.       "Proto": {
  15.         "type": "string"
  16.       }
  17.     },
  18.     {
  19.       "State": {
  20.         "type": "string"
  21.       }
  22.     },
  23.     {
  24.       "Host": {
  25.         "type": "string"
  26.       }
  27.     },
  28.     {
  29.       "ConnID": {
  30.         "type": "string"
  31.       }
  32.     },
  33.     {
  34.       "Time": {
  35.         "type": "string"
  36.       }
  37.     },
  38.     {
  39.       "Work time": {
  40.         "type": "string"
  41.       }
  42.     },
  43.     {
  44.       "Work time CPU": {
  45.         "type": "string"
  46.       }
  47.     },
  48.     {
  49.       "Thd efficiency": {
  50.         "type": "string"
  51.       }
  52.     },
  53.     {
  54.       "Jobs done": {
  55.         "type": "string"
  56.       }
  57.     },
  58.     {
  59.       "Last job took": {
  60.         "type": "string"
  61.       }
  62.     },
  63.     {
  64.       "In idle": {
  65.         "type": "string"
  66.       }
  67.     },
  68.     {
  69.       "Info": {
  70.         "type": "string"
  71.       }
  72.     }
  73.   ],
  74.   "data": [
  75.     {
  76.       "Tid": 6844,
  77.       "Name": "work_3",
  78.       "Proto": "http",
  79.       "State": "query",
  80.       "Host": "127.0.0.1:51752",
  81.       "ConnID": 91,
  82.       "Time": 3245,
  83.       "Work time": "2h",
  84.       "Work time CPU": "0us",
  85.       "Thd efficiency": "0.00%",
  86.       "Jobs done": 1073587960,
  87.       "Last job took": "16ms",
  88.       "In idle": "No (working)",
  89.       "Info": "show_threads"
  90.     }
  91.   ],
  92.   "total": 0,
  93.   "error": "",
  94.   "warning": ""
  95. }
  2. {
  3.   columns=[
  4.     {
  5.       Tid={
  6.         type=string
  7.       }
  8.     },
  9.     {
  10.       Name={
  11.         type=string
  12.       }
  13.     },
  14.     {
  15.       Proto={
  16.         type=string
  17.       }
  18.     },
  19.     {
  20.       State={
  21.         type=string
  22.       }
  23.     },
  24.     {
  25.       Host={
  26.         type=string
  27.       }
  28.     },
  29.     {
  30.       ConnID={
  31.         type=string
  32.       }
  33.     },
  34.     {
  35.       Time={
  36.         type=string
  37.       }
  38.     },
  39.     {
  40.       Work time={
  41.         type=string
  42.       }
  43.     },
  44.     {
  45.       Work time CPU={
  46.         type=string
  47.       }
  48.     },
  49.     {
  50.       Thd efficiency={
  51.         type=string
  52.       }
  53.     },
  54.     {
  55.       Jobs done={
  56.         type=string
  57.       }
  58.     },
  59.     {
  60.       Last job took={
  61.         type=string
  62.       }
  63.     },
  64.     {
  65.       In idle={
  66.         type=string
  67.       }
  68.     },
  69.     {
  70.       Info={
  71.         type=string
  72.       }
  73.     }
  74.   ],
  75.   data=[
  76.     {
  77.       Tid=6844,
  78.       Name=work_3,
  79.       Proto=http,
  80.       State=query,
  81.       Host=127.0.0.1:51752,
  82.       ConnID=91,
  83.       Time=3245,
  84.       Work time=2h,
  85.       Work time CPU=0us,
  86.       Thd efficiency=0.00%,
  87.       Jobs done=1073587960,
  88.       Last job took=16ms,
  89.       In idle=No (working),
  90.       Info=show_threads
  91.     }
  92.   ],
  93.   total=0,
  94.   error=,
  95.   warning=
  96. }

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
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. SHOW THREADS OPTION columns=30\G
  1. POST /cli -d "SHOW THREADS OPTION columns=30"
  1. $client->nodes()->threads(['body'=>['columns'=>30]]);
  1. utilsApi.sql('SHOW THREADS OPTION columns=30')
  1. res = await utilsApi.sql('SHOW THREADS OPTION columns=30');
  1. utilsApi.sql("SHOW THREADS OPTION columns=30");

Response

  1. mysql> show threads option columns=30\G
  2. *************************** 1. row ***************************
  3.            Tid: 9156
  4.           Name: work_2
  5.          Proto: mysql
  6.          State: query
  7.           Host: 127.0.0.1:53298
  8.         ConnID: 20112
  9.           Time: 0.002291
  10.      Work time: 12s
  11.  Work time CPU: 0us
  12. Thd efficiency: 0.00%
  13.      Jobs done: 8497
  14.  Last job took: 2ms
  15.        In idle: No (working)
  16.           Info: insert into t values(0,'abc'),
  17. *************************** 2. row ***************************
  18.            Tid: 9159
  19.           Name: work_5
  20.          Proto: mysql
  21.          State: query
  22.           Host: 127.0.0.1:57698
  23.         ConnID: 8196
  24.           Time: 0.000042
  25.      Work time: 11s
  26.  Work time CPU: 0us
  27. Thd efficiency: 0.00%
  28.      Jobs done: 8547
  29.  Last job took: 78us
  30.        In idle: No (working)
  31.           Info: show threads option columns=30
  32. 2 rows in set (0.00 sec)
  1. {
  2.   "columns": [
  3.     {
  4.       "Tid": {
  5.         "type": "string"
  6.       }
  7.     },
  8.     {
  9.       "Name": {
  10.         "type": "string"
  11.       }
  12.     },
  13.     {
  14.       "Proto": {
  15.         "type": "string"
  16.       }
  17.     },
  18.     {
  19.       "State": {
  20.         "type": "string"
  21.       }
  22.     },
  23.     {
  24.       "Host": {
  25.         "type": "string"
  26.       }
  27.     },
  28.     {
  29.       "ConnID": {
  30.         "type": "string"
  31.       }
  32.     },
  33.     {
  34.       "Time": {
  35.         "type": "string"
  36.       }
  37.     },
  38.     {
  39.       "Work time": {
  40.         "type": "string"
  41.       }
  42.     },
  43.     {
  44.       "Work time CPU": {
  45.         "type": "string"
  46.       }
  47.     },
  48.     {
  49.       "Thd efficiency": {
  50.         "type": "string"
  51.       }
  52.     },
  53.     {
  54.       "Jobs done": {
  55.         "type": "string"
  56.       }
  57.     },
  58.     {
  59.       "Last job took": {
  60.         "type": "string"
  61.       }
  62.     },
  63.     {
  64.       "In idle": {
  65.         "type": "string"
  66.       }
  67.     },
  68.     {
  69.       "Info": {
  70.         "type": "string"
  71.       }
  72.     }
  73.   ],
  74.   "data": [
  75.     {
  76.       "Tid": 6844,
  77.       "Name": "work_3",
  78.       "Proto": "http",
  79.       "State": "query",
  80.       "Host": "127.0.0.1:51752",
  81.       "ConnID": 91,
  82.       "Time": 3245,
  83.       "Work time": "2h",
  84.       "Work time CPU": "0us",
  85.       "Thd efficiency": "0.00%",
  86.       "Jobs done": 1073587960,
  87.       "Last job took": "16ms",
  88.       "In idle": "No (working)",
  89.       "Info": "show_threads"
  90.     }
  91.   ],
  92.   "total": 0,
  93.   "error": "",
  94.   "warning": ""
  95. }
  1. Array
  2. (
  3.     [31795] => Array
  4.         (
  5.             [Name] => work_1
  6.             [Proto] => mysql
  7.             [State] => query
  8.             [Host] => 127.0.0.1:57676
  9.             [ConnID] => 12075
  10.             [Time] => 326
  11.             [Work time] => 3s
  12.             [Work time CPU] => 0us
  13.             [Thd efficiency] => 0.00%
  14.             [Jobs done] => 5092
  15.             [Last job took] => 335us
  16.             [In idle] => No (working)
  17.             [Info] => insert into t values(0,'abc'),
  18.         )
  20.     [31797] => Array
  21.         (
  22.             [Name] => work_3
  23.             [Proto] => http
  24.             [State] => query
  25.             [Host] => 127.0.0.1:56104
  26.             [ConnID] => 12076
  27.             [Time] => 33
  28.             [Work time] => 4s
  29.             [Work time CPU] => 0us
  30.             [Thd efficiency] => 0.00%
  31.             [Jobs done] => 5045
  32.             [Last job took] => 82us
  33.             [In idle] => No (working)
  34.             [Info] => show_threads
  35.         )
  37. )
  1. {
  2.   u'columns': [
  3.     {
  4.       u'Tid': {
  5.         u'type': u'string'
  6.       }
  7.     },
  8.     {
  9.       u'Name': {
  10.         u'type': u'string'
  11.       }
  12.     },
  13.     {
  14.       u'Proto': {
  15.         u'type': u'string'
  16.       }
  17.     },
  18.     {
  19.       u'State': {
  20.         u'type': u'string'
  21.       }
  22.     },
  23.     {
  24.       u'Host': {
  25.         u'type': u'string'
  26.       }
  27.     },
  28.     {
  29.       u'ConnID': {
  30.         u'type': u'string'
  31.       }
  32.     },
  33.     {
  34.       u'Time': {
  35.         u'type': u'string'
  36.       }
  37.     },
  38.     {
  39.       u'Work time': {
  40.         u'type': u'string'
  41.       }
  42.     },
  43.     {
  44.       u'Work time CPU': {
  45.         u'type': u'string'
  46.       }
  47.     },
  48.     {
  49.       u'Thd efficiency': {
  50.         u'type': u'string'
  51.       }
  52.     },
  53.     {
  54.       u'Jobs done': {
  55.         u'type': u'string'
  56.       }
  57.     },
  58.     {
  59.       u'Last job took': {
  60.         u'type': u'string'
  61.       }
  62.     },
  63.     {
  64.       u'In idle': {
  65.         u'type': u'string'
  66.       }
  67.     },
  68.     {
  69.       u'Info': {
  70.         u'type': u'string'
  71.       }
  72.     }
  73.   ],
  74.   u'data': [
  75.     {
  76.       u'Tid': 6844,
  77.       u'Name': u'work_3',
  78.       u'Proto': u'http',
  79.       u'State': u'query',
  80.       u'Host': u'127.0.0.1:51752',
  81.       u'ConnID': 91,
  82.       u'Time': 3245,
  83.       u'Work time': u'2h',
  84.       u'Work time CPU': u'0us',
  85.       u'Thd efficiency': u'0.00%',
  86.       u'Jobs done': 1073587960,
  87.       u'Last job took': u'16ms',
  88.       u'In idle': u'No (working)',
  89.       u'Info': u'show_threads'
  90.     }
  91.   ],
  92.   u'total': 0,
  93.   u'error': u'',
  94.   u'warning': u''
  95. }
  1. {
  2.   "columns": [
  3.     {
  4.       "Tid": {
  5.         "type": "string"
  6.       }
  7.     },
  8.     {
  9.       "Name": {
  10.         "type": "string"
  11.       }
  12.     },
  13.     {
  14.       "Proto": {
  15.         "type": "string"
  16.       }
  17.     },
  18.     {
  19.       "State": {
  20.         "type": "string"
  21.       }
  22.     },
  23.     {
  24.       "Host": {
  25.         "type": "string"
  26.       }
  27.     },
  28.     {
  29.       "ConnID": {
  30.         "type": "string"
  31.       }
  32.     },
  33.     {
  34.       "Time": {
  35.         "type": "string"
  36.       }
  37.     },
  38.     {
  39.       "Work time": {
  40.         "type": "string"
  41.       }
  42.     },
  43.     {
  44.       "Work time CPU": {
  45.         "type": "string"
  46.       }
  47.     },
  48.     {
  49.       "Thd efficiency": {
  50.         "type": "string"
  51.       }
  52.     },
  53.     {
  54.       "Jobs done": {
  55.         "type": "string"
  56.       }
  57.     },
  58.     {
  59.       "Last job took": {
  60.         "type": "string"
  61.       }
  62.     },
  63.     {
  64.       "In idle": {
  65.         "type": "string"
  66.       }
  67.     },
  68.     {
  69.       "Info": {
  70.         "type": "string"
  71.       }
  72.     }
  73.   ],
  74.   "data": [
  75.     {
  76.       "Tid": 6844,
  77.       "Name": "work_3",
  78.       "Proto": "http",
  79.       "State": "query",
  80.       "Host": "127.0.0.1:51752",
  81.       "ConnID": 91,
  82.       "Time": 3245,
  83.       "Work time": "2h",
  84.       "Work time CPU": "0us",
  85.       "Thd efficiency": "0.00%",
  86.       "Jobs done": 1073587960,
  87.       "Last job took": "16ms",
  88.       "In idle": "No (working)",
  89.       "Info": "show_threads"
  90.     }
  91.   ],
  92.   "total": 0,
  93.   "error": "",
  94.   "warning": ""
  95. }
  2. {
  3.   columns=[
  4.     {
  5.       Tid={
  6.         type=string
  7.       }
  8.     },
  9.     {
  10.       Name={
  11.         type=string
  12.       }
  13.     },
  14.     {
  15.       Proto={
  16.         type=string
  17.       }
  18.     },
  19.     {
  20.       State={
  21.         type=string
  22.       }
  23.     },
  24.     {
  25.       Host={
  26.         type=string
  27.       }
  28.     },
  29.     {
  30.       ConnID={
  31.         type=string
  32.       }
  33.     },
  34.     {
  35.       Time={
  36.         type=string
  37.       }
  38.     },
  39.     {
  40.       Work time={
  41.         type=string
  42.       }
  43.     },
  44.     {
  45.       Work time CPU={
  46.         type=string
  47.       }
  48.     },
  49.     {
  50.       Thd efficiency={
  51.         type=string
  52.       }
  53.     },
  54.     {
  55.       Jobs done={
  56.         type=string
  57.       }
  58.     },
  59.     {
  60.       Last job took={
  61.         type=string
  62.       }
  63.     },
  64.     {
  65.       In idle={
  66.         type=string
  67.       }
  68.     },
  69.     {
  70.       Info={
  71.         type=string
  72.       }
  73.     }
  74.   ],
  75.   data=[
  76.     {
  77.       Tid=6844,
  78.       Name=work_3,
  79.       Proto=http,
  80.       State=query,
  81.       Host=127.0.0.1:51752,
  82.       ConnID=91,
  83.       Time=3245,
  84.       Work time=2h,
  85.       Work time CPU=0us,
  86.       Thd efficiency=0.00%,
  87.       Jobs done=1073587960,
  88.       Last job took=16ms,
  89.       In idle=No (working),
  90.       Info=show_threads
  91.     }
  92.   ],
  93.   total=0,
  94.   error=,
  95.   warning=
  96. }

SHOW QUERIES

  1. SHOW QUERIES

SHOW QUERIES returns information about all the queries running now. It outputs a table with the following structure:

  • id: query id which can be used in KILL to terminate the query
  • query: query statement or a part of it
  • protocol: connection protocol, the possible values are sphinx , mysql , http , ssl , compressed and replication or combination (e.g. http,ssl or compressed,mysql )
  • host: ip:port of the client
  • SQL

SQL

  1. mysql> SHOW QUERIES;

Response

  1. +------+--------------+----------+-----------------+
  2. | id   | query        | protocol | host            |
  3. +--------------------------+-----------------------+
  4. |    6 | select       | http     | 127.0.0.1:41128 |
  5. |    4 | show queries | mysql    | 127.0.0.1:56672 |
  6. +------+--------------+----------+-----------------+
  7. 2 rows in set (0.61 sec)

See also SHOW THREADS if you want to know what’s going on from the threads angle of view.

KILL

  1. KILL <query id>

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

  • SQL

SQL

  1. mysql> KILL 4;
  2. Query OK, 1 row affected (0.00 sec)