You are here
MySQL tmpdir on RAM-disk
MySQL temporary tables are created either in memory (as MEMORY
tables) or on disk (as MyISAM
tables). How many tables went to disk and how many tables went to memory you can find with:
mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Created_tmp_disk_tables | 49094 | | Created_tmp_tables | 37842181 | +-------------------------+----------+
Tables created in memory are typically faster than tables created on disk. Thus we want as many as possible tables to be created in memory.
To achieve this we can configure the variables accordingly:
mysql> SHOW GLOBAL VARIABLES LIKE '%table_size'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 25165824 | | tmp_table_size | 25165824 | +---------------------+----------+
All result sets which are smaller than these values can be handled as MEMORY
tables. All result sets bigger than these values are handled as MyISAM
tables an go to disk.
But there is still an other reason for tables going to disk: MEMORY
tables cannot handle TEXT
or BLOB
attributes as it often occurs in CMS like Typo3. In these cases MySQL has to do directly MyISAM
tables on disk and they are counted as Created_tmp_disk_tables
.
If these temporary disk tables are causing serious I/O performance problems one could consider to use a RAM-disk instead of normal physical disks instead.
On Linux we have 2 possibilities to create a RAM-disk: ramfs
and tmpfs
[ 1 ].
We recommend to use tmpfs
.
A RAM-disk can be created as follows:
shell> mkdir -p /mnt/ramdisk shell> mount -t tmpfs -o size=512M tmpfs /mnt/ramdisk shell> chown mysql:mysql /mnt/ramdisk
To make this persistent we have to add it to the fstab:
# # /etc/fstab # tmpfs /mnt/ramdisk tmpfs rw,mode=1777,size=512M 0 0
MySQL still writes to the default location which is found as follows:
mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+
To changes this value you have to configure your my.cnf
accordingly and restart the database...
- Shinguz's blog
- Log in or register to post comments