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
  • number or jobs in queue and number of tasks normalized by number of threads
  • SQL

SQL

  1. mysql> status

Response

  1. --------------
  2. mysql Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper
  3. Connection id: 378
  4. Current database: Manticore
  5. Current user: Usual
  6. SSL: Not in use
  7. Current pager: stdout
  8. Using outfile: ''
  9. Using delimiter: ;
  10. Server version: 3.4.3 a48c61d6@200702 coroutines git branch coroutines_work_junk...origin/coroutines_work_junk
  11. Protocol version: 10
  12. Connection: 0 via TCP/IP
  13. Server characterset:
  14. Db characterset:
  15. Client characterset: utf8
  16. Conn. characterset: utf8
  17. TCP port: 8306
  18. Uptime: 23 hours 6 sec
  19. Threads: 12 Queue: 3 Clients: 1 Vip clients: 0 Tasks: 5 Queries: 318967 Wall: 7h CPU: 0us
  20. Queue/Th: 0.2 Tasks/Th: 0.4
  21. --------------

SHOW STATUS

  1. 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 (or they were enabled via SET GLOBAL iostats/cpustats=1).

  • SQL

SQL

  1. SHOW STATUS;

Response

  1. +-----------------------+---------------------------+
  2. | Counter | Value |
  3. +-----------------------+---------------------------+
  4. | uptime | 1385 |
  5. | connections | 11 |
  6. | maxed_out | 0 |
  7. | version | 3.4.3 ab7cbe5d@200511 dev |
  8. | mysql_version | 3.4.3 ab7cbe5d@200511 dev |
  9. | command_search | 2 |
  10. | command_excerpt | 0 |
  11. | command_update | 0 |
  12. | command_delete | 0 |
  13. | command_keywords | 0 |
  14. | command_persist | 0 |
  15. | command_status | 1 |
  16. | command_flushattrs | 0 |
  17. | command_set | 1 |
  18. | command_insert | 0 |
  19. | command_replace | 0 |
  20. | command_commit | 0 |
  21. | command_suggest | 0 |
  22. | command_json | 0 |
  23. | command_callpq | 0 |
  24. | agent_connect | 0 |
  25. | agent_retry | 0 |
  26. | queries | 12 |
  27. | dist_queries | 0 |
  28. | workers_total | 30 |
  29. | workers_active | 1 |
  30. | workers_clients | 0 |
  31. | workers_clients_vip | 1 |
  32. | work_queue_length | 1 |
  33. | query_wall | 10.805 |
  34. | query_cpu | OFF |
  35. | dist_wall | 0.000 |
  36. | dist_local | 0.000 |
  37. | dist_wait | 0.000 |
  38. | query_reads | OFF |
  39. | query_readkb | OFF |
  40. | query_readtime | OFF |
  41. | avg_query_wall | 0.900 |
  42. | avg_query_cpu | OFF |
  43. | avg_dist_wall | 0.000 |
  44. | avg_dist_local | 0.000 |
  45. | avg_dist_wait | 0.000 |
  46. | avg_query_reads | OFF |
  47. | avg_query_readkb | OFF |
  48. | avg_query_readtime | OFF |
  49. | qcache_max_bytes | 0 |
  50. | qcache_thresh_msec | 3000 |
  51. | qcache_ttl_sec | 60 |
  52. | qcache_cached_queries | 0 |
  53. | qcache_used_bytes | 0 |
  54. | qcache_hits | 0 |
  55. +-----------------------+---------------------------+
  56. 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

SQL

  1. SHOW STATUS LIKE 'qcache%';

Response

  1. +-----------------------+-------+
  2. | Counter | Value |
  3. +-----------------------+-------+
  4. | qcache_max_bytes | 0 |
  5. | qcache_thresh_msec | 3000 |
  6. | qcache_ttl_sec | 60 |
  7. | qcache_cached_queries | 0 |
  8. | qcache_used_bytes | 0 |
  9. | qcache_hits | 0 |
  10. +-----------------------+-------+
  11. 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 contains two additionary values:

  • configuration_file - the path to the configuration file
  • worker_pid - the process id of the running searchd instance
  • SQL

SQL

  1. SHOW SETTINGS;

Response

  1. +--------------------------+-------------------------------------+
  2. | Setting_name | Value |
  3. +--------------------------+-------------------------------------+
  4. | configuration_file | /etc/manticoresearch/manticore.conf |
  5. | worker_pid | 658 |
  6. | searchd.listen | 0.0.0:9312 |
  7. | searchd.listen | 0.0.0:9306:mysql |
  8. | searchd.listen | 0.0.0:9308:http |
  9. | searchd.log | /var/log/manticore/searchd.log |
  10. | searchd.query_log | /var/log/manticore/query.log |
  11. | searchd.pid_file | /var/run/manticore/searchd.pid |
  12. | searchd.data_dir | /var/lib/manticore |
  13. | searchd.query_log_format | sphinxql |
  14. | searchd.binlog_path | /var/lib/manticore/binlog |
  15. | common.plugin_dir | /usr/local/lib/manticore |
  16. | common.lemmatizer_base | /usr/share/manticore/morph/ |
  17. +--------------------------+-------------------------------------+
  18. 13 rows in set (0.00 sec)

SHOW AGENT STATUS

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

SHOW AGENT STATUS displays the statistic of remote agents or of a distributed table. 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.

  • SQL
  • PHP
  • Python
  • javascript
  • Java

SQL PHP Python javascript Java

  1. SHOW AGENT STATUS;
  1. $client->nodes()->agentstatus();
  1. utilsApi.sql('SHOW AGENT STATUS')
  1. res = await utilsApi.sql("SHOW AGENT STATUS");
  1. utilsApi.sql("SHOW AGENT STATUS");

