Isolate Slow MySQL Processes

Last Updated on: October 11, 2022

Even the best MySQL implementation will sometimes have problems with processes taking too long and hogging the CPU and memory.

This will have a knock-on effect on any other processes running on the server and can have a significant effect on performance. In an ideal world, you could just increase the resources available to the server.

For example, faster CPUs or more memory will resolve the problem, but often budget constraints mean that you are stuck with the same hardware configuration.

If you are experiencing problems with performance, then first obtain a baseline report with mysqlreport. Run MySQL for a day first, and then run the following command:

mysqlreport –all

Look through the log file and pay particular attention to these fields:

• Read Ratio
• Slow
• Waited

If the value in “Read Ratio” is over 0.01, it indicates insufficient RAM for MySQL to use. If possible, increase the amount of RAM to stop the system from using a swap file.

The last value in the Slow field indicates the number of slow queries and should ideally be less than 0.05%.

The last column in the Waited field shows the number of table locks that had to wait to acquire a lock. A value above 10% usually indicates an issue with slow queries.

One way to increase performance is to target the slowest MySQL queries so that you isolate them and then work to improve them. You can do this by configuring MySQL to log slow queries with the following amendment to /etc/my.cnf:

[msyqld]
log-slow-queries
long_query_time = 1

Once you restart MySQL, it will log any queries that take longer than one second to execute instead of the default of ten seconds. Wait one day and then examine the slow queries log file, slow_queries.log, and then run mysqlsla on the file to produce a list of the worst offenders like this:

mysql> mysqlsla –log-type slow /var/lib/mysql/slow_queries.log

Once you have the list of slow queries, you can rewrite these queries to improve performance. After completing this process, re-run the baseline collection with mysqlreport again, and check if the reported values have improved.

Over time, your databases will grow, and this will eventually affect MySQL performance. Expect to run this process regularly to keep your database performance at optimum levels.

---

---