Skip to content

Instantly share code, notes, and snippets.

@splacento-incomm

splacento-incomm/gist:a2895551d36f66b3e715286ae232767a

Last active July 27, 2023 09:34
Show Gist options
  • Save splacento-incomm/a2895551d36f66b3e715286ae232767a to your computer and use it in GitHub Desktop.
Save splacento-incomm/a2895551d36f66b3e715286ae232767a to your computer and use it in GitHub Desktop.
Download ZIP
Find all system files in mysql db FTS issue
Raw
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`
PS. See this: https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment