Mysql – innodb fulltext ibdata1 size

ibdatainnodbmariadbMySQLmysqldump

I am running mariadb 10 and have a large innodb table with a full text index.
I have innodb_file_per_table=ON

I've noticed though that the ibdata1 file grows at the same rate as my table file.

I have tried using mysqldump, removing the files and reloading as well as separating the undo tablespaces. Neither of those made any difference so I assume this is not a transaction overhead of some kind.

Does the ibdata1 file store the actual full text index or is there another explanation for the growth in the file?

Best Answer

First of all, with innodb_file_per_table=ON, all data and index pages are stored in the .ibd file of the table.

The growth of the system tablespace (ibdata1) can be a big headache.

I have mentioned this in earlier posts

In your case, there is transaction overhead. You have big transactions because of the mysqldump. Why ?

The default option called --opt has many options enabled, including --extended-insert. Extended inserts will cause mysqldump to INSERT INTO multiple rows in batches. Each batch of rows is a single transaction. Thus, you should expect ibdata1 to get bloated, especially if the table has TEXT or BLOB data (I suspect that since you have fulltext indexes).

The only way around this would be to run the mysqldump again with --skip-extended-insert. This will do three(3) very nasty things:

  1. Runs INSERT for each row
  2. Makes a much larger mysqldump file
  3. Take a ton longer to reload

This makes each individual INSERT transaction hundreds or thousands of times faster because you are inserting one row instead of hundreds or thousands. Unfortunately, you are doing hundreds or thousands longer to execute because of the number of INSERTs. This is just one way to prevent ibdata1 from getting bloated.

Another thing you could try would be to drop the FULLTEXT index from the table, mysqldump it with skip-extended-insert, reload it, then add the FULLTEXT index back. This could help because the adding the FULLTEXT index is not a transaction.

Give it a Try !!!

Related Question