MySQL Performance Tuning and Optimization Tips

Introduction

MySQL is a popular open-source database application that stores and structures data in a meaningful and accessible way. With large applications, the amount of data can lead to performance degradation.

This guide provides several tuning tips on improving a MySQL database's performance.

Prerequisites

• A Linux system with MySQL installed and running. This guide uses Ubuntu.
• An existing database.
• Root privileges for the operating system and the database.

What Are the Benefits of MySQL Performance Tuning?

MySQL performance tuning offers significant benefits, such as improved efficiency, scalability, reduced downtime risks, and enhanced user experience. Optimization helps maximize a MySQL database's potential, ensuring it is reliable and performs well.

The key benefits of performance tuning are:

• Maximized resource utilization. Optimizes hardware resource allocation for better efficiency and cost-effectiveness.
• Enhanced indexing strategies. Boosts query performance by improving indexing techniques.
• Reduced downtime risks. Identifies and mitigates potential bottlenecks to minimize downtime and ensure continuous availability.
• Optimized cache configuration. Configures caching settings to enhance data retrieval speed and system responsiveness.
• Streamlined backup and restore processes. Facilitates backup and restore operations without impacting production systems.
• Improved query optimization. Enhances query execution plans and SQL statements for maximum efficiency.
• Enhanced security and compliance. Improves security configurations to mitigate risks and ensures compliance with standards.

15 MySQL Performance Tuning Tips

This section lists tuning tips to help you use your MySQL database to the fullest. These tips help utilize database features to achieve the best performance.

Improve MySQL Query Optimization

A query is a request to search the database for data that matches a provided value. Some query operators take a long time to run.

Various SQL performance tuning techniques help detect and optimize queries for better run times. Queries on large datasets are usually slow and occupy database tables, making the tables unavailable for other tasks.

For example, an OLTP database requires fast transactions and effective query processing. Running an inefficient query blocks the use of the database and stalls information updates.

One solution is to rewrite queries by restructuring them to be more efficient. Rewriting can include eliminating unnecessary subqueries, simplifying complex JOINs, and optimizing conditions in the WHERE clause. The optimizations lighten the workload on the server and lead to quicker response times.

Additionally, if your environment relies on automated queries such as triggers, they may impact performance. Check and terminate MySQL operations that take a long time.

Use Indexing Strategies

Indexes are essential for optimizing database performance because they significantly reduce query execution time. Queries that effectively utilize indexes are created on frequently queried columns, such as primary keys or those used in JOIN or WHERE clauses.

Many database queries use a structure similar to this:

SELECT … WHERE

These queries involve evaluating, filtering, and retrieving results. You can restructure these by creating indexes for the related tables. The query can be directed at the index to speed it up.

However, do not overuse indexing as it can slow down write speeds and increase disk space usage, ultimately leading to poor database performance.

Monitor and Analyze Resource Utilization

Monitoring resource utilization in a MySQL database prevents bottlenecks and maintains optimal performance. The monitored metrics include the usage of CPU, memory, and disk input/output to help you efficiently manage the server's operation.

By monitoring and analyzing resource utilization, you can resolve the following issues:

• High CPU usage. A high CPU utilization means the server is processing a heavy workload, which can be caused by poor query optimization or high user activity.
• High memory usage. If the server's memory usage is constantly high, it can be the reason for slow query performance. To resolve the issue, optimize query caches and buffer pools for efficient memory management, improving query response times.
• Slow disk I/O operations. Disk I/O metrics show slow I/O operations that severely impact database performance. Optimize queries to reduce disk reads or upgrade the storage solution to improve performance.

Employ Caching Strategies

Caching strategies help speed up query response times in MySQL databases. They retain frequently accessed data or query outcomes in cache memory, which allows subsequent requests to retrieve the same information faster than fetching it directly from disk.

Some common caching strategies are:

• Query caching. Query caching stores SELECT queries and their results for future use. If a user makes the same query later, the database delivers the cached results, reducing query execution time.
• Key-value caching. This strategy stores frequently accessed database rows or objects in a caching system. Storing that data helps retrieve it quickly without the need to access the database.

This configuration involves changing the /etc/mysql/my.cnf file. For query caching, modify settings related to the query_cache items in the my.cnf file (query_cache_type, query_cache_size, and query_cache_limit).

For example, the query_cache_size item specifies the size of the cache of MySQL queries waiting to run. It is best to start with small values (around 10MB) and then increase (no more than 100-200MB).

Configure the innodb_buffer_pool_instances or innodb_buffer_pool_size items in the my.cnf file for key-value caching.

