Monitoring MySQL Full-Text Indexes: Parameters, Metadata Tables, and Practical Demonstrations

Continuing the discussion on MySQL full-text indexes, this article focuses on monitoring MySQL full-text indexes.

MySQL provides comprehensive metadata tables to monitor insert, update, and delete operations on full-text index tables, as well as auxiliary tables for data tracking.

The content is divided into three parts:

Part 1 – Monitoring‑related parameters.

Part 2 – Metadata tables for full-text index monitoring.

Part 3 – Practical demonstration of monitoring.

Part 1 – Full‑Text Index Monitoring Parameters

innodb_ft_aux_table : Dynamically sets the name of the full‑text index table to be monitored (e.g., database_name/table_name such as ytt/ft_sample).

innodb_ft_cache_size : Cache size for INSERT/UPDATE data of a single full‑text index table; default 8 M, range 1.6 M‑80 M.

innodb_ft_total_cache_size : Extends the cache to the whole instance rather than a single table; default 640 M, range 32 M‑1600 M.

innodb_ft_result_cache_limit : Limits the result‑set cache for full‑text searches; default 2 GB, minimum 1 M, maximum 2³²‑1 bytes.

innodb_ft_enable_diag_print : Enables extra diagnostic information in the error log; off by default.

innodb_optimize_fulltext_only : When ON, only the full‑text index is optimized; off by default.

innodb_ft_num_word_optimize : Minimum number of words required to trigger OPTIMIZE TABLE on a full‑text index.

innodb_ft_sort_pll_degree : Number of threads used for creating full‑text indexes and tokenization; default 2, range 1‑32.

Part 2 – Full‑Text Index Monitoring Metadata Tables

MySQL provides the following dictionary tables for monitoring full‑text index information:

INNODB_FT_CONFIG : Stores configuration key/value pairs. Example rows include optimize_checkpoint_limit (max time for OPTIMIZE TABLE) and synced_doc_id (next DOC_ID to be processed).

mysql> select * from information_schema.innodb_ft_config;<br/>+---------------------------+-------+<br/>| KEY                       | VALUE |<br/>+---------------------------+-------+<br/>| optimize_checkpoint_limit | 180   |<br/>| synced_doc_id             | 10    |<br/>| stopword_table_name       |       |<br/>| use_stopword              | 1     |<br/>+---------------------------+-------+<br/>4 rows in set (0.00 sec)

INNODB_FT_INDEX_TABLE : Holds the inverted index data.

INNODB_FT_INDEX_CACHE : Stores newly inserted keyword information to avoid frequent DML‑driven index rebuilds. When the cache (per‑table innodb_ft_cache_size or instance‑wide innodb_ft_total_cache_size) fills, it merges with the on‑disk auxiliary tables, either on server shutdown, manual OPTIMIZE TABLE, or when the cache is flushed.

INNODB_FT_DELETED / INNODB_FT_BEING_DELETED : Keep DOC_IDs that have been removed from the full‑text index. The latter is a snapshot taken during OPTIMIZE TABLE; it is cleared shortly after the operation.

Part 3 – Full‑Text Index Monitoring Demonstration

1. Create a sample table ft1

mysql> create table ft1(id int not null auto_increment primary key, s1 text,fulltext ft_s1 (s1));<br/>Query OK, 0 rows affected (0.39 sec)<br/><br/>mysql> insert into ft1 values (1,'mysql oracle'),(2,'xfs ext3'),(3,'dml ddl dql others'),(4,'oltp olap'),(5,'sqlserver oracle'),(6,'ntfs gfs'),(7,'insert delete update select alter'),(8,'windows linux unix solaris');<br/>Query OK, 8 rows affected (0.01 sec)

2. Add the target table to monitoring

Set the parameter innodb_ft_aux_table:

mysql> set global innodb_ft_aux_table ='ytt/ft1';<br/>Query OK, 0 rows affected (0.01 sec)

3. Query the cache before any optimization

mysql> select * from information_schema.innodb_ft_index_cache;<br/>+-----------+--------------+-------------+-----------+--------+----------+<br/>| WORD      | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |<br/>+-----------+--------------+-------------+-----------+--------+----------+<br/>| alter     |            8 |           8 |         1 |      8 |       28 |<br/>| ... (other rows) ...<br/>+-----------+--------------+-------------+-----------+--------+----------+<br/>23 rows in set (0.00 sec)

4. Trigger cache flush by enabling full‑text only optimization and running OPTIMIZE TABLE

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>mysql> optimize table ft1;<br/>+---------+----------+----------+----------+<br/>| Table   | Op       | Msg_type | Msg_text |<br/>+---------+----------+----------+----------+<br/>| ytt.ft1 | optimize | status   | OK       |<br/>+---------+----------+----------+----------+<br/>1 row in set (0.02 sec)<br/><br/>mysql> select * from information_schema.innodb_ft_index_cache;<br/>Empty set (0.00 sec)

5. Verify that keywords have been merged into the index table

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE  limit 4;<br/>+--------+--------------+-------------+-----------+--------+----------+<br/>| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |<br/>+--------+--------------+-------------+-----------+--------+----------+<br/>| alter  |            8 |           8 |         1 |      8 |       28 |<br/>| ddl    |            4 |           4 |         1 |      4 |        4 |<br/>| delete |            8 |           8 |         1 |      8 |        7 |<br/>| dml    |            4 |           4 |         1 |      4 |        0 |<br/>+--------+--------------+-------------+-----------+--------+----------+

6. Delete some rows and observe the deleted‑document table

mysql> delete from ft1 where id in (2,3,4);<br/>Query OK, 3 rows affected (0.02 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;<br/>+--------+<br/>| DOC_ID |<br/>+--------+<br/>|      3 |<br/>|      4 |<br/>|      5 |<br/>+--------+

7. Optimize again to clear the deleted‑document table

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;<br/>...<br/>mysql> optimize table ft1;<br/>...<br/>mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;<br/>Empty set (0.00 sec)

8. Enable diagnostic printing for deeper insight

First set a minimal cache size, turn on innodb_ft_enable_diag_print, and increase log verbosity:

mysql> set global innodb_ft_enable_diag_print=on;<br/>Query OK, 0 rows affected (0.00 sec)<br/>mysql> set global log_error_verbosity=3;<br/>Query OK, 0 rows affected (0.01 sec)

Insert many rows to fill the cache and observe log messages indicating synchronization events.

9. Additional operations (rebuilding clustered index, running a full‑text query, etc.)

mysql> alter table ft1 engine innodb;<br/>...<br/>mysql> select count(*) from ft1 where match(s1) against('806d');<br/>+----------+<br/>| count(*) |<br/>+----------+<br/>|      512 |<br/>+----------+

Log entries show query time, rows processed, and memory usage, providing a complete picture of full‑text index behavior.

Understanding these monitoring techniques gives a deeper insight into how MySQL handles full‑text indexes.