Erin,
Good to see you're still happliy tinkering away! Anyway, my first reaction was, "Wow, that's a lot of disk tables created...". Just to make sure I wasn't just being reactive, I double checked against a production server I manage that was just restarted last week for maintenance and config changes, and found some striking differences (see below). Remember, the actual numbers aren't important, but rather the *ratios* between a couple different things:
comparison to one production server running a custom application that's pretty well tuned at this point, since we've been slowly adjusting for years now:
--------- snip ---------
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 100663296 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
+------------------------+-----------+
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Questions | 10336761 |
| Qcache_queries_in_cache | 32632 |
| Qcache_inserts | 1440699 |
| Qcache_hits | 3566428 |
| Qcache_lowmem_prunes | 1977 |
| Qcache_not_cached | 14121 |
| Qcache_free_memory | 30198944 |
| Qcache_free_blocks | 15036 |
| Qcache_total_blocks | 80336 |
| Created_tmp_disk_tables | 526 |
| Created_tmp_tables | 1166025 |
| Created_tmp_files | 0 |
+-------------------------+---------+
--------- snip ---------
The differences I'd like to point out:
1) The two ratios of Question to tmp_tables created and of tmp_tables created to tmp_disk_tables. On the comparison I give above, clearly, the queries we're running on this box have a high number of GROUP BY/ORDER BY clauses, as evident by the tmp_table numbers compared to total questions. On yours, that ratio is 131295410/1406717, or 93:1. On the comparison, it is 10336761/1166025, or 9:1, so clearly we're doing many more aggregating queries than the software you're running. But yet, surprisingly, the ratio of tmp_table to tmp_disk_table on your server is quite astonishing. On yours, this ratio is 1406717/348564, or 4:1. On the comparison, it is 1166025/526, or 2217:1. Pretty crazy, huh?
These ratios mean that on your server, MySQL is going through great efforts to aggregate data in queries using ORDER BY and GROUP BY. It is having to create the temporary table on disk, instead of in memory (obviously faster) because the size of the resulting temporary table is larger than the tmp_table_size variable (usually 32MB). Also, this counter may be incremented if the third part application is using an explicit CREATE TEMPORARY TABLE. If the former is actually the case, then consider increasing the tmp_table_size variable until you see this number level off.
Also, if the above is indeed true, and your queries are consistently returning > 32 MB result sets, then something is amiss in the application. Double check the application code to ensure that the programmers haven't done something stupid, like SELECT * FROM table and then returning the first 10 results, instead of using a LIMIT clause (sounds fundamental, but I've seen it happen *WAY* too many times). Now, on to the query cache...
2) Most of your query cache status variables look OK, with one exception. You've got a very high memory pruning operation counter. This counter is incremented every time the query cache needs to remove query cache blocks from the hash of memory bins inside the query cache. To compare the ratios of low_mem_prunes to hits, your is 65619535/3538685, or 19:1. On the comparison, it is 3566428/1977, or 1803:1. Again, big difference.
Here, the high number of pruning operations indicates that you should consider increasing the size of the query cache until this number levels off. Since you've got more than enough RAM, considering doubling the query cache size if you can.
3) Finally, to tie #1 and #2 together, you've got a high ratio of hits to not_cached queries. Yours is 65619535/1080088, or 61:1. The comparison is 3566428/14121, or 252:1. There are a couple reasons why the ratio is so low in your numbers. First, to bring back point number one, if the query result is larger than query_cache_limit, then the query won't be inserted into the query cache. Since we learned that there's a high probability that the queries being returned by your application are larger than 32MB, we can make an educated guess that one of the reasons so many disk tables are being created is because none of these queries is being put in the query cache, since the query_cache_limit is set to only 3MB! Therefore, one of the first things I would do after increasing the query cache size is to also increase the query cache limit to something more appropriate for your needs. Of course, don't go completely overboard. But, it may turn out that there is one or two queries that are very large result sets that are being repeatedly issued by the application, and these could be cached, saving LOTS of disk-based IO costs.
Additionally, another *VERY* common reason that queries are not cached by the query cache is that they look like the following, usually issued from a hack PHP web application:
SELECT * FROM forum_posting WHERE posting_expiration >= CURRENT_DATE();
I will say this very clearly: This query, and any other using a deterministic function, will NEVER be cached by the query cache.
However, how often does this query actually change? Only once every 24 hours! So, to immediately increase the performance of the application, it's so easy to make the simple change from:
<?php
$sql = "SELECT * FROM forum_posting WHERE posting_expiration >= CURRENT_DATE()";
$results = $my_db->Fetch($sql);
?>
to:
<?php
$sql = "SELECT * FROM forum_posting WHERE posting_expiration >= '" . date('Y-m-d') . "'";
$results = $my_db->Fetch($sql);
?>
The latter will be cached every time, the former never will.
Hope this shines some light on a few things, and I'm glad to see you pressing ahead!
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com