10 wichtige Leistungstipps für MySQL

Wie bei allen relationalen Datenbanken kann sich MySQL als kompliziertes Biest erweisen, das kurzfristig zum Stillstand kommen kann und Ihre Anwendungen im Stich lässt und Ihr Unternehmen auf dem Laufenden hält.

Die Wahrheit ist, dass den meisten MySQL-Leistungsproblemen häufige Fehler zugrunde liegen. Um sicherzustellen, dass Ihr MySQL-Server mit Höchstgeschwindigkeit läuft und eine stabile und konsistente Leistung bietet, ist es wichtig, diese Fehler zu beseitigen, die häufig durch Feinheiten in Ihrer Workload oder in einer Konfigurationsfalle verdeckt werden.

Glücklicherweise haben viele MySQL-Leistungsprobleme ähnliche Lösungen, sodass die Fehlerbehebung und Optimierung von MySQL eine überschaubare Aufgabe ist.

Hier sind 10 Tipps, wie Sie MySQL optimal nutzen können.

MySQL-Leistungstipp Nr. 1: Profilieren Sie Ihre Arbeitslast

Der beste Weg, um zu verstehen, wie Ihr Server seine Zeit verbringt, besteht darin, die Arbeitslast des Servers zu profilieren. Durch die Profilerstellung Ihrer Arbeitslast können Sie die teuersten Abfragen für die weitere Optimierung verfügbar machen. Hier ist die Zeit die wichtigste Messgröße, da Sie sich beim Ausgeben einer Abfrage an den Server nur darum kümmern, wie schnell sie abgeschlossen wird.

Der beste Weg, um Ihre Arbeitslast zu profilieren, ist ein Tool wie der Abfrageanalysator von MySQL Enterprise Monitor oder der pt-query-Digest aus dem Percona Toolkit. Diese Tools erfassen Abfragen, die der Server ausführt, und geben eine Aufgabentabelle zurück, die nach abnehmender Reihenfolge der Antwortzeit sortiert ist. Dabei werden die teuersten und zeitaufwändigsten Aufgaben sofort nach oben geschleudert, sodass Sie sehen können, wo Sie sich konzentrieren müssen.

Workload-Profiling-Tools gruppieren ähnliche Abfragen, sodass Sie die langsamen Abfragen sowie die schnellen, aber häufig ausgeführten Abfragen anzeigen können.

MySQL-Leistungstipp Nr. 2: Verstehen Sie die vier grundlegenden Ressourcen

Um zu funktionieren, benötigt ein Datenbankserver vier grundlegende Ressourcen: CPU, Speicher, Festplatte und Netzwerk. Wenn einer dieser Faktoren schwach, fehlerhaft oder überlastet ist, ist es sehr wahrscheinlich, dass der Datenbankserver eine schlechte Leistung erbringt.

Das Verständnis der grundlegenden Ressourcen ist in zwei bestimmten Bereichen wichtig: Auswahl der Hardware und Behebung von Problemen.

Stellen Sie bei der Auswahl der Hardware für MySQL rundum leistungsfähige Komponenten sicher. Ebenso wichtig ist es, sie einigermaßen gut gegeneinander auszubalancieren. Oft wählen Unternehmen Server mit schnellen CPUs und Festplatten aus, die jedoch nicht genügend Speicherplatz haben. In einigen Fällen ist das Hinzufügen von Speicher eine kostengünstige Möglichkeit, die Leistung um Größenordnungen zu steigern, insbesondere bei Workloads, die an die Festplatte gebunden sind. Dies mag nicht intuitiv erscheinen, aber in vielen Fällen sind die Festplatten überlastet, da nicht genügend Speicher vorhanden ist, um den Arbeitsdatensatz des Servers aufzunehmen.

Ein weiteres gutes Beispiel für dieses Gleichgewicht sind CPUs. In den meisten Fällen funktioniert MySQL mit schnellen CPUs gut, da jede Abfrage in einem einzelnen Thread ausgeführt wird und nicht über CPUs hinweg parallelisiert werden kann.

Überprüfen Sie bei der Fehlerbehebung die Leistung und Auslastung aller vier Ressourcen, und achten Sie darauf, ob sie eine schlechte Leistung erbringen oder einfach zu viel Arbeit leisten müssen. Dieses Wissen kann helfen, Probleme schnell zu lösen.

