© Scott Betts, 123RF

Datenbank-Administratoren des Schweizer Forschungs- und Bildungsnetzwerks Switch, das über 30000 Owncloud-User verwaltet, stießen immer öfter auf korrupte Daten. Sie starteten eine aufwändige Fehlersuche. Die gefundene Lösung steigerte zugleich die Performance um das Sechsfache.

Die Fehlersuche in komplexen Cloudinfrastrukturen gleicht schnell der Suche nach der Nadel im Heuhaufen. Dennoch kann sich der Aufwand lohnen, das zeigt ein Praxisbeispiel des Schweizer Bildungsnetzwerks Switch. Die Stiftung organisiert seit über 30 Jahren die produktive Verknüpfung der IT-Ressourcen aller Schweizer Hochschulen [1]. Zur Switch-Community zählen neben den Schweizer Universitäten und weiteren Institutionen aus dem Bildungs- oder Forschungssektor auch private Organisationen, zum Beispiel Finanzinstitute oder industrienahe Forschungseinrichtungen.

Wegen des hohen Vernetzungsgrads ist Switch auf eine leistungsfähige Cloudinfrastruktur angewiesen. Die Stiftung stellt für Zehntausende Nutzer unter anderem den Filesharingdienst Switch Drive bereit, der auf Owncloud aufbaut. Er ist zu 100 Prozent auf Schweizer Servern gehostet und wird vorwiegend für die Synchronisierung und das Teilen von Dokumenten genutzt (Abbildung 1).

Abbildung 1: Überblick über die Switch-Serverinfrastruktur.

Switch Drive ist im Kern ein Owncloud-Angebot unter der Switch-Marke, das 30000 User und etwa 105 Millionen Files verwaltet. Die Tabelle »oc_filecache« umfasst rund 100 Millionen Zeilen. Um diese hohe Zahl zu verwalten, werden sechs Maria-DB-Server [2] in einem Galera-Cluster [3] eingesetzt. Das Hosting erfolgt auf neun Apache-Servern (4-mal Sync, 4-mal Web, 1-mal Management). Als Schlüssel-Werte-Speicher setzt Switch Redis [4] ein. Zudem umfasst die Infrastruktur drei LDAP-Server, sechs NFS-Server (auf Basis von Ceph) und zwei HA-Proxy-Load-Balancer. Das Monitoring erfolgt über Graphite [5] und einen Stack aus Elasticsearch [6], Logstash [7] und Kibana ([8], ELK).

Alle Datenbankknoten bestehen aus separaten Docker-Containern [9], die ihrerseits in virtuellen Maschinen laufen, die Open Stack [10] bereitstellt. Was auf den ersten Blick übermäßig kompliziert erscheint, bietet den Admins viel Flexibilität: Container lassen sich leicht updaten und patchen und die Admins können schnell wieder zu einem vorherigen Stand zurückkehren, sollten sich überraschend Probleme ergeben. Die VMs von Open Stack wiederum lassen sich leicht zwischen Compute-Knoten hin und her schieben. Die leichten Performance-Einbußen, die dabei auftreten, macht die doppelte Flexibilität wett.

Um ein Multi-Master-Setup innerhalb der Datenbank nutzen zu können, zogen die Admins extra von PostgreSQL auf den Galera-Cluster um. Nun profitierten sie davon, dass die Funktion voll erhalten bleibt, wenn ein Server aus dem Cluster ausscheidet. Ein anderes System übernimmt dann automatisch seine Rolle und gibt sie auch wieder zurück, nachdem sich der ursprüngliche Server später zurückgemeldet hat.

Der Datenbankcluster löste aber nicht nur drängende Probleme, sondern schuf zugleich neue. So beobachteten die Admins massive Performance-Einbußen in der Owncloud-Instanz, wobei besonders die Diskrepanz zwischen Schreib- und Leselast auffiel. Um der Flut von Anfragen begegnen zu können, teilten die Admins die Zugriffe auf Knoten auf, die entweder nur lesen oder nur schreiben. Am Ende standen in dem Sechs-Knoten-Cluster einem Write-Node bis zu fünf Read-Nodes gegenüber.

Die Aufteilung der Anfragen auf die beiden Sorten von Datenbankknoten übernahm der Datenbank-Proxy Max Scale. Das hatte den zusätzlichen Nutzen, dass der Proxy Lese-Anfragen auch dann noch korrekt auf die Lese-Knoten verteilen konnte, wenn die nicht mehr vollzählig im Einsatz waren, etwa weil sich einige für Wartungsaufgaben abgemeldet hatten. Alle Schreibanfragen gelangen dabei zum Master-Server, während alle Reads an die Replicas gehen. Derzeit nutzt Switch Max Scale in der Version 1.4, da bei der neuen Version (2.1) Lizenzgebühren anfallen.

