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;+--------+--------------+
| 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;+--------+--------------+
| 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;+--------+--------------+
| 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;+--------+--------------+
| 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%';+----------------+--------+
| 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';+------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
| 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;+----------------+----------------------------------+
| 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 tableTotal queries stored- number of queries stored in the tableTerm 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;+------+
| 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 failedFast rejected queries- number of queries that were not fully evaluated, but quickly matched and rejected with filters or other conditionsTime per query- detailed times for each queryTime 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;+------+
| 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 [ 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 kernelName: thread name, you can also see this name intop,htop,psand other unix tools to monitor thread's statisticsProto: connection protocol, possible values aresphinx,mysql,http,ssl,compressedandreplicationor combination (e.g.http,sslorcompressed,mysql)State: thread state, possible values arehandshake,net_read,net_write,query,net_idleHost:ip:portof the clientConnID: connection id (starting from 0)Time: current job's duration (in seconds, with microsecond precision) or uptime of the thread in case offormat=allwhen the thread is idlingWork time: uptime of the threadWork time CPU: effective CPU time (requires--cpustats)Jobs done: how many jobs this thread has completedLast job took: last job's durationIn idle: whether the thread is idling now or when it was last idlingInfo: 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;*************************** 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 threadsInfo 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\Gmysql> 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 QUERIESSHOW 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 queryquery: query statement or a part of itprotocol: connection protocol, the possible values aresphinx,mysql,http,ssl,compressedandreplicationor combination (e.g.http,sslorcompressed,mysql)host:ip:portof the client
- 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)See also SHOW THREADS if you want to know what's going on from the threads angle of view.