Have you ever looked at your processlist and seen queries in the state “copying to tmp table” and then run an explain on it and noticed that the tmp table is being created on disk? Happens a lot with some servers and some workloads. Of course disk is much slower than RAM so this becomes a slow process and makes queries execute slower than they could if they were allowed to use RAM. So, one way to get this process to speed up (aside from tuning your queries which should be done first) is to create a tmpfs or ram-disk and let MySQL use that for it’s temp-table-on-disk creations. MySQL on Linux defaults to /tmp for the tmpdir location so this will need to be changed.
Here is how you get MySQL to use a 1G size tmpfs. How you size your tmpfs depends how much ram your system has and how much tmpdir space mysql needs for your workload. If you need more tmpdir space than you can make in a tmpfs mount then mysql allows multiple tmpdir locations …
[Read more]