MySQL Performance Tuning: 14 Optimization Tips

Introduce

MySQL is a popular open-source database application that stores and structures data in a meaningful and easily accessible way. For large applications, the sheer volume of data can cause performance issues.

How Do I Optimize a MySQL Database? This guide provides some tuning tips on how to improve the performance of your MySQL database.

prerequisite

Install and run MySQL on a Linux system, Centos or Ubuntu
Existing databases
Administrator credentials for the operating system and database

System MySQL performance tuning

MySQL Performance Tuning and Optimization Tips Based on: At the system level, you’ll adjust hardware and software options to improve MySQL performance.

Balance the four major hardware resources

storage

Take the time to evaluate your storage. If you’re using a traditional hard disk drive (HDD), you can upgrade to a solid-state drive (SSD) to improve performance.

Use tools like iotop or SAR from the SYSSTAT package to monitor your disk input/output rates. If your disk usage is much higher than that of other resources, consider adding more storage or upgrading to faster storage.

Processor

The processor is often considered to be a measure of the speed of the system. Use the Linux top command to learn more about how your resources are being used. Note the MySQL process and the percentage of processor usage it requires.

Processors are more expensive to upgrade, but if your CPU is the bottleneck, you may need to upgrade.

Memory

Memory represents the total amount of RAM in the MySQL database storage server. You can tweak the in-memory cache (more on that later) to improve performance. If you don’t have enough memory, or if your existing memory isn’t optimized, you could end up hurting performance rather than improving it.

As with other bottlenecks, if your server is constantly running low on memory, you can upgrade by adding more memory. If you run out of memory, your server will cache a data store, such as a hard drive, to act as memory. Database caching can slow down your performance.

Internet

It’s important to monitor network traffic to ensure you have enough infrastructure to manage the load.

Network overload can lead to latency, packet loss, and even server outages. Make sure you have enough network bandwidth to accommodate normal levels of database traffic.

Use InnoDB instead of MyISAM

MySQL Performance Tuning and Optimization Tips: MyISAM is an older database style used for some MySQL databases. This is a less efficient database design. Newer InnoDB supports more advanced features and has built-in optimization mechanisms.

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

Use the latest version of MySQL

Ways to optimize MySQL database performance: For legacy and legacy databases, it is not always feasible to use the latest version. But whenever possible, you should check which MySQL version you are using and upgrade to the latest version.

Part of the ongoing development includes performance enhancements. Newer versions of MySQL can make some common performance tunings obsolete. In general, it’s always better to use native MySQL performance enhancements compared to scripts and configuration files.

Software MySQL performance tuning

SQL performance tuning is the process of maximizing the speed of relational database queries. The task usually involves a variety of tools and techniques.

These methods include:

Adjust the MySQL configuration file.
Write more efficient database queries.
Build a database to retrieve data more efficiently.

Note: When adjusting configuration settings, it’s best to make small incremental adjustments. Significant tweaks can overburden another value and slow down performance. In addition, it is recommended that you make one change at a time and then test it. When you only change one variable at a time, it’s easier to track down errors or misconfigurations.

Consider using automated performance improvement tools

How Do I Optimize a MySQL Database? As with most software, not all tools will work with all versions of MySQL. We’ll examine three utilities to evaluate your MySQL database and recommend changes to improve performance.

The first is Tuning-primer. This tool is a bit old and designed for MySQL 5.5 – 5.7. It can analyze your database and suggest settings to improve performance. For example, if it feels like your system can’t process queries fast enough to keep the cache clear, it might suggest that you increase query_cache_size parameters.

The second tuning tool that is useful for most modern SQL databases is MySQLTuner. The script (mysqltuner.pl) is written in Perl. Like tuning-primer, it analyzes your database configuration to look for bottlenecks and inefficiencies. The output shows metrics and recommendations:

At the top of the output, you can see the MySQLTuner tool and the version of your database.

The script is for MySQL 8.x. The log file suggestion is the first on the list, but if you scroll to the bottom, you can see general recommendations to improve MySQL performance.

The third utility that you may already have is phpMyAdmin Advisor. Like the other two utilities, it evaluates your database and suggests adjustments. If you’re already using phpMyAdmin, Advisor is a useful tool that you can use in your GUI.

Note: Check out our list of top SQL query optimization tools and use our in-depth analysis of each tool to find the best tool for your task.

Optimize queries

A query is an encoded request to search a database for data that matches a specific value. There are query operators that, by their very nature, take a long time to run. SQL performance tuning techniques help optimize queries for better run times.

Detecting queries with very short execution times is one of the main tasks of performance tuning. Queries typically implemented on large datasets are slow and database-intensive. Therefore, these tables cannot be used for any other tasks.

Note: Consider looking at the data warehouse schema, which separates the production database from the analytical database.

For example, OLTP databases require fast transactions and efficient query processing. Running inefficient queries prevents the use of the database and stops information from being updated.

If your environment relies on automated queries, such as triggers, they can impact performance. Check for and terminate MySQL processes that may be piling up in a timely manner.

Use indexes where appropriate

Many database queries use a structure similar to the following:

SELECT … WHERE

MySQL Performance Tuning and Optimization Tips: These queries involve evaluating, filtering, and retrieving results. You can refactor these by adding a small set of indexes to related tables. Queries can point directly to the index to speed up queries.

Functions in predicates

Avoid using functions in query predicates. For example:

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

