4 ms·
What I want to know is how people deal with innodb's dirty horrible secret - how ibdata can grow to infinite massive sizes and can never be reduced, ever. Regar
by ck2 13y ago
What I want to know is how people deal with innodb's dirty horrible secret - how ibdata can grow to infinite massive sizes and can never be reduced, ever. Regardless if you are using "file per table".
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.
[1] http://bugs.mysql.com/bug.php?id=1341 http://bugs.mysql.com/bug.php?id=1341
- lotsofcows 13y agoYou want to "defrag" a live database? You're brave. Dump it and rebuild it - you should be able to do a node at a time.
- ck2 13y agoWith terabyte sized databases, thousands of tables, not really an option to dump and reload. 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.
- HarrisonFisk 13y agoThe easiest way is to avoid running into the problem. 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.
- jolan 13y agoWith a default my.cnf + innodb_file_per_table=1, ibdata0/ibdata1 will stay at 512M which isn't a big deal.
- crazygringo 13y agoI agree it's not great, and annoying in theory. 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.