Change location of innodb_temporary

All we need is an easy explanation of the problem, so here it is.

I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see:

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
*************************** 1. row ***************************
             FILE_ID: 4294967293
             FILE_NAME: ./ibtmp1

I changed mysqld.cnf:

[mysqld]
innodb_tmpdir=/$newDir/
innodb_temporary=/$newDir/
innodb_temp_tablespaces_dir=/$newDir/
innodb_temp_data_file_path=/$newDir/

restarted mysqld but that doesn’t change a thing.
Same with:

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

Only if I change in the init.d script:

mysqld_safe –innodb-tmpdir=/$newDir/

I see with mysql> SELECT @@innodb_tmpdir; that the path is set (which unfortunately doesn’t affect the path of ibtmp1.

I am unsure if the mysqld.cnf is read properly, but /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options" reveals one of the conf files is the correct one.

Is there a way to do this via a mysql statement – and how looks it like?
The most easiest way, making a symbolic link in the filesystem, doesn’t work (anymore).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

mysqld --help --verbose

At about line 13, it will say where the config file is. It is probably not spelled the way you spelled it. Mine says

...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
...

Method 2

Rick James’ answer made me aware that (whyever) the conf file was not read. So I copied /etc/mysql/mysql.conf.d/mysqld.cnf to /etc/mysql/my.cnf. That one is read fine.
I then had to define (in my.cnf):

[mysqld]
innodb_tmpdir=$yourNewTmpPath
innodb_temp_data_file_path=$yourNewTmpPath/ibtmp1:50M:autoextend:max:50G
innodb_temp_tablespaces_dir=$yourNewTmpPath
...

Substitute $yourNewTmpPath with your new temp directory. (Adopt your innodb_temp_data_file_path parameters at your need (you may not need 50G for autoextend)).

Restart mysqld.

You can evaluate if the settings are properly consumed by:

mysql> SELECT @@innodb_temp_data_file_path;
+----------------------------------------------------------+
| @@innodb_temp_data_file_path                             |
+----------------------------------------------------------+
| $yourNewTmpPath/ibtmp1:50M:autoextend:max:50G |
+----------------------------------------------------------+

Now I could alter a 160GB table with ... CONVERT TO CHARACTER SET utf8mb4;. On my NFS mount it produces some temporary .nfs0000... files, which are removed from time to time and created again. I think at most they needed around 50GB at a time. I also watched IO operations of mysqld (using iotop -oPa) which amounts to >1TB for write operations and again >1TB for read operations.

So, key here was to copy the config file to a place where it is indeed consumed – and use the proper variables and values for them.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply