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;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 10    |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
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));
Query OK, 0 rows affected (0.39 sec)
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');
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';
Query OK, 0 rows affected (0.01 sec)

3. Query the cache before any optimization

mysql> select * from information_schema.innodb_ft_index_cache;
+-----------+--------------+-------------+-----------+--------+----------+
| WORD      | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------+--------------+-------------+-----------+--------+----------+
| alter     |            8 |           8 |         1 |      8 |       28 |
| ... (other rows) ...
+-----------+--------------+-------------+-----------+--------+----------+
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;
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table ft1;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| ytt.ft1 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (0.02 sec)
mysql> select * from information_schema.innodb_ft_index_cache;
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;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| alter  |            8 |           8 |         1 |      8 |       28 |
| ddl    |            4 |           4 |         1 |      4 |        4 |
| delete |            8 |           8 |         1 |      8 |        7 |
| dml    |            4 |           4 |         1 |      4 |        0 |
+--------+--------------+-------------+-----------+--------+----------+

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

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

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

7. Optimize again to clear the deleted‑document table

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
...
mysql> optimize table ft1;
...
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
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;
Query OK, 0 rows affected (0.00 sec)
mysql> set global log_error_verbosity=3;
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;
...
mysql> select count(*) from ft1 where match(s1) against('806d');
+----------+
| count(*) |
+----------+
|      512 |
+----------+

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.