Tuning System Variables

Foreword

There are a number of variables available to tune within MySQL, all available for you to see via the SHOW VARIABLES; command. To aid in your diagnostics of how well these variables may be tuned, MySQL provides the SHOW STATUS; command.

Out of these variables, there are some that can help you on your way to the ultimate in "mauve" databases - those that detail the various memory cache's available to you. MySQL breaks up it's total memory structure in to smaller caches that service different functions, such as the Key Cache and Table Cache to hold frequently used index and table data in memory, for example.

Here we'll take a look at which variables to look for, how we can select them, how to interpret them, and ultimately, how to tune them. We will start with the thread cache and show the steps involved in tuning the server variables.

The Thread Cache

Before users can even begin to start selecting data, they need to connect to the database. If your application makes a lot of seperate connections to the database over short periods of time (like most PHP based applications) this can cause a lot of overhead with allocating and deallocating the associated "stack" for each specific "Connection" or "Thread".

So wouldn't it make sense to hold a pool of already allocated threads in memory? Enter thread_cache_size - which sets the number of threads to hold open in memory to service new connections.

Using the SHOW STATUS command we can determine how many connections are being serviced by already cached threads, and how many are having to wait for a new thread stack to be allocated. The status variables in question are Threads_created and Connections. We can select very specific status variables by using the LIKE keyword, such as:

SHOW STATUS LIKE '%thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 30    |
| Threads_connected      | 4     |
| Threads_created        | 31    |
| Threads_running        | 2     |
+------------------------+-------+

To determine how the thread_cache_size variable should be set, we use what is known as a "Hit Ratio". Hit Ratios are typically stated as a percentage score, giving the percentage of "requests" that were serviced by in memory caches (also known as logical I/O) vs those that cause disk requests (physical I/O) or extra processing - such as in this case, thread allocation to memory (which causes extra CPU activity).

Threads_created details the number of threads that have been created since the MySQL server started, and Connections is the total number of client connections to the MySQL server since startup. To work out the thread cache hit ratio, we use this calculation:

100 - ((Threads_created / Connections) * 100)

Given the above value of 31 for Threads_created, and the value of 4567 that Connections showed, I am able to determine that my Thread Cache Hit Ratio is 99.32%.

The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% hit ratio as you can. To see the value of thread_cache_size you can use:

SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 30    |
+-------------------+-------+

Of course, these hit ratios can be skewed on a system that has recently been restarted, so should also be evaluated accordingly against Uptime.

The MyISAM Key Cache

The MyISAM storage engine has one main area within the MySQL memory structure that helps to service requests to clients, the MyISAM Key Cache.

InnoDB Specific Caches

To be written.

The Query Cache

To be written.

Sort Buffers

To be written.