Skip to content

MySQL 5.6 innodb_undo_tablespaces very usefull only if …

Who has not experienced a fast growing system table space. This is related to innoDB undos (Rollback segments).

In MySQL 5.5 and in 5.6 by default undos are located in the innoDB system tablespace. Beside the space consomption issue, the second penalty is that the access to the undos generates a lot of random IOs on the system tablespace.

Once the system table space has grow too much there is no way to reclaim the space. The only solution is to recreate the database. This is very problematic for online 24/7 databases. This happens most of the time because of overgrowing undos. Under normal circumstances undos are cleaned up by an innoDB purge mechanism. In MySQL 5.6 the multi threaded purge mechanism has greatly improved this purge undo cleanup process. But even with this purge mechanism it can still happen that the undos growth gets out of control. The main reason is long running transactions or very slow queries. Both these circumstances obliges innoDB to keep undos for consistent read.
A new feature has been introduced in 5.6 that allow to have the undos out of the system tablespace. The undo tablespace can be split into multiple files located in a user defined directory.

One of the advantage of this separate directory for undos is the ability to move the random UNDOs related IOs to to a different place. This allows for example to place undos on SSD for better random IOs.

This feature is available through these 3 new configuration variables :
innodb_undo_dir – where on disk to place undos
innodb_undo_tablespaces – number of UNDO tabespaces. this number must be set at database creation
innodb_undo_logs - number of rollback segments. The number of rollback segments physically present in the system never decreases.

If you have a running MySQL 5.6 instance , you shut it down and try to change innodb_undo_tablespaces to 4 like I did you will get :

130322 22:33:57 InnoDB: Expected to open 4 undo tablespaces but was able
130322 22:33:57 InnoDB: to find only 0 undo tablespaces.
130322 22:33:57 InnoDB: Set the innodb_undo_tablespaces parameter to the
130322 22:33:57 InnoDB: correct value and retry. Suggested value is 0
130322 22:33:57 [ERROR] Plugin 'InnoDB' init function returned error.
130322 22:33:57 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

So yes MySQL 5.6 innodb_undo_tablespaces is very useful but only if this choice has been made at the database creation. The second points that must be noted is that this feature unfortunately does not help to reclaim space. So yes this features is great but it is the first step in a correct management of undos space.

innodb_undo_dir, innodb_undo_tablespaces are part of the numerous MySQL 5.6 server configuration variables. MySQL 5.6 is a major release and there are a lot of obsoleted parameters, new parameters, parameters with different default values. It is very important for users moving to MySQL 5.6 to understand all these database setting changes.
Sheeri Cabral has tracked depreciated variables. Peter Zaitsev has done a complete comparison of the default values differences between variables in MySQL 5.5 and 5.6.

-------------------- Useful Pointers -------------------------------

Reasons-for-run-away-main-innodb-tablespace June 10, 2010 Peter Zaitsev
Purge-thread-spiral-of-death June 10, 2010 Peter Zaitsev
Being-highly-irresponsible-or-howto-dos-nearly-all-rdbmss   Stewart Smith
Finding-and-killing-long-running-innodb-transactions-with-events  
Tracking-long-running-transactions APRIL 6, 2011 Yoshinori Matsunobu
Kill-mysql-procs 2012-12-27  rugmonster.org
How-to-debug-long-running-transactions-in-mysql March 8, 2011 by Baron Schwartz
Killing-idle-transactions-to-increase-database-uptime  August 13th, 2012 by Xaprb

Leave a Reply

Your email address will not be published. Required fields are marked *