MySQL-Leistungstipp Nr. 3: Verwenden Sie MySQL nicht als Warteschlange

Warteschlangen und warteschlangenähnliche Zugriffsmuster können sich in Ihre Anwendung einschleichen, ohne dass Sie es wissen. Wenn Sie beispielsweise den Status eines Elements so festlegen, dass ein bestimmter Arbeitsprozess es beanspruchen kann, bevor Sie darauf reagieren, erstellen Sie unabsichtlich eine Warteschlange. Ein häufiges Beispiel ist das Markieren von E-Mails als nicht gesendet, das Senden und das anschließende Markieren als gesendet.

Warteschlangen verursachen aus zwei Hauptgründen Probleme: Sie serialisieren Ihre Arbeitslast, verhindern, dass Aufgaben parallel ausgeführt werden, und führen häufig zu einer Tabelle, die unfertige Erzeugnisse sowie historische Daten von Aufträgen enthält, die vor langer Zeit verarbeitet wurden. Beide erhöhen die Latenz der Anwendung und laden sie in MySQL.

MySQL-Leistungstipp Nr. 4: Filtern Sie die Ergebnisse zuerst nach dem billigsten

Eine gute Möglichkeit, MySQL zu optimieren, besteht darin, zuerst billige, ungenaue Arbeit zu leisten und dann die harte, präzise Arbeit an dem kleineren, resultierenden Datensatz.

Angenommen, Sie suchen nach etwas innerhalb eines bestimmten Radius eines geografischen Punkts. Das erste Werkzeug in der Toolbox vieler Programmierer ist die Großkreisformel (Haversine) zur Berechnung der Entfernung entlang der Oberfläche einer Kugel. Das Problem bei dieser Technik ist, dass die Formel viele trigonometrische Operationen erfordert, die sehr CPU-intensiv sind. Großkreisberechnungen laufen in der Regel langsam und lassen die CPU-Auslastung der Maschine in die Höhe schnellen.

Bevor Sie die Großkreisformel anwenden, reduzieren Sie Ihre Datensätze auf eine kleine Teilmenge der Gesamtsumme und schneiden Sie die resultierende Menge auf einen präzisen Kreis. Ein Quadrat, das den Kreis enthält (genau oder ungenau), ist eine einfache Möglichkeit, dies zu tun. Auf diese Weise wird die Welt außerhalb des Platzes nie von all diesen kostspieligen Triggerfunktionen getroffen.

MySQL-Leistungstipp Nr. 5: Kennen Sie die beiden Todesfallen für die Skalierbarkeit

Die Skalierbarkeit ist nicht so vage, wie Sie vielleicht glauben. Tatsächlich gibt es genaue mathematische Definitionen der Skalierbarkeit, die als Gleichungen ausgedrückt werden. Diese Gleichungen zeigen, warum Systeme nicht so gut skalieren, wie sie sollten.

Nehmen Sie das universelle Skalierbarkeitsgesetz, eine Definition, die nützlich ist, um die Skalierbarkeitseigenschaften eines Systems auszudrücken und zu quantifizieren. Es erklärt Skalierungsprobleme anhand von zwei grundlegenden Kosten: Serialisierung und Übersprechen.

Parallele Prozesse, die angehalten werden müssen, damit etwas Serialisiertes stattfinden kann, sind in ihrer Skalierbarkeit von Natur aus eingeschränkt. Wenn die parallelen Prozesse ständig miteinander chatten müssen, um ihre Arbeit zu koordinieren, beschränken sie sich ebenfalls gegenseitig.

Vermeiden Sie Serialisierung und Übersprechen, und Ihre Anwendung lässt sich viel besser skalieren. Was bedeutet das für MySQL? Es variiert, aber einige Beispiele würden exklusive Sperren für Zeilen vermeiden. Warteschlangen, Punkt 3 oben, sind aus diesem Grund tendenziell schlecht skaliert.

MySQL-Leistungstipp Nr. 6: Konzentrieren Sie sich nicht zu sehr auf die Konfiguration

