Ibdata is eating up my disk space
In a previous life, when I first started using MySQL, we only had MyISAM table space in MySQL. This was not ACID compliant, which meant it was very error prone with crashes and so on.
Things changed when MySQL added the InnoDB storage engine. But let’s be honest, InnoDB is a complicated beast. In the beginning, all data was stored in the belly of the ibdata1 file. This included shared table data and things like the Undo log. A major drawback of the ibdata file is that it only grows and cannot be shrunk, even if you delete data from a table. The only way to reclaim disk space is to do a full export and import of your data.
The most obvious reason for this is that all table data is stored in it. Fortunately, MariaDB (by this time we had replaced MySQL with MariaDB) added the option to store data in file-based tablespaces, recognizable as .IBD files on your filesystem. To be clear, this is the innodb_file_per_table parameter. While this comes with a slight performance penalty, the benefits made us quickly forget about it.
The problem
The above solution always worked as promised: no more growing ibdata files! Until a few weeks ago we noticed that a customer had a constantly growing ibdata file. First some background: this workload performs a lot of writes (transactions). To be honest, the number of transactions could be less if the customer was more efficient, but they are already working on that. Let's forget about that for now.
As I mentioned above, the Undo logs are stored in the ibdata file. Every executed transaction is stored in the Undo log, and only if the transaction is successful, it is marked as real data. After a while, these Undo logs are deleted. In this workload, transactions contain a lot of changes and can potentially become large. It is important to know that the Undo log does not store the changed data itself, but rather a pointer to a specific "history" version of the data.
In this case we expected some long-running transactions. We checked this by doing the following:
When we looked at the transaction part, we didn’t see anything strange. But then I remembered that MariaDB has a handy tool called innochecksum. This tool allows you to analyze the ibdata file, which can give a good indication of what is eating up your disk space. You can invoke it with the following command:
innochecksum --page-type-summary ibdata1
Please note that this can take a while for large files. When you run it, it will show something like this:
(the numbers above are fictitious)
One thing you should notice right away is the value of the Undo log pages . They are way too high! This pointed me to the InnoDB history list length . You can get this by doing: show status like 'Innodb_history_list_%'. This value gives a good idea of how many log pages are still left to be purged. There is no official rule of thumb, but I would say anything over 100,000 is critical. MariaDB does have a purge process, but it clearly couldn't cope with the amount of transactions. The solution
Our solution was quite drastic, because we needed to reclaim disk space and remove the Undo log (or actually the rollback segments) from the ibdata file. This meant an export and import. The first part of the solution was to separate the Undo log. We did this 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 cannot be done on databases that already contain data. Also, you cannot change these settings afterwards, so choose them carefully. After enabling the above options, you will see six undo00[1-6] files in your /var/lib/mysql directory. You can even place these files on a different disk/partition if you want. Since MariaDB 10.11, innodb_undo_tablespaces is enabled by default (value: 2).
The other part of our solution was to double the number of purge threads so that it could handle the transactions. We did this by adding the following:
innodb_purge_threads = 8
8 was enough in our case, but this is something you should experiment with. Setting it too high can cause performance degradation. If the History Length is still too high, for example due to a one-time action you perform on your database (like a big purge), you can try temporarily lowering the innodb_purge_rseg_truncate_frequency, for example to 32. This means that InnoDB will give more priority to the purge process. The downside of this is that it will impact your performance, so make sure you set it back to the original value once the History Length is back to the desired level.
Conclusion
The InnoDB History Length is not a widely known metric, but it is an important indicator to keep an eye on. Although the value fluctuates a lot, it should not grow in the long term. This can be a sign that the purge process cannot keep up with the amount of writes. Values above 100,000 should be considered critical and can cause the following problems:
- Slow MariaDB shutdowns and startups; entire Undo file needs to be reprocessed.
- Lower performance.
- A growing ibdata file.
The steps we took resolved this particular customer's issues.
Related Articles
Creating a database backup
Before you make any changes to your web application, it is always a good idea to make a backup of your database first. If something goes wrong, you can easily go back to the original situation. Fortunately, making a database backup is quite easy, as ...Set up mail
Set up email Your email can only receive and send emails if you have entered the correct information in the email settings. Without the correct information, it is difficult to use your email. That is why we are happy to provide you with the correct ...