Avoid Functions In Predicates

Avoid using functions in predicates (such as WHERE clauses) because MySQL might not be able to utilize indexes efficiently, resulting in slower query execution times.

For example, in the following statement:

SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'Copy

The UPPER notation creates a function that must operate during the SELECT operation. This doubles the work the query is doing and puts additional stress on the database server. Additionally, the UPPER function prevents the database from utilizing the index on COL1.

If you cannot avoid that function in SQL, create a new function-based index or generate custom columns in the database to improve performance.

Avoid Wildcard (%) at the Beginning of Predicates

Wildcards perform a broader search when searching through textual data. For example, to select all names that start with ch, create an index on the name column and execute the following statement:

SELECT * FROM person WHERE name LIKE "ch%"

The query scans the indexes, lowering the query cost:

However, searching for names using wildcards at the beginning increases the query cost significantly because an indexing scan does not apply to the ends of strings:

Instead of indexing, wildcards at the beginning perform full table scan searches through each row individually, increasing the query cost in the process. In the example query, using a wildcard at the end helps reduce the query cost due to going through fewer table rows.

A clever way to search the ends of strings is to reverse the string, index the reversed strings, and look at the starting characters. Placing the wildcard at the end now searches for the beginning of the reversed string, making the search more efficient.

System Hardware Tuning

Another option for boosting performance is to adjust hardware and software options at the system level. For example:

• Storage. If you use traditional hard disk drives (HDD), upgrade to solid-state drives (SSD) for performance improvements. Use a tool like iotop or sar from the sysstat package to monitor disk input/output rates. If disk usage is higher than the usage of other resources, consider adding storage or upgrading to a faster storage solution.
• Processor. Processors are considered the measure of your system's speed. Use the Linux top command to see how your resources are used. Pay attention to the MySQL processes and their processor use percentage. Processors are more expensive to upgrade, but if your CPU is a bottleneck, an upgrade might be necessary.

• Memory. Memory is the total amount of RAM in your MySQL database storage server. You can adjust the memory cache to improve performance. If you don't have enough memory or the existing memory is not optimized, you can end up hurting your performance instead of improving it. If your server constantly runs out of memory, you can upgrade by adding more.
• Network. Monitor network traffic to ensure the infrastructure is sufficient to handle the workload. Overloading your network can lead to high latency, dropped packets, and server outages. Ensure there is enough network bandwidth to accommodate normal database traffic levels and potential spikes during peak hours.

Tune MySQL for Your Hardware

Another way to boost a MySQL server's performance is to tune it to your hardware. Configuring the server involves changing the /etc/mysql/my.cnf file. You can edit the file with any text editor using sudo. Proceed with caution and make minor changes at a time.

The following list contains some variables in the my.cnf file that impact server performance:

• max_connection - States the maximum number of simultaneous connections allowed. If you are getting errors citing "Too many connections," increasing this value may help.
• innodb_buffer_pool_size - Allocates system memory as a data cache for your database. If you have large chunks of data, increase this value. Take note of the RAM required to run other system resources.
• innodb_io_capacity - Sets the rate for the storage device input/output. It is directly related to the storage drive type and speed. A 5400-rpm HDD will have a much lower capacity than a high-end SSD or Intel Optane. Adjust this value to match your hardware better.
• query_cache_size - As mentioned in the caching strategies section, this item specifies the cache size for MySQL queries waiting to run.
• innodb_stats_on_metadata - Causes InnoDB to update statistics when metadata statements such as SHOW TABLE STATUS or SHOW INDEX are executed. If statistics are unimportant, set it to OFF to avoid updating InnoDB statistics and improve read speeds.
• innodb_flush_method - Controls the data flushing method, which can impact performance, reliability, and compatibility with different storage configurations. Set it to O_DIRECT to avoid a performance penalty from double buffering.

Use InnoDB Instead of MyISAM

InnoDB configurations greatly affect MySQL performance. Some parameters and their effects, such as the innodb_buffer_pool_size and innodb_io_capacity, were discussed in the section above.

MyISAM is an older database style used for some MySQL databases. It is a less efficient database design than InnoDB, which supports more advanced features and has in-built optimization mechanics.

InnoDB uses a clustered index and keeps data in pages, which are stored in consecutive physical blocks. If a value is too large for a page, InnoDB moves it to another location and indexes it. This feature helps keep relevant data in the same place on the storage device, meaning it takes a physical hard drive less time to access data.

For more information, see our in-depth comparison between MyISAM and InnoDB.

