Best Practices for MySQL Performance Tuning: Complete Guide

MySQL performance tuning: What is MySQL used for?

MySQL is a relational database management system built on SQL (Structured Query Language). This relational database management system (RDBMS) is used for various tasks, including data warehousing, e-commerce, and logging.

• A database management system, such as MySQL is required to add, access, and process data contained in a computer database.
• It can hold anything from a single piece of information to an online business's whole inventory of products.
• It is possible to develop websites that interact in real-time with a MySQL database to quickly show classified and searchable information to a website user using a scripting language such as PHP or Perl.

MySQL performance tuning: Discover where MySQL spends the most time on your query and why.

MySQL performance is frequently linked to the efficiency of your MySQL queries. It is critical to confirm that your performance problems are not the result of improperly written MySQL queries. MySQL's sluggish query log, log queries not using indexes, or APM products that provide MySQL performance monitoring, such as Datadog, AppOptics, New Relic, and others, can be used.

SQL query performance has a significant impact on application performance. If some queries take a long time to run, these queries or the database schema may require adjustment. This blog explains how the MySQL optimizer selects a plan for MySQL performance tuning.

MySQL performance tuning: Identifying the causes of MySQL slow-running queries

It's tough for a database administrator to troubleshoot a slow-running query without knowing what's causing it. As a result, before employing a query monitor or optimizer, the first step should be to assess the MySQL db optimization. This check will assist in determining whether the fundamental cause of the problem is a query or something else. Consider the following potential causes:

• Find out if the slow running MySQL is limited to a certain query, batch process, or database, or if other network resources are also affected
• Another cause of SQL performance problems could be incorrect index generation or access to a faulty index from the defined queries.
• Choosing a sluggish execution strategy may reduce performance.
• Running a single query at a time may go smoothly, but determine whether running numerous MySQL queries at the same time degrades server performance.
• If a user is experiencing poor performance with database components, a System Monitor might be quite useful. The performance of both database and non-database components can be checked using a System Monitor.
• Adhoc SQL queries execute outside of a stored procedure: stored procedures nearly always provide greater performance because MySQL can cache their execution plans; ad hoc queries should be converted to stored procedures wherever possible.
• In execution plans, long-running or CPU-intensive searches. Table scan actions indicate the absence of an appropriate index; therefore, implementing an index to eliminate the table scan can have an immediate and favourable effect on performance.
• MySQL slow performance could also happen because of queries with a significant number of joins. Joins take time, and while MySQL Server is obviously equipped to manage them, a large number of joins can significantly slow down performance. Limit the number of joins to seven as a general rule of thumb; if you have more than that, you may need to start searching for ways to cut back.
• A query that runs slowly all the time. This is a query that could be improved by rewriting it. A query that runs slowly at times is most likely caused by external variables such as locks or resource contention.

Best practices for MySQL performance tuning

Since, you are looking for the MySQL performance tuning tips, you might have an idea about the reason behind your issue. If the reason for the slow performing MySQL query is anything from the above listed caused, following performance optimization tips are only for you.

MySQL performance tuning: Always upgrade to the latest version of MySQL

When you already have MySQL installed in your system then why switch to the latest MySQL?
MySQL 8.0 includes significant performance and security enhancements, and as with any migration to a new database version, there are various things to consider before going into production to avoid major issues such as data loss, excessive downtime, or even a rollback during the conversion operation.

Each new version of MySQL released brings significant performance and feature improvements over prior versions. The most important piece of advice is to use the latest version of MySQL. MySQL 8.0 includes a transaction data dictionary and also the ability to store information that are associated with DDL operations in one single, atomic transaction.

Here are some version performance comparisons. If you're looking for more features or flexibility, you might already be utilizing MariaDB or Percona, which are upgraded drop-in replacements for MySQL Server.

Please share your experience if you've experienced significant benefits from utilizing MariaDB or Percona over vanilla MySQL. They are both excellent choices.

MySQL performance tuning: Optimize Hardware Resources for MySQL performance tuning

To increase MySQL performance, you will alter hardware and software variables at the system level.

Memory

