Effiziente MySQL-Leistung

Kapitel 4. Zugriffsmuster

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Zugriffsmuster beschreiben, wie eine Anwendung MySQL nutzt, um auf Daten zuzugreifen. Die Änderung von Zugriffsmustern hat einen großen Einfluss auf die Leistung von MySQL, erfordert aber in der Regel einen größeren Aufwand als andere Optimierungen. Deshalb ist dies die letzte Etappe der Reise, die in "Verbesserung der Abfrage-Antwortzeit" beschrieben wurde: zuerst die Optimierung von Abfragen, Indizes und Daten - dann die Optimierung von Zugriffsmustern. Bevor wir beginnen, lass uns noch einmal über die Felsen aus Kapitel 3 nachdenken.

Angenommen, du hast einen Lkw, der mit MySQL vergleichbar ist. Wenn der Lkw effizient eingesetzt wird, kann er jeden Steinhaufen leicht bergauf transportieren. Wenn er jedoch ineffizient eingesetzt wird, bringt der Lkw nur wenig Nutzen und kann sogar dazu führen, dass die Arbeit länger dauert als nötig. Du könntest zum Beispiel den Lkw benutzen, um die Steine von einen nach dem anderen den Berg hinauf zu transportieren. Das ist zwar einfach für dich (und den Lkw), aber sehr ineffizient und zeitaufwändig. Ein Lkw ist nur so nützlich wie die Person, die ihn benutzt. Genauso ist MySQL nur so nützlich wie die Anwendung, die ihn benutzt.

Manchmal rätselt ein Ingenieur darüber, warum MySQL nicht schneller läuft. Zum Beispiel, wenn MySQL 5.000 QPS ausführt und der Ingenieur sich fragt, warum es nicht 9.000 QPS ausführt. Oder wenn MySQL 50 % der CPU nutzt und der Ingenieur sich fragt, warum es nicht 90 % der CPU nutzt. Der Ingenieur wird wahrscheinlich keine Antwort finden, weil er sich auf die Wirkung (MySQL) konzentriert und nicht auf die Ursache: die Anwendung. Metriken wie QPS und CPU-Nutzung sagen sehr wenig - fast nichts - über MySQL aus; sie spiegeln nur wider, wie die Anwendung MySQL nutzt.

Tipp

MySQL ist nur so schnell und effizient wie die Anwendung, die es nutzt.

Eine Anwendung kann über die Kapazität einer einzelnen MySQL-Instanz hinauswachsen, aber noch einmal: Das sagt mehr über die Anwendung als über MySQL aus, denn es gibt unzählige große, leistungsstarke Anwendungen, die eine einzelne MySQL-Instanz nutzen. Zweifellos ist MySQL schnell genug für die Anwendung. Die eigentliche Frage ist: Nutzt die Anwendung MySQL effizient? Nach vielen Jahren mit MySQL, Hunderten von verschiedenen Anwendungen und Tausenden von verschiedenen MySQL-Instanzen kann ich dir versichern: Die Leistung von MySQL wird durch die Anwendung begrenzt, nicht umgekehrt.

In diesem Kapitel geht es um Datenzugriffsmuster, die bestimmen, wie du die Anwendung ändern kannst, um MySQL effizient zu nutzen. Es gibt sechs Hauptabschnitte. Der erste verdeutlicht, was MySQL abseits der Anwendung tut und warum das wichtig ist. Der zweite beweist, dass die Datenbankleistung nicht linear skaliert, sondern dass es eine Grenze gibt, nach der die Leistung destabilisiert wird. Der dritte Abschnitt erklärt, warum ein Ferrari schneller ist als ein Toyota, obwohl beide Automarken in etwa gleich funktionieren. Die Antwort erklärt, warum einige Anwendungen mit MySQL hervorragend funktionieren, während andere nicht aus dem ersten Gang herauskommen. Der vierte Abschnitt zählt Datenzugriffsmuster auf. Der fünfte Abschnitt stellt einige Anwendungsänderungen vor, mit denen die Datenzugriffsmuster verbessert oder verändert werden können. Der sechste Abschnitt greift einen alten Freund wieder auf: bessere, schnellere Hardware.

MySQL macht nichts

Wenn die Anwendung im Leerlauf ist, ist MySQL im Leerlauf. Wenn die Anwendung mit der Ausführung von Abfragen beschäftigt ist, ist MySQL mit der Ausführung dieser Abfragen beschäftigt. MySQL hat mehrere Hintergrundaufgaben (wie "Page flushing"), die aber nur mit dem Lesen und Schreiben von Daten für diese Abfragen beschäftigt sind. Tatsächlich erhöhen Hintergrundaufgaben die Leistung, indem sie es den Vordergrundaufgaben - der Ausführung von Abfragen - ermöglichen, langsame Operationen aufzuschieben oder zu vermeiden. Wenn MySQL also langsam läuft und es keine externen Probleme gibt, kann die Ursache nur das sein, was MySQL antreibt: die Anwendung.

Tipp

QPS ist direkt und ausschließlich auf die Bewerbung zurückzuführen. Ohne die Bewerbung ist QPS gleich null.

Einige Datenspeicher haben Geister in der Maschine: interne Prozesse, die jederzeit ablaufen können und die Leistung verschlechtern, wenn sie zum ungünstigsten Zeitpunkt ablaufen: wenn der Datenspeicher mit der Ausführung von Abfragen beschäftigt ist. (Verdichtung und Vakuumierung sind zwei Beispiele - MySQL hat keines von beiden.) MySQL hat keine Geister in der Maschine - es sei denn, die Anwendung führt Abfragen aus, von denen du nichts weißt. Wenn du das weißt, kannst du vermeiden, nach nicht existierenden Ursachen zu suchen, und dich auf das konzentrieren, womit MySQL beschäftigt ist: die Ausführung von Abfragen. Aus Kapitel 1 weißt du, wie du das erkennen kannst: "Ein Abfrageprofil zeigt nicht nur langsame Abfragen, sondern auch, womit MySQL gerade beschäftigt ist.

Abfragen beeinflussen andere Abfragen. Der allgemeine Begriff dafür ist Abfragekonkurrenz: wenn Abfragen miteinander konkurrieren und auf gemeinsame Ressourcen warten. Es gibt bestimmte Arten von Konkurrenz: Zeilensperrkonkurrenz, CPU-Konkurrenz usw. Abfragekonkurrenz kann den Anschein erwecken, dass MySQL mit anderen Dingen beschäftigt ist, aber lass dich nicht täuschen: MySQL ist nur mit der Ausführung von Anwendungsabfragen beschäftigt.

Es ist fast unmöglich, Abfragekonflikte zu sehen oder zu beweisen, weil MySQL nur eine Art von Konflikten meldet: Zeilensperrkonflikte. (Selbst Zeilensperrkonflikte sind schwer zu sehen, weil Zeilensperren komplex sind.) Darüber hinaus sind Konflikte flüchtig - fast unmerklich - weil das Problem mit hohen QPS einhergeht (wobei hoch relativ zur Anwendung ist). Abfragekonflikte sind wie ein Verkehrsstau: Es müssen viele Autos auf der Straße sein. Obwohl es fast unmöglich ist, sie zu sehen oder zu beweisen, musst du dir ihrer bewusst sein, weil sie unerklärlich langsame Abfragen erklären können.

Abfragekonflikte spielen eine große Rolle, wenn die Leistung an ihre Grenzen stößt.

Leistung destabilisiert sich an der Grenze

Am Ende von "MySQL: Go Faster", , habe ich gesagt, dass MySQL die meiste moderne Hardware leicht an ihre Grenzen bringen kann. Das stimmt, aber die Grenze könnte dich überraschen.Abbildung 4-1 zeigt, was Ingenieure erwarten: Mit zunehmender Last steigt die Leistung der Datenbank, bis sie 100 % der Systemkapazitätausschöpft - also den Durchsatzder Hardware und des Betriebssystems - und dann bleibt die Leistung konstant. Das nennt man lineare Skalierung (oder lineare Skalierbarkeit), und es ist ein Mythos.

Abbildung 4-1. Erwartete Datenbankleistung (lineare Skalierbarkeit)

Lineare Skalierung ist der Traum eines jeden DBAs und Ingenieurs, aber sie ist nicht möglich. Abbildung 4-2 zeigt stattdessen die Realität der Datenbankleistung in Bezug auf Last und Systemkapazität.

Abbildung 4-2. Reale Datenbankleistung

Die Datenbankleistung steigt mit der Last nur bis zu einer Grenze, die unter 100 % der Systemkapazität liegt. Realistisch gesehen liegt die Grenze der Datenbankleistung bei 80 % bis 95 % der Systemkapazität. Wenn die Last über die Grenze steigt, destabilisiert sich die Datenbankleistung: Durchsatz, Antwortzeit und andere Messwerte schwanken deutlich - manchmal sogar stark - von ihren normalen Werten ab. Im besten Fall ist das Ergebnis eine verringerte Leistung für einige (oder die meisten) Abfragen, im schlimmsten Fall führt es zu einem Ausfall.

Gleichung 4-1 zeigt das von Neil Gunther formulierte universelle Skalierbarkeitsgesetz : eine Gleichung, die die Skalierbarkeit von Hardware- und Softwaresystemen modelliert.

Gleichung 4-1. Gleichung 4-1. Universelles Skalierbarkeitsgesetz

$X\left(N\right)=\frac{\gamma N}{1+\alpha (N-1)+\beta N(N-1)}$

Tabelle 4-1 zeigt, was jeder Begriff in der Gleichung des Universellen Skalierbarkeitsgesetzes bedeutet.

Tabelle 4-1. Begriffe des Universellen Skalierbarkeitsgesetzes

Begriff	Repräsentiert
X	Durchsatz
N	Last: gleichzeitige Anfragen, laufende Prozesse, CPU-Kerne, Knoten in einem verteilten System und so weiter
γ	Gleichzeitigkeit (ideale Parallelität)
α	Contention: Warten auf gemeinsame Ressourcen
β	Kohärenz: Koordinierung gemeinsamer Ressourcen

Hinweis

Ein tieferes Eintauchen in das Universelle Skalierbarkeitsgesetz würde den Rahmen dieses Buches sprengen, daher beschränke ich mich auf das aktuelle Thema: die Grenzen der Datenbankleistung. Um mehr zu erfahren, lies Guerrilla Capacity Planning von Neil Gunther.

Der Durchsatz ist eine Funktion der Last: X(N) Gleichzeitigkeit (γ) trägt dazu bei, dass der Durchsatz mit zunehmender Last (N) ansteigt. Aber Konkurrenzdenken (α) und Kohärenz (β) verringern den Durchsatz mit zunehmender Last. Dies schließt eine lineare Skalierbarkeit aus und begrenzt die Leistung der Datenbank.

