Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

Question

I've got a web application connected to a database running MySQL.

Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. This means that I'm getting lots of temporary tables being created on disk (this behavior is described here https://dba.stackexchange.com/a/17679/16724) and this results in pretty crappy performance.

Is there by any chance an easy solution to this problem? Perhaps some fork or other pluggable engine? I'm actually running the Percona fork of MySQL (XtraDB), but it's the same problem with vanilla InnoDB. I'm frankly considering switching to Postgres.

Thanks

Answer 1

You need to properly tune all connection-based settings as you pointed out from that link.

If you are looking for something quick and dirty, I have just the thing:

Back on Nov 30, 2012, I wrote a nice post about mapping temp tables to RAM, even disk-based temp tables : Is it bad to create many mysql temporary tables simultaneously?

Give it a Try !!!

UPDATE 2013-01-03 22:14 EDT

In terms of the BLOBs, my condolences. Notwithstanding, I have good news. I have a very quick-and-dirty stunt you can do once you set up the RAM disk.

Ready ???

Set the following values in

tmp_table_size = 1K
max_heap_table_size = 1K

That's not a misprint. That's not a typo. I said set tmp_table_size and max_heap_table_size to 1K. You are probably saying "Why set those values so low?"

Those values represent the largest in-memory temp table allowed before transferring the temp table to disk. If you have a RAM disk, then temp tables leap right into the RAM disk as quickly as possible. As the MySQL Documentation says on max_heap_table_size:

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.