Like all other databases, MySQL can be complicated and can stop at a moment’s notice putting all your businesses and tasks on the line. However, common mistakes underlie most of the problems affecting the performance.
To ensure your server operates efficiently and effectively by providing stable and consistent performance, you must eliminate the mistakes often caused by some subtlety in the workload or configuration trap.
As data volume grows, it becomes increasingly complex. Therefore, it is essential to optimize the databases well to deliver an efficient end-user experience. MySQL performance tuning is the ultimate solution as it will help provide solutions to these database problems.
MySQL Performance Tuning
In this article, you will find some helpful tips on how to use MySQL performance tuning. This will help you get the best performance out of your MySQL.
Step 1: Don’t use MySQL as a queue
Without your realization, queue and queue-like patterns can sneak into your application. A typical example is marking emails as unsent, sending them, and then marking them as sent. this is a common but rarely unnoticeable problem that most users tend to ignore.
They cause two major performance complexities:
- They serialize your workload, thus preventing tasks from being completed in parallel sequence. In addition, they often result in a table that contains work in process and historical data from jobs that were processed a long time ago. This generally slows down the processing speed and process.
- Both add latency to the application and load to MySQL.
Step 2: Profile your workload
Profiling your workload is essential as it helps you understand how your server works and the time it spends processing tasks. The best tool to help you do this is MySQL Enterprise Monitors Query analyzer from the Percona toolkit.
Percona toolkit webpage
Note: Only available for Linux users
The tools can capture the queries executed by the server and return a table of sorted tasks in decreasing the order of response time.
Profiling your workload exposes the most expensive queries for further tuning. Time is most critical because what is of importance is how quickly it completes when issuing a query.
The profiling tools also group similar queries, which allows you to see the slow queries and the fast ones but are executed many times.
Step 3: Understanding the Four Fundamental Resources
The CPU, memory, disk, and network are the four fundamental resources needed for a database to function. Therefore, the database is likely to perform poorly if any of these resources are overloaded, weak, or erratic.
You should always ensure that all the mentioned four resources are strong and stable for MySQL to perform flawlessly. Organizations usually choose those servers with fast CPUs and disks that may accommodate more memory slots.
Adding memory is a cheap and easy way of increasing performance by orders of magnitude, especially on disk-bound workloads. This might seem unreasonable, but many disks are overutilized as there is not enough memory to hold the server’s working set of data.
When troubleshooting, check the performance and utilization of all four resources carefully to help determine the performance stats of the four resources. Monitoring their performance is important since it helps the user know what should be improved or needs replacement. You can try out this method since it is one of the quickest methods to solve performance issues in MYSQL.
Step 4: Filter results by the cheapest first
An excellent way for optimization is to do the cheap, imprecise work first, then the hard, precise work on the smaller, resulting in the data set.
Example:
Suppose you are looking for something within a given radius of a geographical point. The first tool in my programmer’s toolbox is the Haversine {Great circle} formula for computing distance along the surface of a sphere.
The problem with the technique is that the formula requires many trigonometric operations, which are very CPU sensitive. As a result, the calculations tend to run slowly and make the machine’s CPU utilization skyrocket.
Before using the formula, pare down your records to a small subset of the total and trim the resulting set to a precise circle. Square that contains the circle, whether precisely or imprecisely, is an easy way to do this. This ensures the world outside the square never gets hit with all those costly trigonometric functions.
Step 5: Knowing and understanding the two scalability death traps.
Scalability may not be as vague as many believe. Instead, there are precise mathematical definitions of scalability expressed as equations that highlight why systems don’t scale as well as they should.
The Universal Scalability Law is a definition that is handy in expressing and quantifying systems’ scalability characteristics. It explains scaling problems in terms of serialization and crosstalk, which are the two fundamental costs.
Parallel processes that must stop for something serialized to take place are inherently limited in their scalability. Furthermore, if parallel processes need to communicate with each other to coordinate their work, they limit each other. Therefore, it’s preferred to avoid serialization and crosstalk to enable your application to scale quickly and efficiently.
Step 6: Do not focus too much on configuration
People spend too much time tweaking configurations. The result is usually not a significant improvement and can sometimes be very damaging. The default settings that ship with MySQL is one size fits none and badly outdated it you do not need to configure anything.
So, it is essential to get the basics right and change settings only if needed. In numerous cases, server tuning tools aren’t recommended because they might mislead users with contradicting information. Some have dangerous, inaccurate advice coded into them like cache hit ratios and memory consumption formulas.
Step 7: Watch out for Pagination Queries
Applications that paginate usually bring the server to its knees. Optimizations can often be found in the other user interface itself. For example, instead of showing the exact number of pages in the results and links, you can show just a link to a page that contains that information. Thus, you can prevent people from overloading the original page.
On the query side, instead of using offset with limit, one more row can be selected, and when you click “next page,” you can designate that final row as the starting point for the next set of results.
Step 8: Save the statistics eagerly, alert reluctantly
Alerting and monitoring are essential but what happens to the typical monitoring system is that it starts sending false positives. Systems administrators set up email filtering rules to stop the noise, and soon your monitoring system becomes useless.
It is important to capture and save all the metrics you possibly can because you will be glad to have them when you try to figure out what changed in the system. Also, when a strange problem crops up, you will be able to point to a graph and easily trace a change in the server workload.
People usually alert on things like the buffer hit ratio or the number of temporary tables created per second. The problem is that there isn’t any reasonable threshold for such a ratio. Moreover, the suitable threshold is different among servers and from time to time as your work changes.
As a result, alert sparingly and only on conditions that indicate a definite, actionable problem. For example, a low buffer hit ratio is not actionable, nor does it indicate a real issue, but a server that doesn’t respond to a connection attempt is an actual problem that needs to be solved.
Step 9: Learn the three rules of indexing
This is the most misunderstood topic in databases because there are many ways to learn about how indexes work and how the server uses them. Indexes, if properly designed, serve three important purposes in a database server;
- Instead of single rows, indexes let the server find groups of adjacent rows. Many people think the purpose of indexes is to find individual rows, but finding single rows leads to random disk operations, rendering the server very slow. Finding groups of rows is much better and interesting than finding rows one at a time.
- It also lets the server avoid sorting by reading the rows in the desired order. Reading rows, unlike sorting, is much faster and less costly.
- Indexes also let the server satisfy entire queries from the index alone, avoiding the need to access the tablet at all. This is variously known as covey index or an index-only query.
Step 10: Leverage the expertise of your peers
Would you mind not doing it alone? Puzzling over problems and doing what seems logical and sensible to you might work most but not all the time. So instead, build a network of MySQL-related resources going beyond toolsets and troubleshooting guides.
People are incredibly knowledgeable lurking in mailing lists, forums, and so on. In addition, conferences, tradeshows, and local user group events provide valuable opportunities for getting insights and building relationships with peers who can help you.
For the few looking for tools to complement these tips, you can check out the Percona Configuration Wizard for MySQL and MySQL Percona monitoring plugins.
Percona plugins webpage
The configuration wizard can assist you in generating a baseline. my.cnf file for a new server superior to the sample files that ship with the server.
The query advisor that’s superior to the sample files that ship with the server. The advisor will analyze your SQL to help detect potentially destructive patterns such as pagination queries (tip 7).
Percona monitoring plugins are sets of monitoring and graphing plugins that help you save statistics eagerly and alert reluctantly (Step no 8). All these tools are freely available.
Benefits of performance tuning
The primary benefit is, it allows you to avoid over-provisioning and reducing costs by right-sizing your services. It also gives you insights into whether moving data storage or adding server capacity will improve performance or not, and if so, how much it will be.
Once a database is tuned correctly, it gives beneficial performance results with great functionalities. It not only lowers unwanted task load but also optimizes the MySQL database for faster data retrieval.
Other settings can make a difference depending on your workload or hardware. The goal is to give you a few MySQL performance tuning to quickly get a sane MySQL configuration without spending too much time changing non-basic settings or reading documentation to understand which settings matter to you.
Conclusion
In conclusion, performance tuning offers many benefits, and it is recommended when working large tracts of data to improve the efficiency of your server. By following the tips given in this article, you will be able to perform MySQL performance tuning on your server and databases comfortably.
