Optimieren von MySQL-Systemvariablen für hohe Leistung

Für die meisten Anwendungsentwickler ist die Datenbank ein Altar der Dämonengötter, der am besten unangetastet bleibt. Aber so muss es nicht sein!

Wenn andere Dinge gleich sind, definiert der Komfort, den ein Entwickler mit der zugrunde liegenden Datenbank hat, sein Dienstalter. Wenig Datenbank und wenig Programmiererfahrung = Nachwuchsentwickler; wenig Datenbank und gute Codierungserfahrung = Entwickler auf mittlerer Ebene; Gute Datenbank und gute Codierungserfahrung = Senior Developer.

Es ist eine harte Realität, dass selbst Entwickler mit 6-8 Jahren Schwierigkeiten haben, die Feinheiten des Abfrageoptimierers zu erklären, und es vorziehen, auf Nachfrage nach dem Himmel zu schauen Datenbankoptimierung.

Warum?

Überraschenderweise ist der Grund nicht Faulheit (obwohl es in einigen Teilen ist).

Der Punkt ist, dass Datenbanken eine eigene Kraft sind, mit der man sich auseinandersetzen muss. Selbst traditionell, wenn es nur die relationalen Arten von Datenbanken gab, mit denen man sich befassen musste, war es ein Wunder und ein Karriereweg für sich, sie zu beherrschen. In diesen Tagen haben wir so viele Arten von Datenbanken dass es einfach unmöglich ist zu erwarten, dass eine einzige, sterbliche Seele alles beherrscht.

Es besteht jedoch eine gute Chance, dass Sie mit relationalen Datenbanken immer noch zufrieden sind oder Teil eines Teams sind, in dem ein Produkt lange, lange Zeit zufriedenstellend auf einer relationalen Datenbank ausgeführt wird. In neun von zehn Fällen verwenden Sie MySQL (oder MariaDB). In diesen Fällen bietet das Tauchen etwas tiefer unter der Haube massive Vorteile bei der Steigerung der Anwendungsleistung und ist in jeder Hinsicht lernenswert.

Neugierig? Lass uns eintauchen!

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

Optimize the MySQL query cache

Fast alle Optimierungen im Bereich Computer sind auf das Caching zurückzuführen. Auf der einen Seite verwaltet die CPU mehrere Cache-Ebenen, um ihre Berechnungen zu beschleunigen, und auf der anderen Seite verwenden Web-Apps Caching-Lösungen wie Redis aggressiv, um vorberechnete Ergebnisse an Benutzer zu senden, anstatt jedes Mal auf die Datenbank zuzugreifen.

Aber hey, sogar die arme MySQL-Datenbank hat einen eigenen Abfrage-Cache! Das heißt, jedes Mal, wenn Sie etwas abfragen und die Daten immer noch veraltet sind, stellt MySQL diese zwischengespeicherten Ergebnisse bereit, anstatt die Abfrage erneut auszuführen, wodurch die App lächerlich schneller wird.

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

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Sie können also sehen, dass ich MariaDB ausführe und dass Abfrage-Caching zum Aktivieren verfügbar ist. Es ist äußerst unwahrscheinlich, dass Sie es deaktiviert haben, wenn Sie eine Standard-MySQL-Installation verwenden.

Nun wollen wir sehen, ob ich den Abfrage-Cache tatsächlich aktiviert habe:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

Ja, ich will. Falls Sie dies nicht tun, können Sie es einschalten, indem Sie sagen:

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

Interessanterweise akzeptiert diese Variable auch einen dritten Wert, der "on-demand" bedeutet, was bedeutet, dass MySQL nur die Abfragen zwischenspeichert, die wir ihm mitteilen, aber darauf werden wir hier nicht eingehen.

Damit haben Sie das Abfrage-Caching aktiviert und den ersten Schritt in Richtung eines robusteren MySQL-Setups getan! Ich sage den ersten Schritt, denn während das Einschalten eine wesentliche Verbesserung darstellt, müssen wir das Caching von Abfragen an unser Setup anpassen. Also lasst uns lernen, das zu tun.

Die andere interessierende Variable ist hier query_cache_size, dessen Funktion selbsterklärend ist:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

Ich habe also einen Abfrage-Cache von ungefähr 16 MB Größe. Beachten Sie, dass das Caching auch dann deaktiviert ist, wenn das Abfrage-Caching aktiviert ist, diese Größe jedoch Null ist. Deshalb reicht es nicht aus, nur eine Variable zu überprüfen. Jetzt sollten Sie eine Abfrage-Cache-Größe festlegen, aber wie viel sollte es sein? Beachten Sie zunächst, dass die Funktion zum Zwischenspeichern von Abfragen selbst 4 KB benötigt, um ihre Metadaten zu speichern. Was auch immer Sie auswählen, sollte darüber liegen.

