⪢ Node info and management

Node status

STATUS

The easiest way to view high-level information about your Manticore node is by running status in the MySQL client. It will display information about various aspects, such as:

  • Current version
  • Whether SSL is in effect or not
  • Current TCP port/Unix socket
  • Uptime
  • Number of threads
  • Number of jobs in queue
  • Number of connections (clients)
  • Number of tasks being processed currently
  • Number of queries executed since the start
  • Number of jobs in queue and number of tasks, normalized by the number of threads
‹›
  • SQL
SQL
📋
mysql> status
‹›
Response
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:      378
Current database:   Manticore
Current user:       Usual
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     3.4.3 a48c61d6@200702 coroutines git branch coroutines_work_junk...origin/coroutines_work_junk
Protocol version:   10
Connection:     0 via TCP/IP
Server characterset:
Db     characterset:
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       8306
Uptime:         23 hours 6 sec

Threads: 12  Queue: 3  Clients: 1  Vip clients: 0  Tasks: 5  Queries: 318967  Wall: 7h  CPU: 0us
Queue/Th: 0.2  Tasks/Th: 0.4
--------------

SHOW STATUS

SHOW STATUS [ LIKE pattern ]

SHOW STATUS is an SQL statement that presents various helpful performance counters. IO and CPU counters will only be available if searchd was started with the --iostats and --cpustats switches, respectively (or if they were enabled via SET GLOBAL iostats/cpustats=1).

‹›
  • SQL
SQL
📋
SHOW STATUS;
‹›
Response
+-----------------------+---------------------------+
| Counter               | Value                     |
+-----------------------+---------------------------+
| uptime                | 1385                      |
| connections           | 11                        |
| maxed_out             | 0                         |
| version               | 3.4.3 ab7cbe5d@200511 dev |
| mysql_version         | 3.4.3 ab7cbe5d@200511 dev |
| command_search        | 2                         |
| command_excerpt       | 0                         |
| command_update        | 0                         |
| command_delete        | 0                         |
| command_keywords      | 0                         |
| command_persist       | 0                         |
| command_status        | 1                         |
| command_flushattrs    | 0                         |
| command_set           | 1                         |
| command_insert        | 0                         |
| command_replace       | 0                         |
| command_commit        | 0                         |
| command_suggest       | 0                         |
| command_json          | 0                         |
| command_callpq        | 0                         |
| agent_connect         | 0                         |
| agent_retry           | 0                         |
| queries               | 12                        |
| dist_queries          | 0                         |
| workers_total         | 30                        |
| workers_active        | 1                         |
| workers_clients       | 0                         |
| workers_clients_vip   | 1                         |
| work_queue_length     | 1                         |
| query_wall            | 10.805                    |
| query_cpu             | OFF                       |
| dist_wall             | 0.000                     |
| dist_local            | 0.000                     |
| dist_wait             | 0.000                     |
| query_reads           | OFF                       |
| query_readkb          | OFF                       |
| query_readtime        | OFF                       |
| avg_query_wall        | 0.900                     |
| avg_query_cpu         | OFF                       |
| avg_dist_wall         | 0.000                     |
| avg_dist_local        | 0.000                     |
| avg_dist_wait         | 0.000                     |
| avg_query_reads       | OFF                       |
| avg_query_readkb      | OFF                       |
| avg_query_readtime    | OFF                       |
| qcache_max_bytes      | 0                         |
| qcache_thresh_msec    | 3000                      |
| qcache_ttl_sec        | 60                        |
| qcache_cached_queries | 0                         |
| qcache_used_bytes     | 0                         |
| qcache_hits           | 0                         |
+-----------------------+---------------------------+
49 rows in set (0.00 sec)

An optional LIKE clause is supported, allowing you to select only the variables that match a specific pattern. The pattern syntax follows standard SQL wildcards, where % represents any number of any characters, and _ represents a single character.

‹›
  • SQL
SQL
📋
SHOW STATUS LIKE 'qcache%';
‹›
Response
+-----------------------+-------+
| Counter               | Value |
+-----------------------+-------+
| qcache_max_bytes      | 0     |
| qcache_thresh_msec    | 3000  |
| qcache_ttl_sec        | 60    |
| qcache_cached_queries | 0     |
| qcache_used_bytes     | 0     |
| qcache_hits           | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

