Node info and management > SHOW QUERIES

SHOW META [ LIKE pattern ]

SHOW META is an SQL statement that shows additional meta-information about the latest query such as query time and keyword statistics. The syntax is:

‹›

SQL

📋

SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') limit 5;
SHOW META;

‹›

Response

+--------+--------------+
| id     | story_author |
+--------+--------------+
| 300263 | throwaway37  |
| 713503 | mahmud       |
| 716804 | mahmud       |
| 776906 | jimbokun     |
| 753332 | foxhop       |
+--------+--------------+
5 rows in set (0.01 sec)

+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| total          | 5      |
| total_found    | 266385 |
| total_relation | eq     |
| time           | 0.012  |
| keyword[0]     | one    |
| docs[0]        | 224387 |
| hits[0]        | 310327 |
| keyword[1]     | three  |
| docs[1]        | 18181  |
| hits[1]        | 21102  |
| keyword[2]     | two    |
| docs[2]        | 63251  |
| hits[2]        | 75961  |
+----------------+--------+
13 rows in set (0.00 sec)

SHOW META can show IO and CPU counters, but they will only be available if searchd was started with --iostats and --cpustats switches respectively.

‹›

SQL

📋

SELECT id,channel_id FROM records WHERE MATCH('one|two|three') limit 5;

SHOW META;

‹›

Response

+--------+--------------+
| id     | story_author |
+--------+--------------+
| 300263 | throwaway37  |
| 713503 | mahmud       |
| 716804 | mahmud       |
| 776906 | jimbokun     |
| 753332 | foxhop       |
+--------+--------------+
5 rows in set (0.01 sec)

+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| total                 | 5      |
| total_found           | 266385 |
| total_relation        | eq     |
| time                  | 0.011  |
| cpu_time              | 18.004 |
| agents_cpu_time       | 0.000  |
| io_read_time          | 0.000  |
| io_read_ops           | 0      |
| io_read_kbytes        | 0.0    |
| io_write_time         | 0.000  |
| io_write_ops          | 0      |
| io_write_kbytes       | 0.0    |
| agent_io_read_time    | 0.000  |
| agent_io_read_ops     | 0      |
| agent_io_read_kbytes  | 0.0    |
| agent_io_write_time   | 0.000  |
| agent_io_write_ops    | 0      |
| agent_io_write_kbytes | 0.0    |
| keyword[0]            | one    |
| docs[0]               | 224387 |
| hits[0]               | 310327 |
| keyword[1]            | three  |
| docs[1]               | 18181  |
| hits[1]               | 21102  |
| keyword[2]            | two    |
| docs[2]               | 63251  |
| hits[2]               | 75961  |
+-----------------------+--------+
27 rows in set (0.00 sec)

Additional predicted_time, dist_predicted_time, local_fetched_docs, local_fetched_hits, local_fetched_skips and their respective dist_fetched_* counterparts will only be available if searchd was configured with predicted time costs and query had predicted_time in the OPTION clause.

‹›

SQL

📋

SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') limit 5 option max_predicted_time=100;

SHOW META;

‹›

Response

+--------+--------------+
| id     | story_author |
+--------+--------------+
| 300263 | throwaway37  |
| 713503 | mahmud       |
| 716804 | mahmud       |
| 776906 | jimbokun     |
| 753332 | foxhop       |
+--------+--------------+
5 rows in set (0.01 sec)

mysql> show meta;
+---------------------+--------+
| Variable_name       | Value  |
+---------------------+--------+
| total               | 5      |
| total_found         | 266385 |
| total_relation      | eq     |
| time                | 0.012  |
| local_fetched_docs  | 307212 |
| local_fetched_hits  | 407390 |
| local_fetched_skips | 24     |
| predicted_time      | 56     |
| keyword[0]          | one    |
| docs[0]             | 224387 |
| hits[0]             | 310327 |
| keyword[1]          | three  |
| docs[1]             | 18181  |
| hits[1]             | 21102  |
| keyword[2]          | two    |
| docs[2]             | 63251  |
| hits[2]             | 75961  |
+---------------------+--------+
17 rows in set (0.00 sec)

SHOW META needs to run right after the query was executed in the same session. As some mysql connectors/libraries use connection pools, running SHOW META in a separate statement an lead to unexpected results like getting meta from another query. In these cases (and recommended in general) is to run a multiple statement containing query + SHOW META. Some connectors/libraries support o multi-queries on same method for single statement, other may require usage of a dedicated method for multi-queries or setting specific options at connection setup.

