Create a temporary directory of MySQL in memory

The MySQL system creates temporary tables in memory and disk (MyISAM), how to know how many temporary tables have been built on disk, and how many temporary tables are built in memory? You can learn from the following commands:

?

1234567

mysql> SHOW GLOBAL STATUS LIKE ‘Created_tmp%tables‘;+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Created_tmp_disk_tables | 49094    || Created_tmp_tables      | 37842181 |+-------------------------+----------+

Obviously, the performance of temporary tables in memory is much better than on disk, so we want to create temporary tables in memory as much as possible.

To achieve this, we first check the configuration size of the temporary table:

?

1234567

mysql> SHOW GLOBAL VARIABLES LIKE ‘%table_size‘;+---------------------+----------+| Variable_name       | Value    |+---------------------+----------+| max_heap_table_size | 25165824 || tmp_table_size      | 25165824 |+---------------------+----------+

If the size of the temporary table is smaller than the above value, it is placed in memory table on-the-go, and if larger than the above value, it is stored in the MyISAM table on the disk.

But there is one other factor that causes the MyISAM table to be stored on the disk, which is that the MEMORY table cannot handle TEXT and BLOG type data. In this case, MySQL will write directly to the MyISAM in the disk and count as the Created_tmp_disk_tables value.

If the temporary table has started to cause serious I/O performance problems, then you should consider using disk memory mapping instead of the physical disk.

On Linux we have two methods to build ram-disk in memory, namely Ramfs and TMPFS.

Here we recommend the use of TMPFS.

You can create ram-disk with the following command:

?

123	shell> mkdir-p /mnt/ramdiskshell> chown mysql:mysql /mnt/ramdiskshell> mount-t tmpfs -o size=512M tmpfs /mnt/ramdisk

In order to be created automatically the next time the system starts, we can place the following script in the/etc/fstab file:

?

1	tmpfs           /mnt/ramdisktmpfs   rw,mode=1777    0       0

MySQL still writes the data to the default disk, which can be checked by the following command:

?

123456

mysql> SHOW GLOBAL VARIABLES LIKE ‘tmpdir‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| tmpdir        | /tmp|+---------------+-------+

You can fix the my.cnf file to change the path, and then restart the database.

English original, Oschina original translation

http://www.oschina.net/question/12_79459

I've seen a similar problem on StackOverflow.

Http://stackoverflow.com/questions/7532307/skip-copying-to-tmp-table-on-disk-mysql

Create a temporary directory of MySQL in memory (GO)