SQL-Datenbank-Tuning: Wie Sie die SQL-Performance optimieren

Aber bitte beachten: Eine Webanwendung ist wie ein Team, dessen Performance von der Leistungsfähigkeit verschiedener Team-Mitglieder abhängt: Webserver inkl. Hardware und Betriebssystem, SQL-Datenbank und PHP-Skripte, meist in Form eines CMS wie WordPress oder Typo3. Wenn Sie möchten, dass Ihr Team gewinnt, sollten Sie allen Mitgliedern etwas Aufmerksamkeit schenken. In unserem Blog finden Sie auch Beiträge zum übergeordneten Thema Performance-Optimierung sowie zu den Themen Server-Performance und Typo3-Caching.

Design oder Konfiguration?

Ist Ihre Datenbank zu langsam, kann das viele Gründe haben. Es ist hilfreich, zwei Fälle zu unterscheiden. Wenn einzelne Abfragen zu viel Zeit brauchen, haben Sie ein Design-Problem, das heißt Optimierungsbedarf bei Tabellen oder Queries. Ist die Datenbank insgesamt zu langsam (und es liegt nicht an der Hardware), dann könnten Konfigurationsänderungen etwas bringen. Der erste Fall, das Design-Problem, ist eine Aufgabe für den Webentwickler, während die Administration beim Admin liegt. Beide Bereiche sind komplex, sodass wir das Thema hier nicht erschöpfend behandeln, sondern nur auf einige wichtige Punkte eingehen können.

Hintergrund: SQL-Datenbanken

Als SQL-Datenbanksysteme (SQL-DBMS) werden relationale Datenbanksysteme (RDBMS) bezeichnet, die üblicherweise über eine SQL-Schnittstelle, also mit Befehlen der Datenbanksprache SQL (Structured Query Language), verwaltet werden. Bekannte SQL-Datenbanken sind etwa Microsoft SQL Server, Oracle Database, IBM DB2, PostGreSQL und natürlich MySQL und dessen Fork MariaDB.

Relationale Datenbanksysteme organisieren Daten in Tabellen (Spalten und Zeilen), zwischen denen „Relationen“ definiert werden. Beispiel: Eine Tabelle enthält aktuelle „Projekte“ eines Unternehmens, eine weitere Tabelle alle „Kunden“. Da zu einem Kunden mehrere Projekte existieren können, besteht eine 1:n-Beziehung zwischen diesen Tabellen.

Jede standardkonforme SQL-Implementierung muss einen Teil der im Standard (ISO/IEC 9075:2016) definierten Funktionen (Core-Features) unterstützen; die anderen sind optional. Deshalb beschreiben wir das Vorgehen bei unseren Optimierungstipps am Beispiel von MySQL (bzw. seiner Standard-Storage-Engine InnoDB), das bei Webanwendungen zu den populärsten Datenbanksystemen gehört.

Workload verstehen

Wer wissen will, muss messen – zumindest wenn es darum geht zu verstehen, was der Datenbank-Server so zu tun hat. Für das Beobachten von Verhalten und Performance der Datenbank bringt MySQL schon ein paar nützliche Werkzeuge mit.

Eines davon ist die SHOW-Anweisung, mit der Sie eine Vielzahl von Infos abrufen können. Mit dem Befehl SHOW STATUS können Sie sich beispielsweise Statistiken und Zähler anzeigen lassen. SHOW VARIABLES zeigt Ihnen die aktuellen Variableneinstellungen (so prüfen Sie auch, ob Konfigurationsänderungen übernommen wurden) und SHOW FULL PROCESSLIST listet sämtliche Prozesse (parallele Verbindungen, einschließlich Nutzern und ausgeführter Kommandos).

Flaschenhälse finden

Nach langsamen Abfragen können Sie fahnden, indem Sie dafür das Logging aktivieren. Um beispielsweise alle Abfragen zu protokollieren, die länger als zehn Sekunden benötigen, tragen Sie das Folgende in Ihre MySQL-Konfigurationsdatei (my.cnf) ein (Neustart nicht vergessen):

long_query_time  =  10

log-slow-queries = /var/log/mysql-slow.log

Mit dem SHOW-Befehl lassen Sie sich die geloggten Abfragen anzeigen.

mysql> SHOW STATUS LIKE '%slow_queries%';

Bitte beachten Sie: Dass eine konkrete Anfrage langsam ausgeführt wird, kann viele Gründe haben. Erst wenn das häufiger passiert, ist es ein Hinweis auf Probleme. Mit dem mitgelieferten Perl-Skript mysqldumpslow können Sie genau das sehen.