Schlimmer noch als die Begrenzung der Leistung ist, dass die Kohärenz zu einer rückläufigen Leistung führt: Die Leistung von nimmt bei hoher Last ab. Der Begriff " rückläufig" ist eine Untertreibung. Er suggeriert, dass MySQL einfach zu weniger Durchsatz zurückkehrt, wenn es die Last nicht bewältigen kann, aber die Realität ist schlimmer. Ich bevorzuge die Begriffe " Instabilität" und "destabilisieren", weil sie die Realität ausdrücken: Das System bricht zusammen und läuft nicht nur langsamer.

Das universelle Skalierbarkeitsgesetz bildet die reale Leistung von MySQL erstaunlich gut ab.¹ Aber als Modell beschreibt und prognostiziert es nur die Skalierbarkeit eines Workloads; es sagt nichts darüber aus, wie oder warum der Workload skaliert (oder nicht skaliert). Das USL wird vor allem von Experten verwendet, die Daten messen und an das Modell anpassen, um die Parameter (γ, α und β) zu bestimmen, und sich dann heldenhaft abmühen, um sie zu reduzieren. Alle anderen schauen sich einfach die Graphen an(Kapitel 6 behandelt die MySQL-Metriken) und warten, bis die MySQL-Leistung destabilisiert - das ist die Grenze.

Abbildung 4-3 zeigt drei Diagramme eines realen Ausfalls, bei dem die Anwendung MySQL über das Limit hinaus belastete.

Der Ausfall hatte drei Phasen:

The Rise (6 bis 9 Uhr morgens)

Zu Beginn des Anstiegs war die Anwendung stabil, aber die Entwickler machten sich langsam Sorgen, weil die angezeigten Kennzahlen langsam aber stetig stiegen. In der Vergangenheit hatte die Anwendung Ausfälle, die mit stetig steigenden Kennzahlen begannen. Als Reaktion darauf erhöhten die Anwendungsentwickler den Transaktionsdurchsatz, um die steigende Nachfrage zu bewältigen. (Die Anwendung kann den Transaktionsdurchsatz drosseln; dies ist keine Funktion von MySQL.) Der Anstieg und die Reaktion wiederholten sich, bis es nicht mehr funktionierte: MySQL hatte die Grenze erreicht.

Abbildung 4-3. Datenbankleistung jenseits der Grenze

The Limit (9 Uhr bis Mittag)

Die Anwendung war völlig instabil und während des Limits praktisch offline. Obwohl die CPU-Auslastung und die QPS hoch und konstant waren, sagten die laufenden Threads etwas anderes aus. Das in Abbildung 4-3 gezeigte "Whipsaw"-Muster der laufenden Threads war ein verräterisches Zeichen dafür, dass MySQL destabilisiert war. Da für eine Abfrage ein Thread benötigt wird, zeigten die großen Schwankungen bei den laufenden Threads an, dass die Abfragen nicht reibungslos durch das System liefen. Stattdessen schlugen die Abfragen in ungleichmäßigen, verunsicherten Schlägen auf MySQL ein.

Hohe und konstante CPU-Auslastung und QPS waren irreführend: Konstant ist nur gut, wenn es kleine Schwankungen gibt, wie vor und nach dem Limit. Konstant ohne Schwankungen, wie während des Limits, ist Flatline. Um zu verstehen, warum das so ist, hier eine seltsame, aber effektive Analogie: Stell dir ein Orchester vor. Wenn das Orchester richtig spielt, gibt es Variationen in allen Aspekten der Musik. Tatsächlich sind diese Variationen die Musik: Rhythmus, Tempo, Tonhöhe, Klang, Melodie, Dynamik usw. Eine Flatline-Metrik ist vergleichbar mit einem gestörten Klarinettisten, der eine einzige, durchgehende Note fortissimo spielt: gleichmäßig, aber keine Musik.

Während des Limits versuchten die Anwendungsentwickler immer wieder, den Transaktionsdurchsatz zu erhöhen, aber es funktionierte nicht. MySQL nutzte die letzten 5% der CPU nicht, die QPS stieg nicht an und die laufenden Threads stabilisierten sich nicht. Aus der USL(Gleichung 4-1) weißt du, warum: Konkurrenz und Kohärenz. Mit zunehmender Last (N) stieg der Transaktionsdurchsatz (X), aber auch die begrenzenden Effekte von Konkurrenz (α) und Kohärenz (β), bis MySQL das Limit erreichte.

The Fix (mittags bis 15.00 Uhr)

Da die Erhöhung des Transaktionsdurchsatzes sein eigener Untergang war, bestand die Lösung darin, den Transaktionsdurchsatz zu verringern. Das scheint kontraintuitiv, aber die Mathematik lügt nicht. Mittags reduzierten die Anwendungsentwickler den Transaktionsdurchsatz, und die Ergebnisse sind in den Diagrammen deutlich zu sehen: Die CPU-Auslastung sank auf 50 %, die QPS kehrte zu einer gleichmäßigen Schwankung zurück (und stieg sogar ein wenig an), und die Anzahl der laufenden Threads kehrte ebenfalls zu einer gleichmäßigen Schwankung zurück (mit ein paar Spitzen, die MySQL auffangen konnte).

Um dir vorzustellen, wie das funktioniert, nimm eine andere Analogie: Stell dir eine Autobahn vor. Wenn viele Autos auf der Straße sind, werden sie (hoffentlich) alle langsamer, denn die Menschen brauchen Zeit, um zu denken und auf andere Autos zu reagieren, vor allem bei Autobahngeschwindigkeiten. Wenn zu viele Autos auf der Straße sind, verursachen sie einen Stau. Die einzige Lösung (abgesehen davon, dass mehr Fahrspuren angelegt werden) ist, die Anzahl der Autos auf der Autobahn zu verringern: Weniger Autos können schneller fahren. Die Verringerung des Transaktionsdurchsatzes ist analog zur Verringerung der Anzahl der Autos auf der Autobahn, wodurch die verbleibenden Autos schneller fahren und der Verkehr reibungslos fließen kann.

Dieses Beispiel zeigt sehr schön die Grenze der Datenbankleistung nach dem universellen Skalierbarkeitsgesetz(Gleichung 4-1), aber es ist auch ein Ausnahmefall, weil die Anwendung in der Lage war, MySQL und die Hardware bis an die Grenze zu belasten. Normalerweise destabilisiert eine hohe Last die Anwendung, was sie daran hindert, die Last auf MySQL zu erhöhen. Mit anderen Worten: Die Anwendung schlägt fehl, bevor sie MySQL bis an die Grenze belasten kann. Aber in diesem Beispiel ist die Anwendung nicht fehlgeschlagen, sondern hat die Skalierung fortgesetzt, bis sie MySQL an die Grenze gebracht hat.

Noch zwei Punkte zur Leistung von MySQL am Limit, bevor wir uns der Anwendung zuwenden:

• Diese Grenze ist nur schwer zu erreichen, es sei denn, die Hardware ist völlig unzureichend. Wie in "Bessere, schnellere Hardware!" erwähnt , ist dies eine der beiden Ausnahmen, bei denen du auf eine vernünftige Hardware skalieren solltest. Außerdem ist es für eine Anwendung schwierig, die gesamte Hardware - also CPU, Arbeitsspeicher und Speicherung - vollständig und gleichzeitig auszunutzen. Eine Anwendung wird höchstwahrscheinlich bei einer Hardware einen Engpass haben, lange bevor sie die gesamte Hardware vollständig und gleichzeitig ausnutzen kann. Wenn das passiert, hat die Anwendung nicht die Grenze der Datenbankleistung erreicht, sondern nur die Grenze dieser einen Hardware.

• Wenn MySQL bei hoher Last langsam reagiert, bedeutet das nicht, dass das Limit erreicht ist. Der Grund dafür ist einfach: γ. Gamma (γ) steht für Gleichzeitigkeit oder ideale Parallelität. Aus der Gleichung des universellen Skalierbarkeitsgesetzes(Gleichung 4-1) wissen wir, dass Gamma im Zähler steht.² Eine langsame Datenbankleistung bedeutet nicht, dass der Grenzwert erreicht ist, denn eine Erhöhung der Gleichzeitigkeit (γ) erhöht den Grenzwert. Eine Verringerung der Konkurrenzsituation (α) erhöht ebenfalls den Grenzwert. (Die Kohärenz [β] liegt außerhalb unserer Kontrolle: Sie ist in MySQL und dem Betriebssystem enthalten, stellt aber normalerweise kein Problem dar.)

Der zweite Punkt führt zu der Frage: Wie können wir die Gleichzeitigkeit erhöhen oder den Konflikt verringern oder beides? Das scheint eine wichtige Frage zu sein, ist es aber nicht: Sie ist irreführend, denn der Nordstern der MySQL-Leistung ist die Abfrageantwortzeit. Die Werte für Gleichzeitigkeit (γ) und Konflikt (α) sind nicht direkt messbar. Sie werden ermittelt, indem Durchsatz- und Lastmessungen an das Modell angepasst werden. Experten verwenden das universelle Skalierbarkeitsgesetz, um die Systemkapazität zu verstehen, nicht um die Leistung zu verbessern. Und dieser Abschnitt hat es benutzt, um zu beweisen, dass die Leistung am Limit destabilisiert.

Toyota und Ferrari

Einige Anwendungen erreichen eine unglaubliche MySQL-Leistung, während andere mit einem geringen Durchsatz zu kämpfen haben. Einige Anwendungen können die Hardware voll ausnutzen - bis zum Limit -, während andere die CPUs kaum erwärmen. Einige Anwendungen haben keine Leistungsprobleme, während andere ständig mit langsamen Abfragen zu kämpfen haben. Es ist eine pauschale Verallgemeinerung, aber ich behaupte, dass jeder Ingenieur seine Anwendung auf der linken Seite von while haben möchte: unglaubliche Leistung, volle Ausnutzung der Hardware und keine Probleme. Der Unterschied zwischen Anwendungen auf der linken Seite von while und denen auf der rechten Seite wird deutlich, wenn man sich überlegt, warum ein Ferrari schneller ist als ein Toyota.

Beide Automarken verwenden ungefähr die gleichen Teile und das gleiche Design, aber die Höchstgeschwindigkeit eines Toyotas liegt in der Regel bei 130 MPH, während die Höchstgeschwindigkeit eines Ferraris 200 MPH beträgt.³ Ein Ferrari hat keine speziellen Teile, die ihn 70 km/h schneller machen als einen Toyota. Warum ist ein Ferrari also so viel schneller als ein Toyota? Die Antwort ist der Unterschied in der Konstruktion und den Details.

Ein Toyota ist nicht für hohe Geschwindigkeiten ausgelegt. Um eine hohe Geschwindigkeit zu erreichen, müssen viele Details beachtet werden. Zu diesen Details gehören bei einem Auto:

• Motorgröße, -konfiguration und -steuerung

• Getriebeübersetzungen, Schaltpunkte und Steuerzeiten

• Reifengröße, Traktion und Rotationskraft

• Lenkung, Federung und Bremsen

• Aerodynamik

