Für die meisten Anwendungsentwickler ist die Datenbank ein Altar der dämonischen Götter, den man am besten nicht anrührt. Aber das muss nicht so sein!

Je nachdem, wie gut ein Entwickler mit der zugrunde liegenden Datenbank vertraut ist, desto höher ist sein Dienstgrad. Wenig Datenbank und wenig Programmiererfahrung = Junior-Entwickler; wenig Datenbank und gute Programmiererfahrung = Mid-Level-Entwickler; gute Datenbank und gute Programmiererfahrung = Senior-Entwickler.

Es ist eine harte Realität, dass selbst Entwickler mit 6-8 Jahren Erfahrung Schwierigkeiten haben, die Feinheiten des Abfrageoptimierers zu erklären und lieber in den Himmel schauen, wenn sie nach dem Datenbanktuning gefragt werden.

Und warum?

Überraschenderweise ist der Grund dafür nicht Faulheit (auch wenn es zum Teil so ist).

Der Grund ist, dass Datenbanken eine ganz eigene Kraft sind, mit der man sich auseinandersetzen muss. Selbst in der Vergangenheit, als es nur relationale Datenbanken gab, war deren Beherrschung ein Wunder und ein eigener Karriereweg. Heutzutage gibt es so viele Arten von Datenbanken, dass es unmöglich ist, von einer einzigen sterblichen Seele zu erwarten, alles zu beherrschen.

Abgesehen davon ist die Wahrscheinlichkeit groß, dass Sie immer noch mit relationalen Datenbanken zufrieden sind oder zu einem Team gehören, das ein Produkt seit langer, langer Zeit zufriedenstellend auf einer relationalen Datenbank laufen lässt. Und in neun von zehn Fällen arbeiten Sie mit MySQL (oder MariaDB). In diesen Fällen bringt es enorme Vorteile, wenn Sie ein wenig tiefer unter die Haube blicken, um die Leistung Ihrer Anwendungen zu steigern, und es lohnt sich auf jeden Fall, das zu lernen.

Neugierig? Lassen Sie uns eintauchen!

Nicht neugierig? Dann tauchen Sie trotzdem ein, denn Ihre Karriere hängt davon ab 😛

Optimieren Sie den MySQL Abfrage-Cache

Fast alle Optimierungen im Bereich der Computer laufen auf das Caching hinaus. Auf der einen Seite unterhält die CPU mehrere Cache-Ebenen, um ihre Berechnungen zu beschleunigen, und auf der anderen Seite machen Webanwendungen aggressiven Gebrauch von Caching-Lösungen wie Redis, um den Benutzern vorberechnete Ergebnisse zu liefern, anstatt jedes Mal die Datenbank anzufahren.

Aber hey, selbst die arme MySQL-Datenbank hat ihren eigenen Abfrage-Cache! Das heißt, jedes Mal, wenn Sie eine Abfrage stellen und die Daten noch nicht veraltet sind, liefert MySQL diese zwischengespeicherten Ergebnisse, anstatt die Abfrage erneut auszuführen, was die Anwendung lächerlich schnell macht.

Sie können überprüfen, ob der Abfrage-Cache in Ihrer Datenbank verfügbar ist (Achtung, verfügbar, nicht aktiviert), indem Sie diese Abfrage in der Datenbankkonsole ausführen:

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
 ------------------ ------- 
| Variablenname | Wert |
 ------------------ ------- 
| have_query_cache | YES |
 ------------------ -------

Sie sehen also, dass ich MariaDB verwende und dass ich das Query Caching aktiviert habe. Wenn Sie eine Standard-MySQL-Installation verwenden, ist es äußerst unwahrscheinlich, dass Sie diese Funktion ausgeschaltet haben.

Lassen Sie uns nun sehen, ob der Abfrage-Cache tatsächlich aktiviert ist:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
 ------------------ ------- 
| Variablenname | Wert |
 ------------------ ------- 
| query_cache_type | ON |
 ------------------ -------

Ja, ich weiß. Aber falls Sie es nicht wissen, können Sie es mit folgendem Befehl einschalten

MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

Interessanterweise akzeptiert diese Variable auch einen dritten Wert, der “on-demand” bedeutet, d.h. MySQL wird nur die Abfragen zwischenspeichern, die wir ihm mitteilen.

Damit haben Sie die Zwischenspeicherung von Abfragen aktiviert und den ersten Schritt zu einer robusteren MySQL-Einrichtung getan! Ich sage erster Schritt, denn obwohl die Aktivierung eine große Verbesserung darstellt, müssen wir das Query Caching an unsere Gegebenheiten anpassen. Lassen Sie uns also lernen, wie man das macht.

