tmp_table_size is really dangerous, but not for the reason you might think.
max_heap_table_size work together. When a
SELECT needs to store intermediate results for, say, a sort (
ORDER BY), it first tries to do it in RAM. There are several cases where this won't work:
BLOB column is being fetched. It will bypass RAM and go straight to disk. (
tmp_table_size is irrelevant in this case.)
- It starts to use RAM but the temp table grows bigger than either of those two settings. At this point, it copies what it has so far to disk. (Having
tmp_table_size big will actually hurt for large temp tables.)
If you have several
SELECTs running at the same time, and doing the second bullet item above, that is potentially several allocations of 500M. If you run out of RAM, the system will start swapping. Swapping is much worse than spilling tmp tables to disk sooner.
Note further, that a single
SELECT can cause more than one temp table to be used! One example is
GROUP BY something ORDER BY some_other_thing
Bottom Line: Set
max_heap_table_size to no more than 1% of RAM.
Now, back to 50% of tmp tables being on disk. Sometimes that is necessary. But there are some common cases where disk can be avoided.
- Don't ever use
- Don't use
TEXT if a modestly sized
VARCHAR will suffice.
- Don't blindly say
VARCHAR(255); make them reasonably sized. (Before spilling to disk, they are stored in a
MEMORY table, which turns
CHAR. Note: 8.0 fixes that.)
- Poor indexes.
- Poor formulation of queries.
For the last two, let's see a couple of the queries that are likely to be spilling to disk. I can often spot what to do.
I dislike chopping disk up into filesystems. The inevitable result is running out of disk on one FS while there is lots of room on another FS.
I dislike using ramdisk for anything; MySQL does a lot of intelligent work to make use of the RAM it is given; don't rob it for transient use.
I strongly agree with your Reason 1 (give RAM to the buffer_pool).
As for reason 2... Note that any DDL (Alter/Optimize/...) that needs to copy a table over would prefer to do it in the same FS so that the final step is
cp + rm.
OPTIMIZE TABLE, I frequently rant about how that is virtually useless for InnoDB; don't use it.