Beide Automarken entwerfen und konstruieren für diese Details, aber die hohe Detailgenauigkeit eines Ferrari erklärt, warum er eine höhere Leistung erzielt. Das kannst du an einem dieser Details sehen: der Aerodynamik. Das einzigartige Außendesign eines Ferrari ist extravagant, aber auch funktional: Es senkt den Luftwiderstandsbeiwert, was die Effizienz erhöht.

Hochleistung wird, wie Hochgeschwindigkeit, nicht zufällig oder mit roher Gewalt erreicht. Sie ist das Ergebnis sorgfältiger Ingenieursarbeit mit dem Ziel der Hochleistung. Ein Ferrari ist schneller als ein Toyota, weil er bis ins kleinste Detail so konstruiert und gebaut wurde, dass er schneller ist.

Ist deine Anwendung bis ins kleinste Detail auf maximale MySQL-Leistung ausgelegt? Wenn ja, dann kannst du den Rest dieses Kapitels wohl überspringen. Wenn nicht, was die übliche Antwort ist, dann befasst sich der nächste Abschnitt mit den grundlegenden technischen Unterschieden, die Toyota-ähnliche Anwendungen von Ferrari-ähnlichen Anwendungen unterscheiden: Datenzugriffsmuster.

Datenzugriffsmuster

Datenzugriffsmuster beschreiben, wie eine Anwendung MySQL für den Datenzugriff nutzt.

Der Begriff Datenzugriffsmuster (oder kurz Zugriffsmuster ) wird zwar häufig verwendet, aber selten erklärt. Das wollen wir ändern, indem wir drei Details über Zugriffsmuster klären:

• Es ist so üblich, von Zugriffsmustern im Plural zu sprechen, dass sie zu verschwimmen beginnen. Aber es ist wichtig zu erkennen, dass sie kein undifferenzierter Klumpen sind. Eine Anwendung hat viele Zugriffsmuster. Der Einfachheit halber sprechen wir von ihnen im Plural. Aber in der Praxis änderst du die Zugriffsmuster einzeln.

• Ein Zugriffsmuster bezieht sich letztlich auf eine Abfrage, und du änderst Abfragen (und die Anwendung), um Zugriffsmuster zu ändern, aber Abfragen stehen nicht im Mittelpunkt. In der Programmiersprache Go ist ein Zugriffsmuster eine Schnittstelle und eine Abfrage eine Implementierung. Konzentriere dich auf die Schnittstelle, nicht auf die Implementierung. So ist es möglich, sich Zugriffsmuster für verschiedene Datenspeicher vorzustellen (und möglicherweise anzuwenden). Bestimmte Zugriffsmuster, die auf MySQL ausgeführt werden, sind zum Beispiel besser für einen Key-Value-Datenspeicher geeignet, aber das ist schwer zu erkennen, wenn man sich auf SQL-Abfragen konzentriert, die keine Ähnlichkeit mit Key-Value-Abfragen haben. In diesem Buch gehe ich auf die Änderung von Zugriffsmustern ein, aber in der Praxis änderst du Abfragen (und die Anwendung).

• Ein Zugriffsmuster besteht aus einem Namen und einer Liste technischer Merkmale. Der Name wird verwendet, um das Zugriffsmuster zu identifizieren und mit anderen Entwicklern zu kommunizieren. (Zugriffsmuster haben keine eigenen Namen.) Wähle einen prägnanten und aussagekräftigen Namen. Die Liste der technischen Merkmale hängt von der jeweiligen Datenspeicherung ab und variiert. Der Datenzugriff auf MySQL unterscheidet sich beispielsweise deutlich vom Datenzugriff auf Redis. In diesem Abschnitt werden neun Merkmale für den Datenzugriff auf MySQL aufgezählt und erläutert.

Theoretisch sollten Anwendungsentwickler jedes einzelne Zugriffsmuster identifizieren, aber seien wir ehrlich: Das ist sehr mühsam. (Ich habe das noch nie gemacht, und es ist vielleicht nicht einmal machbar, wenn sich die Anwendung schnell ändert.) Trotzdem ist das das Ziel. Hier sind drei vernünftige und erreichbare Ansätze, um dieses Ziel zu erreichen:

• Mach ein Brainstorming mit deinem Team, um die offensichtlichsten und häufigsten Zugriffsmuster zu identifizieren.

• Verwende das Abfrageprofil (siehe "Abfrageprofil"), um die wichtigsten und langsamsten Zugriffsmuster zu ermitteln.

• Durchsuche den Code nach weniger bekannten (oder vergessenen) Zugriffsmustern.

Du musst zumindest einmal den ersten oder zweiten Ansatz verfolgen, um das Ziel dieses Kapitels zu erreichen: indirekte Abfrageoptimierung durch veränderte Zugriffsmuster.

Sobald du ein Zugriffsmuster identifiziert (und benannt) hast, ermittle den Wert oder die Antwort auf jedes der folgenden neun Merkmale. Wenn du den Wert oder die Antwort auf ein Merkmal nicht kennst, ist das eine großartige Gelegenheit, um zu lernen und möglicherweise einen Teil der Anwendung zu verbessern. Lass ein Merkmal nicht unbekannt; finde den Wert oder die Antwort heraus.

Bevor wir die neun Merkmale erläutern, müssen wir noch eine Frage klären: Wie verwendet man Zugriffsmuster? Zugriffsmuster sind reines Wissen, und dieses Wissen bildet eine Brücke zwischen dem vorherigen und dem nächsten Abschnitt. Der vorherige Abschnitt, "Toyota und Ferrari", verdeutlicht, dass eine leistungsstarke MySQL-Anwendung eine leistungsstarke Anwendung erfordert. Der nächste Abschnitt, "Anwendungsänderungen", stellt häufige Anwendungsänderungen vor, die dabei helfen, die Anwendung im Hinblick auf die Datenbank leistungsfähiger zu machen. Zugriffsmuster helfen dabei, zu entscheiden (und manchmal auch vorzuschreiben), wie die Anwendung von einem Toyota zu einem Ferrari umgestaltet werden kann.

Ohne weitere Umschweife wollen wir neun Merkmale von Datenzugriffsmustern für MySQL untersuchen.

Lesen/Schreiben

Liest oder schreibt der Zugriff Daten?

Der Lesezugriff ist klar: SELECT. Der Schreibzugriff ist weniger klar, wenn du die Details betrachtest. INSERT ist zum Beispiel ein Schreibzugriff, aber INSERT...SELECT ist ein Lese- und Schreibzugriff. Ebenso sollten UPDATE und DELETE eine WHERE Klausel verwenden, was sie ebenfalls zu Lese- und Schreibzugriffen macht. Der Einfachheit halber: INSERT, UPDATE und DELETE werden immer als Schreibzugriff betrachtet.

Intern sind Lese- und Schreibvorgänge nicht gleich: Sie haben unterschiedliche technische Auswirkungen und rufen unterschiedliche interne Teile von MySQL auf. INSERT und DELETE zum Beispiel sind unter der Haube unterschiedliche Schreibvorgänge - nicht nur, weil Ersteres hinzufügt und Letzteres entfernt. Noch einmal zur Vereinfachung: Alle Lesevorgänge sind gleich und alle Schreibvorgänge sind gleich.

Die Lese-/Schreibeigenschaft ist eine der grundlegendsten und allgegenwärtigsten, da die Skalierung von Lese- und Schreibvorgängen unterschiedliche Änderungen in der Anwendung erfordert. Die Skalierung von Lesevorgängen wird in der Regel durch das Auslagern von Lesevorgängen erreicht, das ich später in "Auslagern von Lesevorgängen" behandle . Die Skalierung von Schreibvorgängen ist schwieriger, aber das Einreihen von Schreibvorgängen in eine Warteschlange ist eine Technik (siehe "Schreiben einreihen"), und Kapitel 5 behandelt die ultimative Lösung: Sharding.

Obwohl diese Eigenschaft recht einfach ist, ist sie wichtig, denn wenn du weißt, ob eine Anwendung lese- oder schreiblastig ist, kannst du deine Aufmerksamkeit schnell auf relevante Änderungen in der Anwendung lenken. Der Einsatz eines Caches ist zum Beispiel für eine schreiblastige Anwendung nicht relevant. Außerdem sind andere Datenspeicher für Lese- oder Schreibvorgänge optimiert, und für MySQL gibt es eine schreiboptimierte Speicherung: MyRocks.

Durchsatz

Wie ist der Durchsatz (in QPS) und die Variation des Datenzugriffs?

Zunächst einmal ist Durchsatz nicht gleich Leistung. Zugriffe mit geringem Durchsatz - selbst mit nur 1 QPS - könnenverheerende Folgen haben. Du kannst dir wahrscheinlich vorstellen, wie das geht; falls nicht, hier ein Beispiel: eine SELECT...FOR UPDATE Anweisung, die eine Tabellendurchsuchung durchführt und jede Zeile sperrt. Es ist selten, dass ein Zugriff so schlimm ist, aber es beweist: Durchsatz ist nicht gleich Leistung.

Trotz des schrecklichen Zugriffs sind sehr hohe QPS (wobei "hoch" im Verhältnis zur Anwendung steht) in der Regel ein Problem, das aus den in "Weniger QPS ist besser" genannten Gründen gelöst werden muss . Wenn die Anwendung beispielsweise Aktiengeschäfte ausführt, hat sie wahrscheinlich um 9:30 Uhr Eastern Time, wenn die amerikanischen Börsen öffnen, einen enormen Lese- und Schreibzugriff. Dieser Durchsatz führt zu ganz anderen Überlegungen als gleichmäßige 500 QPS.

Die Schwankungen - wie QPS ansteigt und abfällt - sind ebenso wichtig. Im vorherigen Absatz wurde von sprunghaften und stetigen Schwankungen gesprochen; eine andere Art von Schwankungen ist zyklisch: Ein häufiges zyklisches Muster ist eine höhere QPS während der Geschäftszeiten - zum Beispiel zwischen 9 und 17 Uhr Eastern Time - und eine niedrigere QPS mitten in der Nacht. Ein häufiges Problem ist, dass eine hohe QPS während der Geschäftszeiten die Entwickler daran hindert, Schemaänderungen (ALTER TABLE) vorzunehmen oder Daten zurückzufüllen.

Daten Alter

Wie ist das Alter der Daten, auf die zugegriffen wird?

DasAlter bezieht sich auf die Reihenfolge der Zugriffe, nicht auf die Zeit. Wenn eine Anwendung innerhalb von 10 Minuten eine Million Zeilen einfügt, ist die erste Zeile die älteste, weil sie die letzte Zeile war, auf die zugegriffen wurde, und nicht, weil sie 10 Minuten alt ist. Wenn die Anwendung die erste Zeile aktualisiert, ist sie die neueste, weil sie die letzte Zeile war, auf die zugegriffen wurde. Und wenn die Anwendung nie wieder auf die erste Zeile zugreift, aber weiterhin auf andere Zeilen, wird die erste Zeile immer älter.

