Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://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

share|improve this question
    
Internal temporary tables used in processing queries are created by the MySQL layer, not the storage engine. They are always handled by the MEMORY engine when they can be, and are handled by MyISAM when they can't, no matter which engine(s) are providing the rows that are going into the internal temporary table. The best solution is to optimize queries to avoid temporary tables when possible. –  Michael - sqlbot Jan 4 '13 at 6:39
add comment

1 Answer

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.

share|improve this answer
    
Are there connection settings that are relevant to tmp tables with BLOBs (as opposed to just being too large)? RAM Drive is a great suggestion though, thanks! –  Eli Jan 4 '13 at 2:58
    
I'm guessing a RAM drive to temp files would be really bad if it doesn't have enough space, right? –  Eli Jan 4 '13 at 3:00
    
SELECT SQL_BIG_RESULT ... can also direct the server to bypass MEMORY tables and go straight to disk-based tables on demand. –  Michael - sqlbot Jan 4 '13 at 6:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.