Erste Spuren

Die Administratoren vermuteten nun genau bei Max Scale eine Quelle ihrer Sorgen. Damit sich derartige Probleme besser eingrenzen lassen, stellt Max Scale eine ganze Reihe von Tools zur Verfügung, darunter etwa Max Scale Top Queries, das MySQL Performance Schema oder die beiden Tools Explain und Analyze.

MySQL Performance Schema enthält eine View MySQL Performance Schema Events Statements Summary By Digest, die eine Zusammenfassung der meistgenutzten Statements samt deren Laufzeit ausgibt. So erkennt der Admin zum Beispiel, welche Arten von Anweisungen der Server wie oft ausführt und welches die Zeitfresser sind. Darüber hinaus enthält das Performance-Schema viele weitere Tabellen mit mannigfaltigen Informationen, die sich auch zurücksetzen lassen. Allerdings sind die Performance-Tabellen bei Maria DB 10.0.12 standardmäßig deaktiviert. Wenn der Datenbank-Admin sie aktivieren will, fügt er die Zeile

performance_schema=on

in die »my.cnf«-Datei ein. Eine detaillierte Anleitung liefert [12].

Eine andere Möglichkeit bietet die Analyse mit Max Scale Top Queries. Das Tool offeriert ebenfalls einen Top-10-Summary-Filter für eine bestimmte Sitzung. Das Filtermodul überwacht dabei jede SQL-Anweisung, die den Filter durchläuft. Es misst die Dauer dieser Anweisung als die Zeit zwischen dem Senden der Anweisung und dem ersten Ergebnis, das zurückgegeben wird.

Diese Zeiten werden zusammen mit dem SQL-Text selbst zwischengespeichert und in einer nach den Ausführungszeiten der Abfrage sortierten Liste beim Schließen der Client-Sitzung in eine Datei geschrieben. Eine Analyse der Switch-Datenbank mit Max Scale Top Queries ergab das Resultat aus Listing 1.