Diese Eigenschaft ist wichtig, weil sie sich auf die Arbeitsmenge auswirkt. Wie im Abschnitt " Größe der Arbeitsmenge" beschrieben, besteht die Arbeitsmenge aus häufig verwendeten Indexwerten und den Primärschlüsselzeilen, auf die sie sich beziehen - was eine lange Umschreibung für häufig verwendete Daten ist - undmacht normalerweise nur einen kleinen Prozentsatz der Tabellengröße aus. MySQL hält so viele Daten wie möglich im Speicher, und das Datenalter hat Einfluss darauf, ob die Daten im Speicher zum Working Set gehören oder nicht. Normalerweise ist das der Fall, denn MySQL ist dank einer Reihe von Algorithmen und Datenstrukturen besonders gut darin, das Working Set im Speicher zu halten.Abbildung 4-4 zeigt eine stark vereinfachte Darstellung des Prozesses.

Das Rechteck in Abbildung 4-4 stellt alle Daten dar. Die Arbeitsmenge ist eine kleine Datenmenge: von der gestrichelten Linie nach oben. Und der Speicher ist kleiner als beides: von der durchgezogenen Linie nach oben. Im MySQL-Jargon werden Daten jung gemacht, wenn auf sie zugegriffen wird. Und wenn nicht auf sie zugegriffen wird, werden sie alt und werden schließlich aus dem Speicher entfernt.

Abbildung 4-4. Datenalterung

Da der Zugriff auf die Daten sie jung und im Speicher hält, bleibt die Arbeitsmenge im Speicher, weil häufig darauf zugegriffen wird. So ist MySQL mit wenig Speicher und vielen Daten sehr schnell.

Der häufige Zugriff auf alte Daten ist in mehrfacher Hinsicht problematisch. Um zu erklären, warum das so ist, muss ich mich in technische Details vertiefen, die den Rahmen dieses Abschnitts sprengen würden . Daten werden in freie Seiten (im Speicher) geladen: Seiten, die noch keine Daten enthalten. (Eine Seite ist eine 16 KB große Einheit des logischen Speichers in InnoDB.) MySQL nutzt den gesamten verfügbaren Speicher, behält aber auch eine bestimmte Anzahl freier Seiten. Wenn es freie Seiten gibt, was normal ist, besteht das Problem nur darin, dass das Lesen von Daten aus der Speicherung langsam ist. Wenn es keine freien Seiten gibt, was nicht normal ist, verschlimmert sich das Problem in dreifacher Hinsicht: Erstens muss MySQL alte Seiten auslagern, die es in einer LRU-Liste (Least Recently Used) erfasst. Zweitens muss MySQL eine alte Seite flushen (persistieren), bevor es sie auslagern kann, wenn sie schmutzig ist (d.h. wenn sie Datenänderungen enthält, die nicht auf der Festplatte gespeichert sind), und das Flushen ist langsam. Drittens bleibt das ursprüngliche Problem bestehen: Das Lesen von Daten aus der Speicherung ist langsam. Kurz gesagt: Das häufige Auslagern alter Daten ist problematisch für die Leistung.

Gelegentlicher Zugriff auf alte Daten ist kein Problem, denn MySQL ist clever: Die Algorithmen, die den Prozess in Abbildung 4-4 steuern, verhindern, dass gelegentlicher Zugriff auf alte Daten neue (junge) Daten beeinträchtigt. Daher solltest du Datenalter und Durchsatz gemeinsam betrachten: Alter und langsamer Zugriff ist wahrscheinlich harmlos, aber alter und schneller Zugriff kann Probleme verursachen.

Das Alter von Daten ist fast unmöglich zu messen.⁴ Zum Glück musst du nur das Alter der Daten schätzen, auf die du zugreifst, und das kannst du, wenn du die Anwendung, die Daten und das Zugriffsmuster kennst. Wenn die Anwendung zum Beispiel Finanztransaktionen speichert, weißt du, dass der Zugriff meist auf neue Daten beschränkt ist: die letzten 90 Tage der Transaktionen. Der Zugriff auf Daten, die älter als 90 Tage sind, sollte selten sein, weil die Transaktionen abgeschlossen und unveränderlich geworden sind. Ein anderer Teil derselben Anwendung, der die Nutzerprofile verwaltet, könnte dagegen häufig auf alte Daten zugreifen, wenn der Prozentsatz der aktiven Nutzer hoch ist. Erinnere dich: Alte Daten sind relativ zum Zugriff, nicht zur Zeit. Das Profil eines Nutzers, der sich vor einer Woche das letzte Mal eingeloggt hat, ist nicht unbedingt zeitlich gesehen alt, aber seine Profildaten sind relativ alt, weil seitdem auf Millionen anderer Profildaten zugegriffen wurde, was bedeutet, dass seine Profildaten aus dem Speicher verdrängt wurden.

Die Kenntnis dieser Eigenschaft ist eine Voraussetzung für das Verständnis von "Partition Data" und Sharding in Kapitel 5.

Datenmodell

Welches Datenmodell weist der Zugang auf?

Obwohl MySQL ein relationaler Datenspeicher ist, wird er häufig mit anderen Datenmodellen verwendet: Key-Value, Dokumente, komplexe Analysen, Graphen usw. Du solltest dir über den nicht-relationalen Zugriff im Klaren sein, denn er ist nicht optimal für MySQL geeignet und kann daher nicht die beste Leistung erbringen. MySQL ist mit anderen Datenmodellen nur bis zu einem gewissen Grad kompatibel. MySQL funktioniert zum Beispiel gut als Key-Value-Datenspeicher, aber RocksDB ist unvergleichlich besser, weil es ein speziell entwickelter Key-Value-Datenspeicher ist.

Die Datenmodelleigenschaft kann nicht wie andere Eigenschaften programmatisch gemessen werden. Stattdessen musst du feststellen, welches Datenmodell der Zugriff aufweist. Das Verb aufweist ist bedeutungsvoll: Der Zugriff kann nur relational sein, weil MySQL der einzige verfügbare Datenspeicher war, als der Zugriff erstellt wurde, aber er weist ein anderes Datenmodell auf, wenn du alle Datenspeicher berücksichtigst. Der Zugriff wird oft in das Datenmodell der verfügbaren Datenspeicher gezwängt. Die bewährte Methode ist jedoch das Gegenteil: Bestimme das ideale Datenmodell für den Zugriff und verwende dann einen Datenspeicher, der für dieses Datenmodell gebaut wurde.

Transaktionsisolierung

Welche Transaktionsisolierung ist für den Zugriff erforderlich?

Isolation ist eine der vier ACID-Eigenschaften : Atomarität, Konsistenz, Isolation und Dauerhaftigkeit. Da die Standard-MySQL-Speicher-Engine InnoDB transaktional ist, wird jede Abfrage standardmäßig in einer Transaktion ausgeführt - auch eine einzelne SELECT -Anweisung.(In Kapitel 8 werden Transaktionen untersucht.) Folglich verfügt der Zugriff über Isolation, ob er sie braucht oder nicht. Diese Eigenschaft klärt, ob Isolation erforderlich ist und wenn ja, auf welchem Niveau.

Wenn ich Ingenieuren diese Frage stelle, fällt die Antwort in eine von drei Kategorien:

Keine

Nein, der Zugriff erfordert keine Isolierung. Er würde auf einer nichttransaktionalen Speicherung korrekt ausgeführt werden. Isolierung ist nur unnötiger Overhead, der aber keine Probleme verursacht oder die Leistung merklich beeinträchtigt.

Standard

Vermutlich ist für den Zugriff eine Isolierung erforderlich, aber es ist nicht bekannt oder unklar, welche Stufe erforderlich ist. Die Anwendung funktioniert korrekt mit der standardmäßigen Transaktionsisolierungsstufe für MySQL: REPEATABLE READ. Es müsste sorgfältig geprüft werden, ob eine andere Isolierungsstufe - oder gar keine Isolierung - korrekt funktionieren würde.

Spezifische

Ja, der Zugriff erfordert ein bestimmtes Isolationslevel, weil er Teil einer Transaktion ist, die gleichzeitig mit anderen Transaktionen ausgeführt wird, die auf dieselben Daten zugreifen. Ohne das spezifische Isolationslevel könnte der Zugriff falsche Versionen der Daten sehen, was ein ernstes Problem für die Anwendung wäre.

Meiner Erfahrung nach ist Standard die häufigste Kategorie, und das macht auch Sinn, denn der standardmäßige Transaktionsisolierungsgrad für MySQL, REPEATABLE READ, ist für die meisten Fälle korrekt. Die Antwort auf diese Eigenschaft sollte jedoch zu Keine oder Spezifisch führen. Wenn der Zugriff keine Isolierung erfordert, dann braucht er möglicherweise keinen transaktionalen Datenspeicher. Wenn der Zugriff jedoch Isolierung erfordert, dann weißt du jetzt genau, welcher Isolierungsgrad und warum.

Andere Datenspeicher haben Transaktionen - auch solche, die nicht grundsätzlich transaktional sind. Der Dokumentenspeicher MongoDB beispielsweise hat in Version 4.0 Multidocument ACID Transaktionen eingeführt. Wenn du weißt, welche Isolationsstufe erforderlich ist und warum, kannst du den Zugriff von MySQL auf einen anderen Datenspeicher übersetzen und verschieben.

Warnung

Transaktionen in anderen Datenspeichern können sich stark von MySQL-Transaktionen unterscheiden, und Transaktionen wirken sich auf andere Aspekte wie das Sperren aus.

Konsistenz lesen

Erfordert der Lesezugriff eine starke oder eine schwache Konsistenz?

Starke Konsistenz (oder stark konsistente Lesevorgänge) bedeutet, dass ein Lesevorgang den aktuellsten Wert zurückgibt. Lesevorgänge auf der Quell-MySQL-Instanz (nicht auf den Replikaten) sind stark konsistent, aber der Transaktionsisolierungsgrad bestimmt den aktuellen Wert. Eine lang laufende Transaktion kann einen alten Wert lesen, aber technisch gesehen ist er der aktuelle Wert in Bezug auf den Transaktionsisolierungsgrad. Kapitel 8 geht auf diese Details ein. Erinnere dich zunächst daran, dass starke Konsistenz die Standardeinstellung (und die einzige Option) auf der MySQL-Quellinstanz ist. Das gilt nicht für alle Datenspeicher.Amazon DynamoDB zum Beispiel ist standardmäßig auf eventuell konsistente Lesevorgänge eingestellt, und stark konsistente Lesevorgänge sind optional, langsamer und teurer.

Eventual Consistency (oder eventually consistent reads) bedeutet, dass ein Read zwar einen alten Wert zurückgibt, aber letztendlich den aktuellen Wert. Reads auf MySQL-Replikaten sind aufgrund der Replikationsverzögerung (Replication Lag) letztendlich konsistent: die Verzögerung zwischen dem Schreiben der Daten auf der Quelle und dem Schreiben (Application) auf dem Replikat. Die Dauer von eventually entspricht in etwa der Replikationsverzögerung, die weniger als eine Sekunde betragen sollte. Replikate, die für Lesezugriffe verwendet werden, nennt man Read Replicas. (Nicht alle Replikate dienen für Lesezugriffe; einige dienen nur der Hochverfügbarkeit oder anderen Zwecken).