Response

  1. +------------------------------------+----------------------------+
  2. | Variable_name | Value |
  3. +------------------------------------+----------------------------+
  4. | status_period_seconds | 60 |
  5. | status_stored_periods | 15 |
  6. | ag_0_hostname | 192.168.0.202:6713 |
  7. | ag_0_references | 2 |
  8. | ag_0_lastquery | 0.41 |
  9. | ag_0_lastanswer | 0.19 |
  10. | ag_0_lastperiodmsec | 222 |
  11. | ag_0_pingtripmsec | 10.521 |
  12. | ag_0_errorsarow | 0 |
  13. | ag_0_1periods_query_timeouts | 0 |
  14. | ag_0_1periods_connect_timeouts | 0 |
  15. | ag_0_1periods_connect_failures | 0 |
  16. | ag_0_1periods_network_errors | 0 |
  17. | ag_0_1periods_wrong_replies | 0 |
  18. | ag_0_1periods_unexpected_closings | 0 |
  19. | ag_0_1periods_warnings | 0 |
  20. | ag_0_1periods_succeeded_queries | 27 |
  21. | ag_0_1periods_msecsperquery | 232.31 |
  22. | ag_0_5periods_query_timeouts | 0 |
  23. | ag_0_5periods_connect_timeouts | 0 |
  24. | ag_0_5periods_connect_failures | 0 |
  25. | ag_0_5periods_network_errors | 0 |
  26. | ag_0_5periods_wrong_replies | 0 |
  27. | ag_0_5periods_unexpected_closings | 0 |
  28. | ag_0_5periods_warnings | 0 |
  29. | ag_0_5periods_succeeded_queries | 146 |
  30. | ag_0_5periods_msecsperquery | 231.83 |
  31. | ag_1_hostname | 192.168.0.202:6714 |
  32. | ag_1_references | 2 |
  33. | ag_1_lastquery | 0.41 |
  34. | ag_1_lastanswer | 0.19 |
  35. | ag_1_lastperiodmsec | 220 |
  36. | ag_1_pingtripmsec | 10.004 |
  37. | ag_1_errorsarow | 0 |
  38. | ag_1_1periods_query_timeouts | 0 |
  39. | ag_1_1periods_connect_timeouts | 0 |
  40. | ag_1_1periods_connect_failures | 0 |
  41. | ag_1_1periods_network_errors | 0 |
  42. | ag_1_1periods_wrong_replies | 0 |
  43. | ag_1_1periods_unexpected_closings | 0 |
  44. | ag_1_1periods_warnings | 0 |
  45. | ag_1_1periods_succeeded_queries | 27 |
  46. | ag_1_1periods_msecsperquery | 231.24 |
  47. | ag_1_5periods_query_timeouts | 0 |
  48. | ag_1_5periods_connect_timeouts | 0 |
  49. | ag_1_5periods_connect_failures | 0 |
  50. | ag_1_5periods_network_errors | 0 |
  51. | ag_1_5periods_wrong_replies | 0 |
  52. | ag_1_5periods_unexpected_closings | 0 |
  53. | ag_1_5periods_warnings | 0 |
  54. | ag_1_5periods_succeeded_queries | 146 |
  55. | ag_1_5periods_msecsperquery | 230.85 |
  56. +------------------------------------+----------------------------+
  57. 50 rows in set (0.01 sec)
  1. Array(
  2. [status_period_seconds] => 60
  3. [status_stored_periods] => 15
  4. [ag_0_hostname] => 192.168.0.202:6713
  5. [ag_0_references] => 2
  6. [ag_0_lastquery] => 0.41
  7. [ag_0_lastanswer] => 0.19
  8. [ag_0_lastperiodmsec] => 222
  9. [ag_0_errorsarow] => 0
  10. [ag_0_1periods_query_timeouts] => 0
  11. [ag_0_1periods_connect_timeouts] => 0
  12. [ag_0_1periods_connect_failures] => 0
  13. [ag_0_1periods_network_errors] => 0
  14. [ag_0_1periods_wrong_replies] => 0
  15. [ag_0_1periods_unexpected_closings] => 0
  16. [ag_0_1periods_warnings] => 0
  17. [ag_0_1periods_succeeded_queries] => 27
  18. [ag_0_1periods_msecsperquery] => 232.31
  19. [ag_0_5periods_query_timeouts] => 0
  20. [ag_0_5periods_connect_timeouts] => 0
  21. [ag_0_5periods_connect_failures] => 0
  22. [ag_0_5periods_network_errors] => 0
  23. [ag_0_5periods_wrong_replies] => 0
  24. [ag_0_5periods_unexpected_closings] => 0
  25. [ag_0_5periods_warnings] => 0
  26. [ag_0_5periods_succeeded_queries] => 146
  27. [ag_0_5periods_msecsperquery] => 231.83
  28. [ag_1_hostname 192.168.0.202:6714
  29. [ag_1_references] => 2
  30. [ag_1_lastquery] => 0.41
  31. [ag_1_lastanswer] => 0.19
  32. [ag_1_lastperiodmsec] => 220
  33. [ag_1_errorsarow] => 0
  34. [ag_1_1periods_query_timeouts] => 0
  35. [ag_1_1periods_connect_timeouts] => 0
  36. [ag_1_1periods_connect_failures] => 0
  37. [ag_1_1periods_network_errors] => 0
  38. [ag_1_1periods_wrong_replies] => 0
  39. [ag_1_1periods_unexpected_closings] => 0
  40. [ag_1_1periods_warnings] => 0
  41. [ag_1_1periods_succeeded_queries] => 27
  42. [ag_1_1periods_msecsperquery] => 231.24
  43. [ag_1_5periods_query_timeouts] => 0
  44. [ag_1_5periods_connect_timeouts] => 0
  45. [ag_1_5periods_connect_failures] => 0
  46. [ag_1_5periods_network_errors] => 0
  47. [ag_1_5periods_wrong_replies] => 0
  48. [ag_1_5periods_unexpected_closings
  49. [ag_1_5periods_warnings] => 0
  50. [ag_1_5periods_succeeded_queries] => 146
  51. [ag_1_5periods_msecsperquery] => 230.85
  52. )
  1. {u'columns': [{u'Key': {u'type': u'string'}},
  2. {u'Value': {u'type': u'string'}}],
  3. u'data': [
  4. {u'Key': u'status_period_seconds', u'Value': u'60'},
  5. {u'Key': u'status_stored_periods', u'Value': u'15'},
  6. {u'Key': u'ag_0_hostname', u'Value': u'192.168.0.202:6713'},
  7. {u'Key': u'ag_0_references', u'Value': u'2'},
  8. {u'Key': u'ag_0_lastquery', u'Value': u'0.41'},
  9. {u'Key': u'ag_0_lastanswer', u'Value': u'0.19'},
  10. {u'Key': u'ag_0_lastperiodmsec', u'Value': u'222'},
  11. {u'Key': u'ag_0_errorsarow', u'Value': u'0'},
  12. {u'Key': u'ag_0_1periods_query_timeouts', u'Value': u'0'},
  13. {u'Key': u'ag_0_1periods_connect_timeouts', u'Value': u'0'},
  14. {u'Key': u'ag_0_1periods_connect_failures', u'Value': u'0'},
  15. {u'Key': u'ag_0_1periods_network_errors', u'Value': u'0'},
  16. {u'Key': u'ag_0_1periods_wrong_replies', u'Value': u'0'},
  17. {u'Key': u'ag_0_1periods_unexpected_closings', u'Value': u'0'},
  18. {u'Key': u'ag_0_1periods_warnings', u'Value': u'0'},
  19. {u'Key': u'ag_0_1periods_succeeded_queries', u'Value': u'27'},
  20. {u'Key': u'ag_0_1periods_msecsperquery', u'Value': u'232.31'},
  21. {u'Key': u'ag_0_5periods_query_timeouts', u'Value': u'0'},
  22. {u'Key': u'ag_0_5periods_connect_timeouts', u'Value': u'0'},
  23. {u'Key': u'ag_0_5periods_connect_failures', u'Value': u'0'},
  24. {u'Key': u'ag_0_5periods_network_errors', u'Value': u'0'},
  25. {u'Key': u'ag_0_5periods_wrong_replies', u'Value': u'0'},
  26. {u'Key': u'ag_0_5periods_unexpected_closings', u'Value': u'0'},
  27. {u'Key': u'ag_0_5periods_warnings', u'Value': u'0'},
  28. {u'Key': u'ag_0_5periods_succeeded_queries', u'Value': u'146'},
  29. {u'Key': u'ag_0_5periods_msecsperquery', u'Value': u'231.83'},
  30. {u'Key': u'ag_1_hostname 192.168.0.202:6714'},
  31. {u'Key': u'ag_1_references', u'Value': u'2'},
  32. {u'Key': u'ag_1_lastquery', u'Value': u'0.41'},
  33. {u'Key': u'ag_1_lastanswer', u'Value': u'0.19'},
  34. {u'Key': u'ag_1_lastperiodmsec', u'Value': u'220'},
  35. {u'Key': u'ag_1_errorsarow', u'Value': u'0'},
  36. {u'Key': u'ag_1_1periods_query_timeouts', u'Value': u'0'},
  37. {u'Key': u'ag_1_1periods_connect_timeouts', u'Value': u'0'},
  38. {u'Key': u'ag_1_1periods_connect_failures', u'Value': u'0'},
  39. {u'Key': u'ag_1_1periods_network_errors', u'Value': u'0'},
  40. {u'Key': u'ag_1_1periods_wrong_replies', u'Value': u'0'},
  41. {u'Key': u'ag_1_1periods_unexpected_closings', u'Value': u'0'},
  42. {u'Key': u'ag_1_1periods_warnings', u'Value': u'0'},
  43. {u'Key': u'ag_1_1periods_succeeded_queries', u'Value': u'27'},
  44. {u'Key': u'ag_1_1periods_msecsperquery', u'Value': u'231.24'},
  45. {u'Key': u'ag_1_5periods_query_timeouts', u'Value': u'0'},
  46. {u'Key': u'ag_1_5periods_connect_timeouts', u'Value': u'0'},
  47. {u'Key': u'ag_1_5periods_connect_failures', u'Value': u'0'},
  48. {u'Key': u'ag_1_5periods_network_errors', u'Value': u'0'},
  49. {u'Key': u'ag_1_5periods_wrong_replies', u'Value': u'0'},
  50. {u'Key': u'ag_1_5periods_warnings', u'Value': u'0'},
  51. {u'Key': u'ag_1_5periods_succeeded_queries', u'Value': u'146'},
  52. {u'Key': u'ag_1_5periods_msecsperquery', u'Value': u'230.85'}],
  53. u'error': u'',
  54. u'total': 0,
  55. u'warning': u''}
  1. {"columns": [{"Key": {"type": "string"}},
  2. {"Value": {"type": "string"}}],
  3. "data": [
  4. {"Key": "status_period_seconds", "Value": "60"},
  5. {"Key": "status_stored_periods", "Value": "15"},
  6. {"Key": "ag_0_hostname", "Value": "192.168.0.202:6713"},
  7. {"Key": "ag_0_references", "Value": "2"},
  8. {"Key": "ag_0_lastquery", "Value": "0.41"},
  9. {"Key": "ag_0_lastanswer", "Value": "0.19"},
  10. {"Key": "ag_0_lastperiodmsec", "Value": "222"},
  11. {"Key": "ag_0_errorsarow", "Value": "0"},
  12. {"Key": "ag_0_1periods_query_timeouts", "Value": "0"},
  13. {"Key": "ag_0_1periods_connect_timeouts", "Value": "0"},
  14. {"Key": "ag_0_1periods_connect_failures", "Value": "0"},
  15. {"Key": "ag_0_1periods_network_errors", "Value": "0"},
  16. {"Key": "ag_0_1periods_wrong_replies", "Value": "0"},
  17. {"Key": "ag_0_1periods_unexpected_closings", "Value": "0"},
  18. {"Key": "ag_0_1periods_warnings", "Value": "0"},
  19. {"Key": "ag_0_1periods_succeeded_queries", "Value": "27"},
  20. {"Key": "ag_0_1periods_msecsperquery", "Value": "232.31"},
  21. {"Key": "ag_0_5periods_query_timeouts", "Value": "0"},
  22. {"Key": "ag_0_5periods_connect_timeouts", "Value": "0"},
  23. {"Key": "ag_0_5periods_connect_failures", "Value": "0"},
  24. {"Key": "ag_0_5periods_network_errors", "Value": "0"},
  25. {"Key": "ag_0_5periods_wrong_replies", "Value": "0"},
  26. {"Key": "ag_0_5periods_unexpected_closings", "Value": "0"},
  27. {"Key": "ag_0_5periods_warnings", "Value": "0"},
  28. {"Key": "ag_0_5periods_succeeded_queries", "Value": "146"},
  29. {"Key": "ag_0_5periods_msecsperquery", "Value": "231.83"},
  30. {"Key": "ag_1_hostname 192.168.0.202:6714"},
  31. {"Key": "ag_1_references", "Value": "2"},
  32. {"Key": "ag_1_lastquery", "Value": "0.41"},
  33. {"Key": "ag_1_lastanswer", "Value": "0.19"},
  34. {"Key": "ag_1_lastperiodmsec", "Value": "220"},
  35. {"Key": "ag_1_errorsarow", "Value": "0"},
  36. {"Key": "ag_1_1periods_query_timeouts", "Value": "0"},
  37. {"Key": "ag_1_1periods_connect_timeouts", "Value": "0"},
  38. {"Key": "ag_1_1periods_connect_failures", "Value": "0"},
  39. {"Key": "ag_1_1periods_network_errors", "Value": "0"},
  40. {"Key": "ag_1_1periods_wrong_replies", "Value": "0"},
  41. {"Key": "ag_1_1periods_unexpected_closings", "Value": "0"},
  42. {"Key": "ag_1_1periods_warnings", "Value": "0"},
  43. {"Key": "ag_1_1periods_succeeded_queries", "Value": "27"},
  44. {"Key": "ag_1_1periods_msecsperquery", "Value": "231.24"},
  45. {"Key": "ag_1_5periods_query_timeouts", "Value": "0"},
  46. {"Key": "ag_1_5periods_connect_timeouts", "Value": "0"},
  47. {"Key": "ag_1_5periods_connect_failures", "Value": "0"},
  48. {"Key": "ag_1_5periods_network_errors", "Value": "0"},
  49. {"Key": "ag_1_5periods_wrong_replies", "Value": "0"},
  50. {"Key": "ag_1_5periods_warnings", "Value": "0"},
  51. {"Key": "ag_1_5periods_succeeded_queries", "Value": "146"},
  52. {"Key": "ag_1_5periods_msecsperquery", "Value": "230.85"}],
  53. "error": "",
  54. "total": 0,
  55. "warning": ""}
  1. {columns=[{ Key : { type=string }},
  2. { Value : { type=string }}],
  3. data : [
  4. { Key=status_period_seconds , Value=60 },
  5. { Key=status_stored_periods , Value=15 },
  6. { Key=ag_0_hostname , Value=192.168.0.202:6713 },
  7. { Key=ag_0_references , Value=2 },
  8. { Key=ag_0_lastquery , Value=0.41 },
  9. { Key=ag_0_lastanswer , Value=0.19 },
  10. { Key=ag_0_lastperiodmsec , Value=222 },
  11. { Key=ag_0_errorsarow , Value=0 },
  12. { Key=ag_0_1periods_query_timeouts , Value=0 },
  13. { Key=ag_0_1periods_connect_timeouts , Value=0 },
  14. { Key=ag_0_1periods_connect_failures , Value=0 },
  15. { Key=ag_0_1periods_network_errors , Value=0 },
  16. { Key=ag_0_1periods_wrong_replies , Value=0 },
  17. { Key=ag_0_1periods_unexpected_closings , Value=0 },
  18. { Key=ag_0_1periods_warnings , Value=0 },
  19. { Key=ag_0_1periods_succeeded_queries , Value=27 },
  20. { Key=ag_0_1periods_msecsperquery , Value=232.31 },
  21. { Key=ag_0_5periods_query_timeouts , Value=0 },
  22. { Key=ag_0_5periods_connect_timeouts , Value=0 },
  23. { Key=ag_0_5periods_connect_failures , Value=0 },
  24. { Key=ag_0_5periods_network_errors , Value=0 },
  25. { Key=ag_0_5periods_wrong_replies , Value=0 },
  26. { Key=ag_0_5periods_unexpected_closings , Value=0 },
  27. { Key=ag_0_5periods_warnings , Value=0 },
  28. { Key=ag_0_5periods_succeeded_queries , Value=146 },
  29. { Key=ag_0_5periods_msecsperquery , Value=231.83 },
  30. { Key=ag_1_hostname 192.168.0.202:6714 },
  31. { Key=ag_1_references , Value=2 },
  32. { Key=ag_1_lastquery , Value=0.41 },
  33. { Key=ag_1_lastanswer , Value=0.19 },
  34. { Key=ag_1_lastperiodmsec , Value=220 },
  35. { Key=ag_1_errorsarow , Value=0 },
  36. { Key=ag_1_1periods_query_timeouts , Value=0 },
  37. { Key=ag_1_1periods_connect_timeouts , Value=0 },
  38. { Key=ag_1_1periods_connect_failures , Value=0 },
  39. { Key=ag_1_1periods_network_errors , Value=0 },
  40. { Key=ag_1_1periods_wrong_replies , Value=0 },
  41. { Key=ag_1_1periods_unexpected_closings , Value=0 },
  42. { Key=ag_1_1periods_warnings , Value=0 },
  43. { Key=ag_1_1periods_succeeded_queries , Value=27 },
  44. { Key=ag_1_1periods_msecsperquery , Value=231.24 },
  45. { Key=ag_1_5periods_query_timeouts , Value=0 },
  46. { Key=ag_1_5periods_connect_timeouts , Value=0 },
  47. { Key=ag_1_5periods_connect_failures , Value=0 },
  48. { Key=ag_1_5periods_network_errors , Value=0 },
  49. { Key=ag_1_5periods_wrong_replies , Value=0 },
  50. { Key=ag_1_5periods_warnings , Value=0 },
  51. { Key=ag_1_5periods_succeeded_queries , Value=146 },
  52. { Key=ag_1_5periods_msecsperquery , Value=230.85 }],
  53. error= ,
  54. total=0,
  55. warning= }

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

  • SQL
  • PHP
  • Python
  • javascript
  • Java

SQL PHP Python javascript Java

  1. SHOW AGENT STATUS LIKE '%5period%msec%';
  1. $client->nodes()->agentstatus(
  2. ['body'=>
  3. ['pattern'=>'%5period%msec%']
  4. ]
  5. );
  1. utilsApi.sql('SHOW AGENT STATUS LIKE \'%5period%msec%\'')
  1. res = await utilsApi.sql("SHOW AGENT STATUS LIKE \"%5period%msec%\"");
  1. utilsApi.sql("SHOW AGENT STATUS LIKE \"%5period%msec%\"");

Response

  1. +-----------------------------+--------+
  2. | Key | Value |
  3. +-----------------------------+--------+
  4. | ag_0_5periods_msecsperquery | 234.72 |
  5. | ag_1_5periods_msecsperquery | 233.73 |
  6. | ag_2_5periods_msecsperquery | 343.81 |
  7. +-----------------------------+--------+
  8. 3 rows in set (0.00 sec)
  1. Array(
  2. [ag_0_5periods_msecsperquery] => 234.72
  3. [ag_1_5periods_msecsperquery] => 233.73
  4. [ag_2_5periods_msecsperquery] => 343.81
  5. )
  1. {u'columns': [{u'Key': {u'type': u'string'}},
  2. {u'Value': {u'type': u'string'}}],
  3. u'data': [
  4. {u'Key': u'ag_0_5periods_msecsperquery', u'Value': u'234.72'},
  5. {u'Key': u'ag_1_5periods_msecsperquery', u'Value': u'233.73'},
  6. {u'Key': u'ag_2_5periods_msecsperquery', u'Value': u'343.81'}],
  7. u'error': u'',
  8. u'total': 0,
  9. u'warning': u''}
  1. {"columns": [{"Key": {"type": "string"}},
  2. {"Value": {"type": "string"}}],
  3. "data": [
  4. {"Key": "ag_0_5periods_msecsperquery", "Value": "234.72"},
  5. {"Key": "ag_1_5periods_msecsperquery", "Value": "233.73"},
  6. {"Key": "ag_2_5periods_msecsperquery", "Value": "343.81"}],
  7. "error": "",
  8. "total": 0,
  9. "warning": ""}
  1. {columns: [{Key={type=string}},
  2. {Value={type=string}}],
  3. data: [
  4. {Key=ag_0_5periods_msecsperquery, Value=234.72},
  5. {Key=ag_1_5periods_msecsperquery, Value=233.73},
  6. {Key=ag_2_5periods_msecsperquery, Value=343.81}],
  7. error: ,
  8. total: 0,
  9. warning: }

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_:

  • SQL
  • PHP
  • Python
  • javascript
  • Java

SQL PHP Python javascript Java

  1. SHOW AGENT '192.168.0.202:6714' STATUS LIKE '%15periods%';
  1. $client->nodes()->agentstatus(
  2. ['body'=>
  3. ['agent'=>'192.168.0.202:6714'],
  4. ['pattern'=>'%5period%msec%']
  5. ]
  6. );
  1. utilsApi.sql('SHOW AGENT \'192.168.0.202:6714\' STATUS LIKE \'%15periods%\'')
  1. res = await utilsApi.sql("SHOW AGENT \"192.168.0.202:6714\" STATUS LIKE \"%15periods%\"");
  1. utilsApi.sql("SHOW AGENT \"192.168.0.202:6714\" STATUS LIKE \"%15periods%\"");

Response

  1. +-------------------------------------+--------+
  2. | Variable_name | Value |
  3. +-------------------------------------+--------+
  4. | agent_15periods_query_timeouts | 0 |
  5. | agent_15periods_connect_timeouts | 0 |
  6. | agent_15periods_connect_failures | 0 |
  7. | agent_15periods_network_errors | 0 |
  8. | agent_15periods_wrong_replies | 0 |
  9. | agent_15periods_unexpected_closings | 0 |
  10. | agent_15periods_warnings | 0 |
  11. | agent_15periods_succeeded_queries | 439 |
  12. | agent_15periods_msecsperquery | 231.73 |
  13. +-------------------------------------+--------+
  14. 9 rows in set (0.00 sec)
  1. Array(
  2. [agent_15periods_query_timeouts] => 0
  3. [agent_15periods_connect_timeouts] => 0
  4. [agent_15periods_connect_failures] => 0
  5. [agent_15periods_network_errors] => 0
  6. [agent_15periods_wrong_replies] => 0
  7. [agent_15periods_unexpected_closings] => 0
  8. [agent_15periods_warnings] => 0
  9. [agent_15periods_succeeded_queries] => 439
  10. [agent_15periods_msecsperquery] => 231.73
  11. )
  1. {u'columns': [{u'Key': {u'type': u'string'}},
  2. {u'Value': {u'type': u'string'}}],
  3. u'data': [
  4. {u'Key': u'agent_15periods_query_timeouts', u'Value': u'0'},
  5. {u'Key': u'agent_15periods_connect_timeouts', u'Value': u'0'},
  6. {u'Key': u'agent_15periods_connect_failures', u'Value': u'0'},
  7. {u'Key': u'agent_15periods_network_errors', u'Value': u'0'},
  8. {u'Key': u'agent_15periods_connect_failures', u'Value': u'0'},
  9. {u'Key': u'agent_15periods_wrong_replies', u'Value': u'0'},
  10. {u'Key': u'agent_15periods_unexpected_closings', u'Value': u'0'},
  11. {u'Key': u'agent_15periods_warnings', u'Value': u'0'},
  12. {u'Key': u'agent_15periods_succeeded_queries', u'Value': u'439'},
  13. {u'Key': u'agent_15periods_msecsperquery', u'Value': u'233.73'},
  14. ],
  15. u'error': u'',
  16. u'total': 0,
  17. u'warning': u''}
  1. {"columns": [{"Key": {"type": "string"}},
  2. {"Value": {"type": "string"}}],
  3. "data": [
  4. {"Key": "agent_15periods_query_timeouts", "Value": "0"},
  5. {"Key": "agent_15periods_connect_timeouts", "Value": "0"},
  6. {"Key": "agent_15periods_connect_failures", "Value": "0"},
  7. {"Key": "agent_15periods_network_errors", "Value": "0"},
  8. {"Key": "agent_15periods_connect_failures", "Value": "0"},
  9. {"Key": "agent_15periods_wrong_replies", "Value": "0"},
  10. {"Key": "agent_15periods_unexpected_closings", "Value": "0"},
  11. {"Key": "agent_15periods_warnings", "Value": "0"},
  12. {"Key": "agent_15periods_succeeded_queries", "Value": "439"},
  13. {"Key": "agent_15periods_msecsperquery", "Value": "233.73"},
  14. ],
  15. "error": "",
  16. "total": 0,
  17. "warning": ""}
  1. {columns=[{Key={type=string}},
  2. {Value={type=string}}],
  3. data=[
  4. {Key=agent_15periods_query_timeouts, Value=0},
  5. {Key=agent_15periods_connect_timeouts, Value=0},
  6. {Key=agent_15periods_connect_failures, Value=0},
  7. {Key=agent_15periods_network_errors, Value=0},
  8. {Key=agent_15periods_connect_failures, Value=0},
  9. {Key=agent_15periods_wrong_replies, Value=0},
  10. {Key=agent_15periods_unexpected_closings, Value=0},
  11. {Key=agent_15periods_warnings, Value=0},
  12. {Key=agent_15periods_succeeded_queries, Value=439},
  13. {Key=agent_15periods_msecsperquery, Value=233.73},
  14. ],
  15. error=,
  16. total=0,
  17. warning=}

Finally, you can check the status of the agents in a specific distributed table. It can be done with a SHOW AGENT index_name STATUS statement. That statement shows the table 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).

  • SQL
  • PHP
  • Python
  • javascript
  • Java

SQL PHP Python javascript Java

  1. SHOW AGENT dist_index STATUS;
  1. $client->nodes()->agentstatus(
  2. ['body'=>
  3. ['agent'=>'dist_index']
  4. ]
  5. );
  1. utilsApi.sql('SHOW AGENT \'192.168.0.202:6714\' STATUS LIKE \'%15periods%\'')
  1. res = await utilsApi.sql("SHOW AGENT \"192.168.0.202:6714\" STATUS LIKE \"%15periods%\"");
  1. utilsApi.sql("SHOW AGENT \"192.168.0.202:6714\" STATUS LIKE \"%15periods%\"");

Response

  1. +--------------------------------------+--------------------------------+
  2. | Variable_name | Value |
  3. +--------------------------------------+--------------------------------+
  4. | dstindex_1_is_ha | 1 |
  5. | dstindex_1mirror1_id | 192.168.0.202:6713:loc |
  6. | dstindex_1mirror1_probability_weight | 0.372864 |
  7. | dstindex_1mirror1_is_blackhole | 0 |
  8. | dstindex_1mirror1_is_persistent | 0 |
  9. | dstindex_1mirror2_id | 192.168.0.202:6714:loc |
  10. | dstindex_1mirror2_probability_weight | 0.374635 |
  11. | dstindex_1mirror2_is_blackhole | 0 |
  12. | dstindex_1mirror2_is_persistent | 0 |
  13. | dstindex_1mirror3_id | dev1.manticoresearch.com:6714:loc |
  14. | dstindex_1mirror3_probability_weight | 0.252501 |
  15. | dstindex_1mirror3_is_blackhole | 0 |
  16. | dstindex_1mirror3_is_persistent | 0 |
  17. +--------------------------------------+--------------------------------+
  18. 13 rows in set (0.00 sec)
  1. Array(
  2. [dstindex_1_is_ha] => 1
  3. [dstindex_1mirror1_id] => 192.168.0.202:6713:loc
  4. [dstindex_1mirror1_probability_weight] => 0.372864
  5. [dstindex_1mirror1_is_blackhole] => 0
  6. [dstindex_1mirror1_is_persistent] => 0
  7. [dstindex_1mirror2_id] => 192.168.0.202:6714:loc
  8. [dstindex_1mirror2_probability_weight] => 0.374635
  9. [dstindex_1mirror2_is_blackhole] => 0
  10. [dstindex_1mirror2_is_persistent] => 0
  11. [dstindex_1mirror3_id] => dev1.manticoresearch.com:6714:loc
  12. [dstindex_1mirror3_probability_weight] => 0.252501
  13. [dstindex_1mirror3_is_blackhole] => 0
  14. [dstindex_1mirror3_is_persistent] => 0
  15. )
  1. {u'columns': [{u'Key': {u'type': u'string'}},
  2. {u'Value': {u'type': u'string'}}],
  3. u'data': [
  4. {u'Key': u'dstindex_1_is_ha', u'Value': u'1'},
  5. {u'Key': u'dstindex_1mirror1_id', u'Value': u'192.168.0.202:6713:loc'},
  6. {u'Key': u'dstindex_1mirror1_probability_weight', u'Value': u'0.372864'},
  7. {u'Key': u'dstindex_1mirror1_is_blackhole', u'Value': u'0'},
  8. {u'Key': u'dstindex_1mirror1_is_persistent', u'Value': u'0'},
  9. {u'Key': u'dstindex_1mirror2_id', u'Value': u'192.168.0.202:6714:loc'},
  10. {u'Key': u'dstindex_1mirror2_probability_weight', u'Value': u'0.374635'},
  11. {u'Key': u'dstindex_1mirror2_is_blackhole', u'Value': u'0'},
  12. {u'Key': u'dstindex_1mirror2_is_persistent', u'Value': u'439'},
  13. {u'Key': u'dstindex_1mirror3_id', u'Value': u'dev1.manticoresearch.com:6714:loc'},
  14. {u'Key': u'dstindex_1mirror3_probability_weight', u'Value': u' 0.252501'},
  15. {u'Key': u'dstindex_1mirror3_is_blackhole', u'Value': u'0'},
  16. {u'Key': u'dstindex_1mirror3_is_persistent', u'Value': u'439'}
  17. ],
  18. u'error': u'',
  19. u'total': 0,
  20. u'warning': u''}
  1. {"columns": [{"Key": {"type": "string"}},
  2. {"Value": {"type": "string"}}],
  3. "data": [
  4. {"Key": "dstindex_1_is_ha", "Value": "1"},
  5. {"Key": "dstindex_1mirror1_id", "Value": "192.168.0.202:6713:loc"},
  6. {"Key": "dstindex_1mirror1_probability_weight", "Value": "0.372864"},
  7. {"Key": "dstindex_1mirror1_is_blackhole", "Value": "0"},
  8. {"Key": "dstindex_1mirror1_is_persistent", "Value": "0"},
  9. {"Key": "dstindex_1mirror2_id", "Value": "192.168.0.202:6714:loc"},
  10. {"Key": "dstindex_1mirror2_probability_weight", "Value": "0.374635"},
  11. {"Key": "dstindex_1mirror2_is_blackhole", "Value": "0"},
  12. {"Key": "dstindex_1mirror2_is_persistent", "Value": "439"},
  13. {"Key": "dstindex_1mirror3_id", "Value": "dev1.manticoresearch.com:6714:loc"},
  14. {"Key": "dstindex_1mirror3_probability_weight", "Value": " 0.252501"},
  15. {"Key": "dstindex_1mirror3_is_blackhole", "Value": "0"},
  16. {"Key": "dstindex_1mirror3_is_persistent", "Value": "439"}
  17. ],
  18. "error": "",
  19. "total": 0,
  20. "warning": ""}
  1. {columns=[{Key={type=string}},
  2. {Value={type=string}}],
  3. data=[
  4. {Key=dstindex_1_is_ha, Value=1},
  5. {Key=dstindex_1mirror1_id, Value=192.168.0.202:6713:loc},
  6. {Key=dstindex_1mirror1_probability_weight, Value=0.372864},
  7. {Key=dstindex_1mirror1_is_blackhole, Value=0},
  8. {Key=dstindex_1mirror1_is_persistent, Value=0},
  9. {Key=dstindex_1mirror2_id, Value=192.168.0.202:6714:loc},
  10. {Key=dstindex_1mirror2_probability_weight, Value=0.374635},
  11. {Key=dstindex_1mirror2_is_blackhole, Value=0},
  12. {Key=dstindex_1mirror2_is_persistent, Value=439},
  13. {Key=dstindex_1mirror3_id, Value=dev1.manticoresearch.com:6714:loc},
  14. {Key=dstindex_1mirror3_probability_weight, Value= 0.252501},
  15. {Key=dstindex_1mirror3_is_blackhole, Value=0},
  16. {Key=dstindex_1mirror3_is_persistent, Value=439}
  17. ],
  18. error=,
  19. total=0,
  20. warning=}

SHOW CHARACTER SET

  1. 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.

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

SHOW META

  1. 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

SQL

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

Response

  1. +--------+--------------+
  2. | id | story_author |
  3. +--------+--------------+
  4. | 300263 | throwaway37 |
  5. | 713503 | mahmud |
  6. | 716804 | mahmud |
  7. | 776906 | jimbokun |
  8. | 753332 | foxhop |
  9. +--------+--------------+
  10. 5 rows in set (0.01 sec)
  11. +----------------+--------+
  12. | Variable_name | Value |
  13. +----------------+--------+
  14. | total | 5 |
  15. | total_found | 266385 |
  16. | total_relation | eq |
  17. | time | 0.012 |
  18. | keyword[0] | one |
  19. | docs[0] | 224387 |
  20. | hits[0] | 310327 |
  21. | keyword[1] | three |
  22. | docs[1] | 18181 |
  23. | hits[1] | 21102 |
  24. | keyword[2] | two |
  25. | docs[2] | 63251 |
  26. | hits[2] | 75961 |
  27. +----------------+--------+
  28. 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

SQL

  1. SELECT id,channel_id FROM records WHERE MATCH('one|two|three') limit 5;
  2. SHOW META;

Response

  1. +--------+--------------+
  2. | id | story_author |
  3. +--------+--------------+
  4. | 300263 | throwaway37 |
  5. | 713503 | mahmud |
  6. | 716804 | mahmud |
  7. | 776906 | jimbokun |
  8. | 753332 | foxhop |
  9. +--------+--------------+
  10. 5 rows in set (0.01 sec)
  11. +-----------------------+--------+
  12. | Variable_name | Value |
  13. +-----------------------+--------+
  14. | total | 5 |
  15. | total_found | 266385 |
  16. | total_relation | eq |
  17. | time | 0.011 |
  18. | cpu_time | 18.004 |
  19. | agents_cpu_time | 0.000 |
  20. | io_read_time | 0.000 |
  21. | io_read_ops | 0 |
  22. | io_read_kbytes | 0.0 |
  23. | io_write_time | 0.000 |
  24. | io_write_ops | 0 |
  25. | io_write_kbytes | 0.0 |
  26. | agent_io_read_time | 0.000 |
  27. | agent_io_read_ops | 0 |
  28. | agent_io_read_kbytes | 0.0 |
  29. | agent_io_write_time | 0.000 |
  30. | agent_io_write_ops | 0 |
  31. | agent_io_write_kbytes | 0.0 |
  32. | keyword[0] | one |
  33. | docs[0] | 224387 |
  34. | hits[0] | 310327 |
  35. | keyword[1] | three |
  36. | docs[1] | 18181 |
  37. | hits[1] | 21102 |
  38. | keyword[2] | two |
  39. | docs[2] | 63251 |
  40. | hits[2] | 75961 |
  41. +-----------------------+--------+
  42. 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

SQL

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

Response

  1. +--------+--------------+
  2. | id | story_author |
  3. +--------+--------------+
  4. | 300263 | throwaway37 |
  5. | 713503 | mahmud |
  6. | 716804 | mahmud |
  7. | 776906 | jimbokun |
  8. | 753332 | foxhop |
  9. +--------+--------------+
  10. 5 rows in set (0.01 sec)
  11. mysql> show meta;
  12. +---------------------+--------+
  13. | Variable_name | Value |
  14. +---------------------+--------+
  15. | total | 5 |
  16. | total_found | 266385 |
  17. | total_relation | eq |
  18. | time | 0.012 |
  19. | local_fetched_docs | 307212 |
  20. | local_fetched_hits | 407390 |
  21. | local_fetched_skips | 24 |
  22. | predicted_time | 56 |
  23. | keyword[0] | one |
  24. | docs[0] | 224387 |
  25. | hits[0] | 310327 |
  26. | keyword[1] | three |
  27. | docs[1] | 18181 |
  28. | hits[1] | 21102 |
  29. | keyword[2] | two |
  30. | docs[2] | 63251 |
  31. | hits[2] | 75961 |
  32. +---------------------+--------+
  33. 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

SQL

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

Response

  1. +--------+--------------+
  2. | id | story_author |
  3. +--------+--------------+
  4. | 300263 | throwaway37 |
  5. | 713503 | mahmud |
  6. | 716804 | mahmud |
  7. | 776906 | jimbokun |
  8. | 753332 | foxhop |
  9. +--------+--------------+
  10. 5 rows in set (0.01 sec)
  11. +----------------+--------+
  12. | Variable_name | Value |
  13. +----------------+--------+
  14. | total | 5 |
  15. | total_found | 266385 |
  16. | total_relation | eq |
  17. | time | 0.011 |
  18. | keyword[0] | one |
  19. | docs[0] | 224387 |
  20. | hits[0] | 310327 |
  21. | keyword[1] | three |
  22. | docs[1] | 18181 |
  23. | hits[1] | 21102 |
  24. | keyword[2] | two |
  25. | docs[2] | 63251 |
  26. | hits[2] | 75961 |
  27. +----------------+--------+
  28. 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

SQL

  1. SHOW META LIKE 'total%';

Response

  1. +----------------+--------+
  2. | Variable_name | Value |
  3. +----------------+--------+
  4. | total | 5 |
  5. | total_found | 266385 |
  6. | total_relation | eq |
  7. +----------------+--------+
  8. 3 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

SQL

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

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  2. | id | price | brand_id | title | brand_name | property | j | categories |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  4. | 1 | 306 | 1 | Product Ten Three | Brand One | Six_Ten | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11 |
  5. ...
  6. +----------+----------+
  7. | brand_id | count(*) |
  8. +----------+----------+
  9. | 1 | 1013 |
  10. ...
  11. +-------+----------+
  12. | price | count(*) |
  13. +-------+----------+
  14. | 306 | 7 |
  15. ...
  16. +------------+----------+
  17. | categories | count(*) |
  18. +------------+----------+
  19. | 10 | 2436 |
  20. ...
  21. +---------------+-------+
  22. | Variable_name | Value |
  23. +---------------+-------+
  24. | multiplier | 4 |
  25. +---------------+-------+
  26. 1 row in set (0.00 sec)

SHOW META and query optimizer

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

SQL

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

Response

  1. +----------------+----------------------------------+
  2. | Variable_name | Value |
  3. +----------------+----------------------------------+
  4. | total | 1 |
  5. | total_found | 1 |
  6. | total_relation | eq |
  7. | time | 0.016 |
  8. | index | tip_amount:SecondaryIndex (100%) |
  9. +----------------+----------------------------------+
  10. 5 rows in set (0.00 sec)

SHOW META for PQ tables

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

SQL

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

Response

  1. +------+
  2. | id |
  3. +------+
  4. | 2 |
  5. +------+
  6. 1 row in set (0.00 sec)
  7. +-----------------------+-----------+
  8. | Name | Value |
  9. +-----------------------+-----------+
  10. | Total | 0.000 sec |
  11. | Queries matched | 1 |
  12. | Queries failed | 0 |
  13. | Document matched | 1 |
  14. | Total queries stored | 2 |
  15. | Term only queries | 2 |
  16. | Fast rejected queries | 1 |
  17. +-----------------------+-----------+
  18. 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

SQL

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

Response

  1. +------+
  2. | id |
  3. +------+
  4. | 2 |
  5. +------+
  6. 1 row in set (0.00 sec)
  7. +-------------------------+-----------+
  8. | Name | Value |
  9. +-------------------------+-----------+
  10. | Total | 0.000 sec |
  11. | Setup | 0.000 sec |
  12. | Queries matched | 1 |
  13. | Queries failed | 0 |
  14. | Document matched | 1 |
  15. | Total queries stored | 2 |
  16. | Term only queries | 2 |
  17. | Fast rejected queries | 1 |
  18. | Time per query | 69 |
  19. | Time of matched queries | 69 |
  20. +-------------------------+-----------+
  21. 10 rows in set (0.00 sec)

SHOW THREADS

  1. 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

SQL JSON PHP Python javascript Java

  1. SHOW THREADS;
  1. POST /cli -d "SHOW THREADS"
  1. $client->nodes()->threads();
  1. utilsApi.sql('SHOW THREADS')
  1. res = await utilsApi.sql('SHOW THREADS');
  1. utilsApi.sql("SHOW THREADS");

Response

  1. *************************** 1. row ***************************
  2. Tid: 31797
  3. Name: work_3
  4. Proto: mysql
  5. State: query
  6. Host: 127.0.0.1:43388
  7. ConnID: 4931
  8. Time: 0.000903
  9. Work time: 2s
  10. Work time CPU: 0us
  11. Thd efficiency: 0.00%
  12. Jobs done: 2066
  13. Last job took: 930us
  14. In idle: No (working)
  15. Info: insert into t values(0,'abc'),(0,'def')
  16. *************************** 2. row ***************************
  17. Tid: 31799
  18. Name: work_5
  19. Proto: mysql
  20. State: query
  21. Host: 127.0.0.1:43390
  22. ConnID: 4932
  23. Time: 0.000042
  24. Work time: 2s
  25. Work time CPU: 0us
  26. Thd efficiency: 0.00%
  27. Jobs done: 2072
  28. Last job took: 66us
  29. In idle: No (working)
  30. Info: show threads
  1. {
  2. "columns": [
  3. {
  4. "Tid": {
  5. "type": "string"
  6. }
  7. },
  8. {
  9. "Name": {
  10. "type": "string"
  11. }
  12. },
  13. {
  14. "Proto": {
  15. "type": "string"
  16. }
  17. },
  18. {
  19. "State": {
  20. "type": "string"
  21. }
  22. },
  23. {
  24. "Host": {
  25. "type": "string"
  26. }
  27. },
  28. {
  29. "ConnID": {
  30. "type": "string"
  31. }
  32. },
  33. {
  34. "Time": {
  35. "type": "string"
  36. }
  37. },
  38. {
  39. "Work time": {
  40. "type": "string"
  41. }
  42. },
  43. {
  44. "Work time CPU": {
  45. "type": "string"
  46. }
  47. },
  48. {
  49. "Thd efficiency": {
  50. "type": "string"
  51. }
  52. },
  53. {
  54. "Jobs done": {
  55. "type": "string"
  56. }
  57. },
  58. {
  59. "Last job took": {
  60. "type": "string"
  61. }
  62. },
  63. {
  64. "In idle": {
  65. "type": "string"
  66. }
  67. },
  68. {
  69. "Info": {
  70. "type": "string"
  71. }
  72. }
  73. ],
  74. "data": [
  75. {
  76. "Tid": 6844,
  77. "Name": "work_3",
  78. "Proto": "http",
  79. "State": "query",
  80. "Host": "127.0.0.1:51752",
  81. "ConnID": 91,
  82. "Time": 3245,
  83. "Work time": "2h",
  84. "Work time CPU": "0us",
  85. "Thd efficiency": "0.00%",
  86. "Jobs done": 1073587960,
  87. "Last job took": "16ms",
  88. "In idle": "No (working)",
  89. "Info": "show_threads"
  90. }
  91. ],
  92. "total": 0,
  93. "error": "",
  94. "warning": ""
  95. }
  1. Array
  2. (
  3. [31796] => Array
  4. (
  5. [Name] => work_2
  6. [Proto] => mysql
  7. [State] => query
  8. [Host] => 127.0.0.1:39320
  9. [ConnID] => 2897
  10. [Time] => 517
  11. [Work time] => 1s
  12. [Work time CPU] => 0us
  13. [Thd efficiency] => 0.00%
  14. [Jobs done] => 1228
  15. [Last job took] => 526us
  16. [In idle] => No (working)
  17. [Info] => insert into t values(0,'abc'),(0,'def')
  18. )
  19. [31798] => Array
  20. (
  21. [Name] => work_4
  22. [Proto] => http
  23. [State] => query
  24. [Host] => 127.0.0.1:37748
  25. [ConnID] => 2898
  26. [Time] => 38
  27. [Work time] => 883ms
  28. [Work time CPU] => 0us
  29. [Thd efficiency] => 0.00%
  30. [Jobs done] => 1237
  31. [Last job took] => 76us
  32. [In idle] => No (working)
  33. [Info] => show_threads
  34. )
  35. )
  1. {
  2. u'columns': [
  3. {
  4. u'Tid': {
  5. u'type': u'string'
  6. }
  7. },
  8. {
  9. u'Name': {
  10. u'type': u'string'
  11. }
  12. },
  13. {
  14. u'Proto': {
  15. u'type': u'string'
  16. }
  17. },
  18. {
  19. u'State': {
  20. u'type': u'string'
  21. }
  22. },
  23. {
  24. u'Host': {
  25. u'type': u'string'
  26. }
  27. },
  28. {
  29. u'ConnID': {
  30. u'type': u'string'
  31. }
  32. },
  33. {
  34. u'Time': {
  35. u'type': u'string'
  36. }
  37. },
  38. {
  39. u'Work time': {
  40. u'type': u'string'
  41. }
  42. },
  43. {
  44. u'Work time CPU': {
  45. u'type': u'string'
  46. }
  47. },
  48. {
  49. u'Thd efficiency': {
  50. u'type': u'string'
  51. }
  52. },
  53. {
  54. u'Jobs done': {
  55. u'type': u'string'
  56. }
  57. },
  58. {
  59. u'Last job took': {
  60. u'type': u'string'
  61. }
  62. },
  63. {
  64. u'In idle': {
  65. u'type': u'string'
  66. }
  67. },
  68. {
  69. u'Info': {
  70. u'type': u'string'
  71. }
  72. }
  73. ],
  74. u'data': [
  75. {
  76. u'Tid': 6844,
  77. u'Name': u'work_3',
  78. u'Proto': u'http',
  79. u'State': u'query',
  80. u'Host': u'127.0.0.1:51752',
  81. u'ConnID': 91,
  82. u'Time': 3245,
  83. u'Work time': u'2h',
  84. u'Work time CPU': u'0us',
  85. u'Thd efficiency': u'0.00%',
  86. u'Jobs done': 1073587960,
  87. u'Last job took': u'16ms',
  88. u'In idle': u'No (working)',
  89. u'Info': u'show_threads'
  90. }
  91. ],
  92. u'total': 0,
  93. u'error': u'',
  94. u'warning': u''
  95. }
  1. {
  2. "columns": [
  3. {
  4. "Tid": {
  5. "type": "string"
  6. }
  7. },
  8. {
  9. "Name": {
  10. "type": "string"
  11. }
  12. },
  13. {
  14. "Proto": {
  15. "type": "string"
  16. }
  17. },
  18. {
  19. "State": {
  20. "type": "string"
  21. }
  22. },
  23. {
  24. "Host": {
  25. "type": "string"
  26. }
  27. },
  28. {
  29. "ConnID": {
  30. "type": "string"
  31. }
  32. },
  33. {
  34. "Time": {
  35. "type": "string"
  36. }
  37. },
  38. {
  39. "Work time": {
  40. "type": "string"
  41. }
  42. },
  43. {
  44. "Work time CPU": {
  45. "type": "string"
  46. }
  47. },
  48. {
  49. "Thd efficiency": {
  50. "type": "string"
  51. }
  52. },
  53. {
  54. "Jobs done": {
  55. "type": "string"
  56. }
  57. },
  58. {
  59. "Last job took": {
  60. "type": "string"
  61. }
  62. },
  63. {
  64. "In idle": {
  65. "type": "string"
  66. }
  67. },
  68. {
  69. "Info": {
  70. "type": "string"
  71. }
  72. }
  73. ],
  74. "data": [
  75. {
  76. "Tid": 6844,
  77. "Name": "work_3",
  78. "Proto": "http",
  79. "State": "query",
  80. "Host": "127.0.0.1:51752",
  81. "ConnID": 91,
  82. "Time": 3245,
  83. "Work time": "2h",
  84. "Work time CPU": "0us",
  85. "Thd efficiency": "0.00%",
  86. "Jobs done": 1073587960,
  87. "Last job took": "16ms",
  88. "In idle": "No (working)",
  89. "Info": "show_threads"
  90. }
  91. ],
  92. "total": 0,
  93. "error": "",
  94. "warning": ""
  95. }
  1. {
  2. columns=[
  3. {
  4. Tid={
  5. type=string
  6. }
  7. },
  8. {
  9. Name={
  10. type=string
  11. }
  12. },
  13. {
  14. Proto={
  15. type=string
  16. }
  17. },
  18. {
  19. State={
  20. type=string
  21. }
  22. },
  23. {
  24. Host={
  25. type=string
  26. }
  27. },
  28. {
  29. ConnID={
  30. type=string
  31. }
  32. },
  33. {
  34. Time={
  35. type=string
  36. }
  37. },
  38. {
  39. Work time={
  40. type=string
  41. }
  42. },
  43. {
  44. Work time CPU={
  45. type=string
  46. }
  47. },
  48. {
  49. Thd efficiency={
  50. type=string
  51. }
  52. },
  53. {
  54. Jobs done={
  55. type=string
  56. }
  57. },
  58. {
  59. Last job took={
  60. type=string
  61. }
  62. },
  63. {
  64. In idle={
  65. type=string
  66. }
  67. },
  68. {
  69. Info={
  70. type=string
  71. }
  72. }
  73. ],
  74. data=[
  75. {
  76. Tid=6844,
  77. Name=work_3,
  78. Proto=http,
  79. State=query,
  80. Host=127.0.0.1:51752,
  81. ConnID=91,
  82. Time=3245,
  83. Work time=2h,
  84. Work time CPU=0us,
  85. Thd efficiency=0.00%,
  86. Jobs done=1073587960,
  87. Last job took=16ms,
  88. In idle=No (working),
  89. Info=show_threads
  90. }
  91. ],
  92. total=0,
  93. error=,
  94. warning=
  95. }

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

SQL JSON PHP Python javascript Java

  1. SHOW THREADS OPTION columns=30\G
  1. POST /cli -d "SHOW THREADS OPTION columns=30"
  1. $client->nodes()->threads(['body'=>['columns'=>30]]);
  1. utilsApi.sql('SHOW THREADS OPTION columns=30')
  1. res = await utilsApi.sql('SHOW THREADS OPTION columns=30');
  1. utilsApi.sql("SHOW THREADS OPTION columns=30");

Response

  1. mysql> show threads option columns=30\G
  2. *************************** 1. row ***************************
  3. Tid: 9156
  4. Name: work_2
  5. Proto: mysql
  6. State: query
  7. Host: 127.0.0.1:53298
  8. ConnID: 20112
  9. Time: 0.002291
  10. Work time: 12s
  11. Work time CPU: 0us
  12. Thd efficiency: 0.00%
  13. Jobs done: 8497
  14. Last job took: 2ms
  15. In idle: No (working)
  16. Info: insert into t values(0,'abc'),
  17. *************************** 2. row ***************************
  18. Tid: 9159
  19. Name: work_5
  20. Proto: mysql
  21. State: query
  22. Host: 127.0.0.1:57698
  23. ConnID: 8196
  24. Time: 0.000042
  25. Work time: 11s
  26. Work time CPU: 0us
  27. Thd efficiency: 0.00%
  28. Jobs done: 8547
  29. Last job took: 78us
  30. In idle: No (working)
  31. Info: show threads option columns=30
  32. 2 rows in set (0.00 sec)
  1. {
  2. "columns": [
  3. {
  4. "Tid": {
  5. "type": "string"
  6. }
  7. },
  8. {
  9. "Name": {
  10. "type": "string"
  11. }
  12. },
  13. {
  14. "Proto": {
  15. "type": "string"
  16. }
  17. },
  18. {
  19. "State": {
  20. "type": "string"
  21. }
  22. },
  23. {
  24. "Host": {
  25. "type": "string"
  26. }
  27. },
  28. {
  29. "ConnID": {
  30. "type": "string"
  31. }
  32. },
  33. {
  34. "Time": {
  35. "type": "string"
  36. }
  37. },
  38. {
  39. "Work time": {
  40. "type": "string"
  41. }
  42. },
  43. {
  44. "Work time CPU": {
  45. "type": "string"
  46. }
  47. },
  48. {
  49. "Thd efficiency": {
  50. "type": "string"
  51. }
  52. },
  53. {
  54. "Jobs done": {
  55. "type": "string"
  56. }
  57. },
  58. {
  59. "Last job took": {
  60. "type": "string"
  61. }
  62. },
  63. {
  64. "In idle": {
  65. "type": "string"
  66. }
  67. },
  68. {
  69. "Info": {
  70. "type": "string"
  71. }
  72. }
  73. ],
  74. "data": [
  75. {
  76. "Tid": 6844,
  77. "Name": "work_3",
  78. "Proto": "http",
  79. "State": "query",
  80. "Host": "127.0.0.1:51752",
  81. "ConnID": 91,
  82. "Time": 3245,
  83. "Work time": "2h",
  84. "Work time CPU": "0us",
  85. "Thd efficiency": "0.00%",
  86. "Jobs done": 1073587960,
  87. "Last job took": "16ms",
  88. "In idle": "No (working)",
  89. "Info": "show_threads"
  90. }
  91. ],
  92. "total": 0,
  93. "error": "",
  94. "warning": ""
  95. }
  1. Array
  2. (
  3. [31795] => Array
  4. (
  5. [Name] => work_1
  6. [Proto] => mysql
  7. [State] => query
  8. [Host] => 127.0.0.1:57676
  9. [ConnID] => 12075
  10. [Time] => 326
  11. [Work time] => 3s
  12. [Work time CPU] => 0us
  13. [Thd efficiency] => 0.00%
  14. [Jobs done] => 5092
  15. [Last job took] => 335us
  16. [In idle] => No (working)
  17. [Info] => insert into t values(0,'abc'),
  18. )
  19. [31797] => Array
  20. (
  21. [Name] => work_3
  22. [Proto] => http
  23. [State] => query
  24. [Host] => 127.0.0.1:56104
  25. [ConnID] => 12076
  26. [Time] => 33
  27. [Work time] => 4s
  28. [Work time CPU] => 0us
  29. [Thd efficiency] => 0.00%
  30. [Jobs done] => 5045
  31. [Last job took] => 82us
  32. [In idle] => No (working)
  33. [Info] => show_threads
  34. )
  35. )
  1. {
  2. u'columns': [
  3. {
  4. u'Tid': {
  5. u'type': u'string'
  6. }
  7. },
  8. {
  9. u'Name': {
  10. u'type': u'string'
  11. }
  12. },
  13. {
  14. u'Proto': {
  15. u'type': u'string'
  16. }
  17. },
  18. {
  19. u'State': {
  20. u'type': u'string'
  21. }
  22. },
  23. {
  24. u'Host': {
  25. u'type': u'string'
  26. }
  27. },
  28. {
  29. u'ConnID': {
  30. u'type': u'string'
  31. }
  32. },
  33. {
  34. u'Time': {
  35. u'type': u'string'
  36. }
  37. },
  38. {
  39. u'Work time': {
  40. u'type': u'string'
  41. }
  42. },
  43. {
  44. u'Work time CPU': {
  45. u'type': u'string'
  46. }
  47. },
  48. {
  49. u'Thd efficiency': {
  50. u'type': u'string'
  51. }
  52. },
  53. {
  54. u'Jobs done': {
  55. u'type': u'string'
  56. }
  57. },
  58. {
  59. u'Last job took': {
  60. u'type': u'string'
  61. }
  62. },
  63. {
  64. u'In idle': {
  65. u'type': u'string'
  66. }
  67. },
  68. {
  69. u'Info': {
  70. u'type': u'string'
  71. }
  72. }
  73. ],
  74. u'data': [
  75. {
  76. u'Tid': 6844,
  77. u'Name': u'work_3',
  78. u'Proto': u'http',
  79. u'State': u'query',
  80. u'Host': u'127.0.0.1:51752',
  81. u'ConnID': 91,
  82. u'Time': 3245,
  83. u'Work time': u'2h',
  84. u'Work time CPU': u'0us',
  85. u'Thd efficiency': u'0.00%',
  86. u'Jobs done': 1073587960,
  87. u'Last job took': u'16ms',
  88. u'In idle': u'No (working)',
  89. u'Info': u'show_threads'
  90. }
  91. ],
  92. u'total': 0,
  93. u'error': u'',
  94. u'warning': u''
  95. }
  1. {
  2. "columns": [
  3. {
  4. "Tid": {
  5. "type": "string"
  6. }
  7. },
  8. {
  9. "Name": {
  10. "type": "string"
  11. }
  12. },
  13. {
  14. "Proto": {
  15. "type": "string"
  16. }
  17. },
  18. {
  19. "State": {
  20. "type": "string"
  21. }
  22. },
  23. {
  24. "Host": {
  25. "type": "string"
  26. }
  27. },
  28. {
  29. "ConnID": {
  30. "type": "string"
  31. }
  32. },
  33. {
  34. "Time": {
  35. "type": "string"
  36. }
  37. },
  38. {
  39. "Work time": {
  40. "type": "string"
  41. }
  42. },
  43. {
  44. "Work time CPU": {
  45. "type": "string"
  46. }
  47. },
  48. {
  49. "Thd efficiency": {
  50. "type": "string"
  51. }
  52. },
  53. {
  54. "Jobs done": {
  55. "type": "string"
  56. }
  57. },
  58. {
  59. "Last job took": {
  60. "type": "string"
  61. }
  62. },
  63. {
  64. "In idle": {
  65. "type": "string"
  66. }
  67. },
  68. {
  69. "Info": {
  70. "type": "string"
  71. }
  72. }
  73. ],
  74. "data": [
  75. {
  76. "Tid": 6844,
  77. "Name": "work_3",
  78. "Proto": "http",
  79. "State": "query",
  80. "Host": "127.0.0.1:51752",
  81. "ConnID": 91,
  82. "Time": 3245,
  83. "Work time": "2h",
  84. "Work time CPU": "0us",
  85. "Thd efficiency": "0.00%",
  86. "Jobs done": 1073587960,
  87. "Last job took": "16ms",
  88. "In idle": "No (working)",
  89. "Info": "show_threads"
  90. }
  91. ],
  92. "total": 0,
  93. "error": "",
  94. "warning": ""
  95. }
  1. {
  2. columns=[
  3. {
  4. Tid={
  5. type=string
  6. }
  7. },
  8. {
  9. Name={
  10. type=string
  11. }
  12. },
  13. {
  14. Proto={
  15. type=string
  16. }
  17. },
  18. {
  19. State={
  20. type=string
  21. }
  22. },
  23. {
  24. Host={
  25. type=string
  26. }
  27. },
  28. {
  29. ConnID={
  30. type=string
  31. }
  32. },
  33. {
  34. Time={
  35. type=string
  36. }
  37. },
  38. {
  39. Work time={
  40. type=string
  41. }
  42. },
  43. {
  44. Work time CPU={
  45. type=string
  46. }
  47. },
  48. {
  49. Thd efficiency={
  50. type=string
  51. }
  52. },
  53. {
  54. Jobs done={
  55. type=string
  56. }
  57. },
  58. {
  59. Last job took={
  60. type=string
  61. }
  62. },
  63. {
  64. In idle={
  65. type=string
  66. }
  67. },
  68. {
  69. Info={
  70. type=string
  71. }
  72. }
  73. ],
  74. data=[
  75. {
  76. Tid=6844,
  77. Name=work_3,
  78. Proto=http,
  79. State=query,
  80. Host=127.0.0.1:51752,
  81. ConnID=91,
  82. Time=3245,
  83. Work time=2h,
  84. Work time CPU=0us,
  85. Thd efficiency=0.00%,
  86. Jobs done=1073587960,
  87. Last job took=16ms,
  88. In idle=No (working),
  89. Info=show_threads
  90. }
  91. ],
  92. total=0,
  93. error=,
  94. warning=
  95. }