How large does MySQL ibtmp1 Temporary Tablespace grow?

user_3556433 0
Viewed 2222

We have a MySQL Master running on a 1TB SSD with a 500GB database. As you can see from the screenshot below, we are running out of space because ibtmp1 is growing too large. It is now 194GB.

enter image description here

The MySQL Manual says:

"When the data file reaches the maximum size, queries fail with an error indicating that the table is full."

We thus have two concerns:

  1. If this file continues to grow and fill the SSD, our DB will stop working.
  2. If we limit the size of this file, say to 100GB, then if it is filled will "queries fail with an error indicating that the table is full."

I am sure the manual is misleading or we are misunderstanding, because surely it cannot be possible that the default settings of MySQL allow it to fill the disk and then fail?

temp-tables innodb mysql
1 Answers

I really like MySQL very much, but there are certain things where you can only slap your forehead. This is one of them. But first things first:

When you have such a huge ibtmp1 file, you either have

  • a query that is building a way too huge temporary table, maybe by accidentaly doing a cross join
  • a huge amount of queries that create relatively small temporary tables all at the same time
  • very long running transactions
  • exceptionally large data to handle

In all cases I would take action immediately and if possible get rid of those queries. Have a look at your slow-query-log, in the innodb status monitor or whatever tools you're using to identify those queries.

To answer your question, don't expect MySQL to use reasonable defaults everywhere.

I don't know about you, but for me a DB stopping to work because of a full disk is not an option. When queries fail it's less of a pain. And keep in mind, that we're most likely talking about queries that are faulty.

I've configured all my servers with a max size of 10GB, and I was very generous with that.

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G

Also keep in mind, that you have to restart MySQL server to shrink the ibtmp1 file. Same for setting the innodb_temp_data_file_path option. Hence the forehead slapping.

user_4474890
Related