Die andere Variable, die hier von Interesse ist, ist query_cache_size, deren Funktion selbsterklärend ist:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
 ------------------ ---------- 
| Variablenname | Wert |
 ------------------ ---------- 
| query_cache_size | 16777216 |
 ------------------ ----------

Ich habe also einen Abfrage-Cache von etwa 16 MB Größe. Beachten Sie, dass die Zwischenspeicherung auch dann ausgeschaltet ist, wenn die Zwischenspeicherung für Abfragen aktiviert ist, diese Größe jedoch null beträgt. Deshalb reicht es nicht aus, nur eine Variable zu überprüfen. Sie sollten also eine Größe für den Abfrage-Cache festlegen, aber wie groß sollte sie sein? Beachten Sie zunächst, dass die Abfrage-Caching-Funktion selbst 4 KB benötigt, um ihre Metadaten zu speichern, so dass die von Ihnen gewählte Größe darüber liegen sollte.

Nehmen wir an, Sie stellen die Größe des Abfrage-Caches auf 500 KB ein:

MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;

Ist dies ausreichend? Nein, denn die tatsächliche Leistung der Abfrage-Engine hängt von einer Reihe weiterer Faktoren ab:

  • Zunächst einmal muss die Variable query_cache_size groß genug sein, um die Ergebnisse Ihrer Abfragen zu speichern. Wenn sie zu klein ist, wird nichts zwischengespeichert.
  • Zweitens, wenn query_cache_size auf eine zu hohe Zahl eingestellt ist, gibt es zwei Arten von Problemen: 1) Die Engine muss zusätzliche Arbeit leisten, um Abfrageergebnisse in diesem riesigen Speicherbereich zu speichern und zu finden. 2) Wenn die meisten Abfragen viel kleinere Werte ergeben, wird der Cache fragmentiert und die Vorteile eines Caches gehen verloren.

Woher wissen Sie, dass der Cache fragmentiert ist? Prüfen Sie die Gesamtzahl der Blöcke im Cache wie folgt:

MariaDB [(none)]> show status like 'Qcache_total_blocks';
 --------------------- ------- 
| Variablenname | Wert |
 --------------------- ------- 
qcache_total_blocks | 33 | Qcache_total_blocks | 33
 --------------------- -------

Wenn die Zahl sehr hoch ist, ist der Cache fragmentiert und muss geleert werden.

Um diese Probleme zu vermeiden, stellen Sie sicher, dass die Größe von query_cache_size klug gewählt ist. Wenn Sie frustriert sind, weil ich Ihnen hier keine konkrete Zahl genannt habe, dann fürchte ich, dass das so ist, sobald Sie die Entwicklungsphase hinter sich gelassen haben und in die Technik einsteigen. Sie müssen sich die Anwendung, die Sie ausführen, ansehen und herausfinden, wie groß die Abfragen für die wichtigen Abfrageergebnisse sind, und dann diese Zahl festlegen. Und selbst dann kann es passieren, dass Sie einen Fehler machen 🙂

Threading, Thread-Pools, Warten und Timeouts

Dies ist wahrscheinlich der interessanteste Teil der Funktionsweise von MySQL. Wenn Sie es richtig machen, wird Ihre Anwendung um ein Vielfaches schneller!

Threading

MySQL ist ein Multithreading-Server. Das bedeutet, dass jedes Mal, wenn eine neue Verbindung zum MySQL-Server hergestellt wird, ein neuer Thread mit den Verbindungsdaten geöffnet und ein Handle darauf an den Client weitergegeben wird (Lesen Sie diesen Artikel, wenn Sie sich fragen, was ein Thread ist). Der Client sendet dann alle Abfragen über diesen Thread und erhält die Ergebnisse. Daraus ergibt sich eine naheliegende Frage: Wie viele Threads kann MySQL aufspinnen? Die Antwort finden Sie im nächsten Abschnitt.

Thread-Pool

Kein Programm in einem Computersystem kann so viele Threads öffnen, wie es will. Dafür gibt es zwei Gründe: 1) Threads kosten Arbeitsspeicher (RAM), und das Betriebssystem lässt es einfach nicht zu, dass Sie durchdrehen und den gesamten Speicher aufbrauchen. 2) Die Verwaltung von, sagen wir, einer Million Threads ist eine gewaltige Aufgabe für sich, und wenn der MySQL-Server so viele Threads erstellen könnte, würde er bei dem Versuch, den Overhead zu bewältigen, sterben.

