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 liegt der Grund nicht in der Faulheit (obwohl dies zum Teil der Fall 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. Heutzutage 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 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! ��

Optimieren Sie den MySQL-Abfragecache

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 [(keine)]> VARIABLEN ANZEIGEN WIE ‘have_query_cache’;
+——————+——-+
| Variablenname | Wert |
+——————+——-+
| have_query_cache | JA |
+——————+——-+

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 der Abfragecache tatsächlich aktiviert ist:

MariaDB [(keine)]> SHAR VARIABLES LIKE ‘query_cache_type’;
+——————+——-+
| Variablenname | Wert |
+——————+——-+
| query_cache_type | EIN |
+——————+——-+

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

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

Interessanterweise akzeptiert diese Variable auch einen dritten Wert, der “On-Demand” bedeutet. Dies 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. Lernen wir das also.

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

MariaDB [(keine)]> SHAR VARIABLES LIKE ‘query_cache_size’;
+——————+———-+
| Variablenname | Wert |
+——————+———-+
| 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 [(keine)]> SET GLOBAL query_cache_size = 500000;

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

  • Zunächst muss die Variable query_cache_size groß genug sein, um das Ergebnis Ihrer Abfragen aufzunehmen. Wenn es 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 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 [(keine)]> Status wie ‘Qcache_total_blocks’ anzeigen;
+———————+——-+
| Variablenname | Wert |
+———————+——-+
| 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 mit Bedacht gewählt wird. Wenn Sie frustriert sind, dass ich Ihnen hier keine konkrete Nummer hinterlassen habe, befürchte ich, dass dies der Fall ist, 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, Warten und 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!

Einfädeln

MySQL ist ein Multithread-Server. Das heißt, jedes Mal, wenn eine neue Verbindung zum MySQL-Server besteht, wird ein neuer Thread mit den Verbindungsdaten geöffnet und ein Handle an den Client übergeben (nur für den Fall, dass Sie sich fragen, was ein Thread ist) diese). 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. Es gibt zwei Gründe: 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 Verbindungsanforderungen bewirken, dass MySQL einen dieser Threads aufnimmt und die Verbindungsdaten zurückgibt. Wenn alle Threads aufgebraucht sind, werden neue Verbindungen natürlich abgelehnt. Mal sehen, wie groß der Thread-Pool ist:

ariaDB [(keine)]> zeige Variablen wie ‘thread_pool_size’;
+——————+——-+
| Variablenname | Wert |
+——————+——-+
| 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 thread_pool_size bestimmen sollte, aber bei kräftigeren Maschinen ist eine Erhöhung bis zu einem gewissen Punkt von Vorteil. Wenn Sie nicht alle neuen Verbindungen warten lassen möchten und einen Leistungseinbruch hinnehmen möchten (dies ist wiederum 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.

Warten und Auszeiten

Sobald ein Thread erstellt und an einen Client angehängt wurde, ist es eine Verschwendung von Ressourcen, wenn der Client in den nächsten Sekunden (oder Minuten) keine Anfragen gesendet hat. Infolgedessen beendet MySQL eine Verbindung nach einer Zeit der Inaktivität. Dies wird durch die Variable wait_timeout gesteuert:

MariaDB [(keine)]> zeige Variablen wie ‘wait%’;
+—————+——-+
| Variablenname | Wert |
+—————+——-+
| 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.

Temporäre Tabellen optimieren

Beginnen wir mit 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.

Nun ist die Frage: 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 [(keine)]> zeige Variablen wie ‘MariaDB [(keine)]> zeige Variablen wie ‘tmp_table_size’;
+—————-+———-+

| Variablenname | Wert |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Variablenname | Wert |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(keine)]> zeige Variablen wie ‘tmp_table_size’;
+—————-+———-+
| Variablenname | Wert |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Der erste Wert, max_heap_table_size, gibt an, wie viel RAM von einer MySQL-Tabelle belegt werden kann („Heap“ bezieht sich hier auf die Datenstruktur, die bei der RAM-Zuweisung und -Verwaltung verwendet wird – lesen Sie mehr Hier), während die zweite, tmp_table_size, die maximale Größe der temporären Tabelle anzeigt. In meinem Fall sind beide auf 16 MB festgelegt, obwohl der Punkt, an dem ich versuche, darauf hinzuweisen, dass das Erhöhen von nur tmp_table_size insgesamt nicht funktioniert, MySQL weiterhin durch max_table_heap_size begrenzt ist.

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 verwendet (z. B. Cache).

Fazit

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 oder 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 erfolgt nach der Produktion der App, nicht vorher), die besten Vermutungen und Messungen anzustellen und mit der Realität zu leben, dass sie niemals perfekt sein 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, die ich jedoch in einem anderen Artikel ansprechen werde. 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? ��

STICHWORTE:

  • Datenbank

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map