Best practise
Check ID/GID for MySQL
|
1 2 |
# id mysql uid=27(mysql) gid=27(mysql) groups=27(mysql) |
Create and mount
|
1 2 3 4 5 |
# 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:
|
1 |
tmpdir = /var/lib/mysqltempinram |
restart mysql
Check if all is applied:
|
1 2 |
# mysql mysql> SHOW VARIABLES LIKE 'tmpdir'; |
Test
|
1 2 3 |
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
|
1 2 |
# mount | grep tmpfs tmpfs on /dev/shm type tmpfs (rw,noexec,nosuid,nodev) |
=> /dev/shm
Add this to my.cnf file:
|
1 2 |
[mysqld] tmpdir = /dev/shm/ |
restart mysql
Check if all is applied:
|
1 2 3 4 5 6 7 |
# mysql mysql> SHOW VARIABLES LIKE 'tmpdir'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | tmpdir | /dev/shm/ | +---------------+-------------+ |
Test:
|
1 2 3 |
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 |