Any way to reduce 'FTS___INDEX_1.ibd' file sizes in MySQL database folder?

Question

I've got a big database with about 240Gb of data and indexes (as read from phpMyAdmin - My tables are in innodb). The database folder on the server however, has the size close to 400Gb, while my ibdata1 file has the size about 6Gb, causing to ran out of space on my SSD.

I investigated this and it seems that there are many files like FTS_0000000000000f86_00000000000019db_INDEX_1.ibd with tens of gigabytes in size. They seem (based on their filenames) my fulltext indexes for their associated tables, and there are 6 of each (FTS_*_INDEX_1.ibd to FTS_*_INDEX_6.ibd).

I've done my searches and have came across these posts:

Howto: Clean a mysql InnoDB storage engine?

and

Database space doesn't match ibdata1 size

I've asked my question in comments on these two questions/answers with no reply yet. So I decided to directly ask my question here.

If I do the 'InnoDB Cleanup' with innodb_file_per_table enabled (as suggested in the first link above), would I still have lots of these huge files starting like FTS_\*_INDEX_1.ibd in my database folder? Is this approach only helping to reduce ibdata1 file size? Would regular OPTIMIZE TABLE on these huge tables (as suggested) help me regarding my problem?

Thanks!

UPDATE:

Here is the list of files bigger than 1Gb reverse sorted by file size (filenames are altered):

-rw-rw----. 1 mysql mysql  1300234240 Jan 30 18:28 FTS_0000000000000fc2_DELETED.ibd
-rw-rw----. 1 mysql mysql  1375731712 Jan  7 21:41 FTS_0000000000000f86_00000000000019db_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  1585446912 Jan 30 23:17 FTS_0000000000001000_0000000000001a68_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  1593835520 Jan  7 21:41 FTS_0000000000000f86_00000000000019bf_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  1673527296 Jan 29 23:41 FTS_0000000000001000_DELETED.ibd
-rw-rw----. 1 mysql mysql  1824522240 Jan  7 21:41 FTS_0000000000000f86_00000000000019cd_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  2172649472 Jan 30 01:16 FTS_0000000000001073_0000000000001b3c_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  2281701376 Jan  7 21:41 FTS_0000000000000f86_00000000000019b1_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  2357198848 Jan 31 02:53 FTS_0000000000000fc2_0000000000001a0f_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  2495610880 Jan 28 13:59 FTS_0000000000000fc2_0000000000001a2b_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  2906652672 Jan 30 23:18 FTS_0000000000001000_0000000000001a76_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  3984588800 Jan 30 23:18 FTS_0000000000001000_0000000000001a6f_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  4135583744 Jan 30 08:03 FTS_0000000000000fc2_00000000000019fa_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  5716836352 Jan 28 13:59 FTS_0000000000000fc2_0000000000001a01_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  6400507904 Jan 31 05:39 my_k.ibd
-rw-rw----. 1 mysql mysql  7449083904 Jan  7 21:41 FTS_0000000000000f86_00000000000019d4_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  8115978240 Jan  7 21:41 FTS_0000000000000f86_00000000000019c6_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  8308916224 Jan 30 08:03 FTS_0000000000000fc2_0000000000001a16_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  8434745344 Jan  7 21:41 FTS_0000000000000f86_00000000000019b8_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  9244246016 Jan  7 21:41 FTS_0000000000000f86_00000000000019aa_INDEX_1.ibd
-rw-rw----. 1 mysql mysql  9714008064 Jan 30 01:16 my_a.ibd
-rw-rw----. 1 mysql mysql 12738101248 Jan 31 05:43 my_s.ibd
-rw-rw----. 1 mysql mysql 14038335488 Jan 31 02:53 FTS_0000000000000fc2_0000000000001a24_INDEX_1.ibd
-rw-rw----. 1 mysql mysql 19906166784 Jan 30 08:03 FTS_0000000000000fc2_0000000000001a1d_INDEX_1.ibd
-rw-rw----. 1 mysql mysql 21185429504 Jan 31 05:43 my_p_s.ibd
-rw-rw----. 1 mysql mysql 29242687488 Jan 31 02:54 FTS_0000000000000fc2_0000000000001a32_INDEX_1.ibd
-rw-rw----. 1 mysql mysql 30131879936 Jan  5 16:35 my_p.ibd
-rw-rw----. 1 mysql mysql 47085256704 Jan 31 05:43 my_a_c.ibd
-rw-rw----. 1 mysql mysql 76499910656 Jan 31 05:43 my_p_c.ibd
-rw-rw----. 1 mysql mysql 76743180288 Jan 30 00:09 my_r.ibd