01 [root@drive-mgmt1 sessions]# cat top10.1181
02 Top 10 longest running queries in session.
03 ==========================================
04 Time (sec) | Query
05 -------- --+-----------------------------------------------------------------
06      0.078 | SELECT `oc_share`.`id`, `item_type`, `item_source`, `item_target`,`oc_share`.`parent`, `share_type`, `share_with`, `uid_owner`,`file_source`, `path`, `file_target`, `oc_share`.`permissions`,`stime`, `expiration`, `token`, `storage`, `mail_send`,`oc_storages`.`id` AS `storage_id`, `oc_filecache`.`parent` as `file_parent` FROM `oc_share` INNER JOIN `oc_filecache` ON `file_source` = `oc_filecache`.`fileid` AND `file_target` IS NOT NULL INNER JOIN `oc_storages` ON `numeric_id` = `oc_filecache`.`storage` AND ((`share_type` in ('0', '2') AND `share_with` = 'username@switch.ch') ) AND `uid_owner` != 'username@switch.ch' ORDER BY `oc_share`.`id` ASC
07      0.040 | INSERT INTO `oc_preferences` (`userid`, `appid`, `configkey`, `configvalue`) VALUES('username@switch.ch', 'user_ldap', 'homePath', '')
08      0.018 | COMMIT
09      0.013 | START TRANSACTION
10      0.012 | SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum` FROM `oc_filecache` WHERE `storage` = '4231' AND `path_hash` =
11 [...]
12 -----------+-----------------------------------------------------------------
13 Session started Wed Aug 30 06:14:49 2017
14 Connection from localhost_from_socket
15 Username        owncloud
16
17 Total of 14349 statements executed.
18 Total statement execution time  13.645 seconds
19 Average statement execution time        0.001 seconds
20 Total connection time   79.461 seconds

Die ersten fünf ermittelten Abfragen bestanden also aus einem Select, je einem Insert, Commit, Start Transaction und einem weiteren Select. Zudem lieferte die Analyse Informationen darüber, wie viel Zeit die einzelnen Abfragen in Anspruch nahmen. Das Resultat war reproduzierbar. In der Folge konnte die in Listing 2 ersichtliche problematische Anfrage identifiziert werden, da deren Reaktionszeiten besonders auffällig waren.

01 SELECT `oc_share`.`id`, `item_type`, `item_source`, `item_target`,`oc_share`.`parent`, `share_type`,
`share_with`, `uid_owner`,`file_source`, `path`, `file_target`, `ocshare`.`permissions`, `stime`, `expiration`, `token`, `storage`, `mail_send`,`oc_storages`.`id` AS `storage_id`, `oc_filecache`.`parent` as `file_parent`
02 FROM `oc_share`
03 INNER JOIN `oc_filecache`
04 ON `file_source` = `oc_filecache`.`fileid`
05 AND `file_target` IS NOT NULL
06 INNER JOIN `oc_storages`
07 ON `numeric_id` = `oc_filecache`.`storage`
08 AND ((`share_type` in ('0', '2')
09 AND `share_with` = 'username@switch.ch') )
10 AND `uid_owner` != 'username@switch.ch'
11 ORDER BY `oc_share`.`id` ASC

Auf die Identifikation des Flaschenhalses folgte dessen Analyse. Dabei half vor allem das SQL-Tool »EXPLAIN«. Es liefert eine Schätzung des Datenbak-Optimizers, nach welchem Plan und zu welchen Kosten die Datenbank die Abfrage abarbeitet. Darüber hinaus kalkuliert »EXPLAIN EXTENDED«, welchen prozentualen Zeitanteil Tabellenzeilen verbrauchen, die sich mit einer bestimmten Bedingung ausfiltern lassen. Außerdem vermittelt das Tool Aufschluss darüber, wie viele Zwischenschritte innerhalb einer Datenbank nötig sind, um zu einem bestimmten Resultat zu gelangen.

Auch zeigt es an, ob eine Sortierung erfolgte, ob ein Filter zum Einsatz kommt (etwa eine Where-Bedingung) oder ob ein Index verwendet wurde. Um möglichst viele Informationen zu generieren, empfiehlt sich die Ausgabe im Json-Format (»EXPLAIN FORMAT=JSON«), das in Maria DB mehr Daten liefert als einfache MySQL-Explainstatements.

Hilfreich ist schließlich auch die »ANALYZE TABLE«-Funktion, mit der ein Index neu geordnet werden kann (im Unterschied zu »EXPLAIN«). Die Funktion berechnet die Statistiken neu, die der Optimizer für die Ausführung einer Abfrage verwendet. Der Befehl ist bei jeder Abfrage möglich, bei der auch »EXPLAIN« benutzbar ist. »ANALYZE« startet den Optimizer, führt die eigentliche Abfrage aus und gibt auf Basis dieser Abfrage die entsprechenden Ergebnisse zurück – langsamer als »EXPLAIN«, dafür aber mit detaillierteren Informationen darüber, was mit den Daten passiert.

Gerade wenn es ein schwerwiegendes Problem mit der Datenbank gibt, dessen Ursache möglicherweise im Optimizer liegt, lässt sich dies am besten mit einem weiteren Statement lokalisieren: »EXPLAIN ANALYZE«.

Die Analyse

Das Ergebnis war – im Falle der Switch-Datenbank – deutlich. Die Analyse ergab, dass die Switch-Datenbank jedes Mal die komplette »oc_share«-Tabelle – also 70305 Zeilen – scannt. Anschließend filterte die Datenbank die Ausgabe der Ergebnisse in eine »JOIN«-Tabelle, in der sie eine »WHERE«-Klausel aufrief. Diese Abfrage stellt gleichzeitig die Top-Abfrage dar, wie sie in diesem Fall Max Scale aufgezeigt hatte.

Bei einer gelegentlich ausgeführten Query wäre das kein Problem. Doch hier handelte es sich um eine der häufigsten Anfragen, die Ausführung dieses Scans dauerte jedes Mal 0,078 Sekunden. Diese Zeiten summierte sich, was schlussendlich zu einer hohen Last auf den Datenbankservern führte.

Bei der weiteren Untersuchung stießen die Admins auf einen verblüffenden Effekt: Würde diese Abfrage ohne einen Index durchgeführt, wären die Responsezeiten deutlich niedriger. Normalerweise ist es genau umgekehrt, ein Index beschleunigt den Zugriff gerade im Vergleich mit einem Full Table Scan. Das Problem liegt in diesem Fall aber in der Verwendung der »WHERE«-Klausel begründet. Sie enthält einen Wert, der nicht im Index selbst, sondern in den Rohdaten zu finden ist. Durch die »ORDER BY«-Klausel wird der Primary Key verwendet, um die gewünschte Reihenfolge zu erhalten. Dabei lassen sich keine sequenziellen Lesevorgänge mehr nutzen und zufällig verteilte Lesezugriffe auf die Rohdaten führen zu schlechterer Performance als bei einem Full-Table-Scan.

Indexe untersuchen

Um nachzuvollziehen, welche Probleme ein falscher Index in einer Datenbank verursachen kann, hilft es, sich dessen Rolle zu vergegenwärtigen: Im Grunde genommen handelt es sich bei einem Index um einen Key Value Store, der eine oder mehrere Spalten als Schlüssel verwendet, um Zeilen in einer Datenbanktabelle abzurufen. Indexe fungieren gewissermaßen als Abkürzungen innerhalb einer Datenbank. Dabei gilt: Einerseits kann ein fehlender Index eine Datenbank deutlich verlangsamen, andererseits verbrauchen aber auch ungenutzte oder selten genutzte Indexe Ressourcen. Beim Hinzufügen eines Index, wird eine Tabelle darüber hinaus während dieses Vorgangs gesperrt.

Indexe helfen auch, wo Tabellen miteinander zu verbinden sind. Normalerweise geschieht das auf Grundlage gemeinsamer Daten in einer Spalte. Auch hier sorgen Indexe dafür, dass der Vorgang möglichst schnell und effizient abläuft. Auch ist es ratsam, verwendete Filter ebenfalls mit Indexen zu versehen.

Auf der anderen Seite gilt: Eine falsch indexierte Verbindung kann im schlechtesten Fall zu einem kartesischen Produkt führen, das dann einen kompletten Datensatz aller möglichen Kombinationen von Daten in beiden Tabellen zurückgibt. Anschließend darauf angewendete Filter können dieses Ergebnis zwar korrekt verarbeiten, doch brauchen sie dafür ein Vielfaches der Ressourcen, die bei korrekter Indexierung nötig wäre.

Ebenso wie ein falscher kann – wie gesagt – auch ein fehlender Index Performanceprobleme hervorrufen, und auch diesen Fall konnten die Admins bei Switch beobachten. Dabei wurde eine riesige Anzahl von Werten zurückgegeben, gerade weil ein Index fehlte. Nachdem dieser Index angelegt war, gab die Datenbank nur noch genau eine Zeile statt zuvor 70305 Zeilen zurück.

Was die neue Situation nach der Fehlerbereinigung konkret für den Ressourcenverbrauch und die Performance bedeutet, zeigt die Grafana-Analyse in den Abbildungen 2 und 3.

Abbildung 2: Grafana-Chart mit der durchschnittlichen Antwortzeit nach Beseitigung des Fehlers.

Abbildung 3: Die CPU-Auslastung ging nach dem Fix von 1200 auf 200 Prozent zurück.

Die durchschnittliche Antwortzeit der Read-Nodes ging von 3 auf etwa 0,15 Millisekunden zurück. Die CPU-Auslastung der Datenbank sank dabei von 1200 auf 200 Prozent. Die Datenbankinfrastruktur war nach der Maßnahme deutlich stabiler. Auch die Cloud-Cronjobs liefen danach etwas schneller. Switch konnte die Anzahl der Datenbankserver auf drei reduzieren, die immer noch deutlich weniger Ressourcen als zuvor verbrauchen, mit einer Antwortzeit des Datenbank-Read-Node von zirka 0,2 Millisekunden.

Fazit

Die Quintessenz nach der Fehlerbehebung: Performance-Probleme bei Datenbanken können zuweilen gravierend sein und zugleich auf sehr einfache Fehler in der Infrastruktur zurückgehen. Eine Schlüsselrolle bei der Fehlersuche spielen die eingesetzten Tools.

Zu bedenken ist auch, dass ein MySQL-Proxy wie Max Scale eine Abfrage nicht immer so ausführt, wie es Administratoren eigentlich vorgesehen haben. Bei Datenbank-Proxys, die als Vermittlungsinstanz zwischen Datenbank und Client dienen, hilft es, wie der eingesetzte Proxy (hier Max Scale) zu denken und dieser Logik zu folgen. Das gilt auch, weil Abfragen, die das eigentliche Problem verursachen, nicht immer im Slow Query Log auftauchen. Womöglich sind es selbst gar keine echten Langläufer, stattdessen mag es sich nur um kleine Ineffizienzen handeln, die sich aber zu großen Verzögerungen summieren.