▪️ Profiling and monitoring
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)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 indexTotal queries stored- number of queries stored in the indexTerm only queries- how many queries in the index 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)