Um diese Probleme zu vermeiden, verfügt MySQL über einen Thread-Pool – eine feste Anzahl von Threads, die zu Beginn Teil eines Pools sind. Neue Verbindungsanfragen führen dazu, dass MySQL einen dieser Threads aufgreift und die Verbindungsdaten zurückgibt. Sind alle Threads aufgebraucht, werden neue Verbindungen natürlich abgelehnt. Lassen Sie uns sehen, wie groß der Thread-Pool ist:

ariaDB [(none)]> zeige Variablen wie 'thread_pool_size';
 ------------------ ------- 
| Variablenname | Wert |
 ------------------ ------- 
| thread_pool_size | 4 |
 ------------------ -------

Mein Rechner erlaubt also maximal vier Verbindungen zur gleichen Zeit. Interessanterweise kommt die Zahl 4 daher, dass ich einen Prozessor mit vier Kernen habe, was bedeutet, dass mein Computer nur 4 parallele Aufgaben gleichzeitig ausführen kann (ich spreche hier von wirklich parallelen Aufgaben, nicht von gleichzeitigen). Idealerweise ist dies die Grenze, die den Wert von thread_pool_size bestimmen sollte, aber auf leistungsfähigeren Rechnern ist eine Erhöhung bis zu einem gewissen Punkt von Vorteil. Wenn Sie nicht alle neuen Verbindungen warten lassen wollen und eine gewisse Leistungseinbuße in Kauf nehmen (auch dies ist ein Bereich, den Sie am besten anhand der Leistung Ihrer Anwendung unter Last beurteilen können), könnte es eine gute Idee sein, den Wert auf 8 zu erhöhen.

Eine Einstellung über 16 ist jedoch eine schlechte Idee, es sei denn, Sie haben einen 32-Kern-Rechner, da die Leistung dann erheblich abnimmt. Das Thema Thread-Pools in MySQL ist sehr umfangreich, aber wenn Sie daran interessiert sind, finden Sie hier eine ausführlichere Diskussion.

Warten und Timeouts

Sobald ein Thread erstellt und an einen Client angehängt wurde, wäre es eine Verschwendung von Ressourcen, wenn der Client in den nächsten Sekunden (oder Minuten) keine Abfragen senden würde. Aus diesem Grund beendet MySQL eine Verbindung nach einer gewissen Zeit der Inaktivität. Dies wird durch die Variable wait_timeout gesteuert:

MariaDB [(none)]> Variablen wie 'wait%' anzeigen;
 --------------- ------- 
| Variablenname | Wert |
 --------------- ------- 
| wait_timeout | 28800 |
 --------------- -------

Der resultierende Wert wird in Sekunden angegeben. Ja, standardmäßig ist MySQL so eingestellt, dass es 8 Stunden wartet, bevor es die Verbindung unterbricht! Das mag gut sein, wenn Sie lange laufende Abfragen haben und tatsächlich auf sie warten wollen (aber selbst dann sind acht Stunden absurd!), aber in den meisten Fällen ist es schrecklich. Wenn eine Abfrage ausgeführt wird, wird dieser Wert auf 0 (d.h. für immer) gesetzt, aber im Allgemeinen sollte dieser Wert auf einen sehr niedrigen Wert gesetzt werden (z.B. 5 Sekunden oder vielleicht sogar weniger), um die Verbindung für andere Prozesse freizugeben.

Optimieren temporärer Tabellen

Beginnen wir damit, was temporäre Tabellen in MySQL sind.

Nehmen wir an, wir haben ein MySQL, das strukturell wie folgt aussieht: TABELLE A UNION (TABELLE B INNER JOIN C). Das heißt, wir sind daran interessiert, die Tabellen B und C zu verbinden und dann eine Vereinigung des Ergebnisses mit der Tabelle A durchzuführen. Nun würde MySQL zuerst die Tabellen B und C verbinden, aber bevor es eine Vereinigung durchführen kann, muss es diese Daten irgendwo speichern. An dieser Stelle kommen temporäre Tabellen ins Spiel. MySQL verwendet sie, um Daten in Zwischenstadien komplexer Abfragen vorübergehend zu speichern.

Nun stellt sich die Frage: Warum sollten wir uns mit all dem beschäftigen?

