MySQL may use temporary tables during query execution. Ideally you would want to avoid this, since its an expensive and slow operation. It can be avoided by optimizing queries. Sometimes it can’t be completely avoided – in that case you want to make sure the temporary table is created as a “memory” storage engine table, since its very fast, as it is never written to disk and remains, as the name states, in memory. But, as the manual explains, there are some conditions, such as TEXT/BLOB columns, or a combination of GROUP BY/ORDER BY clauses that makes MySQL write the temporary table to disk as a MyISAM table. One can spot these queries by the EXPLAIN output:
[...] Using where; Using temporary; Using filesort
In that case performance depends on disk I/O speed. If there are multiple similar queries running simultaneously, they try to read/write a lot of information to the disk, and will become extremely slow.
Solution? TMPFS!
tmpfs is a filesystem, that resides in RAM/Swap, so if your server has enough available RAM, files written there will bypass disk I/O completely, and will perform significantly faster.
Now, “High Performance MySQL, Second Edition” claims that this solution is still not as good as a MEMORY table, since it requires MySQL to use some expensive OS calls to write & read the temporary table, but it is still faster than the disk based temporary table.
To set it up, just mount a tmpfs system on an empty directory (you should also add this to fstab):
mount tmpfs /tmpfs -t tmpfs
and edit my.cnf to make MySQL use that directory as a temporary directory:
tmpdir = /tmpfs
Be careful though, there is a bug in some versions that prevents this from working properly.
For more information, see this blog.
Frank
Good to know!
Ries
I agree that the on disk temporary storage is bad for performance and the first thing to look at is to ‘filter’ data as soon a possible so MySQL has less reason to create such a tmp table.
However your solution posted is very dangerous on buzy databases because what if people run all queries in memory? Then you might end up with out of memory or usage of swap space which slows that down again. Also the solution you provided to store these temp tables on a separate memory HD is dangerous for the similar reasons, what if that space runs out? Will MySQL crash, or happily keep on running and give incorrect or no results back, does anybody know that
behavior?
Usually it’s best to give the RDBM all the memory it can get from the system and let the OS handle disk caching which is more save then create your own tricks.
Ries
Dan Osipov
Ries,
It depends on the Linux kernel version you’re using. Older kernels will freeze when space runs out, but newer kernels are more safe.
As for giving the DB all the memory it can use – sure, but in the case of a filesort MySQL will create a disk based table no matter what – and that’s slow even if the table is small.
You can also limit the size of a tmpfs partition to make sure it never goes into swap.
FYI – we’ve been running it on a high traffic DB server with 8Gb of RAM, and usage never went into swap space so far.
Ries
Dan,
it is still very dangerous to let a DB write that stuff to a limited Disk space because you will never know what happens next. It’s also a bit of a shame that if that ram disk is ‘to large’ you have a lot of memory in use you really don’t use for the DB.
Ries
Dan Osipov
I see your point, but interestingly enough the DB is configured to use all the available memory – it just chooses not to for some reason…
David Abdemoulaie
Dan,
“Using where; Using temporary; Using filesort”
does *not* mean that the temporary table was written to disk. In fact, nothing in EXPLAIN can give you that information. It seems like you’re working under the common assumption that ‘filesort’ means it was written to a file on disk, and then sorted. This is not necessarily the case, filesort is simply the name of the sorting algorithm used, and occurs for in-memory tables as well. It means that the results could not be sorted by an index alone, thus the data was copied to a temporary table, and then the “filesort” algorithm was applied to sort the results.
David Abdemoulaie
In fact, here’s an article that puts it much better than I have:
http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/
Dan Osipov
Good point Dave – it doesn’t, but like I said, if the table contains a TEXT/BLOB field, or there is GROUP BY/ORDER BY clause in the query, the table will have to be written to disk.
To be sure, you can check the process list while running a query, and you will see the query enter into the “Copying to tmp table on disk” state.
Mike
Dan, that last comment is not true. I have plenty of queries that use group by or order by, and are saved in memory and say ‘copying to tmp table’ not ‘copying to tmp table on disk’ within profiler. I don’t know the exact conditions that determine when something is stored on disk, but I know it’s not true that everything that uses order by or group by is…
Will Daniels
Note for Ubuntu and other AppArmor users will need to remember to allow mysqld to write to the tmpfs mount point. Example: edit /etc/apparmor.d/usr.sbin.mysqld and add the line:
/tmpfs/* rw,
…in the list of permitted filesystem locations. Then:
service apparmor reload
Also might want to add the mount to fstab e.g.:
tmpfs /tmpfs tmpfs rw 0 0