Angenommen, Sie legen die Größe des Abfragecaches auf 500 KB fest:

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

Reicht das aus? Nein, denn wie die Abfrage-Engine tatsächlich funktioniert, hängt von ein paar weiteren Dingen ab:

• Zunächst einmal, die query_cache_size Die Variable muss groß genug sein, um das Ergebnis Ihrer Abfragen aufzunehmen. Wenn es zu klein ist, wird nichts zwischengespeichert.
• Zweitens, wenn query_cache_size Wenn die Zahl zu hoch 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 lokalisieren. 2) Wenn die meisten Abfragen zu viel kleineren Abmessungen führen, wird der Cache fragmentiert und die Vorteile der Verwendung eines Caches gehen verloren.

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

MariaDB [(none)]> show status like 'Qcache_total_blocks';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Qcache_total_blocks | 33    |
+---------------------+-------+

Wenn die Anzahl 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 wird mit Bedacht gewählt. Wenn Sie frustriert sind, dass ich Ihnen hier keine konkrete Nummer hinterlassen habe, ist das leider so, wenn Sie die Entwicklung hinter sich lassen und in die Technik einsteigen. Sie müssen in die App schauen, die Sie ausführen, die Abfragegrößen für die wichtigen Abfrageergebnisse ermitteln und dann diese Nummer festlegen. Und selbst dann könnten Sie einen Fehler machen. 🙂

Threading, thread pools, waiting, and timeouts

Dies ist wahrscheinlich der interessanteste Teil der Funktionsweise von MySQL. Um es richtig zu machen, müssen Sie Ihre App um ein Vielfaches schneller machen!

Threading

MySQL ist ein Multithread-Server. Das bedeutet, dass jedes Mal, wenn eine neue Verbindung zum MySQL-Server besteht, 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 ist ein faden). Der Client sendet dann alle Abfragen über diesen Thread und empfängt Ergebnisse. Dies führt uns zu einer natürlichen Frage: Wie viele Threads kann MySQL hochfahren? Die Antwort liegt im nächsten Abschnitt.

Thread-Pool

Kein Programm in einem Computersystem kann so viele Threads öffnen, wie es möchte. Der Grund ist zweierlei: 1) Threads kosten Speicher (RAM), und das Betriebssystem erlaubt Ihnen einfach nicht, wütend zu werden und alles zu verschlingen. 2) Das Verwalten von beispielsweise einer Million Threads ist eine gewaltige Aufgabe für sich. Wenn der MySQL-Server so viele Threads erstellen könnte, würde er beim Versuch, mit dem Overhead fertig zu werden, sterben.

Um diese Probleme zu vermeiden, wird MySQL mit einem Thread-Pool geliefert - einer festen Anzahl von Threads, die zu Beginn Teil eines Pools sind. Neue Verbindungsanfragen veranlassen MySQL, einen dieser Threads aufzunehmen und die Verbindungsdaten zurückzugeben. Wenn alle Threads aufgebraucht sind, werden neue Verbindungen natürlich abgelehnt. Mal sehen, wie groß der Thread-Pool ist:

ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 4     |
+------------------+-------+

Mein Computer erlaubt also maximal vier Verbindungen gleichzeitig. Es ist interessant festzustellen, dass die Nummer 4 von der Tatsache herrührt, dass ich einen Vierkernprozessor habe, was bedeutet, dass mein Computer nur 4 parallele Aufgaben gleichzeitig ausführen kann (ich spreche hier von wirklich parallelen Aufgaben, nicht von gleichzeitigen). Im Idealfall ist dies die Grenze, die den Wert von bestimmen sollte thread_pool_size, aber bei kräftigeren Maschinen profitiert es bis zu einem gewissen Punkt. Wenn Sie nicht alle neuen Verbindungen warten lassen möchten und in Ordnung sind, um einen Leistungseinbruch zu erzielen (auch dies ist ein Bereich, den Sie am besten anhand der Leistung Ihrer App unter Last beurteilen können), ist es möglicherweise eine gute Idee, ihn auf 8 zu erhöhen.

Es ist jedoch eine schreckliche Idee, einen Wert über 16 festzulegen, es sei denn, Sie haben einen 32-Kern-Computer, da sich die Leistung erheblich verschlechtert. Das Kaninchenloch der Thread-Pools in MySQL geht tief, aber wenn Sie interessiert sind, hier ist eine ausführlichere Diskussion.

Waiting and 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 Anfragen senden würde. Infolgedessen beendet MySQL eine Verbindung nach einer Zeit der Inaktivität. Dies wird von der gesteuert wait_timeout Variable:

MariaDB [(none)]> show variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