In der Welt von MySQL ist es üblich, dass alle Zugriffe auf die Quellinstanz erfolgen, wodurch alle Lesevorgänge standardmäßig stark konsistent sind. Es ist aber auch üblich, dass Lesevorgänge keine starke Konsistenz erfordern, vor allem, wenn die Replikationsverzögerung unter einer Sekunde liegt. Wenn eine eventuelle Konsistenz akzeptabel ist, ist das Offloading von Lesevorgängen (siehe "Offload von Lesevorgängen") möglich.

Gleichzeitigkeit

Wird auf die Daten gleichzeitig zugegriffen?

Null-Gleichzeitigkeit bedeutet, dass der Zugriff nicht dieselben Daten zur gleichen Zeit liest (oder schreibt). Wenn er dieselben Daten zu verschiedenen Zeiten liest (oder schreibt), ist das ebenfalls Null-Gleichzeitigkeit. Ein Zugriffsmuster, das einmalige Zeilen einfügt, hat zum Beispiel Null-Gleichzeitigkeit.

Hohe Gleichzeitigkeit bedeutet, dass der Zugriff häufig die gleichen Daten zur gleichen Zeit liest (oder schreibt).

Die Gleichzeitigkeit gibt an, wie wichtig (oder lästig) Zeilensperren für den Schreibzugriff sind. Es überrascht nicht, dass die Zeilensperrenkonkurrenz umso größer ist, je höher die Schreibgleichzeit für dieselben Daten ist. Die Zeilensperrenkonkurrenz ist akzeptabel, solange die dadurch verursachte längere Antwortzeit akzeptabel ist. Sie wird inakzeptabel, wenn sie zu Wartezeiten auf die Sperren führt, d.h. zu einem Abfragefehler, den die Anwendung behandeln und wiederholen muss. Wenn das passiert, gibt es nur zwei Lösungen: die Gleichzeitigkeit verringern (das Zugriffsmuster ändern) oder die Aufteilung (siehe Kapitel 5), um die Schreibvorgänge zu reduzieren.

Die Gleichzeitigkeit gibt auch Aufschluss darüber, wie gut ein Cache für Lesezugriffe geeignet ist. Wenn dieselben Daten mit hoher Gleichzeitigkeit gelesen, aber nur selten geändert werden, eignen sie sich gut für einen Cache. Ich erkläre das in "Offload Reads".

Wie in "Data Age" beschrieben , ist es fast unmöglich, die Gleichzeitigkeit zu messen, aber du musst die Gleichzeitigkeit nur schätzen, was du mit deinem Verständnis der Anwendung, der Daten und des Zugriffsmusters tun kannst.

Zeile Zugang

Wie wird auf die Zeilen zugegriffen? Es gibt drei Arten des Zeilenzugriffs:

Punkt Zugang

Eine einzelne Reihe

Zugang zum Bereich

Geordnete Zeilen zwischen zwei Werten

Zufälliger Zugriff

Mehrere Reihen in beliebiger Reihenfolge

Bei Verwendung des englischen Alphabets(A bis Z) ist der Punktzugriff ein beliebiges einzelnes Zeichen(z. B.A); der Bereichszugriff ist eine beliebige Anzahl von Zeichen in der Reihenfolge(ABC oder AC, wenn B nicht existiert); und der Zufallszugriff ist eine beliebige Anzahl von Zufallszeichen(ASMR).

Diese Eigenschaft scheint einfach zu sein, aber sie ist aus zwei Gründen wichtig für den Schreibzugriff:

• Lückensperren: Bereichsschreibvorgänge und Schreibvorgänge mit wahlfreiem Zugriff, die nicht eindeutige Indizes verwenden, verschärfen den Konflikt um Zeilensperren aufgrund von Lückensperren."Zeilensperren" geht ins Detail.

• Deadlocks: Schreibvorgänge mit wahlfreiem Zugriff sind ein Grund für Deadlocks, d.h. wenn zwei Transaktionen Zeilensperren halten, die die andere Transaktion benötigt. MySQL erkennt und löst Deadlocks auf, aber sie beeinträchtigen die Leistung (MySQL beendet eine Transaktion, um den Deadlock aufzulösen) und sind ärgerlich.

DerZeilenzugriff ist ebenfalls wichtig für die Planung des Shardings. Ein effektives Sharding erfordert, dass die Zugriffsmuster einen einzigen Shard verwenden. Der Punktzugriff funktioniert am besten mit Sharding: eine Zeile, ein Shard. Bereichs- und Zufallszugriff funktionieren mit Sharding, erfordern aber eine sorgfältige Planung, damit die Vorteile des Shardings nicht durch den Zugriff auf zu viele Shards zunichte gemacht werden.Kapitel 5 befasst sich mit Sharding.

Ergebnismenge

Kann den Zugriff gruppieren, sortieren oder die Ergebnismenge einschränken?

Diese Eigenschaft ist leicht zu beantworten: Hat der Zugriff eine GROUP BY, ORDER BY oder LIMIT Klausel? Jede dieser Klauseln hat Auswirkungen darauf, ob und wie der Zugriff in einem anderen Datenspeicher geändert oder ausgeführt werden kann."Datenzugriff" deckt mehrere Änderungen ab. Optimiere zumindest Zugriffe, die Zeilen gruppieren oder sortieren. Die Begrenzung von Zeilen ist kein Problem - sie ist ein Vorteil - aber sie funktioniert in anderen Datenspeichern anders. Ebenso können andere Datenspeicher die Gruppierung oder Sortierung von Zeilen unterstützen oder nicht.

Änderungen in der Anwendung

Du musst die Anwendung ändern, um ihre Datenzugriffsmuster zu ändern. Die in diesem Abschnitt vorgestellten Änderungen sind üblich, aber nicht erschöpfend. Sie sind sehr effektiv, hängen aber auch stark von der Anwendung ab: Einige könnten funktionieren, andere vielleicht nicht. (Mit Ausnahme der ersten Änderung, "Audit the Code": Das funktioniert immer.) Daher ist jede Änderung eine Idee, die mit deinem Team weiter diskutiert und geplant werden muss.

Alle Änderungen mit Ausnahme der ersten haben eine subtile Gemeinsamkeit: Sie erfordern zusätzliche Infrastruktur. Ich weise darauf hin, um dich mental auf die Tatsache vorzubereiten, dass du zusätzlich zu den Codeänderungen auch Änderungen an der Infrastruktur vornehmen musst. Wie zu Beginn des Kapitels "Verbesserung der Antwortzeit von Abfragen" vorausgesagt , erfordert die indirekte Optimierung von Abfragen einen größeren Aufwand. Während das Ändern von Daten(Kapitel 3) potenziell Arbeit ist, ist das Ändern von Zugriffsmustern mit Sicherheit Arbeit. Aber der Aufwand lohnt sich, denn diese Änderungen sind per definitionem transformativ: Wie die Anwendung von einem Toyota zu einem Ferrari wird.

Du fragst dich vielleicht: Wenn diese Änderungen so mächtig sind, warum machst du sie nicht zuerst - bevor du Abfragen und Daten optimierst? Da der Schwerpunkt dieses Buches auf der effizienten Leistung von MySQL liegt, habe ich geplant, die Reise mit den Änderungen an der Anwendung zu beenden, da sie den größten Aufwand erfordern. Im Gegensatz dazu erfordern die direkte Optimierung von Abfragen(Kapitel 2) und die Änderungen an den Daten(Kapitel 3) weit weniger Aufwand, und erstere lösen viele - wenn nicht sogar die meisten - Leistungsprobleme. Wenn du aber die Zeit und Energie hast, dich direkt in die Umgestaltung der Anwendung zu stürzen, hast du meine Unterstützung. Erinnere dich einfach an die Lektion aus Kapitel 2: Indizes bieten den größten und besten Hebel. Schlechte Abfragen ruinieren wunderbare Zugriffsmuster; oder, um den bekannten MySQL-Experten Bill Karwin zu zitieren:

Deine nicht optimierten Abfragen bringen den Datenbankserver um.

Den Kodex prüfen

Du wirst überrascht sein, wie lange der Code existieren und laufen kann, ohne dass ein Mensch darauf achtet. In gewissem Sinne ist das ein Zeichen für guten Code: Er funktioniert einfach und verursacht keine Probleme. Aber "verursacht keine Probleme" bedeutet nicht unbedingt, dass der Code effizient oder sogar erforderlich ist.

Du musst nicht den gesamten Code prüfen (obwohl das keine schlechte Idee ist), sondern nur den Code, der auf die Datenbank zugreift. Schau dir natürlich die eigentlichen Abfragen an, aber betrachte auch den Kontext: die Geschäftslogik, die die Abfragen erfüllen. Vielleicht findest du einen anderen und besseren Weg, um dieselbe Geschäftslogik zu erfüllen.

Bei den Abfragen solltest du auf Folgendes achten:

• Abfragen, die nicht mehr benötigt werden

• Abfragen, die zu häufig ausgeführt werden

• Abfragen, die zu schnell oder zu oft wiederholt werden

• Große oder komplexe Abfragen - können sie vereinfacht werden?

Wenn der Code ORM - oder irgendeine andere Art von Datenbankabstraktion - verwendet, überprüfe die Voreinstellungen und die Konfiguration. Einige Datenbankbibliotheken führen nach jeder Abfrage SHOW WARNINGS aus, um auf Warnungen zu prüfen. Das ist normalerweise kein Problem, aber auch ziemlich verschwenderisch. Überprüfe auch die Voreinstellungen, die Konfiguration und die Versionshinweise des Treibers. Der MySQL-Treiber für die Programmiersprache Go hat sich zum Beispiel im Laufe der Jahre sehr gut entwickelt, daher sollte der Go-Code die neueste Version verwenden.

Überprüfe den Code indirekt, indem du das Abfrageprofil verwendest, um zu sehen, welche Abfragen die Anwendung ausführt - es ist keine Abfrageanalyse erforderlich; verwende einfach das Abfrageprofil als Prüfwerkzeug. Es ist ziemlich üblich, unbekannte Abfragen im Profil zu sehen. Angesichts der Aussage "MySQL tut nichts" stammen die unbekannten Abfragen wahrscheinlich aus der Anwendung - entweder aus deinem Anwendungscode oder einer Datenbankabstraktion wie ORM - aber es gibt noch eine andere Möglichkeit: Ops.Ops bezieht sich auf denjenigen, der den Datenspeicher betreibt und pflegt: DBAs, Cloud-Provider usw. Wenn du unbekannte Abfragen findest und dir sicher bist, dass sie nicht von der Anwendung ausgeführt werden, solltest du dich an den Betreiber des Datenspeichers wenden.

