Posted on 11 Comments

Experiment: MySQL tmpdir on tmpfs

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.

Posted on 11 Comments

11 thoughts on “Experiment: MySQL tmpdir on tmpfs

  1. Have run servers like this for ages, if you have spare RAM and your workload is such that it wont be better used as query caches etc, then this can be a big win.

  2. Interesting. Seems similar but simpler to what Mat BigDBAHead was playing with some time ago: http://www.bigdbahead.com/?p=204

  3. We’ve had this implemented on quite a few production servers as well, it’s performing very well and I’ve yet to spot any problems with it. Thanks to one of the many pesky drupal modules for forcing me to look into this in the first place!

  4. @Henrik haha yea ((I think it was Yves not Matt), much simpler.

  5. Be sure to make it large enough so that you don’t run out of space doing an alter. MiniWheatFS is a very interesting project.

  6. @wultsch ALTER shouldn’t use tmpdir, it’ll create a new table under a temporary name in the target engine; otherwise you’d potentially get into rename-across-filesystem-boundries issues.

  7. This is like the default /tmp settings on solaris. On most Solaris systems /tmp is using a memory backend. That’s why there is also a /var/tmp (which is reboot safe)

  8. We have used this solution for a while on 5.0.67, but we came across many errors caused by missing temporary innodb tables after a crash. I think there were some bugs in the server code, but are resolved at now.

    Just be aware of 5.0.45, it doesn’t respect the tmp_dir option 🙂

  9. […] [TIP] Firefox and tmpfs: a surprising improvement Experiment: MySQL tmpdir on tmpfs […]

  10. An example:
    Our Mysql server has 16 GB, the size of /dev/shm is 7,7 GB.
    We configured our Key Cache 3,5 GB and InnoDB Buffer Pool 5 GB.
    In summery with Query Cache und diverse buffers and the OS cache, the default memory half-size for tmpfs seems to be to big?

    Because sometimes we get following errors:
    Incorrect key file for table ‘/dev/shm/#sql_3ce_14.MYI’

    1. Hi Hector – indeed quite possibly the memory available for tmpfs is insufficient, but that has nothing to do with the other variables you mentioned, at least in terms other than the fact that they all use memory.
      If you have a large key cache (MyISAM) and a reasonably large InnoDB buffer pool, are you running a mixed engine system? There are valid uses for this, but given the numbers there may be something else going on – possibly inadequate server tuning.
      But fundamentally the amount of space required in tmpfs for tmpdir is dependent on the type of queries you run, that is the size of temporary disk tables required (and how many connections create these), on-disk sorting operations, and so on. That will depend on tmp_table_size, sort_buffer_size, but even more importantly the queries themselves including whether they include text/blob columns and many other factors.

Comments are closed.