If your server is frequently running out of memory, you can improve it by adding more. If your server runs out of memory, it will cache data storage (such as a hard drive) to act as memory. Database caching reduces performance.

By adjusting or upgrading your memory, you can increase the overall memory in your MySQL server and improve performance. Navigate to the server settings area, where you'll find the buffer pool size command for memory optimization.

Disc Space

If you're currently utilizing an HDD for storage, changing to an SSD (solid-state drive) may enhance performance. Keep track of how much disc space MySQL consumes in comparison to other resources. If it is significantly disproportionate, add more storage. Tools like sar and iotop are useful for monitoring disc input and output rates.

Network

It is critical to monitor network traffic to ensure that you have enough infrastructure to handle the demand.
Overburdened networks can result in slowness, dropped packets, and even server outages. Check that your network bandwidth is sufficient to handle your regular amounts of database usage.

CPU performance

The speed of your CPU indicates how fast your system performs. The top command will show you how CPU and RAM is being used by each process — in other words, how your resources are being used. To see how your resources are being used, use the Linux top command.

Take note of the MySQL processes and the proportion of processor use they necessitate. Upgrades to processors are more expensive, but if your CPU is a bottleneck, an update may be required.

MySQL performance tuning: Software performance optimization

As previously stated, you may optimize MySQL performance at both the hardware and software levels. Post that, consider MySQL software performance tuning.

In terms of software, MySQL performance tuning entails:

Configuring MySQL server options,
Optimizing the performance of MySQL queries,
Tuning MySQL indexes,
Upgrading to the MySQL InnoDB storage engine

Let us now look at various software performance tuning options that you can apply for MySQL optimization.

MySQL performance tuning: Start optimizing with a solid foundation

It is important that you should start with a good baseline. Only alter one thing at a time – establish a list of feasible changes – attempt each change separately – try in groups of two, three, and so on.

In other words, when strating with MySQL performance tuning, first, list out all possible reasons for the slow performance. In the next step you can start with one singe query and see if the performance improves. If not, then you can start working on the queries in pairs followed by consecutive number of queries and so on.

MySQL performance tuning: Never use MySQL as a queue

Queues can have an immediate impact on database speed and can enter your app databases without your knowledge. For example, if you create a status for a certain item so that a'relevant process' can access it, you unintentionally create a queue. What it does is add extra load time to access the resource for no apparent purpose.

Queues are problematic for two reasons. They serialize your workload, prohibiting activities from being finished concurrently, and they frequently result in a table containing work in progress as well as historical data from previously completed operations. It not only increases delay to the application, but it also makes MySQL performance optimization more difficult.

MySQL performance tuning: Use the latest database designs (Use InnoDB, Not MyISAM)

MyISAM is an outdated database style that is still used in some MySQL databases. Here are some reasons why you should go for InnoDB:

• The latest version of InnoDB offers more advanced functionality and includes optimization mechanics while MyISAMis a less efficient database structure while
• InnoDB employs a clustered index and stores data in pages that are stored in sequential physical blocks.
• If a value is too large for a page, InnoDB moves it to a different location before indexing it.
• InnoDB function keeps relevant data in the same location on the storage device, which means the actual hard drive takes less time to access the data.
• MySQL 5.5 and onwards use the InnoDB engine therefore ensuring higher concurrency and referential integrity requirements.
• InnoDB offers superior crash recovery.
• Row-level locking is available in InnoDB, but MyISAM can only accomplish whole table-level locking.
• As of MySQL 5.6, InnoDB, like MyISAM, now provides FULLTEXT search indexes.
• Transactions, foreign keys, and relationship constraints are supported by InnoDB but not by MyISAM.

MySQL performance tuning: Optimize MySQL performance with tools

As with most software, not all tools are compatible with all versions of MySQL. We'll look at five tools for evaluating your MySQL database and recommend improvements to increase speed.

Tuning-primer

This utility is a little older, and it was built for MySQL 5.5–5.7. It can examine your database and recommend configuration changes to increase performance. For example, if your system isn't processing requests rapidly enough to keep the cache clear, it may propose increasing the query cache size setting.

MySQLTuner performance tuning tool

