MySQL temporary table to RAM

Best practise

Check ID/GID for MySQL

# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

Create and mount

# mkdir -p /var/lib/mysqltempinram
# chown 27:27 /var/lib/mysqltempinram
# echo "tmpfs /var/lib/mysqltempinram tmpfs rw,gid=27,uid=27,size=512M,nr_inodes=10k,mode=0700 0 0" >> <a href="/etc/fstab">/etc/fstab</a>
# mount /var/lib/mysqltempinram
# df -Th /var/lib/mysqltempinram

Add this line under [mysqld] into my.cnf configuration file:

tmpdir = /var/lib/mysqltempinram

restart mysql

Check if all is applied:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';

Test

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

 

Quick method – careful! This might make the server run out of memory!!!

Check what’s the RAM filesystem on the server

# mount | grep tmpfs
tmpfs on /dev/shm type tmpfs (rw,noexec,nosuid,nodev)

=> /dev/shm

Add this to my.cnf file:

[mysqld]
tmpdir = /dev/shm/

restart mysql

Check if all is applied:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| tmpdir | /dev/shm/ |
+---------------+-------------+

 

Test:

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0