MySQL and /tmp on tmpfs

Short but sweet post here. The short version is this: if you are currently running MySQL and don’t have /tmp on a tmpfs, you’re getting burned and performance will be extremely poor. The graphs below pretty much speak for themselves. We went from far too many slow queries and very high I/O on the only two disks this machine has (the box hosting the Arch Linux AUR) to almost no slow queries at all, normal I/O, and reduced CPU and IO wait.

If it wasn’t clear, the reason this makes such a difference is MySQL’s usage of temp for sorting and temporary table purposes when things can’t fit in memory. Moving to a tmpfs means nothing ever needs to get flushed to disk or journaled, even if it is deleted within milliseconds of it being created.

See Also

• Linux command of the day: slabtop - July 7, 2011
• Making things IPv6 capable - June 8, 2011
• Slicehost kernel upgrade - November 1, 2009
• Eee Kernel 2.6.31.2-1 Update - October 6, 2009
• Arch Kernel Eee 2.6.31 built - September 9, 2009