Update MySQL to Latest Version

Using the latest version is not always feasible for older and legacy databases. But whenever possible, you should check your MySQL version and upgrade to the latest.

Performance enhancements are part of ongoing development. Newer MySQL versions may render some common performance adjustments obsolete. In general, it is always better to use native MySQL performance enhancement over scripts and configuration files.

Use Automatic Performance Improvement Tools

As with most software, not all tools work on all MySQL versions. Here are three utilities for evaluating your MySQL database and making changes to improve performance:

• Tuning-primer. The utility is an older tool designed for MySQL 5.5 - 5.7. It analyzes your database and suggests settings to improve performance. For example, it may suggest that you raise the query_cache_size parameter if it determines your system cannot process queries quickly enough to keep the cache clear.
• MySQLTuner. The second tuning tool is useful for modern SQL databases. The Perl script analyzes database configuration looking for bottlenecks and inefficiencies. The output shows metrics and recommendations. MySQLTuner works with MySQL 8.x.
• phpMyAdmin Advisor. Like the previous two utilities, phpMyAdmin Advisor evaluates your database and recommends adjustments. If you already use phpMyAdmin, the Advisor is a panel available within the app's GUI.

Specify Columns in SELECT Function

A commonly used expression for analytical and exploratory queries is SELECT *. Selecting more than you need results in unnecessary performance loss and redundancy. If you specify the necessary columns, your query will scan only the relevant ones.

Instead of using SELECT *, specify columns in the SELECT clause to improve MySQL performance. Excessive columns cause additional load on the database, slowing down the server's performance and overusing the resources.

Therefore, instead of using:

SELECT * FROM table

Use the syntax below:

SELECT column1, column2 FROM table

Use ORDER BY Appropriately

The ORDER BY expression sorts results by the specified column. It can also sort by two columns at once, ascending or descending.

If you try to sort different columns in different order, it will slow down performance. You may combine ORDER BY with an index to speed up the sorting.

GROUP BY Instead of SELECT DISTINCT

The SELECT DISTINCT query is useful when eliminating duplicate values. However, the statement requires a large amount of processing power. Additionally, it is efficient only in specific situations and can sometimes be confusing.

For example, if a table lists information about customers with the following structure:

id	name	lastName	address	city	state	zip
0	John	Smith	652 Flower Street	Los Angeles	CA	90017
1	John	Smith	1215 Ocean Boulevard	Los Angeles	CA	90802
2	Martha	Matthews	3104 Pico Boulevard	Los Angeles	CA	90019
3	Martha	Jones	2712 Venice Boulevard	Los Angeles	CA	90019

Running the following query returns four results:

SELECT DISTINCT name, address FROM person

The statement seems like it should return a list of distinct names and addresses. Instead, the query looks at both the name and address columns. Although two pairs of customers have the same name, their addresses differ.

To filter out duplicate names and return the addresses, try using the GROUP BY statement:

SELECT name, address FROM person GROUP BY name

The result returns the first distinct name along with the address, making the statement less ambiguous. To group by unique addresses, the GROUP BY parameter would just change to address and return the same result as the DISTINCT statement faster.

JOIN, WHERE, UNION, DISTINCT

Use INNER JOINs whenever possible. An OUTER JOIN looks at additional data outside the specified columns, which is often a waste of resources because it includes data that is not required.

Using INNER JOIN is the standard approach to joining tables. Most database engines accept using WHERE as well. For example, the following two queries output the same result:

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

And:

SELECT * FROM table1, table2 WHERE table1.id = table2.id

In theory, they have the same runtime as well.

The choice between JOIN and WHERE query depends on the database engine. While most engines have the same runtime for the two methods, in some database systems, one runs faster than the other.

The UNION and DISTINCT commands are sometimes included in queries. Like an outer join, it is fine to use these expressions if necessary. However, they add additional database sorting and reading operations. It is better to find a more efficient expression if they are not required.

Use the EXPLAIN Function

Modern MySQL databases include an EXPLAIN function.

Appending the EXPLAIN expression to the beginning of a query will read and evaluate the query. If there are inefficient expressions or confusing structures, EXPLAIN can help you find them. You can then adjust the query's phrasing to avoid unintentional table scans or other performance hits.

Conclusion

This article explained different methods for improving MySQL performance and tuning your database. Look for bottlenecks (hardware and software), queries that are doing more work than needed, and consider using automated tools and the EXPLAIN function to evaluate your database.

Next, check out our guide on how to optimize MySQL tables.