‹›

SQL

📋

SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') LIMIT 5; SHOW META;

‹›

Response

+--------+--------------+
| id     | story_author |
+--------+--------------+
| 300263 | throwaway37  |
| 713503 | mahmud       |
| 716804 | mahmud       |
| 776906 | jimbokun     |
| 753332 | foxhop       |
+--------+--------------+
5 rows in set (0.01 sec)

+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| total          | 5      |
| total_found    | 266385 |
| total_relation | eq     |
| time           | 0.011  |
| keyword[0]     | one    |
| docs[0]        | 224387 |
| hits[0]        | 310327 |
| keyword[1]     | three  |
| docs[1]        | 18181  |
| hits[1]        | 21102  |
| keyword[2]     | two    |
| docs[2]        | 63251  |
| hits[2]        | 75961  |
+----------------+--------+
13 rows in set (0.00 sec)

You can also use the optional LIKE clause. It lets you pick just the variables that match a pattern. The pattern syntax is that of regular SQL wildcards, that is, % means any number of any characters, and _ means a single character.

‹›

SQL

📋

SHOW META LIKE 'total%';

‹›

Response

+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| total          | 5      |
| total_found    | 266385 |
| total_relation | eq     |
+----------------+--------+
3 rows in set (0.00 sec)

When using faceted search, you can check multiplier field in SHOW META output to see how many queries were run in an optimized group.

‹›

SQL

📋

SELECT * FROM facetdemo FACET brand_id FACET price FACET categories;
SHOW META LIKE 'multiplier';

‹›

Response

+------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
| id   | price | brand_id | title               | brand_name  | property    | j                                     | categories |
+------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
|    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |
...

+----------+----------+
| brand_id | count(*) |
+----------+----------+
|        1 |     1013 |
...

+-------+----------+
| price | count(*) |
+-------+----------+
|   306 |        7 |
...

+------------+----------+
| categories | count(*) |
+------------+----------+
|         10 |     2436 |
...

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| multiplier    | 4     |
+---------------+-------+
1 row in set (0.00 sec)

When the cost-based query optimizer decides to use DocidIndex, ColumnarScan or SecondaryIndex in place of a plain filter, this is reflected in the SHOW META command.

The index variable lists the names and types of secondary indexes used while executing the query. The percent shows how many disk chunks (in the case of an RT index) or pseudo shards (in the case of a plain index) the secondary index was used on.

‹›

SQL

📋

SELECT count(*) FROM taxi1 WHERE tip_amount = 5;
SHOW META;

‹›

Response

+----------------+----------------------------------+
| Variable_name  | Value                            |
+----------------+----------------------------------+
| total          | 1                                |
| total_found    | 1                                |
| total_relation | eq                               |
| time           | 0.016                            |
| index          | tip_amount:SecondaryIndex (100%) |
+----------------+----------------------------------+
5 rows in set (0.00 sec)

SHOW META can be used after executing a CALL PQ statement. In this case, it provides a different output.

SHOW META after a CALL PQ statement contains:

Total - total time spent on matching the document(s)
Queries matched- how many stored queries match the document(s)
Document matches - how many documents matched the queries stored in the table
Total queries stored - number of queries stored in the table
Term only queries - how many queries in the table have terms. The rest of the queries have extended query syntax.

‹›

SQL

📋

CALL PQ ('pq', ('{"title":"angry", "gid":3 }')); SHOW META;

‹›

Response

+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

+-----------------------+-----------+
| Name                  | Value     |
+-----------------------+-----------+
| Total                 | 0.000 sec |
| Queries matched       | 1         |
| Queries failed        | 0         |
| Document matched      | 1         |
| Total queries stored  | 2         |
| Term only queries     | 2         |
| Fast rejected queries | 1         |
+-----------------------+-----------+
7 rows in set (0.00 sec)

CALL PQ with a verbose option gives a more detailed output.

It includes the following additional entries:

Setup - time spent on initial setup of the matching process: parsing docs, setting options, etc.
Queries failed - number of queries that failed
Fast rejected queries - number of queries that were not fully evaluated, but quickly matched and rejected with filters or other conditions
Time per query - detailed times for each query
Time of matched queries - total time spent on queries that matched any documents

‹›

SQL

📋

CALL PQ ('pq', ('{"title":"angry", "gid":3 }'), 1 as verbose); SHOW META;