Tipp

Um die Überprüfung von Abfragen zu erleichtern, fügen Sie den Abfragen in /* SQL comments */ Anwendungsmetadaten hinzu. SELECT.../* file:app.go line:75 */ verrät zum Beispiel, woher die Abfrage im Quellcode der Anwendung stammt. SQL-Kommentare werden aus Digest-Texten entfernt, daher muss Ihr Abfrage-Metrik-Tool Beispiele enthalten (siehe Beispiel 1-1) oder Metadaten aus SQL-Kommentaren auslesen.

Zuletzt und am meisten übersehen: Überprüfe das MySQL-Fehlerprotokoll. Es sollte ruhig sein: keine Fehler, Warnungen usw. Wenn es verrauscht ist, schau dir die Fehler an, denn sie deuten auf eine Vielzahl von Problemen hin: Netzwerk, Authentifizierung, Replikation, MySQL-Konfiguration, nicht-deterministische Abfragen usw. Diese Arten von Problemen sollten unglaublich selten sein, also ignoriere sie nicht.

Offload-Lesungen

Standardmäßig ( ) bedient eine einzelne MySQL-Instanz, die Quelle, alle Lese- und Schreibvorgänge. In der Produktion sollte die Quelle mindestens ein Replikat haben: eine andere MySQL-Instanz, die alle Schreibvorgänge von der Quelle repliziert.Kapitel 7 befasst sich mit der Replikation, aber ich erwähne sie hier, um die Diskussion über die Auslagerung von Lesevorgängen vorzubereiten.

Die Leistung kann verbessert werden, indem Lesevorgänge von der Quelle ausgelagert werden. Diese Technik nutzt MySQL-Replikate oder Cache-Server, um Lesevorgänge auszuliefern. (Mehr zu diesen beiden Techniken in Kürze.) Sie verbessert die Leistung in zweierlei Hinsicht: Erstens wird die Quelle entlastet, was Zeit und Systemressourcen freisetzt, um die verbleibenden Abfragen schneller auszuführen. Zweitens wird die Antwortzeit für die ausgelagerten Lesevorgänge verbessert, da die Replikate oder Caches, die diese Lesevorgänge ausliefern, nicht mit Schreibvorgängen belastet werden. Diese Technik ist für beide Seiten von Vorteil und wird häufig eingesetzt, um Lesevorgänge mit hohem Durchsatz und geringer Latenzzeit zu erreichen.

Daten, die aus einem Replikat oder Cache gelesen werden, sind nicht garantiert aktuell (der neueste Wert), da es bei der MySQL-Replikation und beim Schreiben in einen Cache eine unvermeidbare Verzögerung gibt. Daher sind Daten aus Replikaten und Caches letztendlich konsistent: Sie werden nach einer (hoffentlich sehr) kurzen Verzögerung aktuell. Nur die Daten in der Quelle sind aktuell (ungeachtet der Transaktionsisolationsebenen). Bevor du also Daten aus einem Replikat oder Cache ausliest, muss Folgendes gelten: Das Lesen von Daten, die nicht mehr aktuell sind (eventuell konsistent), ist akzeptabel und verursacht keine Probleme für die Anwendung oder ihre Benutzer.

Ich habe mehr als einmal erlebt, dass Entwickler darüber nachgedacht und festgestellt haben: "Ja, es ist in Ordnung, wenn die Anwendung leicht veraltete Werte zurückgibt." Ein häufig genanntes Beispiel ist die Anzahl der "Likes" oder "Up-Votes" für einen Beitrag oder ein Video: Wenn der aktuelle Wert 100 ist, der Cache aber 98 zurückgibt, ist das nahe genug - vor allem, wenn der Cache den aktuellen Wert ein paar Millisekunden später zurückgibt. Wenn diese Aussage auf deine Anwendung nicht zutrifft, solltest du diese Technik nicht anwenden.

Zusätzlich zu der Anforderung, dass eventuelle Konsistenz akzeptabel ist, dürfen ausgelagerte Lesevorgänge nicht Teil einer Multi-Statement-Transaktion sein. Multi-Statement-Transaktionen müssen in der Quelle ausgeführt werden.

Warnung

Stelle immer sicher, dass Offload-Lesevorgänge mit eventueller Konsistenz akzeptabel sind und nicht Teil einer Multi-Statement-Transaktion sind.

Bevor du Reads von Replikaten oder Caches bedienst, solltest du dich gründlich mit der Frage auseinandersetzen, wie die Anwendung abläuft, wenn die Replikate oder Caches offline sind.

Die einzige falsche Antwort auf diese Frage ist, es nicht zu wissen. Sobald eine Anwendung Lesevorgänge auslagert, hängt sie in der Regel stark von den Replikaten oder Caches ab, um diese Lesevorgänge zu bedienen. Es ist unbedingt notwendig, die Anwendung so zu konzipieren, zu implementieren und zu testen, dass sie auch dann läuft, wenn die Replikate oder Caches offline sind. Degraded bedeutet, dass die Anwendung zwar läuft, aber deutlich langsamer ist, die Client-Anfragen begrenzt werden oder sie nicht voll funktionsfähig ist, weil einige Teile offline sind oder gedrosselt werden. Solange die Anwendung nicht komplettoffline ist und nicht reagiert, ohne dass es zu einer menschenfreundlichen Fehlermeldung kommt, hast du gute Arbeit geleistet, damit die Anwendung degraded läuft.

Ein letzter Punkt, bevor wir über den Einsatz von MySQL-Replikaten und Cache-Servern sprechen: Lade nicht alle Reads aus. Das Auslagern von Reads verbessert die Leistung, indem du keine Zeit auf der Quelle für Arbeiten verschwendest, die ein Replikat oder ein Cache erledigen kann. Beginne daher mit dem Auslagern langsamer (zeitaufwändiger) Reads: Reads, die als langsame Abfragen im Abfrageprofil auftauchen. Diese Technik ist sehr wirkungsvoll, daher solltest du die Reads nach und nach auslagern, denn es kann sein, dass du nur einige wenige auslagern musst, um die Leistung deutlich zu verbessern.

MySQL Replikat

Die Verwendung von MySQL-Replikaten zur Bereitstellung von Reads ist üblich, da jedes produktive MySQL-Setup bereits über mindestens ein Replikat verfügen sollte und mehr als zwei Replikate üblich sind. Da die Infrastruktur (die Replikate) bereits vorhanden ist, musst du nur noch den Code ändern, um die Replikate für ausgelagerte Reads statt der Quelle zu verwenden.

Bevor wir erklären, warum Replikate den Cache-Servern vorzuziehen sind, müssen wir eine wichtige Frage klären: Kann die Anwendung die Replikate nutzen? Da Replikate für Hochverfügbarkeit eingesetzt werden, kann es sein, dass der MySQL-Verwalter nicht will, dass die Replikate Lesevorgänge durchführen. Das solltest du unbedingt herausfinden, denn wenn nicht, können die Replikate ohne Vorankündigung für Wartungsarbeiten offline genommen werden.

Vorausgesetzt, deine Replikate können zum Lesen verwendet werden, sind sie aus drei Gründen den Cache-Servern vorzuziehen:

Verfügbarkeit

Da Replikate die Grundlage für Hochverfügbarkeit sind, sollten sie die gleiche Verfügbarkeit haben wie die Quelle - zum Beispiel 99,95 % oder 99,99 %. Das macht Replikate fast sorgenfrei: Wer MySQL verwaltet, verwaltet auch die Replikate.

Flexibilität

Im vorigen Abschnitt habe ich gesagt, dass du damit beginnen solltest, langsame (zeitaufwändige) Lesevorgänge auszulagern. Für Caches gilt das besonders, weil der Cache-Server wahrscheinlich nur über begrenzte CPU- und Speicherressourcen verfügt, die nicht für triviale Lesevorgänge verschwendet werden sollten. Im Gegensatz dazu sollten Replikate, die für Hochverfügbarkeit genutzt werden, über die gleiche Hardware wie die Quelle verfügen, sodass sie Ressourcen übrig haben. Das Auslagern trivialer Lesevorgänge auf ein Replikat spielt keine so große Rolle, daher die Flexibilität bei der Auswahl der auszulagernden Vorgänge. Wenn du reine Lese-Replikate - also Replikate , die nicht für Hochverfügbarkeit genutzt werden - mit weniger leistungsfähiger Hardware hast, solltest du keine Ressourcen für triviale Lesevorgänge verschwenden. Das ist in der Cloud häufiger der Fall, weil es einfach ist, Lese-Replikate mit großer Speicherung, aber geringer CPU und geringem Speicher bereitzustellen (um Geld zu sparen).

Einfachheit

Die Anwendung muss nichts tun, um die Replikate mit der Quelle synchron zu halten - das liegt in der Natur eines Replikats. Bei einem Cache muss die Anwendung Aktualisierungen, Ungültigkeitserklärungen und (möglicherweise) Löschungen verwalten. Das wirklich Einfache ist jedoch, dass Replikate keine Abfrageänderungen erfordern: Die Anwendung kann genau dieselben SQL-Anweisungen auf einem Replikat ausführen.

Das sind drei zwingende Gründe, MySQL-Replikate den Cache-Servern vorzuziehen, aber letztere haben einen wichtigen Vorteil: Ein Cache-Server kann unglaublich schneller sein als MySQL.

Cache-Server

Ein Cache-Server ist nicht mit SQL, Transaktionen oder dauerhafter Speicherung belastet. Das macht ihn unglaublich schneller als MySQL, aber er erfordert auch mehr Arbeit in der Anwendung, um ihn richtig zu nutzen. Wie im vorigen Abschnitt erwähnt, muss die Anwendung Cache-Aktualisierungen, Invalidierung und (möglicherweise) Räumung verwalten. Außerdem braucht die Anwendung ein Datenmodell, das mit dem Cache zusammenarbeitet, in der Regel ein Key-Value-Modell. Der zusätzliche Aufwand lohnt sich, denn praktisch nichts ist schneller als ein Cache.Memcached und Redis sind zwei beliebte und weit verbreitete Cache-Server.

Hinweis

Wenn du hörst, dass MySQL einen eingebauten Abfrage-Cache hat: Vergiss ihn und benutze ihn nie. Er wurde mit MySQL 5.7.20 veraltet und mit MySQL 8.0 entfernt.

Caching ist ideal für Daten, auf die häufig zugegriffen wird, die aber selten geändert werden. Bei MySQL Replikaten ist das kein Thema, da alle Änderungen repliziert werden, aber ein Cache speichert nur das, was die Anwendung in ihn einspeist. Ein schlechtes Beispiel ist der aktuelle Unix-Zeitstempel in Sekunden: Er ändert sich ständig. Die Ausnahme in einem solchen Fall: wenn die Häufigkeit des Zugriffs deutlich größer ist als die Häufigkeit der Änderung. Wenn zum Beispiel der aktuelle Unix-Zeitstempel in Sekunden eine Million Mal pro Sekunde angefordert wird, könnte das Zwischenspeichern des aktuellen Zeitstempels sinnvoll sein. Ein gutes Beispiel ist das aktuelle Jahr: Es ändert sich nur selten. Die Ausnahme in einem guten Fall wie diesem: wenn die Zugriffshäufigkeit deutlich geringer ist als die Änderungshäufigkeit. Wenn das aktuelle Jahr zum Beispiel nur einmal pro Sekunde angefordert wird, dann bringt ein Cache fast nichts, weil 1 QPS für diesen Datenzugriff keinen Unterschied macht.

