MySql show content of ibdata1 file, block rising exponentially, why is it growing so fast?

Question

I have a database with most of tables being stored on the InnoDB engine, however an alert raised the other day showing up that the ibdata1 file was growing exponentially.

For what I searched so far, there is no way I could resize nor shrink this file rather than start fresh again and rebuild the entire database.

However, I've read that some of the cases where this file rises too fast is when there are several transactions that might not be committing fast enough for the database to manage the file but letting it grow.

Is there a way to see what is inside this file in order to know what is taking most of the space?

Besides, is there a way to put a limit on this file so we can control when rises and when not?

Answer 1

InnoDb ruby is a great tool which does exactly what you want.

Troubleshoot

If you don't use innodb_file_per_table this can also be normal data growth so confirm this first.

If you use innodb_file_per_table then it is most likely indeed the undo history. You can confirm that by running:

mysql root@192.168.99.100:(none)> pager grep History
PAGER set to grep History.
Time: 0.000s
mysql root@192.168.99.100:(none)> show engine innodb status\G
History list length 0
Time: 0.001s

You can also check it with innodb ruby:

innodb_space -s ibdata1 undo-history-summary

This command will give you the number of pages used by undo history:

 innodb_space -s ibdata1 undo-history-summary |grep -v Page | awk '{print $1}' | sort | uniq | wc -l

Any of these will only reports current data. Unless you have a consistent high load with many long open transaction where the purge thread is unable to keep up you will mostly have free pages. It may be beneficial to plot this over time so you have an understanding of what your server is doing.

Fix

Shrinking ibdata is not possible. The only thing you can do is to dump and restore your data via mysqldump (or any other equivalent tool of your choice).

Other considerations

Since 5.6.3 it's also possible to separate your undo tablespace from the main ibdata file (https://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html). There may be some interesting thing for you.

In 5.7.5 innodb_max_undo_log_size parameter was introduced.