Managing ibtmp1 File Growth on MySQL 5.7

Question

We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where we thought we might fill up disks and whatnot.

From the (slightly vague) docs on the subject, it looks like the file can only grow in size, and the only way to reduce it is to restart the server (I take note of the comment about bad queries using up lots of temp space being the cause of the problem).

Is there actually any way to manage this file, whilst keeping the server online? I know disk space isn't such a limiting factor these days, but in the cloudy world we now live in, it costs money to keep extending disks without ever being able to shrink them again.

Answer 1

Unfortunately, all you can do is what said in my earlier post

• SET GLOBAL innodb_fast_shutdown = 0;
• Shutdown mysqld
• delete ibtmp1
• Start MySQL DB

ALTERNATIVE

Attach another volume to the VM and map ibtmp1 to that other volume using

• innodb_temp_data_file_path (As of MySQL 5.7.1)
• innodb_tmpdir (As of MySQL 5.7.11)

Please be sure to read these docs carefully. Also, try to optimize your queries to avoid make large tmp tables.