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.