Need for Speed: Top Five MySQL Performance Tuning Tips

Janis Griffin is a Database Performance Evangelist for SolarWinds.

With the added complexity of growing data volumes and ever changing workloads, database performance tuning is now necessary to maximize the use of system resources to perform work as efficiently and effectively as possible. However, for many database administrators (DBAs), performance tuning is often easier said than done.

Let’s face it, tuning is difficult for a number of reasons. For one thing, it requires a significant amount of expertise in order to read, understand the execution plan, and write a good SQL statement in case the query needs to be updated or changed. On top of that, the nature of tuning necessitates a lot of time and effort. There will always be a large volume of SQL statements to sort through, which may lead to uncertainty around which specific statement needs tuning; and given every statement is different, so too is the tuning approach.

However, as data volumes grow and IT becomes increasingly complex, it’s become a necessity for DBAs to tune databases properly to save themselves and their organization time and energy. Performance tuning can help DBAs quickly identify bottlenecks, target insufficient operations through review of query execution plans and eliminate any guessing games.

Regardless of the complexity or a DBA’s skill level, the following performance tuning tips will serve as a step-by-step guide to solving MySQL performance problems and help alleviate the pain points that often accompany performance tuning.

Gather Baseline Metrics

Effective data collection and analysis is essential for identifying and correcting performance problems. That said, before performance tuning begins, it’s important to set expectations for how long the process will take, as well as provide an acceptable timeframe to close the query, whether it be 10 seconds, 10 minutes or one hour. This timeframe should include gathering all of your baseline metrics. From there, it’s critical to collect wait and thread states, which include: system block, sending data, calculating statistics and writing to the network.

Develop an Execution Plan

Developing an execution plan is incredibly important as you work to create a roadmap for query performance. Luckily, MySQL offers many ways to choose an execution plan and simple navigation to close the query. For example, to gain visual or tabular views into the database, use EXPLAIN, Explain EXTENDED, Optimizer Trace or MySQL Workbench. These plans list out steps from top to bottom, select type, table names, possible keys to target, key length, reference and the number of rows to read. Also, the "extra columns" will give you more information about how it’s going to filter and access the data.

Review the Table and Index

Now that the metrics have been gathered and the execution plan has been put in place, it’s time to review the table and information in the query, as these details will ultimately inform your tuning strategy. To start, it’s important to know where the table resides to review the keys and constraints, plus how the tables are related. Another area to focus on is the size and makeup of the columns – especially in the “where” clause. A little trick DBAs can use to get the size of the tables is to use the statement "mysqlshow with–status." Also using the "show indices from table" statement is helpful to check on the indices and their cardinality, as this will help drive the execution plan. Notably, identify if the indices are multi-column or not, and what order they fall within the index.

Consider SQL Diagramming

After gathering and reviewing all of this information, it’s time to finally start tuning. Often, there may be so many possible execution paths to resolve a poorly performing query that the optimizer cannot examine them all. To circumvent this, a useful technique is SQL Diagramming, which provides a view of the issue mathematically to help the tuner ultimately find a better execution path than the optimizer. SQL diagramming can also be implemented when tuning to help expose bugs within a complete query. Many times, it’s hard to understand why the optimizer is doing what it’s doing, but SQL diagramming helps create a clearer path to the issue, which can save businesses from costly mistakes.

Monitor

Monitoring can easily be forgotten, but it’s an integral step in ensuring the problem within the database is resolved – and stays resolved. After tuning, it’s important remember to monitor the improvements made. To do this, make sure to take new metric measurements, then compare to the initial readings to prove that tuning made a difference. Following a continuous monitoring process, it’s also critical to monitor for the next tuning opportunity, as there’s always room for improvement.

As with much in IT, database performance tuning is not without its challenges. However, tuning proves to be worthwhile when DBAs are tasked with managing queries to ensure business success, on top of performing their daily tasks. Tuning can also give businesses more bang for the buck, rather than simply throwing more hardware at the issue. But as it’s not unusual for a DBA responsible for upwards of 50 databases to also handle SQL tuning, these tips will also be helpful as DBAs look to find the best and most efficient plan of action. Remember: tuning is an iterative process. As data grows and workloads change, there will always be new tuning opportunities.

Industry Perspectives is a content channel at Data Center Knowledge highlighting thought leadership in the data center arena. See our guidelines and submission process for information on participating. View previously published Industry Perspectives in our Knowledge Library.