Ganz einfach, weil es sich bei der temporären Tabelle, die lediglich ein Abfrageergebnis ist, um Daten handelt, die von MySQL für Berechnungen verwendet werden, und die Geschwindigkeit des Zugriffs auf diese Daten (neben anderen Einschränkungen) bestimmt, wie schnell die Abfrage ausgeführt wird. Wenn Sie die temporäre Tabelle beispielsweise im RAM speichern, ist sie um ein Vielfaches schneller als auf der Festplatte.

Es gibt zwei Variablen, die dieses Verhalten steuern:

MariaDB [(none)]> zeige Variablen wie 'MariaDB [(none)]> zeige Variablen wie 'tmp_table_size';  
 ---------------- ---------- 

| Variablenname | Wert |

 ---------------- ---------- 

| tmp_table_size | 16777216 |

 ---------------- ---------- 
';
 --------------------- ---------- 
| Variable_name | Wert |
 --------------------- ---------- 
| max_heap_table_size | 16777216 |
 --------------------- ---------- 

MariaDB [(none)]> zeige Variablen wie 'tmp_table_size';
 ---------------- ---------- 
| Variable_name | Wert |
 ---------------- ---------- 
| tmp_table_size | 16777216 |
 ---------------- ----------

Die erste, max_heap_table_size, gibt an, wie viel RAM von einer MySQL-Tabelle verbraucht werden kann (“heap” bezieht sich hier auf die Datenstruktur, die bei der RAM-Zuweisung und -Verwaltung verwendet wird — lesen Sie hier mehr), während die zweite, tmp_table_size, die maximale Größe der temporären Tabelle angibt. In meinem Fall sind beide auf 16 MB eingestellt, obwohl ich damit sagen will, dass eine Erhöhung nur von tmp_table_size nicht funktionieren wird, da MySQL insgesamt immer noch durch max_table_heap_size begrenzt ist.

Jetzt kommt der springende Punkt: Wenn die temporären Tabellen, die erstellt werden, größer sind als die von diesen Variablen erlaubte Grenze, wäre MySQL gezwungen, sie auf die Festplatte zu schreiben, was zu einer extrem schlechten Leistung führt. Unsere Aufgabe ist jetzt ganz einfach: Wir müssen unser Bestes tun, um die genaueste Datengröße für temporäre Tabellen abzuschätzen und diese Variablen auf diese Grenze einzustellen. Ich möchte Sie jedoch vor Absurditäten warnen: Wenn Sie diese Grenze auf 16 GB setzen (vorausgesetzt, Sie haben so viel RAM), während die meisten Ihrer temporären Tabellen weniger als 24 MB groß sind, ist das Unsinn – Sie verschwenden einfach RAM, das von anderen Abfragen oder Teilen des Systems (z.B. dem Cache) hätte genutzt werden können.

Fazit

Es ist nicht möglich, alle Systemvariablen in einem Artikel zu behandeln, nicht einmal alle wichtigen, wenn die MySQL-Dokumentation selbst mehrere tausend Wörter umfasst. Wir haben hier zwar einige universelle Variablen behandelt, aber ich empfehle Ihnen, sich mit den Systemvariablen für die von Ihnen verwendete Engine(InnoDB oder MyISAM) zu beschäftigen.

Ich wünsche mir, dass Sie aus diesem Artikel drei Dinge mitnehmen:

  1. MySQL ist ein typisches Stück Software, das innerhalb der vom Betriebssystem gesetzten Grenzen arbeitet. Es ist kein mysteriöses Programm, das Gott weiß was tut und nicht zu bändigen ist. Zum Glück ist es auch nicht so schwierig zu verstehen, wie es eingerichtet ist und durch seine Systemvariablen gesteuert wird.
  2. Es gibt keine einzige Einstellung, mit der Sie Ihre MySQL-Installation zum Laufen bringen können. Sie haben keine andere Wahl, als Ihre laufenden Systeme zu überprüfen (denken Sie daran, dass die Optimierung erst erfolgt , wenn die Anwendung in Produktion ist, nicht vorher), die besten Schätzungen und Messungen vorzunehmen und mit der Tatsache zu leben, dass es nie perfekt sein wird.
  3. Das Tuning der Variablen ist nicht die einzige Möglichkeit, MySQL zu optimieren. Das effiziente Schreiben von Abfragen ist ein weiterer wichtiger Punkt, mit dem ich mich in einem anderen Artikel befassen werde. Aber selbst wenn Sie eine gottgleiche Analyse durchgeführt und diese Parameter optimal eingestellt haben, ist es immer noch möglich, dass Sie alles zum Stillstand bringen.

Was ist Ihre bevorzugte Systemvariable für das Tuning? 🙂