MySQL is an open source database application that creates meaningful structure and accessibility for large amounts of data.. But, with large data comes performance issues. This article will give you performance tuning in MySQL tips in order to boost its performance.
If you are a legacy or older database, this may not be viable. But if it is, check on the latest version of MySQL and upgrade to the latest (most recent NoSQL databases).
Newer versions come with performance improvements by default, saving you the need to find further solutions to common performance tuning in MySQL issues. It’s typically best to use default or native MySQL performance improvements rather than scripting or configuration files.
MyISAM has fewer advanced features than InnoDB. Overall, MyISAM is less efficient, with fewer native optimisation enhancements. For instance, InnoDB has a clustered index, with data in pages and consecutive physical blocks. If a value is too much to fit onto a page, InnoDB migrates it to a different location, indexing its value — pertinent data stays in the same location on the hard drive, shortening the time to retrieve it.
The processor’s speed shows how fast your machine is. The top command will give you insight into your CPU and memory usage per process — in other words, how your resources are being used. When using MySQL, keep an eye on that particular process as a percentage of usage. Anything too high and the bottleneck is likely your machine, which means it needs to be upgraded.
Adjusting or improving your memory will boost the total RAM in your MySQL server and improve performance. Head to the server configuration section, where you will see the buffer pool size command for optimising memory.
If you’re using HDD for storage, upgrading to a solid-state drive could improve your performance. Keep an eye on how much disk usage MySQL is using compared to other resources. Add more storage if it is substantially disproportionate. Tools like sar or iotop (sysstat package) are good for monitoring disk input vs output rates.
Bottlenecks in the network bandwidth lead to dropped packets, latency, or even complete downtime. You should be able to provide for normal database traffic.
Adjusting scripts allows for more efficient database queries, MySQL configuration files, and optimal database design.
PRO TIP: tweaking your software configuration can cause more problems than it solves. I recommend only making small changes at the time, testing each time, as this will make it easier to identify issues and overall assess the impact to performance.
Using a code profiling tool, like Stackify Prefix, allows you to profile and test code as you write it. By validating the performance of code as it is written, Prefix users push better code to testing, receive fewer support tickets from production, and have happier dev managers. Prefix’s profiling and tracing help even the most experienced developers find slow SQL queries, hidden exceptions, and more.
Discover underperforming SQL queries, ORM generated queries, and previously unknown bottlenecks. Track every SQL call parameter, affected record, and download times. Prefix also makes it easy to spot dreaded N+1 patterns. Prefix covers your entire tech stack, with support for .NET, Java, PHP, Node.js, Python, and Ruby on both Windows and Mac operating systems. Download Prefix today for FREE.
When you query a database, you ask it to retrieve specific data that matches the values you inputted. Be wary of automated queries that may be draining forms in the background. Keep an eye on them using the show processlist function and kill those that are not needed. Some growers take longer than others. If you allow unnecessary MySQL processes to run, these compile and prevent other users from accessing and modifying data.
Our recommendation for the top three automatic performance improvement tools are MySQLTuner, tuning-primer, and PHPMyAdmin Adviser. The first tool, MySQLTuner, is most handy with modern MySQL databases. It finds sections that can be optimised, recommending adjustments, and is compatible with MySQL 8.x. The other two do similar functions although tuning-primer is older, made for MySQL 5.5 to 5.7.
Again, make sure you only make incremental changes here. We are going to adjust the /etc/mysql/my.cnf file.
The command configuration sets apart the system memory as a data cache for your database. The larger your data chunks are the larger the value should be. While making changes, note how much RAM is used for other system resources.
This shows the input/output rate from your storage device. The hard limit here is whatever the type of storage drive you use. Tweaks can be made to better align your database with your hardware
This sets the size of cache of pending MySQL queries. We recommend starting small, at around 10MB, then increasing to 100-200MB, but no more. If you have too many queries and receive the message “Waiting for cache lock”, try the EXPLAIN function instead to evaluate individual queries and ways to optimise each.
This shows the number of connections enabled in the database. To overcome errors for “Too many connections,” increase this number.
Modern MySQL databases include the explain functionality. If you use this expression at the start of a query, the query gets read and evaluated. In the event of inefficiencies in the expressions or some anomaly in the structure, EXPLAIN can help you to pinpoint them. You then tweak the query phrasing to eliminate performance drains and unnecessary table scans.
While an “inner join” is the go-to type to use, you should also use join, union, and distinct when applicable.. The outer join searches for extraneous data outside the pertinent columns. In some cases, the data may be needed, but it’s an unnecessary performance drain to the search for data that isn’t pertinent.
DISTINCT and UNION commands are sometimes involved in queries. Once again, use these if needed, but note that they require extra reading and sorting from the database. If they are not needed, you’re better off finding a more efficient command.
The SELECT … WHERE structure is commonly used in databases. They handle filtering, retrieving, and evaluating results. Creating a small index set for connected tables is a useful structure that allows the query to be pointed at the index in order to speed it up when it runs.
Avoid using functions in query predicates. For instance, if you were to create an UPPER notation (making a function), this will force you to operate within the SELECT operation. Working in this order doubles the workload of the query.
The same goes for using a wildcard — a wildcard, %, represents zero or more characters. For instance, the expression ‘%345’ includes all values ending with 345. While the % stands for any character appearing before that string, including the absence of any proceeding character. This forces the search to do a full table scan for any of these possibilities.
When using the SELECT * operation, keep in mind that when scanning the specific columns, you may need to improve the search query time as not all database columns are being scanned.
The ORDER BY expression arranges results by the selected column .This also works for sorting two columns simultaneously. However, both columns should be sorted homogeneously, in ascending or descending order. If you sort non-homogeneously it will slow performance. Add an index to this to speed up sorting.
You should have a good idea of how to tune your MySQL database for improved performance.
In all, consider automations, eliminating query processes unnecessarily, bottlenecks in software and hardware resources, and the EXPLAIN function for database evaluation.
Download Prefix for free today!
Stackify's APM tools are used by thousands of .NET, Java, PHP, Node.js, Python, & Ruby developers all over the world.
Explore Retrace's product features to learn more.
If you would like to be a guest contributor to the Stackify blog please reach out to [email protected]