‹›

Response

+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

+-------------------------+-----------+
| Name                    | Value     |
+-------------------------+-----------+
| Total                   | 0.000 sec |
| Setup                   | 0.000 sec |
| Queries matched         | 1         |
| Queries failed          | 0         |
| Document matched        | 1         |
| Total queries stored    | 2         |
| Term only queries       | 2         |
| Fast rejected queries   | 1         |
| Time per query          | 69        |
| Time of matched queries | 69        |
+-------------------------+-----------+
10 rows in set (0.00 sec)

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 table or a real-time table

‹›

SQL
JSON
PHP
Python
javascript
Java

📋

SHOW THREADS;

POST /cli -d "SHOW THREADS"

$client->nodes()->threads();

utilsApi.sql('SHOW THREADS')

res = await utilsApi.sql('SHOW THREADS');

utilsApi.sql("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

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}

Array
(
    [31796] => Array
        (
            [Name] => work_2
            [Proto] => mysql
            [State] => query
            [Host] => 127.0.0.1:39320
            [ConnID] => 2897
            [Time] => 517
            [Work time] => 1s
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 1228
            [Last job took] => 526us
            [In idle] => No (working)
            [Info] => insert into t values(0,'abc'),(0,'def')
        )

    [31798] => Array
        (
            [Name] => work_4
            [Proto] => http
            [State] => query
            [Host] => 127.0.0.1:37748
            [ConnID] => 2898
            [Time] => 38
            [Work time] => 883ms
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 1237
            [Last job took] => 76us
            [In idle] => No (working)
            [Info] => show_threads
        )

)

{
  u'columns': [
    {
      u'Tid': {
        u'type': u'string'
      }
    },
    {
      u'Name': {
        u'type': u'string'
      }
    },
    {
      u'Proto': {
        u'type': u'string'
      }
    },
    {
      u'State': {
        u'type': u'string'
      }
    },
    {
      u'Host': {
        u'type': u'string'
      }
    },
    {
      u'ConnID': {
        u'type': u'string'
      }
    },
    {
      u'Time': {
        u'type': u'string'
      }
    },
    {
      u'Work time': {
        u'type': u'string'
      }
    },
    {
      u'Work time CPU': {
        u'type': u'string'
      }
    },
    {
      u'Thd efficiency': {
        u'type': u'string'
      }
    },
    {
      u'Jobs done': {
        u'type': u'string'
      }
    },
    {
      u'Last job took': {
        u'type': u'string'
      }
    },
    {
      u'In idle': {
        u'type': u'string'
      }
    },
    {
      u'Info': {
        u'type': u'string'
      }
    }
  ],
  u'data': [
    {
      u'Tid': 6844,
      u'Name': u'work_3',
      u'Proto': u'http',
      u'State': u'query',
      u'Host': u'127.0.0.1:51752',
      u'ConnID': 91,
      u'Time': 3245,
      u'Work time': u'2h',
      u'Work time CPU': u'0us',
      u'Thd efficiency': u'0.00%',
      u'Jobs done': 1073587960,
      u'Last job took': u'16ms',
      u'In idle': u'No (working)',
      u'Info': u'show_threads'
    }
  ],
  u'total': 0,
  u'error': u'',
  u'warning': u''
}

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}


{
  columns=[
    {
      Tid={
        type=string
      }
    },
    {
      Name={
        type=string
      }
    },
    {
      Proto={
        type=string
      }
    },
    {
      State={
        type=string
      }
    },
    {
      Host={
        type=string
      }
    },
    {
      ConnID={
        type=string
      }
    },
    {
      Time={
        type=string
      }
    },
    {
      Work time={
        type=string
      }
    },
    {
      Work time CPU={
        type=string
      }
    },
    {
      Thd efficiency={
        type=string
      }
    },
    {
      Jobs done={
        type=string
      }
    },
    {
      Last job took={
        type=string
      }
    },
    {
      In idle={
        type=string
      }
    },
    {
      Info={
        type=string
      }
    }
  ],
  data=[
    {
      Tid=6844,
      Name=work_3,
      Proto=http,
      State=query,
      Host=127.0.0.1:51752,
      ConnID=91,
      Time=3245,
      Work time=2h,
      Work time CPU=0us,
      Thd efficiency=0.00%,
      Jobs done=1073587960,
      Last job took=16ms,
      In idle=No (working),
      Info=show_threads
    }
  ],
  total=0,
  error=,
  warning=
}

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