SHOW SETTINGS

SHOW SETTINGS is an SQL statement that displays the current settings from your configuration file. The setting names are represented in the following format: 'config_section_name'.'setting_name'

The result also includes two additional values:

  • configuration_file - The path to the configuration file
  • worker_pid - The process ID of the running searchd instance
‹›
  • SQL
SQL
📋
SHOW SETTINGS;
‹›
Response
+--------------------------+-------------------------------------+
| Setting_name             | Value                               |
+--------------------------+-------------------------------------+
| configuration_file       | /etc/manticoresearch/manticore.conf |
| worker_pid               | 658                                 |
| searchd.listen           | 0.0.0:9312                          |
| searchd.listen           | 0.0.0:9306:mysql                    |
| searchd.listen           | 0.0.0:9308:http                     |
| searchd.log              | /var/log/manticore/searchd.log      |
| searchd.query_log        | /var/log/manticore/query.log        |
| searchd.pid_file         | /var/run/manticore/searchd.pid      |
| searchd.data_dir         | /var/lib/manticore                  |
| searchd.query_log_format | sphinxql                            |
| searchd.binlog_path      | /var/lib/manticore/binlog           |
| common.plugin_dir        | /usr/local/lib/manticore            |
| common.lemmatizer_base   | /usr/share/manticore/morph/         |
+--------------------------+-------------------------------------+
13 rows in set (0.00 sec)

SHOW AGENT STATUS

SHOW AGENT ['agent_or_index'] STATUS [ LIKE pattern ]

SHOW AGENT STATUS displays the statistics of remote agents or a distributed table. It includes values such as the age of the last request, last answer, the number of various types of errors and successes, and so on. Statistics are displayed for every agent for the last 1, 5, and 15 intervals, each consisting of ha_period_karma seconds.

‹›
  • SQL
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
SHOW AGENT STATUS;
‹›
Response
+------------------------------------+----------------------------+
| Variable_name                      | Value                      |
+------------------------------------+----------------------------+
| status_period_seconds              | 60                         |
| status_stored_periods              | 15                         |
| ag_0_hostname                      | 192.168.0.202:6713         |
| ag_0_references                    | 2                          |
| ag_0_lastquery                     | 0.41                       |
| ag_0_lastanswer                    | 0.19                       |
| ag_0_lastperiodmsec                | 222                        |
| ag_0_pingtripmsec                  | 10.521                     |
| ag_0_errorsarow                    | 0                          |
| ag_0_1periods_query_timeouts       | 0                          |
| ag_0_1periods_connect_timeouts     | 0                          |
| ag_0_1periods_connect_failures     | 0                          |
| ag_0_1periods_network_errors       | 0                          |
| ag_0_1periods_wrong_replies        | 0                          |
| ag_0_1periods_unexpected_closings  | 0                          |
| ag_0_1periods_warnings             | 0                          |
| ag_0_1periods_succeeded_queries    | 27                         |
| ag_0_1periods_msecsperquery        | 232.31                     |
| ag_0_5periods_query_timeouts       | 0                          |
| ag_0_5periods_connect_timeouts     | 0                          |
| ag_0_5periods_connect_failures     | 0                          |
| ag_0_5periods_network_errors       | 0                          |
| ag_0_5periods_wrong_replies        | 0                          |
| ag_0_5periods_unexpected_closings  | 0                          |
| ag_0_5periods_warnings             | 0                          |
| ag_0_5periods_succeeded_queries    | 146                        |
| ag_0_5periods_msecsperquery        | 231.83                     |
| ag_1_hostname                      | 192.168.0.202:6714         |
| ag_1_references                    | 2                          |
| ag_1_lastquery                     | 0.41                       |
| ag_1_lastanswer                    | 0.19                       |
| ag_1_lastperiodmsec                | 220                        |
| ag_1_pingtripmsec                  | 10.004                     |
| ag_1_errorsarow                    | 0                          |
| ag_1_1periods_query_timeouts       | 0                          |
| ag_1_1periods_connect_timeouts     | 0                          |
| ag_1_1periods_connect_failures     | 0                          |
| ag_1_1periods_network_errors       | 0                          |
| ag_1_1periods_wrong_replies        | 0                          |
| ag_1_1periods_unexpected_closings  | 0                          |
| ag_1_1periods_warnings             | 0                          |
| ag_1_1periods_succeeded_queries    | 27                         |
| ag_1_1periods_msecsperquery        | 231.24                     |
| ag_1_5periods_query_timeouts       | 0                          |
| ag_1_5periods_connect_timeouts     | 0                          |
| ag_1_5periods_connect_failures     | 0                          |
| ag_1_5periods_network_errors       | 0                          |
| ag_1_5periods_wrong_replies        | 0                          |
| ag_1_5periods_unexpected_closings  | 0                          |
| ag_1_5periods_warnings             | 0                          |
| ag_1_5periods_succeeded_queries    | 146                        |
| ag_1_5periods_msecsperquery        | 230.85                     |
+------------------------------------+----------------------------+
50 rows in set (0.01 sec)

