Optimizing MySQL

Posted in: Ryan Kopf's Blog
Aside from part-time robot engineer, convention organizer, and a billion other things, I'm also sometimes a DBA (database administrator). It's a big deal! Walk into a Vegas casino, tell them you're a DBA, and see the looks you get.

(Just hope they don't think the 'A' stands for auditor...)

Here are some of the latest changes I've made to my MySQL database powering all these crazy web nonsense things.

key_buffer = 32M
This buffer is used only for MyISAM, which I don't use. I think some default MySQL tables use it, so I increased it from 16M.

table_cache = 128
Some people recommend a value of 512, but I don't even have that many tables in the database, so I went from 64 to 128.

tmp_table_size = 64M
These tables can be created when doing big operations, and it's probably best to minimize them in general. However, sometimes it can't be avoided, and the default value of 16M would leave you wishing for more.

query_cache_size = 128M
I think I increased this from a measly 16M, and this will probably be the best performance enhancer here. Every time a SELECT query is made, it can be cached for future use and better speed. The more that are cached, the more likely repetative queries will hit the in-memory cache instead of trying to access to filesystem (which is much slower).

innodb_buffer_pool_size=1G
This let's the database store an in-memory copy of an entire gigabyte of my data, minimizing disk access even more. This could/should probably be increased a lot more in the future, when I add more RAM.

Please login to post.