In MySQL, the tmpdir path is mainly used for disk-based sorts (if the sort_buffer_size is not enough) and disk-based temp tables. The latter cannot always be avoided even if you made tmp_table_size and max_heap_table_size quite large, since MEMORY tables don’t support TEXT/BLOB type columns, and also since you just really don’t want to run the risk of exceeding available memory by setting these things too large.
You can see how your server is doing with temporary tables, how many of those become disk tables, and also other created temporary files, by looking at SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;
So why might tmpfs be better? Well, not just any tmpfs, but specifically /dev/shm. Its behaviour (see the kernel docs) may just fit the need, provided you have sufficient “spare” RAM in the machine anyway. Essentially the files will live in kernel cache space, and swap is potentially possible. You can even tweak limits, live. Mind you, this is Linux specific. Any system with glibc 2.2 or higher will have a /dev/shm available.
We’re currently running a comparative test in a production environment with one slave on tmpdir=/dev/shm. It being significantly faster is no real surprise but we just want to make sure that it behaves well over time, in the real world. Naturally it is best to not need disk-based temp tables at all for most queries, but for existing apps you can’t always change problematic design issues “right now”. It’s not a solution, but extra “breathing space” is useful. We’ll report back later with more results and possible insights from this experiment.
2009-06-16: See followup at Update: MySQL tmpdir on tmpfs.