Find slow-queries in MySQL

Often when a web-application seems slow it’s not (or not only) the parse time, but also or mayorly database speed. Most scripting-languages don’t act “intelligent” in parsing as it would be best for user experience, but go through the code line by line (whom would expect it otherwise?). So if you have a website containing some slow MySQL-Queries it seems as if all the page just takes a eternity to load. One step would be to put all the page loading into some seperate code-piece and load it later with ajax-technology. This gives a great boost in user-experienced speed, because the most parts of the website load and are already usable before the database content pops in. But otherwise often not the database is the problem, but bad written querys. But how to find them? Luckily mysql already provides us with a feature called slow-query-log which can be activated easily through my.cnf. So just look for this:

vi /etc/mysql/my.cnf
........
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
...........

uncomment these lines and you will have a nice log showing which queries take longer than 2 seconds which is a quite good value. Of course you can set it to what you want. It’s the time in seconds it takes mysql to qualify a query as slow. A plus is the option “log-queries-not-using-indexes” which does exactly this. It’s good to find queries which do a full table scan, but would (perhaps) be more performant if they would use an index. Actually using an index not everytime is better. You have to test this practically. In some cases it is faster to do the full table scan (and have some of the result already in buffers). So just try-and-error. In some cases it gives a huge performance boost. Read this to get more information:
http://dev.mysql.com/doc/refman/5.1-maria/en/mysql-indexes.html
So what to do if you logged some queries which take long? Try to find why the queries are slow. Sometimes there are tables used in a SELECT which data isn’t needed at all at this point (often happens through copy&paste by developers) in other cases perhaps the information would be better retrieved once and stored in memory to be used again. See memcache in this case:
http://php.net/manual/en/book.memcache.php
Perhaps also the data isn’t important for displaying the inital page and can be loaded with ajax, as mentioned above.

A simple Online-Slow-Query Logger

Often in a production environment the slowquery-Log is disabled. Now there’s a problem, you want to enable it, but you can’t restart the database. That’s why I wrote a little php-script which connects to the database and shows slow-queries.
Command:
The only argument is the slow-query-threshold in seconds.

php slow-query-logger.php 2
<?php
 
if( $argc != 2) die("Wrong number of arguments, just give number of seconds");
 
$time = $argv[1];
 
$db = @new mysqli('localhost', 'root', 'somepw', 'somedb');
if (mysqli_connect_errno()) {
    die ('could not connect to database: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
 
while(1)
{
$sql = "SELECT * FROM information_schema.PROCESSLIST WHERE time >= ".$time." AND info IS NOT NULL";
$result = $db->query($sql);
if (!$result) {
    die ('query error: '.$db->error);
}
while($data=$result->fetch_assoc())
{
        echo implode(" ", $data)."\n";
}
sleep(1);
}
$db->close();
?>
pixelstats trackingpixel

Leave a comment

Your comment

Time limit is exhausted. Please reload the CAPTCHA.