Isolate Slow MySQL Processes

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 major 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:

{code type=php}
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 that there is insufficient RAM for MySQL to use. If possible, increase the amount of RAM to stop the system 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:

{code type=php}
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:

{code type=php}
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 on a regular basis to keep your database performance at optimum levels.


Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,


If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the