Datenbankadministratoren verbringen in der Regel viel Zeit damit, Konfigurationen zu optimieren. Das Ergebnis ist normalerweise keine große Verbesserung und kann manchmal sogar sehr schädlich sein. Ich habe viele „optimierte“ Server gesehen, die ständig abstürzten, keinen Speicher mehr hatten und eine schlechte Leistung zeigten, wenn die Arbeitslast etwas intensiver wurde.

Die mit MySQL gelieferten Standardeinstellungen sind einheitlich und stark veraltet, aber Sie müssen nicht alles konfigurieren. Es ist besser, die Grundlagen richtig zu machen und andere Einstellungen nur bei Bedarf zu ändern. In den meisten Fällen können Sie 95 Prozent der Spitzenleistung des Servers erzielen, indem Sie etwa 10 Optionen richtig einstellen. Die wenigen Situationen, in denen dies nicht zutrifft, sind Randfälle, die auf Ihre Umstände zugeschnitten sind.

In den meisten Fällen werden Server-Tuning-Tools nicht empfohlen, da sie Richtlinien enthalten, die für bestimmte Fälle nicht sinnvoll sind. Einige enthalten sogar gefährliche, ungenaue Ratschläge - wie z. B. Cache-Trefferquoten und Speicherverbrauchsformeln. Diese waren nie richtig und sind im Laufe der Zeit noch weniger korrekt geworden.

MySQL-Leistungstipp Nr. 7: Achten Sie auf Paginierungsabfragen

Anwendungen, die paginieren, neigen dazu, den Server in die Knie zu zwingen. Wenn diese Anwendungen eine Ergebnisseite mit einem Link zur nächsten Seite anzeigen, gruppieren und sortieren diese Anwendungen in der Regel auf eine Weise, die keine Indizes verwenden kann, und sie verwenden ein LIMITund offsetverursachen, dass der Server viel Arbeit beim Generieren leistet. dann Zeilen verwerfen.

Optimierungen finden Sie häufig in der Benutzeroberfläche. Anstatt die genaue Anzahl der Seiten in den Ergebnissen und Links zu jeder Seite einzeln anzuzeigen, können Sie einfach einen Link zur nächsten Seite anzeigen. Sie können auch verhindern, dass Personen zu weit von der ersten Seite entfernt zu Seiten wechseln.

Auf der Abfrageseite können Sie anstelle von LIMITmit offseteine Zeile mehr auswählen, als Sie benötigen. Wenn der Benutzer auf den Link "Nächste Seite" klickt, können Sie diese letzte Zeile als Ausgangspunkt für die nächste Ergebnismenge festlegen. Wenn der Benutzer beispielsweise eine Seite mit den Zeilen 101 bis 120 angezeigt hat, würden Sie auch die Zeile 121 auswählen. Um die nächste Seite zu rendern, fragen Sie den Server nach Zeilen ab, die größer oder gleich 121 sind (Limit 21).

MySQL-Leistungstipp Nr. 8: Speichern Sie Statistiken eifrig und alarmieren Sie sie nur ungern

Überwachung und Alarmierung sind unerlässlich, aber was passiert mit dem typischen Überwachungssystem? Es werden Fehlalarme gesendet, und Systemadministratoren richten E-Mail-Filterregeln ein, um das Rauschen zu stoppen. Bald ist Ihr Überwachungssystem völlig unbrauchbar.

Ich denke gerne über zwei Arten der Überwachung nach: Erfassung von Metriken und Alarmierung. Es ist sehr wichtig, alle möglichen Metriken zu erfassen und zu speichern, da Sie sich freuen werden, wenn Sie herausfinden möchten, was sich im System geändert hat. Eines Tages wird ein seltsames Problem auftreten, und Sie werden die Möglichkeit lieben, auf ein Diagramm zu zeigen und eine Änderung der Arbeitslast des Servers anzuzeigen.

Im Gegensatz dazu besteht die Tendenz, viel zu viel zu alarmieren. Menschen werden häufig auf Dinge wie die Puffer-Trefferquote oder die Anzahl der pro Sekunde erstellten temporären Tabellen aufmerksam. Das Problem ist, dass es für ein solches Verhältnis keinen guten Schwellenwert gibt. Der richtige Schwellenwert unterscheidet sich nicht nur von Server zu Server, sondern auch von Stunde zu Stunde, wenn sich Ihre Arbeitslast ändert.