Ein Wort der Vorsicht bei der Verwendung eines Caches: Entscheide, ob der Cache ephemer oder dauerhaft ist. Auch dies ist bei MySQL-Replikaten kein Thema, da sie immer dauerhaft sind, aber einige Cache-Server können beides sein. Wenn der Cache wirklich ephemer ist, solltest du das Äquivalent von TRUNCATE TABLE auf den Cache-Daten durchführen können, ohne die Anwendung zu beeinträchtigen. Du musst auch entscheiden, wie der ephemere Cache neu aufgebaut wird. Einige Anwendungen bauen den Cache bei einem Cache-Miss neu auf: wenn die angeforderten Daten nicht im Cache sind. Andere Anwendungen haben einen externen Prozess, um den Cache aus einer anderen Datenquelle neu aufzubauen (z. B. das Laden des Caches mit Bildern, die in Amazon S3 gespeichert sind). Und manche Anwendungen verlassen sich so stark auf den Cache oder der Cache ist so groß, dass ein Neuaufbau nicht möglich ist. Für solche Anwendungen ist ein dauerhafter Cache erforderlich. In beiden Fällen - ephemeral oder dauerhaft - solltest du deine Entscheidung testen, um sicherzustellen, dass die Anwendung wie erwartet funktioniert, wenn der Cache fehlschlägt und wiederhergestellt wird.

Enqueue Writes

Verwende eine Warteschlange, um den Schreibdurchsatz zu stabilisieren.Abbildung 4-5 zeigt einen instabil-erratischen Schreibdurchsatz, der über 30.000 QPS ansteigt und unter 10.000 QPS sinkt.

Abbildung 4-5. Erratischer Schreibdurchsatz

Selbst wenn die Leistung bei instabilem Schreibdurchsatz derzeit akzeptabel ist, ist das kein Erfolgsrezept, denn der instabile Durchsatz verschlechtert sich bei der Skalierung - er stabilisiert sich nie spontan. (Und wenn du dich an Abbildung 4-3 aus "Leistung destabilisiert sich am Limit" erinnerst , ist ein flacher Wert nicht stabil.) Mit einer Warteschlange kann die Anwendung Änderungen (Schreibvorgänge) mit einer stabilen Rate verarbeiten, wie in Abbildung 4-6 gezeigt.

Abbildung 4-6. Stabiler Schreibdurchsatz

Die wahre Stärke des Enqueueing und des stabilen Schreibdurchsatzes liegt darin, dass sie es der Anwendung ermöglichen, anmutig und vorhersehbar auf einen Ansturm zu reagieren: eine Flut von Anfragen, die die Anwendung oder die Datenbank oder beide überfordert. Stell dir zum Beispiel vor, dass die Anwendung normalerweise 20.000 Änderungen pro Sekunde verarbeitet. Aber sie geht für fünf Sekunden offline, was zu 100.000 ausstehenden Änderungen führt. In dem Moment, in dem die Anwendung wieder online geht, wird sie mit den 100.000 ausstehenden Änderungen konfrontiert - eine donnernde Herde - plus den normalen 20.000 Änderungen für die aktuelle Sekunde. Wie werden die Anwendung und MySQL mit der donnernden Herde umgehen?

Bei einer Warteschlange wirkt sich die donnernde Herde nicht auf MySQL aus: Sie geht in die Warteschlange, und MySQL verarbeitet die Änderungen wie gewohnt. Der einzige Unterschied ist, dass einige Änderungen später als üblich stattfinden. Solange der Schreibdurchsatz stabil ist, kannst du die Anzahl der Warteschlangenkonsumenten erhöhen, um die Warteschlange schneller zu verarbeiten.

Ohne Warteschlange lehrt die Erfahrung, dass eines von zwei Dingen passieren wird: Entweder du hast großes Glück und MySQL wird mit der tobenden Herde fertig, oder nicht. Verlass dich nicht auf Glück. MySQL drosselt die Ausführung von Abfragen nicht, also wird es versuchen, alle Abfragen auszuführen, wenn die tobende Herde auftritt. (MySQL Enterprise Edition, Percona Server und MariaDB Server verfügen jedoch über einen Thread-Pool, der die Anzahl der gleichzeitig ausgeführten Abfragen begrenzt und somit als Drossel wirkt.) Das funktioniert nie, weil CPU, Speicher und Festplatten-E/A von Natur aus begrenzt sind - ganz zu schweigen vom universellen Skalierbarkeitsgesetz(Gleichung 4-1). Trotzdem versucht MySQL es immer, weil es unglaublich ehrgeizig und ein bisschen tollkühn ist.

Diese Technik bietet weitere Vorteile, die es wert sind, sie zu implementieren. Ein Vorteil ist, dass sie die Anwendung von der MySQL-Verfügbarkeit entkoppelt: Die Anwendung kann Änderungen annehmen, wenn MySQL offline ist. Ein weiterer Vorteil ist, dass sie genutzt werden kann, um verlorene oder aufgegebene Änderungen wiederherzustellen. Angenommen, eine Änderung erfordert mehrere Schritte, von denen einige langwierig oder unzuverlässig sind. Wenn ein Schritt fehlschlägt oder eine Zeitüberschreitung auftritt, kann die Anwendung die Änderung erneut in die Warteschlange stellen und es erneut versuchen. Ein dritter Vorteil ist die Möglichkeit, Änderungen wiederzugeben, wenn die Warteschlange ein Ereignisstrom wie Kafka ist.

Tipp

Für schreibintensive Anwendungen ist das Einreihen in eine Warteschlange die bewährte Methode und praktisch eine Voraussetzung. Investiere die Zeit, um eine Warteschlange zu lernen und zu implementieren.

Daten aufteilen

Nach Kapitel 3 sollte es keine Überraschung sein, dass es einfacher ist, die Leistung mit weniger Daten zu verbessern. Daten sind wertvoll für dich, aber für MySQL sind sie totes Gewicht . Wenn du Daten nicht löschen oder archivieren kannst (siehe "Daten löschen oder archivieren"), dann solltest du die Daten zumindest partitionieren (physisch trennen).

Zunächst wollen wir kurz auf die MySQL-Partitionierung eingehen und sie dann beiseite legen. MySQL unterstützt zwar Partitionierung, aber sie erfordert eine besondere Handhabung. Sie ist nicht einfach zu implementieren oder zu warten, und einige MySQL-Tools von Drittanbietern unterstützen sie nicht. Daher empfehle ich nicht, MySQL-Partitionierung zu verwenden.

Die nützlichste, gebräuchlichste und für Anwendungsentwickler am einfachsten umzusetzende Art der Datenpartitionierung ist die Trennung von heißen und kalten Daten: Daten, auf die häufig bzw. selten zugegriffen wird. Die Trennung von heißen und kalten Daten ist eine Kombination aus Partitionierung und Archivierung. Die Partitionierung erfolgt nach Zugriff und die Archivierung, indem die Daten, auf die selten zugegriffen wird (kalte Daten), aus dem Zugriffspfad der Daten, auf die häufig zugegriffen wird (heiße Daten), entfernt werden.

Nehmen wir ein Beispiel: eine Datenbank, in der Zahlungen gespeichert werden. Die heißen Daten sind die Zahlungen der letzten 90 Tage, und zwar aus zwei Gründen: Erstens ändern sich die Zahlungen nach der Abrechnung normalerweise nicht mehr, aber es gibt Ausnahmen wie Erstattungen, die später vorgenommen werden können. Nach einem bestimmten Zeitraum sind die Zahlungen jedoch abgeschlossen und können nicht mehr geändert werden. Zweitens zeigt die Anwendung nur die Zahlungen der letzten 90 Tage an. Um ältere Zahlungen zu sehen, müssen die Nutzer/innen frühere Kontoauszüge einsehen. Die kalten Daten sind die Zahlungen nach 90 Tagen. Für ein Jahr sind das 275 Tage, also etwa 75 % der Daten. Warum sollten 75 % der Daten in einem Transaktionsdatenspeicher wie MySQL untätig bleiben? Das ist eine rhetorische Frage: Es gibt keinen guten Grund.

Die Trennung von heißen und kalten Daten ist in erster Linie eine Optimierung für erstere. Die Speicherung kalter Daten an einem anderen Ort hat drei unmittelbare Vorteile: Es passen mehr heiße Daten in den Speicher, Abfragen verschwenden keine Zeit mit der Prüfung kalter Daten und Operationen (wie Schemaänderungen) sind schneller. Die Trennung von heißen und kalten Daten ist auch eine Optimierung für letztere, wenn sie völlig unterschiedliche Zugriffsmuster haben. Im vorangegangenen Beispiel könnten alte Zahlungen nach Monaten in einem einzigen Datenobjekt gruppiert werden, das nicht mehr für jede Zahlung eine Zeile benötigt. In diesem Fall könnte ein Dokumentenspeicher oder ein Key-Value-Store besser für die Speicherung und den Zugriff auf die kalten Daten geeignet sein.

Zumindest kannst du kalte Daten in einer anderen Tabelle derselben Datenbank archivieren. Das ist relativ einfach mit einer kontrollierten INSERT...SELECT Anweisung, um aus der heißen Tabelle auszuwählen und in die kalte Tabelle einzufügen. Dann DELETE die archivierten kalten Daten aus der heißen Tabelle. Um die Konsistenz zu gewährleisten, verpackst du das Ganze in eine Transaktion. Siehe "Daten löschen oder archivieren".

Diese Technik kann auf viele verschiedene Arten implementiert werden, vor allem im Hinblick darauf, wie und wo die kalten Daten gespeichert werden und auf sie zugegriffen wird. Aber im Grunde ist sie sehr einfach und hocheffektiv: Man verschiebt die Daten, auf die nur selten zugegriffen wird (kalte Daten), aus dem Zugriffspfad der Daten, auf die häufig zugegriffen wird (heiße Daten), um die Leistung für letztere zu verbessern.

Verwende kein MySQL

