Mysql – Error 1041 “Out Of Memory” after pointing tmpdir to new disk space

indexmemoryMySQL

I want to index a very large column in a very large table. The default /tmp does not have enough disk space and is throwing no space left on device error.

I have a much larger filesystem partitioned where I can point innodb_tmpdir to.

  • I created a tmp dir in this partition: /mnt/partition/tmp
  • Changed the permissions of this directory to 777 chmod -R 777 /mnt/partition/tmp
  • Then I set the innodb_tmpdir variable to point to this directory via mysql client: SET VARIABLE innodb_tmpdir='/mnt/partition/tmp';
  • The variable is set correctly by executing: SHOW VARIABLES LIKE 'innodb_tmpdir';

The issue arises when I try to create an index with the following basic command structure:

create index a on b (c, d, e, f);

The following error is instantly thrown:

ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

What is with this error? Do I need to set some permissions/configs for my partition?

NOTE If I do not set innodb_tmpdir and keep everything to default, the index command runs without this issue. (obviously it crashes halfway through due to the limited disk space which is the actual reason why I am changing tmpdir

my.cnf

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=8
wait_timeout=604800
interactive_timeout=604800
local_infile=ON
binlog_expire_logs_seconds=600
skip-log-bin

swap size: 8.39GB. There is 7GB of memory on the server.

MYSQL Version 8 on Ubuntu 18.04. Machine is fully dedicated to MySQL.

After executing the create index command on new innodb_tmpdir error.log file is showing:

[ERROR] [MY-012792] [InnoDB] Cannot create temporary merge file

Best Answer

If innodb_tmpdir being set to the new location is causing an instant complaint from the server, there's a very good chance that apparmor is preventing MySQL from accessing the location or that the system is reacting to the 777 permissions, which are often rejected by a lot of server applications for obvious reasons. You will need to check your error.log file to see whether the innodb_tmpdir location is being used.

apparmor fixes are quite easy to fix from Oracle a blog a long time ago:

If you want to relocate the data directory in MySQL (in this example, to the /data/ directory), and AppArmor is not letting you, add the following two lines to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:

/data/ r,
/data/** rwk,

...and then reload the AppArmor profiles:

# service apparmor reload
Related Question