Enquire
MySQLperformance

Experiment: MySQL tmpdir on tmpfs

Point tmpdir at tmpfs to speed spills — and know the OOM failure mode.

MySQL writes on-disk internal temporary tables and some sort/group intermediates to tmpdir. On temp-heavy workloads that path becomes hidden IO against the data volume.

Setup

[mysqld]
tmpdir = /dev/shm/mysql-tmp
mkdir -p /dev/shm/mysql-tmp
chown mysql:mysql /dev/shm/mysql-tmp
# Size explicitly. Default /dev/shm is often ~50% of RAM.
mount | grep /dev/shm
SHOW VARIABLES LIKE 'tmpdir';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Watch Created_tmp_disk_tables under a fixed workload before and after.

Helps when

  • Large sorts/groups that cannot use an index
  • Complex joins creating intermediates
  • Spare RAM and measured disk temp creation

Hurts when

  • A runaway query that used to fill a disk partition now OOMs the host or triggers reclaim storms that stall InnoDB
  • Multiple mysqld instances share one undersized tmpfs
  • You never capped or monitored tmpfs usage

Temps vanish on reboot — expected for ephemeral files. Binary logs and tablespaces must not live on tmpfs.

Order of operations

Fix indexes and query shape first. RAM tmpdir is a targeted optimisation, not a substitute for schema work. If Created_tmp_disk_tables barely moves, your pain is elsewhere (buffer pool, tablespace IO, locks).