skip copying to tmp table on disk mysql
There are two things you can do to lessen the impact by this
OPTION #1 : Increase the variables tmp_table_size and/or max_heap_table_size
These options will govern how large an in-memory temp table can be before it is deemed too large and then pages to disk as a temporary MyISAM table. The larger these values are, the less likely you will get 'copying to tmp table on disk'. Please, make sure your server has enough RAM and max_connections is moderately configured should a single DB connection need a lot of RAM for its own temp tables.
OPTION #2 : Use a RAM disk for tmp tables
You should be able to configure a RAM disk in Linux and then set the tmpdir in mysql to be the folder that has the RAM disk mounted.
For starters, configure a RAM disk in the OS
Create a folder in the Linux called /var/tmpfs
mkdir /var/tmpfs
Next, add this line to /etc/fstab (for example, if you want a 16GB RAM disk)
none /var/tmpfs tmpfs defaults,size=16g 1 2
and reboot the server.
Note : It is possible to make a RAM disk without rebooting. Just remember to still add the aforementioned line to /etc/fstab to have the RAM disk after a server reboot.
Now for MySQL:
Add this line in /etc/my.cnf
[mysqld]
tmpdir=/var/tmpfs
and restart mysql.
OPTION #3 : Get tmp table into the RAM Disk ASAP (assuming you apply OPTION #2 first)
You may want to force tmp tables into the RAM disk as quickly as possible so that MySQL does not spin its wheels migrating large in-memory tmp tables into a RAM disk. Just add this to /etc/my.cnf:
[mysqld]
tmpdir=/var/tmpfs
tmp_table_size=2K
and restart mysql. This will cause even the tiniest temp table to be brought into existence right in the RAM disk. You could periodically run ls -l /var/tmpfs to watch temp tables come and go.
Give it a Try !!!
CAVEAT
If you see nothing but temp tables in /var/tmpfs 24/7, this could impact OS functionality/performance. To make sure /var/tmpfs does not get overpopulated, look into tuning your queries. Once you do, you should see less tmp tables materializing in /var/tmpfs.
You can also skip the copy to tmp table on disk part (not answered in the selected answer)
- If you avoid some data types :
Support for variable-length data types (including BLOB and TEXT) not supported by MEMORY.
from https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html (or https://mariadb.com/kb/en/library/memory-storage-engine/ if you are using mariadb).
- If your temporary table is small enough : as said in selected answer, you can
Increase the variables tmp_table_size and/or max_heap_table_size
But if you split your query in smaller queries (not having the query does not help to analyze your problem), you can make it fit inside a memory temporary table.