In a previous life, when I started using MySQL, we only had MyISAM table space in MySQL. It was not being ACID compliant and so it was very error prone in case of crashes et cetera. Transactions, anyone?!?

Things changed when MySQL added the InnoDB storage engine. But to be honest InnoDB is a beast and is very complex. In the beginning all data was stored in the belly of the ibdata1 file. This means shared table data and Undo log for example. One of the big cons of the ibdata file is that it will only grow and cannot be shrinked, even when you delete data from a table. Doing a full export and import of your data is the only way to reclaim the disk space.

The most obvious reason is that all table data is stored in it. Luckily MariaDB (we’ve replaced MySQL by this time) has added the option to store data in file-per-table tablespaces. You can recognize these as “.IBD” files on your filesystem. For your info that is the innodb_file_per_table parameter. Although this gives a little performance penalty, the pros made us forget about this.

The problem

The above solution always worked as advertised; no more growing ibdata files for us! Until a couple weeks ago we noticed a customer who had a constantly growing ibdata file. First some background information, this workload is doing a lot of writes (transactions). To be honest the amount of transactions could be lower if our customer was a little bit more efficient. But they are already working on it. But let’s forget about that.

As I mentioned above one of the things stored in the ibdata file are the Undo logs. Every transaction executed is stored in the Undo log, and only if the transaction is successful it will be marked as actual data. After a while these Undo logs will be removed. In this workload transactions contain a lot of changes and could potentially grow in size. Important to know is that the Undo log does not save the changed data itselves but it’s rather a pointer to a specific history kind of data.

In this case we expected to have some long running transactions. We checked this by using show engine innodb status \G;

Looking at the Transactions part we did not see anything strange, but then I remembered that MariaDB comes with a nice and handy tool called “innochecksum. With this tool you can analyse the ibdata file and it might give you a good indication what is eating your disk space. You can call it with with this command:

innochecksum --page-type-summary ibdata1

Please note that this could take a while on big files. When ran, it will show you something like this:

The numbers above are fictitious but one thing you should notice right away is the value of the Undo log page. These are way too high! So this pointed me to the InnoDB history list length. You can get it by: show status like ‘Innodb_history_list_%’. This value gives a good idea of the amount of log pages which need to be purged. There is not an official rule of thumb here, but I would say everything above 100.000 is critical. MariaDB comes with a purge process and clearly it cannot hold up against the amount of transactions.

Solution

Our solution was quite rigoreus as we had to reclaim the disk space and we wanted to get the Undo log (actually rollback segments) out of the ibdata file. This means an export and import. The first part of the solution is to separate the Undo log. We did his by adding the following configuration:

innodb_undo_tablespaces=6
innodb_max_undo_log_size=5G
innodb_undo_log_truncate=ON

This requires an empty database. In other words, this can’t be done on databases already containing data. Also you cannot change these settings afterwards, so choose them wisely. After enabling the above options you will see six undo00[1-6] files in your /var/lib/mysql directory. You could even put these files on another disk/partition if you want. As of MariaDB 10.11 innodb_undo_tablespaces will be enabled by default (value: 2).

The other part of our solution was to double the amount of purge threads so it could handle the amount of transactions. We did this by adding:

innodb_purge_threads = 8

8 was sufficient in our case but it’s something you have to play with. Making it too high could give you a performance penalty. In case your “History Length” is still too high, maybe because of a one time action you’re performing on your database (large purge for example), you could try to lower the innodb_purge_rseg_truncate_frequency temporarily to eg. 32. This means InnoDB will give more priority to the purge process. The con of this is that it will impact your performance, so be sure you change it back to its original value when History Length is on the desired level again.

Conclusion

The InnoDB History Length is not a very well known metric to watch, but it’s a key metric you should monitor. Although the value changes a lot it should not grow in the long term as this could be an indicator the purge process cannot keep up with the amount of writes. Values higher than 100.000 should be treated as critical and could cause the following issues:

• Slow shutdowns and starts for MariaDB; the whole Undo file has to be processed again
• Performance is lower
• Ibdata file will grow

The steps we took have fixed our issues with this particular customer.