0
537
ViewsHow large does MySQL ibtmp1 Temporary Tablespace grow?
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.
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:
- If this file continues to grow and fill the SSD, our DB will stop working.
- 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?
1 answers
Answer question0
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.