This UPPER symbol creates a function that must perform a SELECT operation during the operation. This doubles the work done by the query, and you should avoid it if possible.

Avoid using % wildcards in predicates

When searching for text data, wildcards help with a broader search. For example, to select all names that start with ch, create an index on the name column and run:

SELECT * FROM person WHERE name LIKE "ch%"

Query scans the index to make the query cost low:

However, searching for names with wildcards at the beginning significantly increases the query cost because index scanning doesn’t work at the end of a string:

Wildcards at the beginning of the search don’t have an index applied. Conversely, a full-table scan searches each row individually, increasing the query cost in the process. In the example query, using wildcards at the end can help reduce the cost of the query due to traversing fewer table rows.

Note: Check out our MySQL Command Cheat Sheet which contains indexing commands.

One way to search for the end of a string is to invert the string, index the inverted string and look at the starting character. Putting wildcards at the end now searches for the beginning of the inverted string, making the search more efficient.

Specify the columns in the SELECT function

Methods for optimizing the performance of MySQL databases: A common expression for analyzing and exploratory queries is SELECT*. Choosing more than you need can lead to unnecessary performance loss and redundancy. If you specify the required columns, your query won’t need to scan for irrelevant columns.

If you need all the columns, there is no other way to fix this. However, most business needs don’t require all of the columns available in the dataset. Consider selecting a specific column instead.

In conclusion, avoid using:

SELECT * FROM table

Instead, try:

SELECT column1, column2 FROM table

Use ORDER BY appropriately

The ORDER BY expression sorts the results by the specified column. It can be used to sort by two columns at a time. These should be sorted in the same order, ascending or descending.

If you try to sort different columns in different orders, you’ll slow down performance. You can use it in conjunction with indexes to speed up sorting.

GROUP BY instead of SELECT DISTINCT

The SELECT DISTINCT comes in handy when trying to get rid of duplicate value queries. However, statements require a lot of processing power.

How Do I Optimize a MySQL Database? Avoid using SELECT DISTINCT as much as possible, as it is very inefficient and sometimes confusing. For example, if a table lists customer information with the following structure:

ID	name	surname	address	city	state	compress
0	John	smith	652 Flower Street	angel	that	90017
1	John	smith	1215 Ocean Drive	angel	that	90802
2	Martha	Matthews	3104 Hilltop Avenue	angel	that	90019
3	Martha	Jones	2712 Venice Avenue	angel	that	90019

Running the following query returns four results:

SELECT DISTINCT name, address FROM person

It seems like the statement should return a list of different names and their addresses. Instead, the query looks at the names and address bars of both parties. Although there are two pairs of customers with the same name, they have different addresses.

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

SELECT name, address FROM person GROUP BY name

The result returns a first different name and address, making the statement less ambiguous. To group by unique address, the GROUP BY parameter will be changed to address and DISTINCT will return the same result as the statement faster.

In conclusion, avoid using:

SELECT DISTINCT column1, column2 FROM table

Instead, try using:

SELECT column1, column2 FROM table GROUP BY column1

JOIN, WHERE, UNION, DISTINCT

MySQL performance tuning and optimization tips: Try using inner joins whenever possible. The outer join looks at additional data beyond the specified column. If you need that data, that’s fine, but including data you don’t need is a waste of performance.

Using INNER JOIN is the standard way to join tables. Most database engines also accept the use of WHERE. For example, the following two queries output the same result:

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

Contrast:

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

Theoretically, they also have the same running time.

The choice of use or query depends on the database engine. While most engines have the same run time for both methods, in some database systems, one runs faster than the other. JOINWHERE

Note: Learn more about MySQL JOINS and how to use them.

and commands are sometimes included in queries. As with outer joins, you can use these expressions if you want. However, they add additional sorting and reading to the database. If you don’t need them, it’s best to find more efficient expressions. UNIONDISTINCT

Use the EXPLAIN function

Modern MySQL databases include an EXPLAIN function.

How to optimize MySQL database performance: Appending an 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 wording of the query to avoid unintentional table scans or other performance degradation.

MySQL Server Configuration

How Do I Optimize a MySQL Database? This configuration involves making changes to the /etc/mysql/my.cnf file. Proceed with caution and make small changes at a time.

query_cache_size – Specifies the cache size for MySQL queries waiting to run. It is recommended to start with a small value of around 10MB and increase to no more than 100-200MB. If you cache too many queries, you may encounter cascading queries that “wait for cache locks”. If your queries are constantly backed up, a better approach is to use EXPLAIN to evaluate each query and find ways to make them more efficient.

max_connection – Refers to the number of connections allowed into the database. If you get an error referencing “Too Many Connections“, increasing this value may help.

innodb_buffer_pool_size – This setting allocates system memory as a data cache for the database. If you have a lot of data, increase this value. Make a note of the amount of RAM required to run other system resources.

innodb_io_capacity – This variable sets the input/output rate of the storage device. This is directly related to the type and speed of the storage drive. The capacity of a 5400-rpm HDD is much lower than that of a high-end SSD or Intel Optane. You can adjust this value to better match your hardware.

Conclusion

You should now know some MySQL performance tuning and optimization tips, as well as how to improve MySQL performance and tune your database.

Look for bottlenecks (hardware and software), do queries that require more work than they do, and consider using automation tools and EXPLAIN features to assess your database.

Optimizing MySQL tables helps reorder information in a dedicated storage server to increase data input and output speed. Check out our guide on how to optimize MySQL tables.

Database, IT Technology