▪️ Profiling and monitoring

Node status

STATUS

The easiest way to see high-level information about your Manticore node is by running status in mysql client. It will show you information about different things:

  • 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 now
  • number of queries made since start
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  Tasks: 5  Queries: 318967  Wall: 7h  CPU: 0us
--------------

SHOW STATUS

SHOW STATUS [ LIKE pattern ]

SHOW STATUS is an SQL statement that displays a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

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                         |
| 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. 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 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 AGENT STATUS

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

SHOW AGENT STATUS displays the statistic of remote agents or of a distributed index. It includes the values like the age of the last request, last answer, the number of different kind of errors and successes, etc. Statistic is shown for every agent for last 1, 5 and 15 intervals, each of them of ha_period_karma seconds.

📋
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_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_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, syntax is the same as in SHOW STATUS.

📋
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. Also, agent_ prefix will be used instead of ag_N_:

📋
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 index. It can be done with a SHOW AGENT index_name STATUS statement. That statement shows the index HA status (i.e. whether or not it uses agent mirrors at all), and then the mirror information (specifically: address, blackhole and persistent flags, and the mirror selection probability used when one of the weighted probability strategies is in effect).

📋
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.sphinxsearch.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 CHARACTER SET

SHOW CHARACTER SET

This is currently a placeholder query that does nothing and reports that a UTF-8 character set is available. It was added in order to keep compatibility with frameworks and connectors that automatically execute this statement.

mysql> SHOW CHARACTER SET;
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci   | 3      |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)

SHOW META

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,channel_id FROM records WHERE MATCH('one|two|three') limit 5;

SHOW META;
Response
+--------+----------------+
| id     | channel_id     |
+--------+----------------+
| 630768 | 1054702.000000 |
| 586645 | 1057204.000000 |
| 523391 | 1061514.000000 |
| 402383 | 1069381.000000 |
| 456106 | 1065936.000000 |
+--------+----------------+
5 rows in set (0.40 sec)

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| total         | 1000   |
| total_found   | 311736 |
| time          | 0.407  |
| keyword[0]    | one    |
| docs[0]       | 265709 |
| hits[0]       | 538323 |
| keyword[1]    | two    |
| docs[1]       | 96044  |
| hits[1]       | 138576 |
| keyword[2]    | three  |
| docs[2]       | 43272  |
| hits[2]       | 69104  |
+---------------+--------+
12 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     | channel_id     |
+--------+----------------+
| 630768 | 1054702.000000 |
| 586645 | 1057204.000000 |
| 523391 | 1061514.000000 |
| 402383 | 1069381.000000 |
| 456106 | 1065936.000000 |
+--------+----------------+
5 rows in set (0.43 sec)

+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| total                 | 1000    |
| total_found           | 311736  |
| time                  | 0.431   |
| cpu_time              | 431.096 |
| 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]               | 265709  |
| hits[0]               | 538323  |
| keyword[1]            | two     |
| docs[1]               | 96044   |
| hits[1]               | 138576  |
| keyword[2]            | three   |
| docs[2]               | 43272   |
| hits[2]               | 69104   |
+-----------------------+---------+
26 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,channel_id FROM records WHERE MATCH('one|two|three') limit 5 option max_predicted_time=100;

SHOW META;
Response
+--------+----------------+
| id     | channel_id     |
+--------+----------------+
| 630768 | 1054702.000000 |
| 586645 | 1057204.000000 |
| 523391 | 1061514.000000 |
| 402383 | 1069381.000000 |
| 456106 | 1065936.000000 |
+--------+----------------+
5 rows in set (0.41 sec)

+---------------------+--------+
| Variable_name       | Value  |
+---------------------+--------+
| total               | 1000   |
| total_found         | 311736 |
| time                | 0.405  |
| local_fetched_docs  | 405025 |
| local_fetched_hits  | 746003 |
| local_fetched_skips | 0      |
| predicted_time      | 81     |
| keyword[0]          | one    |
| docs[0]             | 265709 |
| hits[0]             | 538323 |
| keyword[1]          | two    |
| docs[1]             | 96044  |
| hits[1]             | 138576 |
| keyword[2]          | three  |
| docs[2]             | 43272  |
| hits[2]             | 69104  |
+---------------------+--------+
16 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,channel_id FROM records WHERE MATCH('one|two|three') LIMIT 5; SHOW META;
Response
+--------+----------------+
| id     | channel_id     |
+--------+----------------+
| 630768 | 1054702.000000 |
| 586645 | 1057204.000000 |
| 523391 | 1061514.000000 |
| 402383 | 1069381.000000 |
| 456106 | 1065936.000000 |
+--------+----------------+
5 rows in set (0.41 sec)

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| total         | 1000   |
| total_found   | 311736 |
| time          | 0.407  |
| keyword[0]    | one    |
| docs[0]       | 265709 |
| hits[0]       | 538323 |
| keyword[1]    | two    |
| docs[1]       | 96044  |
| hits[1]       | 138576 |
| keyword[2]    | three  |
| docs[2]       | 43272  |
| hits[2]       | 69104  |
+---------------+--------+
12 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         | 1000   |
| total_found   | 311736 |
+---------------+--------+
2 rows in set (0.00 sec)

SHOW META and facets

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)

SHOW META for PQ indexes

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 index
  • Total queries stored - number of queries stored in the index
  • Term 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;
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)