500M is really dangerous, but not for the reason you might think.

• A TEXT or BLOB column is being fetched. It will bypass RAM and go straight to disk.
• 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.

Note further, that a single SELECT can cause more than one temp table to be used! One examples is GROUP BY something ORDER BY some_other_thing

So, set tmp_table_size and max_heap_table_size to no more than 1% of RAM.

Now, back to 50% being on disk. Sometimes that is necessary. But there are some common cases where disk can be avoided.

• Don't ever use TINYTEXT.
• 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 VARCHAR into CHAR. 8.0 fixes that.)
• Poor indexes.
• Poor formulation of queries.

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 mv, not cp + rm.

As for OPTIMIZE TABLE, I frequently rant about how that is virtually useless for InnoDB; don't use it.

500M for tmp_table_size is really dangerous, but not for the reason you might think.

• A TEXT or 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.)

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 tmp_table_size and 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 TINYTEXT.
• 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 VARCHAR into CHAR. Note: 8.0 fixes that.)
• Poor indexes.
• Poor formulation of queries.

Filesystems

• 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 mv, not cp + rm.

• As for OPTIMIZE TABLE, I frequently rant about how that is virtually useless for InnoDB; don't use it.