It is suitable for latest MySQL db optimization. It evaluates your database configuration, similar to tuning-primer, looking for bottlenecks and inefficiencies. As you run it, you will see the general recommendations to improve the performance of your database.

phpMyAdmin Advisor

It analyses your database and makes recommendations, much like the other two utilities. If you already use phpMyAdmin, the Advisor is a useful tool that you may use using the GUI.

MySQL Enterprise Monitor (MEM)

It is the official MySQL monitoring tool from Oracle. MEM, like the other methods discussed here, mainly relies on the MySQL Performance Schema to retrieve metrics.This tool's can monitor errors, slow queries, latency, max connections, health of the disc (swapping), replication health etc. It also allows you to work on specific query.

Percona Monitoring and Management Tool (PMM)

The PMM is an open-source monitoring toolkit. It includes pre-built dashboards to assist you understand the performance of your MySQL system. PMM extracts MySQL metrics using Prometheus and the MySQLD Exporter tool . Mysqld exporter requires access to your MySQL instances in order to query them.

Code profiling tool for MySQL performance tuning

A code profiling tool allows you to profile and test code as you develop it. With the help of profiling tool users push better code to testing, receive fewer support tickets from production, and have happier dev managers by verifying the performance of code as it is produced. These tools also assist the experienced developers in locating delayed SQL queries, hidden errors, and other issues.

You can easily find out about slow SQL queries, ORM-generated queries, and previously unknown bottlenecks. Maintain a record of every SQL call parameter, affected record, and download timings.

MySQL performance tuning: Don’t focus too much on configuration

The MySQL defaults are one-size-fits-all and out of date, but you don't need to configure anything. It is preferable to focus on the core and modify additional variables only when necessary. Correctly configuring roughly ten choices will yield 95 percent of the server's top performance in most circumstances. The few exceptions are going to be edge cases that are specific to your situation.

When modifying configuration settings, make tiny incremental changes. A significant change may overload another value and impede performance. It's also a good idea to make one modification at a time and then test it. When you simply modify one variable at a time, it's easy to trace problems or misconfigurations.

MySQL performance tuning: Capturing metrics and alerting

When we talk about metrics, we're talking about the numbers that show what's going on inside your servers. We want to capture as many of these metrics as possible because they'll be very helpful when you're trying to figure out what is making MySQL slow.

For instance, someday there might be a strange problem that crops up and you'll be glad you have the ability to point to a graph and show a change in the server's workload.

Remember, you receive alert sparingly and only on conditions that indicate an actionable problem. Getting unnecessary alerts will waste your time and effectiveness. For example, if a server doesn't respond to connection attempts (as opposed to low buffer hit ratio), then that's an actual problem that needs solving!

MySQL performance tuning: Query Cache in MySQL

Caching content is one of the most significant factors of gauging performance. MySQL supports database query caching, which saves the text of the SELECT command as well as the retrieved result. As a result, anytime you create a duplicate database and call MySQL query cache, it will answer and display the cached result, and no calls will be parsed repeatedly. This allows you to optimise the MySQL cache optimization procedure.

MySQL performance tuning: Use indexing for MySQL performance tuning

In databases, the SELECT, WHERE structure is often used. They are in charge of filtering, retrieving, and analysing results. Creating a tiny index set for related tables is a handy structure that allows the query to be aimed to the index to accelerate its execution.
Main purpose of indexing is:

1. Indexes allow the server to find groups of neighbouring rows rather than single rows.
2. Indexes allow the server to avoid sorting by reading the rows in the order desired.
3. Indexes allow the server to satisfy full queries based just on the index, without the need to query the table at all.

If you can build your indexes and queries to take advantage of these three chances, you can speed up your queries by orders of magnitude.

MySQL performance tuning: Wrapping up

This article on MySQL performance tuning describes in detail the best techniques for database optimization and useful MySQL performance tuning tips that every developer should be aware of. It's a comprehensive tutorial for backend developers who are concerned about their bad database performance and require some practical strategies to optimise MySQL databases from the ground up.

If you want to contribute your ideas on the topic or ask some questions about it, please leave them in the comments area.