Quick wins with MySQL

September 25, 2007 - by jason

Working at Joyent I get to see many different types of MySQL setups whether it’s replicated, clustered, load balanced or just a single instance.

Each one runs a lot differently than the other so they all require different settings. Even though they each need custom tuning, there are a few performance wins you can get with almost any MySQL database:

- If you are using MyISAM tables, make sure you have a good key_buffer size. If you are using InnoDB tables, make sure you are using a good innodb_buffer_pool_size. If you don’t have either of these set they default at only 8MB. Bumping these values up can decrease disk IO and make your database run a lot smoother. So many times I’ve seen these set to such low or default values with huge DB’s.

- Use indexes. Enable the slow query log and look for queries that show up most often, then learn the explain command on them.

- Check Created_tmp_disk_tables in show status; this value usually goes in hand with huge queries that aren’t using indexes. If it keeps increasing, it means you are having to create on disk files to execute the query. Consider increasing tmp_table_size or putting your disk files on a TMPFS by setting tmpdir = /tmp.

- Use the query cache if it can help you. Check the query_cache_size. Check how well it is performing. You can do this by doing a ’show status’ and then using this formula:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

If there are a lot of prunes, that means your query cache size isn’t big enough.

- If Opened_tables is increasing you probably have table_cache too small.

- If you have a lot of connections, check Threads_created in show status; If it keeps increasing, you need to up the thread_cache_size in my.cnf. The max connections in MySQL are default at 200. You can easily check how many connections you have by doing a prstat in Solaris and looking at the number of LWP (MySQL starts 10 LWP when it starts and then 1 LWP per connection). If you have a lot of connections it can eat up CPU having to create the threads over and over.