3 ms·
Did you remember to move the undo log outside of ibdata1 ? http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html http://dev.mysql.com/doc/refman/5
by ck2 12y ago
Did you remember to move the undo log outside of ibdata1 ?
http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespac...
Because in mysql, ibdata1 can never shrink, only grow.
And that's how you end up with massive ibdata1 files that cannot be managed.
Oh and undo can only be moved outside of ibdata when mysql is being initialized, not afterwards.
innodb has some serious flaws
http://www.percona.com/blog/wp-content/uploads/2010/04/InnoDB_int.png http://www.percona.com/blog/wp-content/uploads/2010/04/InnoD...
- lmz 12y agoCan you actually shrink the undo logs once you move them out of ibdata1? From your link: Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces. The most important part to control ibdata1 growth is innodb_file_per_table (default now).
- ck2 12y agoNo you cannot shrink them but more importantly they do not have to be cached. The rest of ibdata1 has to be cached for performance. Impossible to isolate the caching needs unless undo is moved outside and 99% of servers out there have probably not been setup with external undo logs because most admin do not know of this limitation after it has been configured. Only way around this is to rebuild the entire database, loads of fun.
- vtal 12y agocouldn't you use innodb_file_per_table and not worry about the huge ibdata1 file?
- ck2 12y agoNo file_per_table does not change anything about the unlimited growth of the undo log in the ibdata1 file.