mySQL Performance untersuchen

diesmal habe ich ein etwas spezielleres Thema: Wie kann ich mit den Boardmitteln von mySQL und mit freien Tools die Performance einer mySQL Datenbank untersuchen?

Ich habe dies anlässlich eines Performanceproblems eines nicht kommerziellen rege genutzten Internetforums getan und will hier kurz zeigen, was nötig war bzw.  wie ich die wichtigsten Informationen gewonnen habe.

In dem zu untersuchenden Fall kam es beim Speichern von Beiträgen teilweise zu erheblich langsamen Antwortzeiten. Verwender wird die phpBB Board Software, welche eine mySQL Datenbank nutzt. (s. Artikel http://www.sanchofock.de/?p=69)

Vorausgegangene Performancetests hatten das Problem bereits bestätigt und reproduzierbar gemacht. Hierauf konnte ich also gut zurückgreifen.

Um zu überprüfen, ob diese langen Wartezeiten auf der Datenbank entstehen habe ich zuerst den phpMyAdmin für die mySQL Datenbank verwendet, welcher bei diesem Forum bereits installiert war.

Der phpMyAdmin bietet einen guten ersten Überblick über die Datenbank, ihre Struktur und auch grundlegende Performance Eigenschaften.

Tabellen, Indizes, Trigger, stored procedures und Views können hier ebenso per UI übersichtlich angesehen werden, wie SQL Abfragen ausgeführt werden.

Um sich den Performance Status anzusehen wechselt man von der aktuellen Datenbank auf die Server Ansicht:

Und dann Auf die Status Seite:

Unter „Server“ finden sich einige allgemeine Basis Informationen, unter „Prozesse“ die Liste der aktiven Client Prozesse, unter „Statistiken“ die allgemeinen Abfrage Statistiken, unter „Status Variablen“ eine Menge aktueller Status Variablen und deren Inhalt, unter „Überwachung“ kann man ein Basis Monitoring aktivieren und schliesslich ist da noch der „Ratgeber“.

Die ersten beiden Reiter sind interessant, enthalten jedoch nur Informationen, die so allgemein sind, dass sie für mein Problem hier nicht weitergeholfen haben,

Zu den Status Variablen komme ich später noch. Die Überwachung sieht so aus:

Leider sind diese Daten nicht historisierbar und je nach gewählter Granularität entweder nur für einen kurzen Zeitraum oder aber sehr grob.

Aber immerhin gab dieses einen ersten Überblick und ich konnte feststellen, dass keine grundlegenden Probleme mit den Kernressourcen (CPU/Speicher) vorlagen. Auch unter einem Lasttest zeigten diese wie auch der Netzwerk traffic keine ungewöhnlichen oder kritischen Muster.

Der Ratgeber gibt auch noch mal interessante Hinweise, die sich auf die Konfiguration unter Berücksichtigung der aktuellen Nutzungsstatistiken und das Applikationsverhalten ergeben. Für Hobby DBAs wie mich und Entwickler also durchaus eine einfache Möglichkeit gute Tipps zu bekommen.

In unserem Fall fanden sich jedoch keine Hinweise, die bei diesem Problem helfen konnten.

Wenn man den phpMyAdmin nicht zur Verfügung hat kann man an der Konsole auch das Script mysqltuner.pl verwenden.

Es kann ganz einfach in zwei Schritten ausgeführt werden.

Zuerst muss die aktuelle Version mit wget heruntergeladen werden:

server:~# wget http://mysqltuner.pl

Alternativ kann mysqltuner unter Ubuntu und Debian auch direkt aus dem Repository installiert werden:

server:~# apt-get install mysqltuner

Danach wird der MySQL-Tuner gestartet, wobei eventuell MySQL Login Daten eingegeben werden müssen.

server:~# perl mysqltuner.pl

Aber alles das half nun nicht um die Datenbank als Ursache für das darüber liegende Problem auszuschließen oder fest zu machen.

Die wichtigste Frage, die sich also stellte: Gibt es lang laufende Abfragen an dieser Datenbank und wenn ja stehen diese im Zusammenhang mit den schlechten Antwortzeiten?

mySQL bietet von Haus aus die Möglichkeit, lang laufende Queries zu protokollieren. Hierfür kommen wir mit dem phpMyAdmin aber nur bedingt weiter, es sei denn unser Datenbankuser hat „super“ Rechte.

Um diese Protokollierung zu aktivieren müssen ein paar globale Server Variablen geändert werden. Für einige benötigt man die „super“ Rechte.

Wenn man diese nicht hat muss man sich entweder als root an der mySQL Konsole anmelden und diese Rechte erteilen oder direkt die Variablen ändern.

Ist einem das mySQL root Passwort nicht (mehr) bekannt, dann hilft dieser Artikel weiter: https://www.yourhelpcenter.de/2010/07/mysql-vergessenes-root-passwort-zurucksetzen/

Um das Logging zu aktivieren muss die Variable SLOW_QUERY_LOG auf ON bzw 1 gesetzte werden.

Die Variable LONG_QUERY_TIME gibt an ab welcher Laufzeit die Abfragen geloggt warden sollen. 0 Bedeutet gar nicht und 10 (Sekunden ist das Maximum.

Nach der Aktivierung legt der Server ein entsprechendes Log File an. Der Ort wird in der Variable SLOW_QUERY_LOG_FILE angegeben. Macht man das nicht selbst wählt der Server das data Verzeichnis der Datenbank. Weitere Details zur Konfiguration und zur Verwendung findet man unter https://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

Ich habe die Zeit auf 1 Sekunde eingestellt und den Performancetest ausgeführt in dem es wieder zu langsamen Antworten kam. Doch das Log blieb leer. Um zu prüfen ob es wirklich funktioniert habe ich dann noch die Variable LOG_QUERYS_NOT_USING_INDEXES aktiviert und siehe da das Log füllte sich. Je nach Einstellung und Performance kann das Log schnell gross werden und produziert Einträge wie diesen hier:

# Time: 180110 13:39:26
# User@Host: phpbb_1[phpbb_1] @ localhost []
# Query_time: 0.000137  Lock_time: 0.000026 Rows_sent: 0  Rows_examined: 34
SET timestamp=1515587966;
SELECT ban_userid
FROM phpbb_banlist
WHERE ban_userid IN (130, 171, 160, 232, 96, 230, 97, 139, 262, 48, 390)
AND ban_exclude <> 1 AND ban_end = 0;

Um eine bessere Übersicht zu bekommen habe ich ein freies Tool verwendet. Es sind die PT-Tools. Von Percona. Zu finden unter https://www.percona.com/software/database-tools/percona-toolkit

Hilfreich für meine Fragestellung war konkret das Tool pt-query-digest

Die Auswertung damit hat deutlich ergeben, dass es am Datenbank Server keine lang laufenden Abfragen gab. Die Datenbank war also als Verdächtiger ausgeschlossen. Die Fehlersuche musste also an einer anderen Stelle weitergehen.

Wären hier langsame Queries aufgefallen, so hätte man das selbe Tool auch nutzen können um die Ursachen herauszufinden. Hier zum Schluss noch die Ausgabe als Anschauung:

1. Zusammenfassung:

2. Profiling Übersicht

3. Je Query eine detaillierte Auswertung