Find all system files in mysql db FTS issue

gistfile1.txt

magento2 db server was crashing and we had to investigate why.
Long story short: it ran out of space. 220GB ssd with 0 bytes free.
Mysql folder size 70GB while db itself is somewhere near 300MB.
So most of space was used by:
FTS_0000000000274d82_000000000031bd04_INDEX_1.ibd
FTS_0000000000274d82_000000000031bd04_INDEX_2.ibd
FTS_0000000000274d82_000000000031bd04_INDEX_3.ibd
FTS_0000000000274d82_000000000031bd04_INDEX_4.ibd
FTS_0000000000274d82_000000000031bd04_INDEX_5.ibd
FTS_0000000000274d82_000000000031bd04_INDEX_6.ibd

Full text indexes. 
How it was solved and to which table this belongs to?

FTS naming is:  FTS_TABLEID_INDEXID_INDEX_*.ibd



SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'DATABASE_NAME_HERE/%' order by table_id;

So to find out which table is it we need to convert it to decimal, so 274d82 is 2575746. And there it is.
There is a simpler method: just order the query by tables_id and search for your big file id. 
First lower table_id containing table name is your problematic table.

For example:
+----------+-------------------------------------------------------------------+---------+
| table_id | name                                                              | space   |
+----------+-------------------------------------------------------------------+---------+
|  2575746 | c1magento/mst_misspell_index                                      | 2575728 |
|  2575747 | c1magento/FTS_0000000000274d82_DELETED                            | 2575729 |
|  2575748 | c1magento/FTS_0000000000274d82_DELETED_CACHE                      | 2575730 |
|  2575749 | c1magento/FTS_0000000000274d82_BEING_DELETED                      | 2575731 |
|  2575750 | c1magento/FTS_0000000000274d82_BEING_DELETED_CACHE                | 2575732 |
|  2575751 | c1magento/FTS_0000000000274d82_CONFIG                             | 2575733 |
|  2575752 | c1magento/FTS_0000000000274d82_000000000031bd04_INDEX_1           | 2575734 |
|  2575753 | c1magento/FTS_0000000000274d82_000000000031bd04_INDEX_2           | 2575735 |
|  2575754 | c1magento/FTS_0000000000274d82_000000000031bd04_INDEX_3           | 2575736 |
|  2575755 | c1magento/FTS_0000000000274d82_000000000031bd04_INDEX_4           | 2575737 |
|  2575756 | c1magento/FTS_0000000000274d82_000000000031bd04_INDEX_5           | 2575738 |
|  2575757 | c1magento/FTS_0000000000274d82_000000000031bd04_INDEX_6           | 2575739 |

In both cases its mst_misspell_index .

In this case 70GB of index files was caused by mst_misspell_index which is part of Mirasvit's Search Spell-Correction for Magento 2  ( https://mirasvit.com/magento-2-extensions/search-spell-correction.html ).
Awesome extension to crash servers.

So in order to fix this we need is convert 31bd04 to decimal, which is 3259652.

SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
       WHERE index_id = 3259652;
       
       
There it is. Index MST_MISSPELL_INDEX_TRIGRAM is causing issues.

+----------+----------------------------+----------+---------+
| index_id | name                       | table_id | space   |
+----------+----------------------------+----------+---------+
|  3259652 | MST_MISSPELL_INDEX_TRIGRAM |  2575746 | 2575728 |
+----------+----------------------------+----------+---------+

So simple command in mysql fixed that:

OPTIMIZE TABLE  `mst_misspell_index`




As you suspected, the FTS_*.ibd files are the InnoDB FULLTEXT index files. The best way to shrink those files is typically to drop and recreate your FULLTEXT indexes. Doing an OPTIMIZE TABLE may or may not help, depending on whether you have innodb_optimize_fulltext_only enabled, but the safest bet to reclaim space is the drop/add.

On an insert-only workload the drop/add will usually make the files smaller, and if those tables get a large volume of updates and/or deletes then the size savings of a drop/add should be even greater. The presence of large FTS_*_DELETED.ibd files implies that you have deleted some data from those tables, so doing a drop/add of the indexes will save you some disk space.

You can use SHOW CREATE TABLE to find out the names and columns of the existing FULLTEXT indexes in order to recreate them properly.

For example:
```
mysql > show create table your_table\G
*************************** 1. row ***************************
       Table: your_table
Create Table: CREATE TABLE `your_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`),
  FULLTEXT KEY `fti_idx` (`col1`,`col2`)
) ENGINE=InnoDB;
1 row in set (0.00 sec)
```

Then you can drop and add the index in a single ALTER TABLE statement:

```
alter table your_table
  drop index fti_idx,
  add fulltext index fti_idx (col1,col2);
```


PS. See this: https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html