Update: MySQL tmpdir on tmpfs

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!

6 Responses to “Update: MySQL tmpdir on tmpfs”

  1. If you do use a shared memory tmpdir, if you are using replication, you will want to change slave_load_tmpdir on your slave to be a 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.

  2. Suppose you put MySQL in a VM environment where 8GB RAM is available and disk access is via NFS–does tmpdir on tmpfs sound viable and preferable?

  3. Yea Doug, that’s a valid use example. Of course it’s preferably to not have DB servers on NFS.

  4. Thanks Harry, I’ll update the post to make sure people don’t trip over that. Wasn’t needed for the setup I was dealing with.

  5. Using tmpdir on a tmpfs is fine provided it doesn’t get exhausted, say you have a 2G tmpfs and occasionally a query needs to do a filesort using 3G of temporary space, this query will fail.

    Calculating how much space a filesort() needs is nontrivial and it often seems like a lot more than you expect

  6. excellent …. I agree with Mark R … the tmpfs size limit might be an issue

    http://dev.mysql.com/doc/refman/4.1/en/server-options.html#option_mysqld_tmpdir

Leave a Comment