6

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.

2

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

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

  • 1
    innodb_tmpdir seems to be only for ALTER TABLE ... ALGORITHM=INPLACE. – Rick James Apr 27 '16 at 17:41
  • 1
    That's sort of a shame - I was hoping there was some way to do something a bit smarter here. Ever-growing files aren't exactly a great thing to have in production (great work, Oracle!). – Ralph Bolton Apr 28 '16 at 20:01

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.