Diverse Open-Source-Werkzeuge helfen ebenfalls bei der Analyse. Empfehlenswert ist mytop (ein MySQL-Klon von top für Unix). Das Tool zeigt viele Infos zu Abfragen, aber auch  zu den laufenden Prozessen. Noch leistungsfähiger ist innotop für MySQLs und MariaDBs InnoDB-Engine. Einen Blick wert ist auch das Percona Toolkit, hier insbesondere pt-query-digest. Wenn Sie eine Abfrage als möglichen Flaschenhals identifiziert haben, können Sie diesen mit dem MySQL-Befehl EXPLAIN genauer inspizieren.

Eine Frage des Designs: Indizes

Zu den effizientesten Optimierungsmaßnahmen gehört die Nutzung von Index-Strukturen. Denn weil die Nutzdaten in relationalen Datenbanken unsortiert gespeichert werden, müssen bei Abfragen die entsprechenden Tabellen nacheinander durchsucht werden, was sehr lange dauern kann.

Indizes fungieren (ähnlich wie die Schlagwort- und Autorenindizes in einem Buch) als Suchanweisungen und können damit Abfragen extrem beschleunigen. Weil das so ist, bringt InnoDB für jede Tabelle schon einen Index mit, einen sogenannten Clustered Index. Das ist der von Ihnen oder von MySQL festgelegte Primärschlüssel (Primary Key). Spalten, die in wichtigen oder zeitkritischen Abfragen genutzt werden und eindeutige Werte enthalten, sollten Sie diesem Primärschlüssel bzw. Primärindex hinzufügen. Falls benötigt, können Sie per CREATE INDEX und DROP INDEX flexibel weitere sekundäre Indizes anlegen bzw. löschen.

Aber Achtung: Primärindizes beschleunigen die Suche, aber verlangsamen viele Schreiboperationen. Außerdem werden sie in sekundären Indizes mitgespeichert und binden Ressourcen. Sehr viel mehr Informationen zum Thema Indizes finden Sie hier.

MySQL optimal konfigurieren: Caching

MySQL kennt zahllose Variablen, die per Konfigurationsdatei gesetzt werden können (allein über 160 Variablen für InnoDB). Allerdings wird der Anteil der Konfiguration an den insgesamt erzielbaren Performancesteigerungen häufig überschätzt. Zudem sind viele Tipps, die Sie so im Internet finden, nicht mehr aktuell. Zum Beispiel wird der häufig genannte Query Cache seit MySQL 8.0 nicht mehr unterstützt und war wegen diverser Einschränkungen schon seit 2013 (MySQL 5.6) im Standard deaktiviert.

Und sicher haben Sie auch schon den Tipp gelesen, über key_buffer_size den Cache für Indizes zu beeinflussen. Allerdings ist dieser Parameter für MyISAM bestimmt, die Standard-Storage-Engine bis MySQL 5.1 (Systemtabellen bis MySQL 8.0). Falls Sie nicht explizit die MyISAM für Ihre Tabellen nutzen, bringt Ihnen dieser Parameter nichts.

Ein vergleichbarer Parameter für InnoDB ist innodb_buffer_pool_size. Der Buffer-Pool cacht sowohl Indizes (siehe unten) als auch Daten aus Tabellen. Das Caching – das Vorhalten benötigter Daten im Arbeitsspeicher – verringert die Zahl nötiger Leseoperationen von der Festplatte.

Die angemessene Größe des Buffer-Pools hängt von der Größe des Arbeitsspeichers ab, der tatsächlich für MySQL zur Verfügung steht. Den nutzen aber auch Queries, Query-Cache, MySQL-Connections und Replikationen. In der Regel ist der Defaultwert nicht die schlechteste Wahl. Wollen Sie andere Werte testen, stellen Sie sicher, dass genügend Speicher für die Standardaufgaben Ihres Servers bei zu erwartenden Produktivlasten zur Verfügung steht und erlauben Sie so viel Buffer-Cache, dass nicht geswappt werden muss. Moderne Versionen verlangen dafür keinen Neustart mehr; Sie können den Wert dynamisch setzen:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Tipp: Achten Sie darauf, dass die gesetzten Werte zu denen für innodb_buffer_instances und innodb_buffer_chunk_size passen müssen (Näheres hier).

Weitere Maßnahmen

Es gibt weitere Maßnahmen, die bei Problemen eventuell Abhilfe schaffen. Beispielsweise optimiert OPTIMIZE TABLE die physische Speicherung von Tabellen- und Indexdaten, reduziert den benötigten Speicherplatz und kann den Zugriff beschleunigen. Nutzen Sie das Statement, wenn Ihre Tabellen eine stabile Größe erreicht haben oder stark gewachsen sind. Einen Überblick über die zahlreichen Optimierungsmöglichkeiten von MySQL bietet ein umfangreiches Kapitel im Reference Manual – lassen Sie sich inspirieren!

Tipp: Ändern Sie immer nur je einen Parameter und testen Sie dann mit realistischen Datensätzen den erzielten Performance-Effekt.