My Rails application for Gigantic News Corp. has been pretty successful in its first two months of life, but we’ve been seeing some intermittent, nagging performance issues. I’ve spent the last couple weeks optimizing Rails and SQL code to reduce object instantiation overhead and also reduce a number of database table scans. That’s shown good results so far, but I’ve been banging my head on one issue the last few days - namely, an extremely high proportion of MySQL’s temp tables are being written on disk. Using temp tables is slower than buffers, of course, and disk of course is much slower than in-memory temp tables.
I could not figure out why this was happening, even though I could duplicate the on-disk temp table creation on my laptop, until I stepped back a little bit and compared last week’s statistics to this week’s, did a little research online, and found it’s the result of two bugs of sorts.
For above mentioned workflow app, we subscribed to the MySQL Enterprise service, at least for the first year. It comes with an automated advisor which gives you some performance graphing and also attempts to advise you on ways to improve database performance. It’s not bad but I’m not sure it’s worth $3K/server/year.
These two graphs are examples of what it produces - one for last Friday May 9 AM, the other for this Friday May 16 AM. They show the number of temporary tables created and number of on-disk temporary tables created.
Last Friday:

This Friday:

Compare the scales on the two graphs; the worst temp table peak this morning is about one third that of last Friday morning, and the sustained number is much lower at under 200 compared to over 500. After a code update on production earlier this week, I had focused on the on-disk-to-total ratio being much, much higher, and had overlooked the fact that the overall numbers were down significantly.
Once I noticed that, I thought I was getting closer. Enterprise Advisor gives me a little message like so:

I figured I just had to up one of MySQL’s memory buffers a bit more to get the last temp tables off the disk, and spent some time experimenting with that.
No joy.
After much time spent loading Rails pages and running the queries to see if I could get them to increment my development MySQL’s created_tmp_disk_tables counter, I stumbled upon the fact that when one issues a ‘SHOW COLUMNS FROM table’, MySQL not only creates an on-disk temp table due to the type of data it presents (LONGTEXT), it counts it as such in the performance stats. Yikes. Bug if you ask me.
OK, that’s fine and all, you say, but according to all the comments from all the people on all the sites about Rails’ behavior in a production environment, Rails doesn’t issue those incessant ‘SHOW COLUMNS FROM’ commands in production mode, where classes are cached. Only in development mode so you don’t have to restart the web server every time you tweak your code.
Right?
Wrong.
Turns out that Rails in production mode (i.e. config.cache_classes = true) still issues those commands for the join tables used in has-and-belongs-to-many (many-to-many) relationships. Perhaps because they’re not a class unto themselves. Bug.
Combine the Rails bug with MySQL’s odd buggish behavior that a command to describe a table is counted as an on-disk temp table in performance analysis, and you’ve got an ongoing problem getting accurate info on the number of temp tables being generated. Bummer.
On the bright side, I’ve reduced overall temp table usage by perhaps two-thirds, and I feel very comfortable saying based on the high ratio seen in the graph that all but a handful of the on-disk temp tables reported are the result of the MySQL bug.