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:
- Runs INSERT for each row
- Makes a much larger mysqldump file
- 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 !!!