Many users new to MySQL will find themselves overwhelmed when tasked with configuring their system for best performance. It doesn't need to be intimidating, however. MySQL offers nearly 400 settings to consider, but a user should understand that most of the system's benefits can be achieved just by getting a few fundamental configurations right. The vast majority of optimizing configuration comes from selecting those very important options. Knowing where to look is the first step, and utilizing tools such as a monitoring product can be a huge boon.
Look Before You Leap
Even before beginning, a user should internalize a few ideas. First of all, while you should
not be afraid to approach and adjust your server’s configuration, there
are some common mistakes that it’s good to know about so you can avoid them.
The first thing to know is that some settings
can harm your server's performance! There’s such a thing as too much of a good thing for specific memory buffers, for example. The most common consequence of setting a buffer too large is using swap, which slows the server down a lot. If your server is actively swapping everything will grind to a halt. For instance, you're able to see swapping activity in SolarWinds® Database Performance Monitor's memory graphs. Note that it’s okay if some swap is used; what you want to avoid is active paging in and out. Notice the difference between the graph on the left and the graph on the right in this screenshot.
One of the most common sources of query performance problems is a bad setting for the
sort_buffer_size variable. That one is dicussed in more depth in the second half of this post.
Much online advice can be found about configuration. You should take it all, including what you’re reading right now, with a grain of salt. You know your server better than an online forum posting; if something seems fishy to you, check into it. Likewise, a lot of the tuning scripts have caused performance problems when rules of thumb are put to use in situations that are exceptions to the rule.
Much of this is because of their use of equations to suggest values for various settings. Many of these are cargo-cult advice that is like a broken clock: it’s right twice a day, so someone decided it’s a universal truth. Be careful with those. Finally, you should always change only one setting at a time, then measure what happens. Before we had DPM, we used to diff before-and-after snapshots of the
SHOW STATUS command. DPM makes this easy, though, because every status variable is automatically available in our extensive set of graphs.
Even better than looking at status variables, though, is actual query performance. DPM measures and records every query’s execution, making it trivially easy to see what’s changed around a point in time. You can use the Compare Queries feature to see a before-and-after view of what’s changed, or just eyeball specific queries of interest in the Top Queries view. We’ve found many changes in query behavior in this fashion.
Keeping track of your changes is a good idea, too. DPM will help with this: each variable change is registered as an event, with the before-and-after value in the message. You can see this in your Event dashboard.
The Sort Buffer: A Tricky MySQL Topic
The sort buffer, which is controlled by the
sort_buffer_size setting, is probably one of the most-discussed MySQL settings. Interestingly, in many cases it has a lot less potential to help performance than to hurt it, so configuring it is often about avoiding trouble rather than gaining a lot of performance.
The variable controls the size of a buffer that’s created whenever MySQL has to sort rows. It is per-query, meaning each query gets its own buffer, and it’s allocated to its full size, not as-much-as-needed. This makes large settings potentially dangerous.
The worst abuse of this variable we’ve seen came from a server that was tuned with a script. The script relied on a naive formula that looked at a simplistic ratio of some server variables. Due to the server’s workload the script was never satisfied and continually suggested increasing this variable, which eventually was set to 1GB. The effect was that every time the server needed to sort a few rows, 1GB of memory was allocated. As you might expect, this was not only slow, but crashed the server pretty frequently due to the out-of-memory killer being invoked.
What if the variable is too small? This matters most when there’s a large number of rows to sort (the type of situation you might see with a large analytical query). In this case, the server will generate rows and write them to the buffer in memory, fill the buffer, then write the buffer to a sort file and repeat. The server then sorts these temporary files and merges them to sort the whole result-set. If this happens many times, it can be slow.
This variable can be set per-connection temporarily if such a large query is anticipated, and that’s probably better than setting it large globally. Settings that help large queries can seriously hurt small, fast queries.
You can find out whether a query is potentially a candidate for a larger sort buffer by looking at the number of rows sorted or the sort merge passes required. DPM can use regression to estimate this on a per-query basis (MySQL makes it available as a global status counter, not per-query, unless you are using the Performance Schema in MySQL 5.6 and later). If you identify queries that might benefit from a larger sort buffer, you can then try some experiments by hand. You can look at the
Sort_merge_passes local (not global) status counter before and after running the query, and tune and time until you determine whether the setting helps.
If you do configure a large per-connection setting, be sure that you either close the connection or reset the variable before, for example, returning the connection to a connection pool where the setting might impact other queries.
Having powerful analysis services at-hand prove to be hugely helpful in smart MySQL configuration, both for the topics discussed here and many beyond. If you don’t use SolarWinds DPM, you should certainly consider it or another performance management tool that is capable of high-resolution query analysis. There’s no substitute to be found in other methods, which often do not measure and expose information about vital metrics such as query latency.
You should also always keep careful records of what you change, and examine performance changes to all queries whenever you reconfigure something with a specific query’s performance in mind. Just because MySQL configuration involves hundreds of settings and variables, it doesn't mean users should shy away from setting up their systems smartly and for maximum efficacy. Happy configuring!
