How to clean or resize the ibtmp1 file in MySQL?

Question

MySQL 5.7 introduces a new file ibtmp1 for storing temporary data in InnoDB to increase the performance.

But I have noted that its size increases continuously. On my db server its sizes increases to 92GB.

Is there any way of reducing size or deleting the file without restarting the server ?

Thanks

Possibly useful: http://dba.stackexchange.com/questions/136627/managing-ibtmp1-file-growth-on-mysql-5-7 — Tim Biegeleisen, Dec 19 '16 at 06:06
@TimBiegeleisen This solution required restart, and I can't afford downtime. — Aman Aggarwal, Dec 19 '16 at 06:07
@TimBiegeleisen I am looking a solution to resolve this without service restart because in my case this file increases very rapidly.. — Aman Aggarwal, Dec 19 '16 at 06:17
I upvoted your question, this is as much as I can do at the moment. I hope you get an answer. — Tim Biegeleisen, Dec 19 '16 at 06:18
(Not an immediate solution) I suggest you look critically at the queries that need huge tmp tables. Good queries with good indexes won't chew up 92GB of tmp. — Rick James, Dec 20 '16 at 21:51
Also, long-running queries can chew up tmp space due to saving undo stuff. — Rick James, Dec 20 '16 at 21:56
If you must restart, look into saving and reloading the buffer_pool. This should decrease the restart time. — Rick James, Dec 20 '16 at 21:58

score 47 · Accepted Answer · edited Oct 07 '19 at 10:18

47

The ibtmp1 once created can't be shrink by any method without restarting mysql service.

There are two ways to handle it:

Precaution : At the time of server start you should limit the size of this file as:

 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

where max 5G means this file size limits to 5GB.

Cure : If file already created you need to restart service:

SET GLOBAL innodb_fast_shutdown = 0;
Shutdown MySQL
remove ibtmp1
start MySQL.

edited Oct 07 '19 at 10:18

Avatar

answered Dec 27 '16 at 10:15

Aman Aggarwal

1

Running AWS RDS MySQL 5.7, a "failover-with-reboot" was sufficient to truncate this file. – Alex Jan 18 '19 at 05:43
1

this question was particular for native mysql not for cloud. – Aman Aggarwal Jan 20 '19 at 03:02
9

Mini-Tutorial: Edit file `/etc/mysql/mysql.conf.d/mysqld.cnf`. Add as last line: `innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G` (or xG). Restart Mysql: `sudo service mysql restart` --- Check the file size at `/var/lib/mysql/ibtmp1`. – Avatar Oct 07 '19 at 10:22
I found it was unnecessary to remove ibtmp1, the file was resized when I restarted the mysql service. Ver 14.14 – Jon Hulka Mar 04 '20 at 17:09
1

@JonHulka the question is how to remove if you can't restart - So precaution is better than cure.. – Aman Aggarwal Mar 05 '20 at 09:47
@AmanAggarwal Precaution works both ways. I was erring on the side of doing as little as possible to mess with how MySQL manages its data - it probably makes no difference. I've had a bad experience in the past moving ib* files around. I didn't have to restart the server, just the MySQL service. – Jon Hulka Mar 05 '20 at 18:27

score 6 · Answer 2 · answered Jun 08 '20 at 17:27

6

In mysql 5.7 and higher all you need to do to reclaim the space used by the ibtmp1 file is restart the service.

You do not have to set GLOBAL innodb_fast_shutdown = 0; or manually delete the file.

answered Jun 08 '20 at 17:27

Geoff_Clapp

score 4 · Answer 3 · answered Dec 17 '21 at 10:57

To solve this problem just do the following steps:

1- run this command:
sudo nano /etc/mysql/my.cnf

2- Add the following row under [mysqld] row
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

3- Save the file.

4- Now we need to free up some disk space and delete ibtmp1, we can do that by restarting the SQL server:

sudo service mysql restart

3 Answers3