temp tables | Open Query

Followup on Experiment: MySQL tmpdir on tmpfs, about tmpdir=/dev/shm in my.cnf (it’s not a dynamic variable that can be set at runtime). It’s working well, also confirmed by comments from others that they’ve been using it for a while.

This particular setting is Linux specific. On Solaris, the default /tmp is already on a tmpfs so that’s fine too. Brian reminded me that this tweak is also useful if you’re stuck with a 32-bit OS as you can then utilise some more memory in a practical way.

Extra useful hint from Harrison: if you are using replication, you will also want slave_load_tmpdir=/tmp on your slave (real disk which survives a restart). The issue is that with statement based binary logging, there are many events which create a file for a replicated LOAD DATA INFILE. If you stop your server after some of these events have occurred, but not all, it will break after you restart.

Thanks everyone for their feedback!

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.

Share this:

Share this: