-1

Even though we are using "innodb_file_per_table = 1" from day one. The ibdata file still continues to grow. Total size of our database is around 800GB and the ibdata file is currently around 50GB. All our tables are innodb.

How can it grow when we use innodb_file_per_table?
Could it be blobs, mediumtext or these kinds of columns?
Is there a way to shrink this considering we already use innodb_file_per_table?

Haluk
  • 619
  • 3
  • 12
  • 22

2 Answers2

6

One common cause of the tablespace growing is the undo space. Simplified: transactions that haven't committed yet keep track of changes that can be rolled-back if necessary. See the article that Phil links on further explanations for the growing tablespace.

Of course, there is still no way to shrink the tablespace other than a logical restore [see decreasing tablespace in the documentation].

If undo space is the reason yours is growing, starting in 5.6 you can store the undo logs in separate files. Some things to keep in mind:

  • This can only be done on a newly created instance, so you can separate the undo space when you do the aforementioned logical restore.
  • You cannot change the innodb_undo_tablespaces, so estimate high.
  • You can change the number of innodb_undo_logs, so this can be tuned based on your performance tests.
  • Creating the undo logs in a directory on SSD storage is recommended by specifying the innodb_undo_directory
  • You can't shrink the size of the undo space until 5.7. See this article on how it works.
  • You can't downgrade to a version below 5.6 once you've separated the undo space. I suspect this is not an issue, but it requires mentioning.

My recommendations:

  • Plan upgrade to 5.7. It's worth it for added performance schema insights alone.
  • Identify if undo space is truly why the tablespace is growing. It likely is.
  • Identify if this is growing tablespace is really a concern: are you running into performance issues? Are you running out of disk space?
  • If it is, plan for the logical restore to separate the undo space, realizing that the larger dataset will take longer time. Plan accordingly.

Additional insight on moving the undo space:

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

Community
  • 1
Derek Downey
  • 23,568
  • 11
  • 79
  • 104
1

Look at the queries you have performed. Probably one of them was really huge -- such as a massive UPDATE to a 50GB table. Guess what? It had to save the old values of that table just in case you did a ROLLBACK or the system crashed.

Consider writing "smaller" queries.

Rick James
  • 80,479
  • 5
  • 52
  • 119