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();
?>
Deutsche Version: Slow-Queries in MySQL finden
Wenn eine Web-Applikation langsam ist liegt das oft nicht (oder zumindest nicht nur) an der Parse-Zeit. Oft ist die Datenbank-Geschwindigkeit ein kritischer Faktor. Die meisten Skript-Sprachen parsen nicht “intelligent” soll heißen so wie es für den User am besten wäre. Stattdessen wird der Code Zeile für Zeile abgearbeitet (wer würde auch anderes erwarten). Hat man nun eine Webseite die langsame MySQL-Queries enthält hat man oft das Gefühl die Seite lädt eine halbe Ewigkeit. Ein Schritt wäre z.B. die Teile mit Datenbank-Zugriff in extra Seiten auszulagern und mit Ajax nachzuladen. Die gefühlte Geschwindigkeit steigt dadurch oft erheblich, da der User bereits Bildchen sieht und links klicken kann, obwohl Teile der Webseite noch fehlen. Oft ist auch nicht die Datenbank schuld, sondern einfach schlecht geschriebene Querys. Nur, wie findet man die? Zum Glück bringt MySQL diese Option bereits mit. Mit dem Slow-Query-Log kann man Querys die länger als eine bestimmte Zeit brauchen mitloggen. Diese Funktion wird einfach über die my.cnf aktiviert:
vi /etc/mysql/my.cnf
........
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
...........
Wenn man diese Zeilen auskommentiert bekommt man ein schönes Log mit allen Queries die länger als 2 Sekunden dauern. Natürlich kann man die Zeitspanne beliebig verändern. Die Zeit wird in Sekunden angegeben und definiert ab wann MySQL einen Query als langsam bezeichnet. Ein Bonus ist die Option “log-queries-not-using-indexes” die genau das macht. Nämlich Queries loggen welche keinen Index benutzen. Dies ist praktisch um Queries zu finden die einen sogenannten “Full Table Scan” durchführen, also die gesamte Tabelle durchsuchen obwohl es vielleicht schneller wäre nur eine Spalte zu durchsuchen. Nicht in allen Fällen ist der Index von Vorteil, teilweise ist die Performance ohne Index besser, weil die Daten bei einem Full-Table-Scan schon in Puffern vorliegen und schneller genutzt werden können. Das muss man im konkreten Einzelfall praktisch ausprobieren. In einigen Situation bringt der Index aber einen großen Performance-Zuwachs. Hier Infos zum index:
http://dev.mysql.com/doc/refman/5.1-maria/de/mysql-indexes.html
Was macht man jetzt wenn die langsamen Queries identifiziert sind? Natürlich versucht man herauszufinden warum dies der Fall ist. Manchmal werden Tabellen im Query abgefragt, deren Inhalt an dieser Stell überhaupt nicht benötigt wird. Das passiert z.B. bei Copy&Paste durch Programmierer. In manchen Fällen ist es günstiger die Information einmal auszulesen und im RAM abzulegen. z.B. per memcache:
http://php.net/manual/de/book.memcache.php
Vielleicht reicht es auch die Informationen per Ajax nachzuladen, wie oben angesprochen.
Ein einfacher Online-Slow-Query Logger
Oft hat man das Problem, dass das Query-Log in einer produktiven Umgebung deaktiviert ist. Nun gibt es Probleme und man würde es gerne aktivieren, aber die Datenbank darf nicht neugestartet werden. Hierfür habe ich mir ein kleines Skript geschrieben, welches die Slow-Queries auf der Konsole ausgibt.
Aufruf:
Als einziges Argument wird die Zeit für die Slow-Query-Grenze in ganzen Sekunden angegeben
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();
?>