Limiting MySQL tmpdir size

Thu, 2021-04-29 17:37 — Shinguz

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

MySQL itself has no possibility to limit explicitly the total size nor the number of implicit temporary tables. So this can happen easily if your application runs amok or you do not have your application under control.

An sometimes there is no possibility to have an extra mount point for tmpdir because the disk is completely used by volumes etc. But you have still some space in the file system.

In this case you can, similar to a swap file, use a file in the file system as volume and mount it in a way you can use it as an separate mount point for your tmpdir directory. So in case your application runs amok it just fills up your tmpdir volume/file and not the whole / (root) filesystem.

# fallocate -l 4G /mysql-tmpdir
# mkfs.ext4 /mysql-tmpdir
# mke2fs 1.44.1 (24-Mar-2018)
Discarding device blocks: done                            
Creating filesystem with 1048576 4k blocks and 262144 inodes
Filesystem UUID: 74c51e5c-bed8-4a7d-8f1b-e89669726e1d
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (16384 blocks): done
Writing superblocks and filesystem accounting information: done 

# grep loop /proc/mounts
# mount | grep loop

# mkdir /var/lib/mysql-tmpdir
# mount -o loop=/dev/loop0 /mysql-tmpdir /var/lib/mysql-tmpdir

# df -h | grep -e mysql-tmpdir -e Size
Filesystem          Size  Used Avail Use% Mounted on
/dev/loop0          3.9G   16M  3.7G   1% /var/lib/mysql-tmpdir

# umount /var/lib/mysql-tmpdir
# chown -R mysql: /var/lib/mysql-tmpdir

Add the entry to your /etc/fstab:

/mysql-tmpdir   /var/lib/mysql-tmpdir       ext4       loop       0 0

Test the fstab entry:

# mount /mysql-tmpdir
# sudo -u mysql touch /var/lib/mysql-tmpdir/test

Configure your MySQL database accordingly (my.cnf):

tmpdir = /var/lib/mysql-tmpdir

and after database restart:

SQL> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| tmpdir        | /var/lib/mysql-tmpdir |
+---------------+-----------------------+

Then create some implicit temporary tables on disk:

# ls -la 
drwx------  2 mysql mysql  16K Apr 29 16:41  lost+found
-rw-rw----  1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAD'
-rw-rw----  1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAI'

and check if tmpdir is really capped at 4 Gibyte:

# dd if=/dev/zero of=/var/lib/mysql-tmpdir/tmp bs=1M count=5000
dd: error writing '/var/lib/mysql-tmpdir/tmp': No space left on device
3731+0 records in
3730+0 records out
3912126464 bytes (3.9 GB, 3.6 GiB) copied, 13.708 s, 285 MB/s