Mysql query larger than tmpfs (ramdisk) I recently set up a ramdisk tmpfs on our mysql server and the performance improvement has been quite noticeable.  I've allocated 24 out of 32 GB's of ram for the ramdisk.  My only concern is what happens when there's a terrible query that ends up requiring more space than the allocated tmpfs?  I've read in a few places that it depends on the type of query but wanted to confirm that in some instances such a query can effectively shut down mysql.
Grrr.  Neither of the answers so far quite says the right things, so I feel obligated to give yet another Answer.

@jkavalik gives a nice description of why the problem occurred.  (But not what to do next.)

@DaveRix starts out good by saying "use RAM for caches, not RAM disk".  I very much agree.  But then he goes on to advocate dangerously high values for max_heap_size and tmp_table_size.  Those settings limit the amount of RAM allocated multiple time per connection.  That is, a complex query might allocate N*64MB.  This risks running out of RAM.  I suggest no more than 1% of RAM for them.

Another common mistake is to put tmpdir on a small disk filesystem.  This also causes abrupt halts.

Once tmpdir has room, @Cosi's query should run.  But perhaps not efficiently.  The next thing to do is look at the query, its EXPLAIN SELECT ..., the schema of the table(s), and especially the indexes.  Some common mistakes:


Subqueries instead of JOINs.
Single-column indexes when "composite" indexes would be better.