0

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
0

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.