0

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?

|improve this question
0

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.

|improve this answer
  • great, I might be forced to use innodb_file_per_table property, still, making the undo tablespace a separate file is also a good performance practice? – BlackStar Aug 10 '16 at 13:50
  • It really depends on your workload. Unless your servers are very heavily hit with a lot of big inserts/updates, high level of concurrency and has to frequently flush (otherwise undo history is in RAM) I wouldn't bother with this. There are other items which are higher in a optimisation priority that gives more benefit. Like innodb_log_file_size, innodb_buffer_pool_size and buffer_pool_instances. And of course proper queries (inserts and updates too) with short lived transactions makes all the difference. :) And I always recommend people do not try to prematurely optimise. – Károly Nagy Aug 10 '16 at 15:09
  • Perfect, I'll be taking all of that into consideration. Thanks a lot!. – BlackStar Aug 10 '16 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.