⪢ Table settings and status

SHOW TABLE STATUS

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

The syntax is:

SHOW TABLE index_name STATUS

Depending on index type, displayed statistic includes different set of rows:

  • template: index_type.
  • distributed: index_type, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • percolate: index_type, stored_queries, ram_bytes, disk_bytes, max_stack_need, average_stack_base, desired_thread_stack, tid, tid_saved, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • plain: index_type, indexed_documents, indexed_bytes, may be set of field_tokens_* and total_tokens, ram_bytes, disk_bytes, disk_mapped, disk_mapped_cached, disk_mapped_doclists, disk_mapped_cached_doclists, disk_mapped_hitlists, disk_mapped_cached_hitlists, killed_documents, killed_rate, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • rt: index_type, indexed_documents, indexed_bytes, may be set of field_tokens_* and total_tokens, ram_bytes, disk_bytes, disk_mapped, disk_mapped_cached, disk_mapped_doclists, disk_mapped_cached_doclists, disk_mapped_hitlists, disk_mapped_cached_hitlists, killed_documents, killed_rate, ram_chunk, ram_chunk_segments_count, disk_chunks, mem_limit, mem_limit_rate, ram_bytes_retired, locked, tid, tid_saved, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.

Here is the meaning of these values:

  • index_type: currently one of disk, rt, percolate, template, and distributed.
  • indexed_documents: number of indexed documents.
  • indexed_bytes: overall size of indexed text. Notice, this value is not strict, since in full-text index that is impossible to strictly recover back stored text to measure it.
  • stored_queries: number of percolate queries, stored in the table.
  • field_tokens_XXX: optional, total per-field lengths (in tokens) across the entire table (used internally for BM25A and BM25F ranking functions). Only available for tables built with index_field_lengths=1.
  • total_tokens: optional, overall sum of all field_tokens_XXX.
  • ram_bytes: total RAM occupied by table.
  • disk_bytes: total disk space, occupied by table.
  • 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: portion of total and cached mappings belonging to document lists.
  • disk_mapped_hitlists and disk_mapped_cached_hitlists: portion of total and cached mappings belonging to hit lists. Doclists and hitlists values are shown separately since they're typically large (e.g., about 90% of the whole table's size).
  • killed_documents and killed_rate: the first indicates the number of deleted documents and the rate of deleted/indexed. Technically, deleting a document means suppressing it in search output, but it still physically exists in the table and will only be purged after merging/optimizing the table.
  • ram_chunk: size of the RAM chunk of real-time or percolate table.
  • ram_chunk_segments_count: RAM chunk is internally composed of segments, typically no more than 32. This line shows the current count.
  • disk_chunks: number of disk chunks in the real-time table.
  • mem_limit: actual value of rt_mem_limit for the table.
  • mem_limit_rate: the rate at 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 when the RAM chunk exceeds 64M.
  • ram_bytes_retired: represents the size of garbage in RAM chunks (e.g., deleted or replaced documents not yet permanently removed).
  • locked: a value greater than 0 indicates that the table is currently locked by FREEZE. The number represents how many times the table has been frozen. For instance, a table might be frozen by manticore-backup and then frozen again by replication. It should only be completely unfrozen when no other process requires it to be frozen.
  • max_stack_need: stack space we need to calculate most complex from the stored percolate queries. That is dynamic value, depends on build details as compiler, optimization, hardware, etc.
  • average_stack_base: stack space which is usually occupied on start of calculation of percolate query.
  • desired_thread_stack: sum of above values, rounded up to 128 bytes edge. If this value is greater than thread_stack, you may not execute call pq over this table, as some stored queries will fail. Default thread_stack value is 1M (which is 1048576); other values should be configured.
  • tid and tid_saved: represent the state of saving the table. tid increases with each change (transaction). tid_saved shows the max tid of the state saved in a RAM chunk in <table>.ram file. When the numbers differ, some changes exist only in RAM and are also backed by binlog (if enabled). Performing FLUSH TABLE or scheduling periodic flushing saves these changes. After flushing, the binlog is cleared, and tid_saved represents the new actual state.
  • query_time_*, exact_query_time_*: query execution time statistics for the last 1 minute, 5 minutes, 15 minutes, and total since server start; data is encapsulated as a JSON object, including the number of queries and min, max, avg, 95, and 99 percentile values.
  • found_rows_*: statistics of rows found by queries; provided for the last 1 minute, 5 minutes, 15 minutes, and total since server start; data is encapsulated as a JSON object, including the number of queries and min, max, avg, 95, and 99 percentile values.
‹›
  • SQL
  • PHP
  • Python
  • Javascript
  • Java
  • C#
  • TypeScript
  • Go
📋
mysql> SHOW TABLE statistic STATUS;
‹›
Response
+-----------------------------+--------------------------------------------------------------------------+
| Variable_name               | Value                                                                    |
+-----------------------------+--------------------------------------------------------------------------+
| index_type                  | rt                                                                       |
| indexed_documents           | 146000                                                                   |
| indexed_bytes               | 149504000                                                                |
| ram_bytes                   | 87674788                                                                 |
| disk_bytes                  | 1762811                                                                  |
| disk_mapped                 | 794147                                                                   |
| disk_mapped_cached          | 802816                                                                   |
| disk_mapped_doclists        | 0                                                                        |
| disk_mapped_cached_doclists | 0                                                                        |
| disk_mapped_hitlists        | 0                                                                        |
| disk_mapped_cached_hitlists | 0                                                                        |
| killed_documents            | 0                                                                        |
| killed_rate                 | 0.00%                                                                    |
| ram_chunk                   | 86865484                                                                 |
| ram_chunk_segments_count    | 24                                                                       |
| disk_chunks                 | 1                                                                        |
| mem_limit                   | 134217728                                                                |
| mem_limit_rate              | 95.00%                                                                   |
| ram_bytes_retired           | 0                                                                        |
| locked                      | 0                                                                        |
| tid                         | 0                                                                        |
| tid_saved                   | 0                                                                        |
| query_time_1min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_5min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_15min            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_total            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_1min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_5min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_15min            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_total            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
+-----------------------------+--------------------------------------------------------------------------+
29 rows in set (0.00 sec)

SHOW TABLE SETTINGS

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

The syntax is:

SHOW TABLE index_name[.N | CHUNK N] SETTINGS

The output resembles the --dumpconfig option of the indextool utility. The report provides a breakdown of all table settings, including tokenizer and dictionary options.

‹›
  • SQL
SQL
📋
SHOW TABLE forum SETTINGS;
‹›
Response
+---------------+-----------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                     |
+---------------+-----------------------------------------------------------------------------------------------------------+
| settings      | min_prefix_len = 3
charset_table = 0..9, A..Z->a..z, _, -, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F |
+---------------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

‹›
  • SQL
SQL
📋
SHOW TABLE forum CHUNK 0 SETTINGS;
‹›
Response
+---------------+-----------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                     |
+---------------+-----------------------------------------------------------------------------------------------------------+
| settings      | min_prefix_len = 3
charset_table = 0..9, A..Z->a..z, _, -, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F |
+---------------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)