An optional LIKE clause is supported, with the syntax being the same as in SHOW STATUS.

‹›
  • SQL
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
SHOW AGENT STATUS LIKE '%5period%msec%';
‹›
Response
+-----------------------------+--------+
| Key                         | Value  |
+-----------------------------+--------+
| ag_0_5periods_msecsperquery | 234.72 |
| ag_1_5periods_msecsperquery | 233.73 |
| ag_2_5periods_msecsperquery | 343.81 |
+-----------------------------+--------+
3 rows in set (0.00 sec)

You can specify a particular agent by its address. In this case, only that agent's data will be displayed. Additionally, the agent_ prefix will be used instead of ag_N_:

‹›
  • SQL
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
SHOW AGENT '192.168.0.202:6714' STATUS LIKE '%15periods%';
‹›
Response
+-------------------------------------+--------+
| Variable_name                       | Value  |
+-------------------------------------+--------+
| agent_15periods_query_timeouts      | 0      |
| agent_15periods_connect_timeouts    | 0      |
| agent_15periods_connect_failures    | 0      |
| agent_15periods_network_errors      | 0      |
| agent_15periods_wrong_replies       | 0      |
| agent_15periods_unexpected_closings | 0      |
| agent_15periods_warnings            | 0      |
| agent_15periods_succeeded_queries   | 439    |
| agent_15periods_msecsperquery       | 231.73 |
+-------------------------------------+--------+
9 rows in set (0.00 sec)

Finally, you can check the status of the agents in a specific distributed table using the SHOW AGENT index_name STATUS statement. This statement displays the table's HA status (i.e., whether or not it uses agent mirrors at all) and provides information on the mirrors, including: address, blackhole and persistent flags, and the mirror selection probability used when one of the weighted probability strategies is in effect.

‹›
  • SQL
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
SHOW AGENT dist_index STATUS;
‹›
Response
+--------------------------------------+--------------------------------+
| Variable_name                        | Value                          |
+--------------------------------------+--------------------------------+
| dstindex_1_is_ha                     | 1                              |
| dstindex_1mirror1_id                 | 192.168.0.202:6713:loc         |
| dstindex_1mirror1_probability_weight | 0.372864                       |
| dstindex_1mirror1_is_blackhole       | 0                              |
| dstindex_1mirror1_is_persistent      | 0                              |
| dstindex_1mirror2_id                 | 192.168.0.202:6714:loc         |
| dstindex_1mirror2_probability_weight | 0.374635                       |
| dstindex_1mirror2_is_blackhole       | 0                              |
| dstindex_1mirror2_is_persistent      | 0                              |
| dstindex_1mirror3_id                 | dev1.manticoresearch.com:6714:loc |
| dstindex_1mirror3_probability_weight | 0.252501                       |
| dstindex_1mirror3_is_blackhole       | 0                              |
| dstindex_1mirror3_is_persistent      | 0                              |
+--------------------------------------+--------------------------------+
13 rows in set (0.00 sec)

SHOW META

SHOW META [ LIKE pattern ]

SHOW META is an SQL statement that displays additional meta-information about the processed query, including the query time, keyword statistics, and information about the secondary indexes used. The syntax is:

