Table settings and status

SHOW TABLE STATUS

SHOW TABLE STATUS is an SQL statement that displays various per-table statistics.

The syntax is:

  1. SHOW TABLE index_name STATUS

Displayed statistics include:

  • index_type: for now that is one of disk, rt, percolate, template, and distributed.
  • indexed_documents and indexed_bytes: number of indexed documents and their text size in bytes, respectively.
  • field_tokens_XXX: sums of per-field lengths (in tokens) over the entire table (that is used internally in BM25A and BM25F functions for ranking purposes). Only available for tables built with index_field_lengths=1.
  • ram_bytes: total size (in bytes) of RAM-resident table portion.
  • disk_bytes: total size (in bytes) of all table files.
  • disk_mapped: total size of file mappings.
  • disk_mapped_cached: total size of file mappings actually cached in RAM.
  • disk_mapped_doclists and disk_mapped_cached_doclists: part of the total and cached mappings belonging to document lists.
  • disk_mapped_hitlists and disk_mapped_cached_hitlists: part of the total and cached mappings belonging to hit lists. Values for doclists and hitlists are shown separately since they’re usually huge (say, about 90% size of the whole table).
  • killed_documents and killed_rate: the first one indicates the number of deleted documents and the rate of deleted/indexed. Technically deletion of a document just means that the document gets suppressed in search output, but physically it still persists in a table and will be purged only after merging/optimizing the table.
  • ram_chunk: size of RAM chunk of real-time or percolate table.
  • ram_chunk_segments_count: RAM chunk internally consists of segments, usually there are no more than 32 of them. This line shows the current count.
  • disk_chunks: number of disk chunks of the real-time table.
  • mem_limit: actual value of rt_mem_limit for the table.
  • mem_limit_rate: the rate after which the ram chunk will be flushed as a disk chunk, e.g. if rt_mem_limit is 128M and the rate is 50%, a new disk chunk will be saved as soon as the ram chunk exceeds 64M.
  • ram_bytes_retired: represents size of garbage in RAM chunks (for example, deleted or replaced documents not yet finally wiped away).
  • tid and tid_saved: represent the state of saving the table (real-time or percolate only). tid gets increased with each change (transaction). tid_saved shows max tid of the state saved in a RAM chunk in <table>.ram file. When the numbers are different, some changes exist only in RAM and also backed by binlog (if enabled). Performing FLUSH TABLE or scheduling periodical flushing causes these changes to be saved. After flushing the binlog gets cleared, and the tid_saved represents the actual new state.
  • query_time_*: query execution time statistics of last 1 minute, 5 minutes, 15 minutes and total since server start; the data is encapsulated as a JSON object which includes the number of queries and min, max, avg, 95 and 99 percentile values.
  • found_rows_*: statistics of rows found by queries; provided for last 1 minute, 5 minutes, 15 minutes and total since server start; the data is encapsulated as a JSON object which includes the number of queries and min, max, avg, 95 and 99 percentile values.
  • SQL
  • PHP
  • Python
  • Javascript
  • Java

SQL PHP Python Javascript Java

  1. mysql> SHOW TABLE statistic STATUS;
  1. $index->status();
  1. utilsApi.sql('SHOW TABLE statistic STATUS')
  1. res = await utilsApi.sql('SHOW TABLE statistic STATUS');
  1. utilsApi.sql("SHOW TABLE statistic STATUS");

