Many businesses find their data volumes increase rapidly and their workloads continuously evolve. As a result, database performance tuning has become a crucial part of maximizing system performance and resource use, to ensure the system is operating at peak efficiency.
Database tuning and optimization requires extensive expertise, an understanding of execution plans, and the ability to write SQL. It’s also highly time-consuming, because there’s likely to be a vast number of SQL statements to fine tune. Once you’ve determined which statements need tuning, you then face the challenge of refining your tuning approach to suit each one.
Like all relational databases, MySQL can be complicated. Issues can cause it to halt entirely without notice, putting your applications and your business at risk. MySQL performance issues are usually caused by common mistakes, including subtle issues within a configuration trap or workload. To keep your MySQL server’s performance stable, consistent, and running at optimum speed, it’s important to eradicate these mistakes. Fortunately, many MySQL performance problems can be rectified by implementing MySQL optimize DB best practices.
Database optimization can help you identify bottlenecks, eliminate the guesswork associated with tuning queries, and target insufficient processes. To help you achieve your database optimization goals, I’ve compiled this guide to MySQL. Optimize database performance and solve MySQL problems with these best practices and tools. For those who don’t have time to read the full guide, I recommend two high-quality MySQL optimize DB performance tools: SolarWinds® Database Performance Analyzer (DPA) and SolarWinds Database Performance Monitor (DPM).
MySQL: Optimize Database Best Practices
Choosing the Right Tools for MySQL Database Optimization
Getting Started With MySQL: Optimize DB
MySQL: Optimize Database Best Practices
Profile Your Server Workload
To gain a robust understanding of how your server functions, you must first profile the server’s workload. This can reveal which queries are the most expensive, so they can be identified for further tuning efforts. Remember, time is the most important metric, because when a query is issued against the server, completion speed should be considered a priority.
Certain tools can assist you with profiling your workload by capturing queries the server executes and returning a table of tasks. The table is usually sorted by response time, in decreasing order, giving you instant visibility into the most time-consuming and expensive tasks. Tools profiling your workload can group queries of a similar nature together, letting you view fast queries executed numerous times, and slow queries.
Understand the Key Resources
Every database server relies on four key resources, which are critical to its functionality.
- Memory
- CPU
- Disk
- Network
When one of these is overwhelmed, weak, or inconsistent, your server is going to experience performance issues and lags. MySQL database optimization starts with understanding these resources, to better inform your choice of hardware and facilitate successful troubleshooting practices.
Hardware is extremely important. All the tuning in the world won’t make up for bad hardware, so it’s fundamental you choose high-performing components right from the start. Many businesses choose servers with rapid CPUs and disks but without memory capacity. Adding memory can be a low-cost way of maximizing performance, especially on disk-bound workloads. While this might sound counterintuitive, disks are often overwhelmed because there isn’t enough memory available to hold the server’s working set of data.
MySQL is likely to perform well with a rapid CPU, because each query runs in an individual thread and cannot be parallelized across CPUs. When you’re performing troubleshooting processes, check the use and performance of all four resources: CPU, disk, memory, and network. Carefully consider whether they’re performing poorly, or whether they’re simply overloaded. This is a crucial distinction. Knowing the difference can help you rectify performance issues more quickly.
Curate Baseline Metrics
Data collection and analysis is an important part of rectifying database performance issues. Before you begin the MySQL optimize database process, you should establish a rough idea of how much time the process will take. It’s also useful to gain an understanding of how long a query should run. This could be a single second, 15 minutes, or even a full hour.
At this stage, it’s recommended you gather the necessary baseline metrics. This should include rows examined and rows sent. In addition, you should note how long the query currently takes to run. Ensure you’ve collected wait and thread states—such as sending data, calculating statistics, system blocks, and writing to the network—before moving on to the optimization process. These wait states can give you valuable insight into where you should be focusing your optimization efforts.
Analyze the Execution Plan
Creating an execution plan is a critical aspect of your MySQL optimize database mission, because it contributes to the creation of a roadmap for overall query performance. MySQL provides you with multiple ways of selecting an execution plan, along with simple navigational structures for examining the query. You can view a tabular version of the plan by using EXPLAIN, EXPLAIN EXTENDED, or the optimizer trace.
Alternatively, you can access a graphical view of the plan, and gain a clear understanding of which steps are the costliest, by using MySQL Workbench. These execution plans provide steps from top to bottom, table names, key length, select type, potential keys to target, reference, and number of rows to read. Moreover, extra columns can give you insight into how data will be accessed, sorted, and filtered.
Review the Index and Table
Once you’ve curated the metrics and analyzed your execution plan, you’ll need to review the query table and index information. This review will inform your optimization approach.
First, take the time to gain an understanding of table size and location. It’s also useful, at this stage, to review the constraints and keys to help you understand how the tables are related. Column size and makeup, especially when the “where” clause is in use, should also be considered.
To view table size, you can input the following statement into the command line: mysqlshow –status <dbname>. The show index from <table_name> statement is also helpful, because it’ll allow you to check the indices and their relative cardinality. This insight can drive your execution plan.
Be sure to identify whether or not the indices are multi-column and take note of the order of the columns within the index. If the left-leading column is referenced, MySQL will use the index. It won’t use the index under any other circumstances.
Avoid Using MySQL as a Queue
Queues, and access patterns resembling queues, can infiltrate your application without you noticing. If, for instance, you establish an item status, so a certain worker process can claim it before acting, then you have unintentionally created a queue. A common example of creating a queue would be marking emails as unsent, sending them, and then marking them as sent.
Queues should be avoided because they’re responsible for two issues. The first is they prevent tasks from being run in parallel, because the workload has been serialized. The second is queues are often responsible for tables containing work in progress alongside historical data from jobs processed at a much earlier date. Both issues create application latency and unnecessary MySQL load.
Be Aware of Scalability Traps
Scalability is often thought of as a vague and obscure concept, but there are mathematical definitions attributed to scalability, which are represented as precise equations. These equations highlight why systems are so difficult to scale—and how, often, they don’t scale as well as they ought to.
The Universal Scalability Law can be applied to a system’s scalability characteristics. This law explains scalability issues in relation to crosstalk and serialization costs. If parallel processes must stop for a serialized element, then scalability is inherently limited. Similarly, if parallel processes need to communicate to coordinate their efforts, this is also an extreme limitation.
Avoiding serialization and crosstalk is key to optimizing your database, because it’ll help your applications scale more successfully. In MySQL, this means avoiding exclusive locks on rows. Queues often scale poorly because of this.
Use Response Time Analysis to Identify MySQL Bottlenecks
When your applications are slowing down, and your end-user experience is being affected, getting to the root cause of the issue quickly is absolutely critical. Traditional MySQL performance monitoring solutions focus on server health and track resource metrics. Response-time analysis tools, on the other hand, focus on time, rather than resource metrics. This type of analysis is based on what the database engine and application are waiting for, which is captured in waits.
Response-time analysis is the most efficient and successful route to solving complex performance issues, because it allows you to look at where time is being spent by the database engine. This goes one step further than identifying query execution times or slow queries. Response-time analysis tools, like SolarWinds DPA, correlate wait times with queries, resources, storage performance, response time, execution plans, and many other dimensions. This gives you a comprehensive understanding of what’s happening inside your database and the causes of database issues.
Choosing the Right Tools for MySQL Database Optimization
If you’re hoping to make MySQL optimize database processes easier and more effectively. there are two tools you should put at the top of your list: SolarWinds DPA and SolarWinds DPM. These tools offer a range of advanced features and are easy to use. They’re scalable and suitable for small and large businesses alike.
SolarWinds Database Performance Analyzer
DPA is designed to offer users multidimensional database performance analysis, which helps simplify the process of deciphering the thousands of factors contributing to database performance. This tool draws correlations for you, making MySQL database optimization significantly less labor-intensive. Because response time is crucial to isolating the root causes of problems, DPA collects SQL statement data continuously, every second. This data is then ranked according to response time, helping you determine which SQL query to focus on.
DPA monitors your database on a 24-hour basis, collating data points both historically and within seconds. This means it can collate and analyze data from five years earlier and five seconds earlier, simultaneously. Results are presented in an easy-to-read way, in the form of bar charts.
Customizable alerts keep you informed of any unusual activity and provide extensively detailed information regarding specific issues as they arise. This helps database administrators rapidly identify the cause of a problem. DPA also gives you access to tuning advisors, a feature designed to point out issues needing your immediate attention. All tuning advice provided by the tuning advisors is actionable and clear.
A 14-day free trial of DPA for MySQL optimization is available here.
SolarWinds Database Performance Monitor
DPM gives you insight into all the information you need to identify MySQL performance problems. This tool measures errors, throughput, latency, warnings, execution plans, index usage, and much more. It measures these metrics for every single executed query in microsecond resolution.
The “Top Queries” feature is one of DPM’s best. It shows a master-detail view of all the servers in your application, allowing you to drill down for per-second visibility into query samples, EXPLAIN plans, and cross-correlations with other metrics. You can drill down from an overview to a single problem query on an individual server in an instant. You’re also able to thin-slice queries, databases, and users, and compare across multiple time periods to conduct before-and-after change analysis.
DPM measures an impressive range of metrics, as it monitors and classifies multi-dimensional data on every query, process, user, database, CPU, disk, and other key system elements. This tool is powerful and can capture more than 10,000 metrics every second from every system it monitors. This is significantly more than the standard metrics revealed by MySQL.
A 14-day free trial of DPM is available.
Getting Started with MySQL: Optimize DB
Knowing what drives your database performance can help you avoid overprovisioning and reduce costs. It gives you an understanding of how certain optimization activities, like adding server capacity or moving flash storage, might impact performance.
Optimizing your MySQL database can be challenging, but it’s made easier by using SolarWinds DPA and DPM. Remember, tuning is an iterative operation. As workloads change and data grows, new tuning opportunities will arise.