The included items are:

  • total: The number of matches actually retrieved and sent to the client.
  • total_found: The estimated total number of matches for the query in the index.
  • total_relation: If Manticore cannot calculate the exact total value, this field will display total_relation: gte, indicating that the actual count is Greater Than or Equal to total_found. If the total value is precise, total_relation: eq will be shown.
  • time: The duration (in seconds) it took to process the search query.
  • keyword[N]: The n-th keyword used in the search query. Note that the keyword can be presented as a wildcard, e.g., abc*.
  • docs[N]: The total number of documents (or records) containing the n-th keyword from the search query. If the keyword is presented as a wildcard, this value represents the sum of documents for all expanded sub-keywords, potentially exceeding the actual number of matched documents.
  • hits[N]: The total number of occurrences (or hits) of the n-th keyword across all documents.
  • index: Information about the utilized index (e.g., secondary index).
‹›
  • SQL
SQL
📋
SELECT id, story_author FROM hn_small WHERE MATCH('one|two|three') and comment_ranking > 2 limit 5;
show meta;
‹›
Response
+---------+--------------+
| id      | story_author |
+---------+--------------+
|  151171 | anewkid      |
|  302758 | bks          |
|  805806 | drRoflol     |
| 1099245 | tnorthcutt   |
|  303252 | whiten       |
+---------+--------------+
5 rows in set (0.00 sec)

+----------------+---------------------------------------+
| Variable_name  | Value                                 |
+----------------+---------------------------------------+
| total          | 5                                     |
| total_found    | 2308                                  |
| total_relation | eq                                    |
| time           | 0.001                                 |
| 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                                 |
| index          | comment_ranking:SecondaryIndex (100%) |
+----------------+---------------------------------------+
14 rows in set (0.00 sec)

SHOW META can display I/O and CPU counters, but they will only be available if searchd was started with the --iostats and --cpustats switches, respectively.

‹›
  • SQL
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 values, such as 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 the query included predicted_time in the OPTION clause.

‹›
  • SQL
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 must be executed immediately after the query in the same session. Since some MySQL connectors/libraries use connection pools, running SHOW META in a separate statement can lead to unexpected results, such as retrieving metadata from another query. In these cases (and generally recommended), run a multiple statement containing both the query and SHOW META. Some connectors/libraries support multi-queries within the same method for a single statement, while others may require the use of a dedicated method for multi-queries or setting specific options during connection setup.

‹›
  • SQL
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, which allows you to select only the variables that match a specific pattern. The pattern syntax follows standard SQL wildcards, where % represents any number of any characters, and _ represents a single character.

‹›
  • SQL
SQL
📋
SHOW META LIKE 'total%';
‹›
Response
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| total          | 5      |
| total_found    | 266385 |
| total_relation | eq     |
+----------------+--------+
3 rows in set (0.00 sec)

SHOW META and facets

When utilizing faceted search, you can examine the multiplier field in the SHOW META output to determine how many queries were executed in an optimized group.

‹›
  • SQL
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)

SHOW META and query optimizer

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

The index variable displays the names and types of secondary indexes used during query execution. The percentage indicates how many disk chunks (in the case of an RT index) or pseudo shards (in the case of a plain index) utilized the secondary index.

‹›
  • SQL
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 for PQ tables

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

SHOW META following a CALL PQ statement includes:

  • Total - Total time spent on matching the document(s)
  • Queries matched - Number of stored queries that match the document(s)
  • Document matches - Number of documents that matched the queries stored in the table
  • Total queries stored - Total number of queries stored in the table
  • Term only queries - Number of queries in the table that have terms; the remaining queries use extended query syntax.
‹›
  • SQL
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)

Using CALL PQ with a verbose option provides more detailed output.

It includes the following additional entries:

  • Setup - Time spent on the initial setup of the matching process, such as parsing docs and setting options
  • Queries failed - Number of queries that failed
  • Fast rejected queries - Number of queries that were not fully evaluated but quickly matched and rejected using filters or other conditions
  • Time per query - Detailed time for each query
  • Time of matched queries - Total time spent on queries that matched any documents
‹›
  • SQL
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)