MySQL: Five Dials to Set

Friday Jun 12th 2009 by Sean Hull

In this article, Sean Hull looks at the first five out of ten dials that you can turn to get an initial MySQL vanilla install working for your specific application requirements.

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.

1. Connections

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.

This should be at minimum the sum of the maximum number of apache connections allowed from all your web servers. Keep in mind that each connection uses session memory (see below).

The maximum packet size is typically the size of your largest dataset you'll need to return in one chunk. If you're using mysqldump remote, it may need to be larger.

Check this system status counter, and be sure it is not increasing. If it is, your clients are getting errors connecting.

Incoming connections create a new thread in mysql each time they are opened. Since opening and closing connections in mysql is very cheap (resource-wise) and fast, persistent connections aren't as typical as they are with other databases such as Oracle. However, having threads pre-created does save some time, and that's what the mysql thread cache is for.

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.

Blocks in the query cache are allocated in chunks of this size. Calculate the average size of your queries with the formula below, and set this variable according to that. MySQL will then use query cache memory more efficiently, caching more queries and wasting less memory.

This sets the total size of the query cache.

This tells mysql to discard queries larger than this size. The theory goes that large queries run infrequently, such as a batch job doing a large report, so those results shouldn't clog up with query cache.

qcache hit ratio = qcache_hits / (qcache_hits + com_select)
Find these variables with:

SQL> show status like 'qcache%';
SQL> show status like 'com_%';

average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache
query_cache_size you can get with:

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.


Whenever you create temp tables in MySQL it will use the minimum of these two variables as the cutoff, beyond which it will build the temp table on disk. Keep in mind that there are many many sessions, all fighting for limited resources, so better to tune queries than just go setting this too high. Also, keep in mind tables with BLOB or TEXT will go straight to disk.

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.

Caches blocks from sequential scans. This buffer is across storage engines, not just MyISAM tables.

Any session that performs a sort allocates this buffer. Again beware setting it to a large value system-wide. Better to leave it at 1M or 2M, and then set it within a session, to a higher value for a specific query.

Any session that performs a join allocates this buffer. Set it to 1M or 2M and then modify it on a per-session basis as needed.

Used for sorting, and ORDER BY operations. You're better off setting this to 1M, and setting it as a session variable for queries that can use a larger value.

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.

Set this in the my.cnf file to turn it on. MySQL will default to putting the file in your datadir with a name hostname-slow.log but you can also give it a name when you specify this option.

This defaults to 10 seconds. I suggest leaving this alone. You can set the value dynamically to 1 second to turn it on, and if the database gets restarted, the log will be off by default. As of 5.1.21 and for folks who installed a Google patch, this option can be set in microseconds. This is a REALLY great feature, because once you wipeout all the queries 1 second and over, finer tuning becomes possible. This will help you eliminate problem SQL long before it becomes a bigger problem.

This option is a good one to enable as well. It actually logs any queries that return all rows.


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

Mobile Site | Full Site