Index settings and status
SHOW INDEX STATUS
is an SQL statement that displays various per-index statistics.
The syntax is:
SHOW INDEX index_name STATUS
Displayed statistics include:
index_type
: for now that is one ofdisk
,rt
,percolate
,template
, anddistributed
.indexed_documents
andindexed_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 index (that is used internally inBM25A
andBM25F
functions for ranking purposes). Only available for indexes built withindex_field_lengths=1
.ram_bytes
: total size (in bytes) of RAM-resident index portion.disk_bytes
: total size (in bytes) of all index files.disk_mapped
: total size of file mappings.disk_mapped_cached
: total size of file mappings actually cached in RAM.disk_mapped_doclists
anddisk_mapped_cached_doclists
: part of the total and cached mappings belonging to document lists.disk_mapped_hitlists
anddisk_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 index).killed_documents
andkilled_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 an index and will be purged only after merging/optimizing the index.ram_chunk
: size of RAM chunk of real-time or percolate index.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 index.mem_limit
: actual value ofrt_mem_limit
for the index.mem_limit_rate
: the rate after which the ram chunk will be flushed as a disk chunk, e.g. ifrt_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
andtid_saved
: represent the state of saving the index (real-time or percolate only).tid
gets increased with each change (transaction).tid_saved
shows maxtid
of the state saved in a RAM chunk in '.ram' file. When the numbers are different, some changes exist only in RAM and also backed by binlog (if enabled). Performing 'flush rtindex' 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
📋
mysql> SHOW INDEX 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 |
| 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 INDEX SETTINGS
is an SQL statement that displays per-index settings in a format that is compatible with the config file.
The syntax is:
SHOW INDEX 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 index settings, including tokenizer and dictionary options.
- SQL
SQL
📋
SHOW INDEX 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 may also specify a particular chunk number to view the settings of a particular chunk of an RT index. The number is 0-based.
- SQL
SQL
📋
SHOW INDEX 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)