It looks like no matter what I do, one of my queries is always going to disk to create its temp file, instead of creating one in memory, and this is killing my server’s performance.
I have an 8G machine running MySQL 4.1.16, and I’ve set my tmp_table_size=64M and my max_heap_table_size=64M. I’ve verified these settings using show variables. I’m checking to see if temp files are created by watching Created_tmp_disk_tables and the temp data directory. Temp files are always created, the largest being 20M in my tests, which is way under my settings (and in test I’ve bumped both the of the table settings from 64M all the way to 4G with no change in behavior).
My query doesn’t use BLOB or TEXT fields at all, but is grouping by a VARCHAR. I’ve read on the forums that VARCHAR is automatically converted to CHAR for HEAP tables, with the cost being a much larger temp table due to the fixed size conversion. Even if I restrict my query to a tiny range of data (like 100 rows between the two tables I’m joining), so that the result set is under 10 rows, it will still create a temp file on disk.
If I change the query to group by an unindexed integer id in one of these tables, this query NEVER creates a disk temp table, even if I increase the source data range to be 10x the size of my original test. Basically, it looks like grouping by varchar equals disk based temp tables. Is anyone else seeing this?
On the bright side, using tmpfs does help the performance considerably, but it’s not my preferred solution (and neither is normalizing my character data by some integer id).