Maximizing Database Performance – MySQL Tuning Best Practices

With the added complexity of growing data volumes and ever changing workloads, database performance tuning is now necessary to maximize resource utilizations and system performance. However, 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 understand execution plans, and often update or re-write good SQL. On top of that, tuning is usually very time consuming. 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.

As data volumes grow and technology becomes increasingly complex, it is becoming more important to tune databases properly to deliver end-user experience and to lower infrastructure costs. Performance tuning can help database professionals quickly identify bottlenecks, target insufficient operations through review of query execution plans and eliminate any guessing games.

Regardless of the complexity or skill level, the following MySQL 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 is important to set expectations for how long the process will take, as well as understanding how long the query should run in a perfect world, whether it be 1 second, 10 minutes or one hour.

This phase should include gathering all of your baseline metrics, such as rows examined and rows sent, and record how long the query is running now.  It’s also critical to collect wait and thread states, such as system blocks, sending data, calculating statistics and writing to the network. These wait states give great clues on where to focus tuning efforts.

Examine the 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 examine the query. For example, to gain tabular views of the plan, use EXPLAIN, Explain EXTENDED, or Optimizer Trace.

For a more graphical view and additional insight into the costly steps of an execution plan, use 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, sort and access the data.

Review the Table and Index

Now that the metrics have been gathered and the execution plan has been examined, it’s time to review the table and index information in the query, as these details will ultimately inform your tuning strategy. To start, it’s important to know where the tables reside and their sizes. Also, review the keys and constraints to see 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 you can use to get the size of the tables is to use the statement “mysqlshow  –status <dbname>” at the command line. Also using the “show index from <table_name>” 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 and what order those columns fall within the index.  MySQL will only use the index if the left-leading column is referenced.

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.

Effective Monitoring for MySQL Tuning

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

Identify MySQL Bottlenecks with Response-Time Analysis

If there are application slow-downs, and your end-users are complaining, you need to get to the root-cause of the issue – and fast. Traditional MySQL performance monitoring tools track resource metrics and focus on server health.

Response-time analysis tools are different because they focus time, not on resource metrics – the analysis is based on what the application and database engine are waiting for, which is captured in MySQL waits. Response-time analysis is the most effective way to resolve complex performance issues by looking at where the database engine is spending time. It goes beyond identifying query execution times or slow queries to pinpoint what exactly is causing a query to be slow.

Response-time analysis tools, such as DPA, go beyond showing wait times or hardware metrics– they correlate wait-times with queries, response time, resources, storage performance, execution plans and other dimensions to give you the ability to understand what goes on inside your database and what is slowing down performance.

The Benefits of Performance Tuning MySQL

Understanding what drives performance for your database allows you to reduce costs by right-sizing your servers and avoiding over-provisioning. It also helps you understand if moving to flash storage, or adding server capacity, will improve performance, and if so, how much.

As with much in IT, database performance tuning is not without its challenges. However, tuning proves to be worthwhile as it can give businesses more bang for the buck, rather than simply throwing more hardware at the issue.

Remember: MySQL tuning is an iterative process. As data grows and workloads change, there will always be new tuning opportunities.