Just show us the sizes of all the (relevant) files in the directory; I'm getting lost in the handwaving. — Rick James, Jan 30 '17 at 23:26
I have actually 8 tables that have sizes over a gigabyte and about 20 `FTS_*_INDEX_1.idb` files with sizes above a gigabyte. How do you want me to show you the files and their sizes? Like a list in the main question? — SAVAFA, Jan 31 '17 at 00:22
In Unix, `ls -l` in the directory. In Windows `dir`. Either will show the names and the sizes. — Rick James, Jan 31 '17 at 01:47
I _think_ you may benefit from dropping and rebuilding an InnoDB `FULLTEXT` index. See [_Optimizing InnoDB Full-Text Indexes_](https://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html) And perhaps other references in the manual. — Rick James, Feb 01 '17 at 03:11

score 5 · Answer 1 · answered Jan 31 '17 at 02:55

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);

The problem I've got here is that my 400Gb SSD is almost full and I cannot maneuver a lot. I think I'd dump the database and import it again. This should recreate all the indexes including FT indexes, and hopefully make some space. I'll get back here if I have any update on that. — SAVAFA, Jan 31 '17 at 05:12
Unlike rebuilding the entire table, dropping the FULLTEXT indexes and then adding them again shouldn't require any additional disk space beyond what's already being used. If you do a dump and re-import it, that should make the files smaller by reclaiming wasted space due to updates and deletes, but it will not gain the full space savings you get if you drop/add the full text indexes. — Ike Walker, Jan 31 '17 at 13:21
I just started to redefine my FT indexes after an unsuccessful dump-import process of the whole database. I removed old indexes easily but in redefining some of the indexes (those that are on columns with a paragraph of text) the mysql connection gets lost with 'mysql gone away problem'. I'm doing these using phpMyAdmin — SAVAFA, Feb 09 '17 at 01:07
I can clarify that in my case a simple OPTIMIZE reduced gigabyte sized files down to files or just a couple of megabytes. This should definitely be the first thing to try. — AdamJones, Apr 22 '20 at 19:12

score 3 · Answer 2 · edited Jan 31 '17 at 06:42

3

The *.ibd files are all InnoDB tablespace files. They contain data and/or indexes for InnoDB tables that are stored in the file-per-table manner. Typically the filenames match the table they store, as you have no doubt figured out.

The FTS_*.ibd files are indexes for InnoDB's implementation of full-text index. This is a new feature introduced in MySQL 5.6, and there's not much documented knowledge about the storage characteristics. Apparently they are pretty bulky for some reason. I have no idea if OPTIMIZE TABLE has any effect on the fulltext indexes.

One way you might get rid of these big files is of course to drop any fulltext indexes you defined for InnoDB tables.

Some other fulltext indexing product like ElasticSearch or Apache Solr or Sphinx Search may store their indexes more compactly. I did a comparison of the fulltext indexing solutions here:

Full Text Search Throwdown

InnoDB's fulltext index is the slowest solution I tested, other than table-scans with LIKE '%pattern%'.

edited Jan 31 '17 at 06:42

SAVAFA

818
8
23

answered Jan 31 '17 at 02:30

Bill Karwin

538,548
86
673
828

Thanks Bill, The problem I've got here is that my 400Gb SSD is almost full and I cannot maneuver a lot. I think I'd dump the database and import it again. This should recreate all the indexes including FT indexes, and hopefully make some space. I'll get back here if I have any update on that. Regarding your slideshow, which I've seen that before, I think I should stick to slide 64 of 69! :) I have no idea how to migrate to something like Sphinx Search and what complications it can bring in. – SAVAFA Jan 31 '17 at 05:17
Bill, the process of dumping and regenerating the whole database was not successful. It took more than 48 hours and still processing, so I cancelled it. I just started to redefine my FT indexes then. I removed old indexes easily but in redefining some of the indexes (those that are on columns with a paragraph of text) the mysql connection gets lost with 'mysql gone away problem'. I'm doing these using phpMyAdmin – SAVAFA Feb 09 '17 at 01:09
If you're so short on space, this is one of the few times I would suggest switching to MyISAM, at least for the table that needs the fulltext index. MyISAM usually stores data more compactly than InnoDB. I manage one db server where we use MyISAM because the data literally would not fit on that server if we were to use InnoDB. – Bill Karwin Feb 09 '17 at 01:14
1

FWIW, I never use phpMyAdmin. I work at the command-line. And make sure to work in a shell running `tmux` or `screen` so if your connection drops, whatever you were doing in that window continues, and you can reattach to that session. – Bill Karwin Feb 09 '17 at 01:15

Any way to reduce 'FTS_*_*_INDEX_1.ibd' file sizes in MySQL database folder?

2 Answers2

Any way to reduce 'FTS___INDEX_1.ibd' file sizes in MySQL database folder?