To operate efficiently, MySQL database performance must be tuned. Businesses can’t afford to have customers waiting for pages to load on their websites. Poor performance can lead to lost business among a host of other issues. There are likely alternative solutions that users will be tempted to try when database performance slows down e-commerce applications.
The database administrators (DBAs) responsible for optimizing the performance of MySQL systems have a formidable task on their hands. A MySQL instance is a complex beast that has many moving parts, any of which can be the root cause of degraded performance. Identifying the culprit and taking the proper corrective actions can be challenging to even the most experienced DBA.
The following five tips should be considered by database teams looking to improve the performance of their MySQL systems.
Top Five Tips for MySQL Database Performance Tuning
1. Change Only One Thing at a Time
It can be tempting to resolve performance issues by implementing a set of changes all at once. After studying the issues plaguing the system, DBAs might have identified several corrective actions they would like to take. Some team members may want to push through the changes so they can be on to their next project.
Taking this approach would, in most cases, be a mistake. The interactions of more than one change can make it impossible to determine which one made the difference and improved performance. It also makes it difficult to decide which change is responsible for further degrading performance.
Changing one thing at a time allows the team to fully understand how the modification affects the system before going on to the next one. They may find that all proposed changes are not needed, as some early in the rotation have successfully addressed the issues.
2. Review Configuration Settings
MySQL configuration settings can have a big impact on database performance. The location of the configuration file depends on the operating system used to run MySQL and is found at /etc/mysql/my.cnf for Linux machines. The file is located at C: ProgramDataMySQLMySQL Server x.xmy.ini on Windows systems with x.x replaced by the current version of MySQL.
Many variables that influence the operation and performance of a MySQL instance are available in the configuration file. In many cases, the default settings are not appropriate for a specific MySQL implementation. Some of the most important to investigate and potentially modify are:
- innodb_buffer_pool_size – The memory buffer pool stores data in memory to minimize the number of disk reads. You should allow the buffer pool to use all but 2GB to 4GB of memory that will be used by the operating system. So on a system with 16GB available, innodb_buffer_pool_size = 12GB would provide the maximum amount of memory for the pool.
- innodb_log_file_size – This variable controls how often checkpoint flushes are required when writing data from the buffer pool to disk. The default of 48MB is too small for the majority of databases.
- max_connection – Modifying this variable changes the maximum allowable number of database connections. Its default is 151 which may need to be changed for your environment.
There are many other variables in the configuration file that may offer a way to improve system performance. As previously discussed, you probably should only change one variable at a time to observe its effects.
3. Ensure Appropriate Hardware
Without the right hardware on which to operate, a MySQL instance can be doomed to bad performance and slow response time. Four main hardware resources are responsible for system performance.
- Storage – Both the capacity and read/write speed of the storage subsystem impacts database performance.
- Processor – An under-performing processor will slow down your database.
- Memory – Memory capacity and speed influence database performance by minimizing the number of disk reads required during system operation.
- Network – Inconsistent and increased latency can result from inadequate network resources.
All of these resources may have been provisioned correctly when the database was installed. Over time, requirements may have changed and a review is appropriate to address performance issues.
4. Optimize Database Schemas
A database schema can have a considerable effect on system performance. Looking at schema aspects such as data type and the number of columns in tables can result in faster responding MySQL databases. Reducing the number of columns and normalizing tables are two specific things to investigate to address lagging performance.
5. Tune Problem SQL Queries
Optimizing SQL queries can provide major performance enhancements. Finding the queries that are running most often and taking the longest to execute is a good place to start when reviewing queries. Getting rid of leading wildcards and making sure indexes are being used properly are two of the tactics you can employ when tuning SQL queries.
Use the Proper Tools for Performance Tuning
Third-party tools can be instrumental in helping identify and isolate MySQL performance problems. SQL Diagnostic Manager for MySQL offers DBAs a reliable monitoring platform that can monitor and alert on system issues so they can be promptly addressed to maintain availability and performance. The tool also enables a database team to find the problem queries that may be at the root of degraded performance.
Features of SQL Diagnostic Manager for MySQL include:
- Real-time customized monitoring and alerting;
- Identification of slow, locked, and other problematic queries;
- Customizable dashboards to address business requirements;
- Detection and resolution of security vulnerabilities;
- Support for physical, virtual, and cloud instances of MySQL and MariaDB.
An IDERA whitepaper is available that takes a more in-depth look at tuning MySQL instances for improved performance. It covers more configuration file settings and other aspects of MySQL systems that may be the cause of reduced performance.