You are here
Limiting MySQL tmpdir size
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
Literature
- Shinguz's blog
- Log in or register to post comments