📋

SHOW THREADS OPTION columns=30\G

POST /cli -d "SHOW THREADS OPTION columns=30"

$client->nodes()->threads(['body'=>['columns'=>30]]);

utilsApi.sql('SHOW THREADS OPTION columns=30')

res = await utilsApi.sql('SHOW THREADS OPTION columns=30');

utilsApi.sql("SHOW THREADS OPTION columns=30");

‹›

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)

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}

Array
(
    [31795] => Array
        (
            [Name] => work_1
            [Proto] => mysql
            [State] => query
            [Host] => 127.0.0.1:57676
            [ConnID] => 12075
            [Time] => 326
            [Work time] => 3s
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 5092
            [Last job took] => 335us
            [In idle] => No (working)
            [Info] => insert into t values(0,'abc'),
        )

    [31797] => Array
        (
            [Name] => work_3
            [Proto] => http
            [State] => query
            [Host] => 127.0.0.1:56104
            [ConnID] => 12076
            [Time] => 33
            [Work time] => 4s
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 5045
            [Last job took] => 82us
            [In idle] => No (working)
            [Info] => show_threads
        )

)

{
  u'columns': [
    {
      u'Tid': {
        u'type': u'string'
      }
    },
    {
      u'Name': {
        u'type': u'string'
      }
    },
    {
      u'Proto': {
        u'type': u'string'
      }
    },
    {
      u'State': {
        u'type': u'string'
      }
    },
    {
      u'Host': {
        u'type': u'string'
      }
    },
    {
      u'ConnID': {
        u'type': u'string'
      }
    },
    {
      u'Time': {
        u'type': u'string'
      }
    },
    {
      u'Work time': {
        u'type': u'string'
      }
    },
    {
      u'Work time CPU': {
        u'type': u'string'
      }
    },
    {
      u'Thd efficiency': {
        u'type': u'string'
      }
    },
    {
      u'Jobs done': {
        u'type': u'string'
      }
    },
    {
      u'Last job took': {
        u'type': u'string'
      }
    },
    {
      u'In idle': {
        u'type': u'string'
      }
    },
    {
      u'Info': {
        u'type': u'string'
      }
    }
  ],
  u'data': [
    {
      u'Tid': 6844,
      u'Name': u'work_3',
      u'Proto': u'http',
      u'State': u'query',
      u'Host': u'127.0.0.1:51752',
      u'ConnID': 91,
      u'Time': 3245,
      u'Work time': u'2h',
      u'Work time CPU': u'0us',
      u'Thd efficiency': u'0.00%',
      u'Jobs done': 1073587960,
      u'Last job took': u'16ms',
      u'In idle': u'No (working)',
      u'Info': u'show_threads'
    }
  ],
  u'total': 0,
  u'error': u'',
  u'warning': u''
}

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}


{
  columns=[
    {
      Tid={
        type=string
      }
    },
    {
      Name={
        type=string
      }
    },
    {
      Proto={
        type=string
      }
    },
    {
      State={
        type=string
      }
    },
    {
      Host={
        type=string
      }
    },
    {
      ConnID={
        type=string
      }
    },
    {
      Time={
        type=string
      }
    },
    {
      Work time={
        type=string
      }
    },
    {
      Work time CPU={
        type=string
      }
    },
    {
      Thd efficiency={
        type=string
      }
    },
    {
      Jobs done={
        type=string
      }
    },
    {
      Last job took={
        type=string
      }
    },
    {
      In idle={
        type=string
      }
    },
    {
      Info={
        type=string
      }
    }
  ],
  data=[
    {
      Tid=6844,
      Name=work_3,
      Proto=http,
      State=query,
      Host=127.0.0.1:51752,
      ConnID=91,
      Time=3245,
      Work time=2h,
      Work time CPU=0us,
      Thd efficiency=0.00%,
      Jobs done=1073587960,
      Last job took=16ms,
      In idle=No (working),
      Info=show_threads
    }
  ],
  total=0,
  error=,
  warning=
}

SHOW META SHOW QUERIES

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

📋

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)

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

SHOW THREADS KILL

KILL <query id>

KILL terminates 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)

SHOW QUERIES SHOW WARNINGS

SHOW META

SHOW META and facets

SHOW META and query optimizer

SHOW META for PQ tables

SHOW THREADS

SHOW QUERIES

KILL