Ich möchte der aktuellen Diskussion über Anwendungsänderungen einen bildlichen Schlusspunkt setzen: Die wichtigste Änderung besteht darin, MySQL nicht zu verwenden, wenn es eindeutig nicht der beste Datenspeicher für die Zugriffsmuster ist. Manchmal ist es sehr leicht zu erkennen, wann MySQL nicht die beste Wahl ist. In den vorangegangenen Kapiteln habe ich zum Beispiel auf eine Abfrage mit der Last 5.962 verwiesen. Diese Abfrage wird verwendet, um Eckpunkte in einem Graphen auszuwählen. Es ist klar, dass eine relationale Datenbank nicht die beste Wahl für Graphdaten ist; die beste Wahl ist ein Graphdatenspeicher. Sogar ein Key-Value-Store wäre besser, denn Graphdaten haben nichts mit relationalen Datenbankkonzepten wie Normalisierung und Transaktionen zu tun. Ein weiteres einfaches und gängiges Beispiel sind Zeitreihendaten: Eine zeilenorientierte Transaktionsdatenbank ist nicht die beste Wahl; die beste Wahl ist eine Zeitreihendatenbank oder vielleicht ein Spaltenspeicher.

MySQL lässt sich für eine Vielzahl von Daten und Zugriffsmustern erstaunlich gut skalieren, auch wenn es nicht die beste Wahl ist. Aber nimm das nicht als selbstverständlich hin: Sei der erste Ingenieur in deinem Team, der sagt: "Vielleicht ist MySQL nicht die beste Wahl." Das ist okay: Wenn ich das sagen kann, dann kannst du das auch. Wenn dir jemand Kummer macht, sag ihm, dass ich deine Entscheidung unterstütze, das beste Werkzeug für die Aufgabe zu verwenden.

Abgesehen davon ist MySQL fantastisch. Bitte beende zumindest dieses Kapitel und das nächste, Kapitel 5, bevor du bei MySQL nach links wischst.

Bessere, schnellere Hardware?

"Bessere, schnellere Hardware!" warnt davor, die Hardware aufzustocken, um die Leistung zu erhöhen. Aber der erste Satz dieses Abschnitts ist sorgfältig formuliert: "Wenn die Leistung von MySQL nicht akzeptabel ist, sollten Sie nicht mit einer Aufstockung beginnen..." Das Schlüsselwort in diesem Satz ist " beginnen", und die entscheidende Frage, die sich daraus ergibt, ist: Wann ist der richtige Zeitpunkt, um die Hardware aufzustocken?

Diese Frage ist schwer zu beantworten, weil sie von einer Kombination von Faktoren abhängt: Abfragen, Indizes, Daten, Zugriffsmuster und wie diese die aktuelle Hardware ausnutzen. Nehmen wir an, die Anwendung hat ein sehr ineffizientes Zugriffsmuster: Sie nutzt MySQL als Warteschlange und fragt es sehr schnell von vielen Anwendungsinstanzen ab. Ich würde die Hardware nicht skalieren, bevor ich nicht das Zugriffsmuster behoben habe. Aber manchmal haben Ingenieure nicht den Luxus der Zeit, solche Anwendungsänderungen vorzunehmen.

Tabelle 4-2 ist eine Checkliste, mit der du feststellen kannst, ob es an der Zeit ist, die Hardware aufzustocken. Wenn du alle Punkte in Spalte 1 und mindestens zwei Punkte in Spalte 2 ankreuzen kannst, ist das ein deutliches Zeichen dafür, dass es an der Zeit ist, die Hardware aufzustocken.

Tabelle 4-2. Checkliste für das Hardware-Upgrade

1. Prüfe alle	2. Prüfe mindestens zwei
☐ Die Reaktionszeit ist zu hoch	☐ Die CPU-Auslastung ist größer als 80%.
☐ Langsame Abfragen wurden optimiert	Die Anzahl der laufenden Threads ist größer als die Anzahl der CPU-Kerne.
☐ Die Daten wurden gelöscht oder archiviert	☐ Der Speicherplatz beträgt weniger als 10% der Gesamtdatengröße
☐ Die Zugriffsmuster wurden überprüft und optimiert	☐ Die IOPS-Auslastung der Speicherung ist größer als 80%.

Spalte 1 ist eine ungeschminkte Wiederholung von allem, was seit Kapitel 1 passiert ist, aber auch eine eindeutige Rechtfertigung dafür, Geld für die Aufrüstung der Hardware auszugeben. Spalte 2 erfordert mindestens zwei Überprüfungen, weil Hardware zusammenarbeitet. Wenn nur ein Teil der Hardware stark ausgelastet ist, ist das keine Garantie für ein Problem oder eine schlechte Leistung. Stattdessen ist es wahrscheinlich ein gutes Zeichen: Du nutzt diese Hardware voll aus. Wenn aber eine Hardware überlastet ist, wirkt sich das in der Regel auch auf andere Hardware aus. Wenn zum Beispiel eine langsame Speicherung zu einem Rückstau von Abfragen führt, der wiederum einen Rückstau von Clients verursacht, der wiederum eine hohe CPU-Auslastung verursacht, weil MySQL versucht, zu viele Threads auszuführen. Deshalb sind für Spalte 2 zwei Prüfungen erforderlich.

Die Werte in Spalte 2 sollten konstant größer oder kleiner als die vorgeschlagenen Schwellenwerte sein. Gelegentliche Ausschläge und Einbrüche sind normal.

Die maximale Anzahl an Speicher-IOPS wird durch das Speichergerät bestimmt, wenn du deine eigene Hardware verwendest. Wenn du dir nicht sicher bist, überprüfe die Gerätespezifikationen oder frage die Techniker, die die Hardware verwalten. In der Cloud werden die IOPS der Speicherung zugewiesen oder bereitgestellt, sodass es normalerweise einfacher ist, das Maximum zu bestimmen, da du die IOPS kaufst. Wenn du dir nicht sicher bist, überprüfe die MySQL-Speichereinstellungen oder frage den Cloud-Provider."IOPS" zeigt an, welche Metriken die IOPS der Speicherung anzeigen.

Die IOPS-Auslastung der Speicherung hängt zusätzlich davon ab, ob die Anwendung lese- oder schreiblastig ist (siehe "Lesen/Schreiben"):

Leselastig

Bei leseintensiven Zugriffsmustern ist konstant hohe IOPS wahrscheinlich auf unzureichenden Speicher und nicht auf unzureichende IOPS zurückzuführen. MySQL liest Daten von der Festplatte, wenn sie sich nicht im Speicher befinden, und ist außergewöhnlich gut darin, die Arbeitsmenge im Speicher zu halten (siehe "Größe der Arbeitsmenge"). Aber eine Kombination aus zwei Faktoren kann zu hohen IOPS für Lesevorgänge führen: Die Größe des Working Set ist deutlich größer als der Speicher und der Lesedurchsatz ist außergewöhnlich hoch (siehe "Durchsatz"). Diese Kombination führt dazu, dass MySQL so viele Daten zwischen Festplatte und Speicher austauscht, dass sich das Problem als hohe IOPS zeigt. Das ist selten, aber möglich.

Schreiblastig

Bei schreibintensiven Zugriffsmustern ist gleichbleibend hohe IOPS wahrscheinlich auf unzureichende IOPS zurückzuführen. Einfach ausgedrückt: Der Speicher kann die Daten nicht schnell genug schreiben. Normalerweise erreicht der Speicher einen hohen Durchsatz (IOPS) mit Schreib-Caches, aber Caches sind nicht dauerhaft. MySQL benötigt eine dauerhafte Speicherung: Daten physisch auf der Festplatte, nicht in Caches. (Der Ausdruck "auf der Festplatte" wird auch für Flash-basierte Speicher verwendet, die keine Festplatten haben.) Folglich muss MySQL die Daten flushen, d.h. auf die Festplatte schreiben. Flushing schränkt den Speicherdurchsatz stark ein, aber MySQL verfügt über ausgefeilte Techniken und Algorithmen, um Leistung mit Haltbarkeit zu verbinden -"Page Flushing" geht darauf ein. Die einzige Lösung an diesem Punkt - weil du Abfragen, Daten und Zugriffsmuster bereits optimiert hast - ist mehr IOPS für die Speicherung.

Mit einem vorsichtigen Nicken in Richtung Skalierung der Hardware könnte es so aussehen, als ob wir das Ende erreicht hätten. Egal wie viele Kieselsteine, oder Pflastersteine, oder Felsbrocken wir bewegen müssen, wir können immer einen größeren Lkw benutzen, um sie zu bewegen. Aber was, wenn du einen Berg bewegen musst? Dann brauchst du das nächste Kapitel: Sharding.

Zusammenfassung

In diesem Kapitel ging es um Datenzugriffsmuster, die bestimmen, wie du die Anwendung ändern kannst, um MySQL effizient zu nutzen. Die wichtigsten Punkte sind:

• MySQL macht nichts anderes, als Anwendungsabfragen auszuführen.

• Die Datenbankleistung destabilisiert sich bei einer Grenze, die weniger als 100% der Hardwarekapazität beträgt.

• Einige Anwendungen haben eine viel höhere Leistung von MySQL, weil jedes Detail auf hohe Leistung ausgelegt ist.

• Zugriffsmuster beschreiben, wie eine Anwendung MySQL für den Datenzugriff nutzt.

• Du musst die Anwendung ändern, um ihre Datenzugriffsmuster zu ändern.

• Skaliere die Hardware, um die Leistung zu verbessern, nachdem du andere Lösungen ausgeschöpft hast.

Das nächste Kapitel führt in die grundlegenden Mechanismen des Sharding von MySQL ein, um MySQL in großem Umfang zu nutzen.

Übung: Beschreibe ein Zugriffsmuster

Das Ziel dieser Übung ist es, das Zugriffsmuster der langsamsten Abfrage zu beschreiben. (Um langsame Abfragen zu erhalten, siehe "Abfrageprofil" und "Übung": Langsame Abfragen identifizieren".) Beschreibe für die langsamste Abfrage alle neun Zugriffsmuster-Eigenschaften aus "Datenzugriffsmuster". Wie in diesem Abschnitt erwähnt, sind Zugriffsmuster reines Wissen. Nutze dieses Wissen, um zu überlegen, welche "Anwendungsänderungen" vorgenommen werden könnten, um die Abfrage indirekt zu optimieren, indem ihre Zugriffsmuster geändert werden. Selbst wenn keine Anwendungsänderungen möglich sind, ist die Kenntnis der Zugriffsmuster eine Expertenübung, denn die Leistung von MySQL hängt von Abfragen, Daten und Zugriffsmustern ab.

¹ Schau dir das Video Universal Scalability Law Modeling Workbook des renommierten MySQL-Experten Baron Schwartz an, um die USL in Aktion mit Werten von echten MySQL-Servern zu sehen.

² Tatsächlich hat der renommierte MySQL-Experte Baron Schwartz ihn dort eingeführt. Neil Gunther schrieb in seinem Blogbeitrag "USL Scalability Modeling with Three Parameters", dass Baron Schwartz den dritten Parameter hinzufügte, weil er es der USL ermöglichte, Daten aus echten Datenbanken anzupassen.

³ Toyota: 210 Km/h; Ferrari: 320 Km/h.

⁴ Technisch gesehen ist es möglich, die LSN der Datenseiten im InnoDB-Pufferpool zu überprüfen, aber das ist störend und wird daher praktisch nie gemacht.