MySQL performance

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()) >= 7 will 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.

One Response to “MySQL performance”

  1. arabxptyltd Says:

    You make a good point at “Literal current date is better than using the CURRENT_DATE() function because literals will use the query cache but functions won’t.”

    So much so I wrote about it for aggregation with Planet MySQL at http://blog.arabx.com.au/?p=749

Leave a Reply

You must be logged in to post a comment.