Der resultierende Wert ist in Sekunden. Also ja, standardmäßig ist MySQL so eingestellt, dass es mehr als 8 Stunden wartet, bevor es das Kabel durchtrennt! Dies kann gut sein, wenn Sie lange laufende Fragen haben und tatsächlich darauf warten möchten (aber selbst dann sind acht Stunden absurd!), Aber in den meisten Fällen schrecklich. Wenn eine Abfrage ausgeführt wird, wird dieser Wert auf 0 gesetzt (was für immer bedeutet). Im Allgemeinen sollte dieser Wert jedoch auf einen sehr niedrigen Wert gesetzt werden (z. B. 5 Sekunden oder sogar weniger), um die Verbindung für andere Prozesse freizugeben.

Tuning temporary tables

Beginnen wir mit den temporären Tabellen in MySQL.

Angenommen, wir haben ein MySQL, das strukturell so 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 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 um diese Daten irgendwo zu speichern. Hier kommen temporäre Tabellen ins Spiel - MySQL verwendet sie, um Daten in Zwischenabschnitten in komplexen Abfragen vorübergehend zu speichern. Sobald die Abfrage beendet ist, wird diese temporäre Tabelle verworfen.

Die Frage ist nun: Warum sollten wir uns mit all dem beschäftigen?

Nur weil die temporäre Tabelle, nur ein Abfrageergebnis, Daten sind, die von MySQL bei der Berechnung verwendet werden, bestimmt die Geschwindigkeit des Zugriffs (unter anderem), wie schnell die Abfrage ausgeführt wird. Das Speichern der temporären Tabelle im RAM ist beispielsweise um ein Vielfaches schneller als das Speichern auf der Festplatte.

Es gibt zwei Variablen, die dieses Verhalten steuern:

MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';  
+----------------+----------+

| Variable_name  | Value    |

+----------------+----------+

| tmp_table_size | 16777216 |

+----------------+----------+
';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

Der erste, max_heap_table_size, sagt uns, 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 mehr hier ), während der zweite, tmp_table_sizezeigt die maximale Größe der temporären Tabelle an. In meinem Fall sind beide auf 16 MB eingestellt, obwohl der Punkt, den ich versuche, nur so zu erhöhen tmp_table_size wird insgesamt nicht funktionieren, MySQL wäre immer noch begrenzt durch max_table_heap_size.

Jetzt kommt der Punkt: Wenn die temporären Tabellen, die erstellt werden, größer sind als das von diesen Variablen zugelassene Limit, müsste MySQL sie auf die Festplatte schreiben, was zu einer extrem schlechten Leistung führt. Unsere Aufgabe ist jetzt einfach: Geben Sie unser Bestes, um die genaueste Datengröße für temporäre Tabellen zu erraten, und passen Sie diese Variablen an diese Grenze an. Ich möchte jedoch vor Absurdität warnen: Es ist dumm, dieses Limit auf 16 GB zu setzen (vorausgesetzt, Sie haben so viel RAM), wenn die meisten Ihrer temporären Tabellen weniger als 24 MB groß sind - Sie verschwenden einfach RAM, das könnte ' wurden von anderen Abfragen oder Teilen des Systems (z. B. Cache) verwendet.

Conclusion

Es ist nicht möglich, alle Systemvariablen in einem Artikel oder sogar alle wichtigen Variablen in einem Artikel zu behandeln, wenn die MySQL-Dokumentation selbst mehrere tausend Wörter umfasst. Während wir hier einige universelle Variablen behandelt haben, möchte ich Sie ermutigen, sich die Systemvariablen für die von Ihnen verwendete Engine anzusehen (InnoDB or MyISAM).

Mein wünschenswertestes Ergebnis beim Schreiben dieses Artikels ist, dass Sie drei Dinge wegnehmen:

1. MySQL ist eine typische Software, die innerhalb der vom Betriebssystem festgelegten Grenzen arbeitet. Es ist kein mysteriöses Programm, das Gott-weiß-was tut und unmöglich zu zähmen ist. Zum Glück ist es auch nicht so schwer zu verstehen, wie es eingerichtet ist und von seinen Systemvariablen gesteuert wird.
2.  Es gibt keine einzige Einstellung, die Ihre MySQL-Installation zum Zoomen bringt. Sie haben keine andere Wahl, als in Ihre laufenden Systeme zu schauen (denken Sie daran, die Optimierung kommt nach Die App ist in Produktion, nicht vorher), machen Sie die besten Vermutungen und Messungen und leben Sie mit der Realität, dass es niemals perfekt werden wird.
3. Das Optimieren der Variablen ist nicht die einzige Möglichkeit, MySQL zu optimieren - effizientes Schreiben von Abfragen ist eine weitere große Sache, aber ich werde sie in einem anderen Artikel ansprechen. Aber der Punkt ist, selbst wenn Sie eine gottähnliche Analyse durchgeführt und diese Parameter optimal eingestellt haben, ist es Ihnen dennoch möglich, alles zum Stillstand zu bringen.

Was ist Ihre bevorzugte Systemvariable für die Abstimmung? 🙂