tutorial on creating a temporary directory of MySQL in memory

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

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 implement this requirement, we first check the configuration size of the temporary table:

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 the MEMORY table in memory, or the MyISAM table on the disk if larger than the above value.

But there is one other factor that causes the MyISAM table to be stored on the disk, that is, the MEMORY table cannot handle TEXT and BLOG type data. In this case, MySQL writes directly to the MyISAM on the disk and counts as the created_tmp_disk_tables value.

If a temporary table has started causing serious I/O performance problems, consider using a disk memory map instead of a physical disk.

On Linux we have two ways to create ram-disk in memory, respectively, Ramfs and TMPFS.

Here we recommend using TMPFS.

You can create ram-disk by using the following command:

Shell> mkdir-p/mnt/ramdisk

Shell> Chown Mysql:mysql/mnt/ramdisk

shell> 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:

Tmpfs/mnt/ramdisk Tmpfs rw,mode=1777 0 0

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

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.