Question :
There is a file called ibtmp1
and it is consuming 138 GB. I understand, this stores uncompressed data. Do we really need these files or can we delete it? if deleting is fine, then could you please share the command or the process. Please note that I am using Windows system and MySQL Server version is 5.6
thank you.
Answer :
That file comes from MySQL 5.7, not 5.6. You can check by connecting to MySQL and running:
mysql> SELECT VERSION();
Shrinking ibtmp1
would require shutting down MySQL. You cannot just the delete the file because there will be a open file handle on that file coming from mysqld.exe
.
First, log into MySQL and run:
mysql> SET GLOBAL innodb_fast_shutdown = 0;
Next, shutdown MySQL, delet the file and start mysql back up:
C:> net stop mysql
C:> del ibtmp1
C:> net start mysql
If your ibtmp1
grew that much, then you have been running some bad queries that create large temp tables.
You can restrict size using:
**innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G**