Seien Sie daher sparsam und nur unter Bedingungen, die auf ein bestimmtes, umsetzbares Problem hinweisen. Eine niedrige Puffer-Trefferquote ist weder umsetzbar noch weist sie auf ein echtes Problem hin, aber ein Server, der nicht auf einen Verbindungsversuch reagiert, ist ein tatsächliches Problem, das gelöst werden muss.

MySQL-Leistungstipp Nr. 9: Lernen Sie die drei Regeln der Indizierung

Die Indizierung ist wahrscheinlich das am meisten missverstandene Thema in Datenbanken, da es so viele Möglichkeiten gibt, sich darüber zu verwirren, wie Indizes funktionieren und wie der Server sie verwendet. Es erfordert viel Mühe, um wirklich zu verstehen, was los ist.

Indizes dienen bei ordnungsgemäßer Gestaltung drei wichtigen Zwecken auf einem Datenbankserver:

  1. Mithilfe von Indizes kann der Server Gruppen benachbarter Zeilen anstelle einzelner Zeilen finden. Viele Leute denken, dass der Zweck eines Index darin besteht, einzelne Zeilen zu finden, aber das Finden einzelner Zeilen führt zu zufälligen Plattenoperationen, was langsam ist. Es ist viel besser, Gruppen von Zeilen zu finden, von denen alle oder die meisten interessant sind, als Zeilen nacheinander zu finden.
  2. Mithilfe von Indizes kann der Server das Sortieren vermeiden, indem er die Zeilen in der gewünschten Reihenfolge liest. Das Sortieren ist teuer. Das Lesen von Zeilen in der gewünschten Reihenfolge ist viel schneller.
  3. Mit Indizes kann der Server nur ganze Abfragen aus dem Index erfüllen, ohne dass auf die Tabelle zugegriffen werden muss. Dies wird verschiedentlich als Deckungsindex oder Nur-Index-Abfrage bezeichnet.

Wenn Sie Ihre Indizes und Abfragen so gestalten können, dass diese drei Möglichkeiten genutzt werden, können Sie Ihre Abfragen um mehrere Größenordnungen beschleunigen.

MySQL-Leistungstipp Nr. 10: Nutzen Sie das Fachwissen Ihrer Kollegen

Versuche nicht, es alleine zu machen. Wenn Sie über ein Problem rätseln und das tun, was Ihnen logisch und vernünftig erscheint, ist das großartig. Dies funktioniert ungefähr 19 von 20 Fällen. Das andere Mal gehen Sie in ein Kaninchenloch, das sehr kostspielig und zeitaufwändig ist, gerade weil die Lösung, die Sie versuchen, sehr sinnvoll zu sein scheint.

Bauen Sie ein Netzwerk von MySQL-bezogenen Ressourcen auf - und dies geht über Toolsets und Anleitungen zur Fehlerbehebung hinaus. Auf Mailinglisten, Foren, Q & A-Websites usw. lauern einige äußerst sachkundige Personen. Konferenzen, Messen und lokale Benutzergruppenveranstaltungen bieten wertvolle Möglichkeiten, um Einblicke zu gewinnen und Beziehungen zu Kollegen aufzubauen, die Ihnen zur Not helfen können.

Wenn Sie nach Tools suchen, die diese Tipps ergänzen, können Sie den Percona-Konfigurationsassistenten für MySQL, den Percona Query Advisor für MySQL und die Percona-Überwachungs-Plugins lesen. (Hinweis: Sie müssen ein Percona-Konto erstellen, um auf diese ersten beiden Links zugreifen zu können. Es ist kostenlos.) Mit dem Konfigurationsassistenten können Sie eine my.cnf-Basisdatei für einen neuen Server erstellen, die den mitgelieferten Beispieldateien überlegen ist Server. Der Abfrageberater analysiert Ihre SQL, um potenziell fehlerhafte Muster wie Paginierungsabfragen zu erkennen (Nr. 7). Percona Monitoring Plugins sind eine Reihe von Überwachungs- und Grafik-Plugins, mit denen Sie Statistiken eifrig speichern und widerstrebend alarmieren können (Nr. 8). Alle diese Tools sind frei verfügbar.