Response

  1. +-----------------------------+--------------------------------------------------------------------------+
  2. | Variable_name               | Value                                                                    |
  3. +-----------------------------+--------------------------------------------------------------------------+
  4. | index_type                  | rt                                                                       |
  5. | indexed_documents           | 146000                                                                   |
  6. | indexed_bytes               | 149504000                                                                |
  7. | ram_bytes                   | 87674788                                                                 |
  8. | disk_bytes                  | 1762811                                                                  |
  9. | disk_mapped                 | 794147                                                                   |
  10. | disk_mapped_cached          | 802816                                                                   |
  11. | disk_mapped_doclists        | 0                                                                        |
  12. | disk_mapped_cached_doclists | 0                                                                        |
  13. | disk_mapped_hitlists        | 0                                                                        |
  14. | disk_mapped_cached_hitlists | 0                                                                        |
  15. | killed_documents            | 0                                                                        |
  16. | killed_rate                 | 0.00%                                                                    |
  17. | ram_chunk                   | 86865484                                                                 |
  18. | ram_chunk_segments_count    | 24                                                                       |
  19. | disk_chunks                 | 1                                                                        |
  20. | mem_limit                   | 134217728                                                                |
  21. | mem_limit_rate              | 95.00%                                                                   |
  22. | ram_bytes_retired           | 0                                                                        |
  23. | tid                         | 0                                                                        |
  24. | tid_saved                   | 0                                                                        |
  25. | query_time_1min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  26. | query_time_5min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  27. | query_time_15min            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  28. | query_time_total            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  29. | found_rows_1min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  30. | found_rows_5min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  31. | found_rows_15min            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  32. | found_rows_total            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
  33. +-----------------------------+--------------------------------------------------------------------------+
  34. 29 rows in set (0.00 sec)
  1. Array(
  2.     [index_type] => rt
  3.     [indexed_documents] => 3
  4.     [indexed_bytes] => 0
  5.     [ram_bytes] => 6678
  6.     [disk_bytes] => 611
  7.     [ram_chunk] => 990
  8.     [ram_chunk_segments_count] => 2
  9.     [mem_limit] => 134217728
  10.     [ram_bytes_retired] => 0
  11.     [tid] => 15
  12.     [query_time_1min] => {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}
  13.     [query_time_5min] => {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}
  14.     [query_time_15min] => {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}
  15.     [query_time_total] => {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}
  16.     [found_rows_1min] => {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}
  17.     [found_rows_5min] => {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}
  18.     [found_rows_15min] => {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}
  19.     [found_rows_total] => {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}
  21. )
  1. {u'columns': [{u'Key': {u'type': u'string'}},
  2.               {u'Value': {u'type': u'string'}}],
  3.  u'data': [
  4.    {u'Key': u'index_type', u'Value': u'rt'}
  5.     {u'Key': u'indexed_documents', u'Value': u'3'}
  6.     {u'Key': u'indexed_bytes', u'Value': u'0'}
  7.     {u'Key': u'ram_bytes', u'Value': u'6678'}
  8.     {u'Key': u'disk_bytes', u'Value': u'611'}
  9.     {u'Key': u'ram_chunk', u'Value': u'990'}
  10.     {u'Key': u'ram_chunk_segments_count', u'Value': u'2'}
  11.     {u'Key': u'mem_limit', u'Value': u'134217728'}
  12.     {u'Key': u'ram_bytes_retired', u'Value': u'0'}
  13.     {u'Key': u'tid', u'Value': u'15'}
  14.     {u'Key': u'query_time_1min', u'Value': u'{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}'}
  15.     {u'Key': u'query_time_5min', u'Value': u'{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}'}
  16.     {u'Key': u'query_time_15min', u'Value': u'{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}'}
  17.     {u'Key': u'query_time_total', u'Value': u'{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}'}
  18.     {u'Key': u'found_rows_1min', u'Value': u'{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}'}
  19.     {u'Key': u'found_rows_5min', u'Value': u'{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}'}
  20.     {u'Key': u'found_rows_15min', u'Value': u'{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}'}
  21.     {u'Key': u'found_rows_total', u'Value': u'{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}'}],
  22.  u'error': u'',
  23.  u'total': 0,
  24.  u'warning': u''}
  1. {"columns": [{"Key": {"type": "string"}},
  2.               {"Value": {"type": "string"}}],
  3.  "data": [
  4.    {"Key": "index_type", "Value": "rt"}
  5.     {"Key": "indexed_documents", "Value": "3"}
  6.     {"Key": "indexed_bytes", "Value": "0"}
  7.     {"Key": "ram_bytes", "Value": "6678"}
  8.     {"Key": "disk_bytes", "Value": "611"}
  9.     {"Key": "ram_chunk", "Value": "990"}
  10.     {"Key": "ram_chunk_segments_count", "Value": "2"}
  11.     {"Key": "mem_limit", "Value": "134217728"}
  12.     {"Key": "ram_bytes_retired", "Value": "0"}
  13.     {"Key": "tid", "Value": "15"}
  14.     {"Key": "query_time_1min", "Value": "{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}"}
  15.     {"Key": "query_time_5min", "Value": "{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}"}
  16.     {"Key": "query_time_15min", "Value": "{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}"}
  17.     {"Key": "query_time_total", "Value": "{"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001}"}
  18.     {"Key": "found_rows_1min", "Value": "{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}"}
  19.     {"Key": "found_rows_5min", "Value": "{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}"}
  20.     {"Key": "found_rows_15min", "Value": "{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}"}
  21.     {"Key": "found_rows_total", "Value": "{"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3}"}],
  22.  "error": "",
  23.  "total": 0,
  24.  "warning": ""}
  1. {columns=[{ Key : { type=string }},
  2.               { Value : { type=string }}],
  3.   data : [
  4.    { Key=index_type, Value=rt}
  5.     { Key=indexed_documents, Value=3}
  6.     { Key=indexed_bytes, Value=0}
  7.     { Key=ram_bytes, Value=6678}
  8.     { Key=disk_bytes, Value=611}
  9.     { Key=ram_chunk, Value=990}
  10.     { Key=ram_chunk_segments_count, Value=2}
  11.     { Key=mem_limit, Value=134217728}
  12.     { Key=ram_bytes_retired, Value=0}
  13.     { Key=tid, Value=15}
  14.     { Key=query_time_1min, Value={queries:1, avg_sec:0.001, min_sec:0.001, max_sec:0.001, pct95_sec:0.001, pct99_sec:0.001}}
  15.     { Key=query_time_5min, Value={queries:1, avg_sec:0.001, min_sec:0.001, max_sec:0.001, pct95_sec:0.001, pct99_sec:0.001}}
  16.     { Key=query_time_15min, Value={queries:1, avg_sec:0.001, min_sec:0.001, max_sec:0.001, pct95_sec:0.001, pct99_sec:0.001}}
  17.     { Key=query_time_total, Value={queries:1, avg_sec:0.001, min_sec:0.001, max_sec:0.001, pct95_sec:0.001, pct99_sec:0.001}}
  18.     { Key=found_rows_1min, Value={queries:1, avg:3, min:3, max:3, pct95:3, pct99:3}}
  19.     { Key=found_rows_5min, Value={queries:1, avg:3, min:3, max:3, pct95:3, pct99:3}}
  20.     { Key=found_rows_15min, Value={queries:1, avg:3, min:3, max:3, pct95:3, pct99:3}}
  21.     { Key=found_rows_total, Value={queries:1, avg:3, min:3, max:3, pct95:3, pct99:3}}],
  22.   error= ,
  23.   total=0,
  24.   warning= }

SHOW TABLE SETTINGS

SHOW TABLE SETTINGS is an SQL statement that displays per-table settings in a format that is compatible with the config file.

The syntax is:

  1. SHOW TABLE index_name[.N | CHUNK N] SETTINGS

Output is similar to the --dumpconfig option of the indextool utility. The report provides a breakdown of all the table settings, including tokenizer and dictionary options.

  • SQL

SQL

  1. SHOW TABLE forum SETTINGS;

Response

  1. +---------------+-----------------------------------------------------------------------------------------------------------+
  2. | Variable_name | Value                                                                                                     |
  3. +---------------+-----------------------------------------------------------------------------------------------------------+
  4. | settings      | min_prefix_len = 3
  5. charset_table = 0..9, A..Z->a..z, _, -, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F |
  6. +---------------+-----------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)

You can also specify a particular chunk number to view the settings of a particular chunk of an RT table. The number is 0-based.

  • SQL

SQL

  1. SHOW TABLE forum CHUNK 0 SETTINGS;

Response

  1. +---------------+-----------------------------------------------------------------------------------------------------------+
  2. | Variable_name | Value                                                                                                     |
  3. +---------------+-----------------------------------------------------------------------------------------------------------+
  4. | settings      | min_prefix_len = 3
  5. charset_table = 0..9, A..Z->a..z, _, -, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F |
  6. +---------------+-----------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)