It is a ten year old bug [1] never addressed, no tools ever made to optimize it, and the larger your data, the sooner it will come to bite you. Doesn't matter if you are using mysql, percona or mariadb.
It is a ten year old bug [1] never addressed, no tools ever made to optimize it, and the larger your data, the sooner it will come to bite you. Doesn't matter if you are using mysql, percona or mariadb.
The main reason the central table space grows is due to rollback segments. To avoid collecting a lot of them there are two tips (in addition to file per table you mentioned):
1. Use multi-threaded purge. 2. Avoid very long running transactions (ie. 12+ hours)
Once we have done that, we don't really see this problem any longer.
But if you use file-per-table, I've never actually had a problem in practice, since in production, most tables tend to only grow. And even if you remove half the rows of a large table, you're probably expecting that space to be reclaimed eventually.
If, on the other hand, you do a big restructuring where you're deleting 90% of your rows in one big swoop... then it's probably actually easier and faster to just create a new table and copy the 10% valid rows into it, then drop the old table, and rename. All space reclaimed.
It's definitely something annoying to have to keep in mind, and it's why I prefer to use MyISAM when doing big data manipulation on my local dev machine (everything's faster without transactions too). But using InnoDB in production, I've found the never-shrinking-table-files to be a nonissue.
Curious if anyone else has had different experiences.
Alter and optimize are already possible on a live innodb table, in theory it should not be exponentially harder to optimize ibdata.
Note that oracle solved this problem on their commercial database product.