I’ve just been doing a bit of work on MySQL performance tuning.
I did a
show variables; and that revealed that I had the maximum number of tables cached (opened_tables = table_cache = 64) so I upped table_cache to 256. Since then opened_tables has been constant at 145 so I guess that this is all that is required with my present number of threads/table count.
I also doubled the key buffer from 16M to 32M although I had a pretty impressive hit rate on the cache anyway (key_read_requests / key_reads) but it won’t do any harm and looking at now there has been an improvement.
Finally, I checked the slow query log but I didn’t have any queries in it. I’d set the limit at one second. Maybe I should set it to log queries that aren’t using indices.
There’s a brilliant talk on MySQL performance at Google Video. He talked about:
- mytop which is a Unix-style top but for MySQL.
- Poor indexing being the number one problem.
- Bloated schema design (please normalise!) number two problem.
- Uses the smallest types possible.
- Consider horizontally splitting many column tables (eg. main and detail).
- Don’t use surrogate keys when you have a naturally occurring key (although, for example, if the natural key is a larger varchar then there may be a performance benefit on using an int).
- On InnoDB use counter tables (but count(*) is fine on MyISAM as it uses the indices).
- Isolate indexed fields, eg.
WHERE TO_DAYS(order_created) - TO_DAYS(CURRENT_DATE()) >= 7will be much slower than
WHERE order_created >= CURRENT_DATE() - INTERVAL 7 DAY
- Literal current date is better than using the CURRENT_DATE() function because literals will use the query cache but functions won’t.
- Make sure query cache is turned on (with a size) when the application is read intensive. It’s off by default. (But not on Debian.)
- From a hardware point of view memory is the best, easiest and cheapest way to get better performance from MySQL.
Looking at top it seems that the main problem I now have is with my Python code. But I need to do some profiling to work out exactly where the problem is.