MySQL: Five Dials to Set
June 12, 2009
MySQL can be installed and up and running in a matter of minutes with RPMs or even quicker with tools like xampp. However, there are so many dials to turn, and options to set that even experienced DBAs can feel overwhelmed.
With that in mind, we'd like to focus your attention on ten areas that will put you on solid footing.
Connections are typically coming from a web server, as many MySQL databases back internet websites. Here are some of the parameters related to connections, and how to set them.
Keep an eye on threads_created and if it is increasing, make your thread cache bigger. It doesn't cost much in terms of memory to have 25, 50 or 100 thread_cache_size.
2. Query Cache
Caching queries in mysql includes both the parsed query plan, as well as the return dataset. If the underlying table data or structure changes, that will invalidate the entry in the query cache.
qcache hit ratio = qcache_hits / (qcache_hits + com_select)
SQL> show status like 'qcache%'; SQL> show status like 'com_%';
average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache
SQL> show variables like 'query%'; qcache_* status variables you can get with: SQL> show status like 'qcache%';
3. temp tables
Memory is extremely fast, so when it comes to sorting, we want to keep the entire result set in memory while we're working on it. We can do this by tuning queries to make that set smaller, *OR* by setting these variables larger.
4. session memory
Each session in MySQL will use it's own memory for work it needs to do. This is memory that directly contributes to your SQL queries, so you want to make it large enough to satisfy those needs. However, you have to balance that with the number of concurrent sessions in your database at one time. What makes this a little bit of a black art here is that MySQL allocates these buffers as needed. So you can't just add them up and multiply by the number of sessions. That estimate will be much higher than MySQL typically uses. Best thing to do is to startup MySQL, connect all of your sessions, then keep and eye on the VIRT column of a top session. The number for the mysqld row usually stays pretty steady. That's your real-world total memory usage. Subtract all your static MySQL memory areas and you have the total session memory in the real world. Then divide by the number of sessions to get the average.
5. slow query log
The slow query log is a really useful feature of MySQL. As queries tend to be the often overlooked, yet most important part of a performing database, this is an important facility to have enabled.
We've dipped into MySQL looking at a few dials that we can turn to get an initial MySQL vanilla install working for your specific application requirements.
Next month we will finish our discussion of ten dials, touching upon InnoDB buffers, MyISAM buffers, Index, table scans and query tuning, security, and lastly logging in MySQL.
» See All Articles by Columnist Sean Hull