Hochleistungs-MySQL, 4. Auflage

Kapitel 4. Betriebssystem und Hardware-Optimierung

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

Dein MySQL-Server kann nur so gut sein wie sein schwächstes Glied, und das Betriebssystem und die Hardware, auf der er läuft, sind oft begrenzende Faktoren. Die Größe der Festplatte, der verfügbare Arbeitsspeicher und die CPU-Ressourcen, das Netzwerk und die Komponenten, die sie miteinander verbinden, begrenzen die letztendliche Kapazität des Systems. Daher musst du deine Hardware sorgfältig auswählen und die Hardware und das Betriebssystem entsprechend konfigurieren. Wenn deine Arbeitslast zum Beispiel I/O-gebunden ist, besteht eine Möglichkeit darin, deine Anwendung so zu gestalten, dass die I/O-Last von MySQL minimiert wird. Oft ist es jedoch klüger, das E/A-Subsystem aufzurüsten, mehr Arbeitsspeicher zu installieren oder vorhandene Festplatten neu zu konfigurieren. Wenn du in einer Cloud-Umgebung arbeitest, können die Informationen in diesem Kapitel trotzdem sehr nützlich sein, vor allem wenn du die Einschränkungen des Dateisystems und die Zeitplannungsprogramme von Linux verstehen willst.

Was schränkt die Leistung von MySQL ein?

Viele verschiedene Hardwarekomponenten können die Leistung von MySQL beeinträchtigen, aber der häufigste Engpass, den wir sehen, ist die Erschöpfung der CPU. CPU-Sättigung kann auftreten, wenn MySQL versucht, zu viele Abfragen parallel auszuführen oder wenn eine kleinere Anzahl von Abfragen zu lange auf der CPU läuft.

Eine E/A-Sättigung kann immer noch auftreten, aber viel seltener als eine CPU-Erschöpfung. Das liegt vor allem an der Umstellung auf die Verwendung von Solid-State-Laufwerken (SSDs). In der Vergangenheit war der Leistungsverlust extrem, wenn man nicht mehr im Arbeitsspeicher arbeitete, sondern auf die Festplatte (HDD) auswich. SSDs sind in der Regel 10 bis 20 Mal schneller als SSH. Wenn Abfragen heutzutage auf die Festplatte zugreifen müssen, bieten sie immer noch eine gute Leistung.

Eine Erschöpfung des Arbeitsspeichers kann immer noch auftreten, aber normalerweise nur, wenn du versuchst, MySQL zu viel Speicher zuzuweisen. Über die optimalen Konfigurationseinstellungen, um dies zu verhindern, sprechen wir in Kapitel 5 , "Konfigurieren der Speichernutzung".

Wie man CPUs für MySQL auswählt

Du solltest überlegen, ob deine Arbeitslast CPU-gebunden ist, wenn du deine aktuelle Hardware aufrüstest oder neue Hardware kaufst. Du kannst eine CPU-gebundene Arbeitslast erkennen, indem du die CPU-Auslastung überprüfst, aber anstatt nur darauf zu achten, wie stark deine CPUs insgesamt ausgelastet sind, solltest du dir das Verhältnis von CPU-Nutzung und E/A für deine wichtigsten Abfragen ansehen und feststellen, ob die CPUs gleichmäßig ausgelastet sind.

Im Großen und Ganzen hast du zwei Ziele für deinen Server:

Geringe Latenz (schnelle Reaktionszeit)

Um das zu erreichen, brauchst du schnelle CPUs, denn jede Abfrage wird nur eine einzige CPU nutzen.

Hoher Durchsatz

Wenn du viele Abfragen gleichzeitig ausführen kannst, profitierst du vielleicht von mehreren CPUs, die die Abfragen bedienen.

Wenn deine Arbeitslast nicht alle CPUs auslastet, kann MySQL die zusätzlichen CPUs immer noch für Hintergrundaufgaben wie das Leeren von InnoDB-Puffern, Netzwerkoperationen usw. verwenden. Diese Aufträge sind jedoch in der Regel unbedeutend im Vergleich zur Ausführung von Abfragen.

Ausgleich von Speicher- und Festplattenressourcen

Der Hauptgrund für viel Arbeitsspeicher ist nicht, dass du viele Daten im Speicher halten kannst, sondern dass du Festplatten-E/A vermeiden kannst, die um Größenordnungen langsamer sind als der Zugriff auf Daten im Speicher. Der Trick besteht darin, die Größe des Speichers und der Festplatte, die Geschwindigkeit, die Kosten und andere Eigenschaften so aufeinander abzustimmen, dass du eine gute Leistung für deine Arbeitslast erhältst.

Caching, Lese- und Schreibvorgänge

Wenn du genug Speicher hast, kannst du die Festplatte komplett von Leseanfragen abschirmen. Wenn alle Daten in den Arbeitsspeicher passen, wird jeder Lesevorgang ein Cache-Treffer sein, sobald die Caches des Servers aufgewärmt sind. Es gibt dann zwar noch logische Lesezugriffe aus dem Speicher, aber keine physischen Lesezugriffe von der Festplatte. Bei Schreibvorgängen ist das anders. Ein Schreibvorgang kann genauso im Speicher ausgeführt werden wie ein Lesevorgang, aber früher oder später muss er auf die Festplatte geschrieben werden, damit er dauerhaft ist. Mit anderen Worten: Ein Cache kann Schreibvorgänge verzögern, aber das Caching kann Schreibvorgänge nicht ausschließen, wie es bei Lesevorgängen der Fall ist.

Durch das Caching können Schreibvorgänge nicht nur verzögert, sondern auch auf zwei wichtige Arten zusammengefasst werden:

Viele Schreiben, eine Spülung

Ein einziger Datensatz kann viele Male im Speicher geändert werden, ohne dass alle neuen Werte auf die Festplatte geschrieben werden. Wenn die Daten schließlich auf die Festplatte geschrieben werden, sind alle Änderungen, die seit dem letzten physischen Schreibvorgang vorgenommen wurden, dauerhaft. Zum Beispiel können viele Anweisungen einen Zähler im Speicher aktualisieren. Wenn der Zähler hundertmal erhöht und dann auf die Festplatte geschrieben wird, wurden hundert Änderungen in einem einzigen Schreibvorgang zusammengefasst.

E/A-Zusammenführung

Viele verschiedene Daten können im Speicher geändert werden, und die Änderungen können gesammelt werden, so dass die physischen Schreibvorgänge als ein einziger Festplattenvorgang durchgeführt werden können.

Aus diesem Grund verwenden viele transaktionale Systeme eine write-ahead logging Strategie. Mit Write-ahead Logging können sie Änderungen an den Seiten im Speicher vornehmen, ohne die Änderungen auf die Festplatte zu übertragen, was normalerweise zufällige E/A erfordert und sehr langsam ist. Stattdessen werden die Änderungen in eine sequentielle Logdatei geschrieben, was viel schneller ist. Ein Hintergrund-Thread kann die geänderten Seiten später auf die Festplatte spülen und die Schreibvorgänge optimieren.

Schreibvorgänge profitieren stark von der Pufferung, da sie zufällige E/A in sequenzielle E/A umwandeln. Asynchrone (gepufferte) Schreibvorgänge werden in der Regel vom Betriebssystem verwaltet und in Stapeln zusammengefasst, damit sie optimal auf die Festplatte übertragen werden können. Synchrone (ungepufferte) Schreibvorgänge müssen auf die Festplatte geschrieben werden, bevor sie abgeschlossen sind. Deshalb profitieren sie von der Pufferung im batteriegepufferten Write-Back-Cache eines RAID-Controllers (Redundant Array of Inexpensive Disks) (auf RAID gehen wir später noch ein).

Was ist dein Arbeitsset?

Jede Anwendung hat einen "Arbeitssatz" von Daten, d.h. die Daten, die sie wirklich für ihre Arbeit benötigt. In vielen Datenbanken gibt es auch viele Daten, die nicht zum Arbeitsset gehören. Du kannst dir die Datenbank wie einen Schreibtisch mit Aktenschubladen vorstellen. Die Arbeitsmappe besteht aus den Unterlagen, die du auf dem Schreibtisch haben musst, um deine Arbeit zu erledigen. Der Schreibtisch ist in dieser Analogie der Hauptspeicher, während die Schubladen die Festplatten sind. Genauso wie du nicht jedes Blatt Papier auf dem Schreibtisch haben musst, um deine Arbeit zu erledigen, muss auch nicht die gesamte Datenbank in den Arbeitsspeicher passen, um eine optimale Leistung zu erzielen - nur die Arbeitsmenge.

Beim Umgang mit HDDs war es eine gute Praxis, ein effektives Verhältnis von Speicher zu Festplatte zu finden. Das lag vor allem an der langsameren Latenzzeit und den niedrigen Ein-/Ausgabeoperationen pro Sekunde (IOPS) von HDDs. Bei SSDs ist das Verhältnis von Speicher zu Festplatte weit weniger wichtig.

Solid-State Speicherung

Solid-State-Speicherung (Flash) ist der Standard für die meisten Datenbanksysteme, insbesondere für die Online-Transaktionsverarbeitung (OLTP). Nur in sehr großen Data Warehouses oder Legacy-Systemen findet man normalerweise HDDs. Dieser Wandel kam, als der Preis für SSDs um 2015 herum deutlich sank.

Solid-State-Speichermedien verwenden nichtflüchtige Flash-Speicherchips, die aus Zellen anstelle von Magnetplatten bestehen. Sie werden auch nonvolatile random access memory (NVRAM) genannt. Sie haben keine beweglichen Teile und verhalten sich deshalb ganz anders als Festplatten.

Hier ist eine kurze Zusammenfassung der Flash-Leistung. Hochwertige Flash-Geräte haben:

Viel bessere zufällige Lese- und Schreibleistung im Vergleich zu Festplatten

Flash-Geräte sind normalerweise etwas besser beim Lesen als beim Schreiben.

Bessere sequentielle Lese- und Schreibleistung als Festplatten

Allerdings ist die Verbesserung nicht so dramatisch wie bei der zufälligen E/A, weil Festplatten bei der zufälligen E/A viel langsamer sind als bei der sequentiellen E/A.

Viel bessere Unterstützung für Gleichzeitigkeit als Festplatten

Flash-Geräte können viel mehr gleichzeitige Operationen unterstützen und erreichen ihren maximalen Durchsatz erst, wenn du viele gleichzeitige Operationen hast.

Das Wichtigste sind Verbesserungen bei Random I/O und Gleichzeitigkeit. Flash-Speicher bietet eine sehr gute Random-I/O-Leistung bei hoher Gleichzeitigkeit.

Ein Überblick über Flash-Speicher

Festplatten mit rotierenden Platten und schwingenden Köpfen haben inhärente Beschränkungen und Eigenschaften, die auf die Physik zurückzuführen sind. Das Gleiche gilt für die Solid-State-Speicherung, die auf einem Flash-Speicher aufbaut. Glaube nicht, dass eine Solid-State Speicherung einfach ist. In mancher Hinsicht ist er sogar komplexer als eine Festplatte. Die Grenzen des Flash-Speichers sind ziemlich streng und schwer zu überwinden, so dass das typische Solid-State-Gerät eine komplizierte Architektur mit vielen Abstraktionen, Zwischenspeichern und proprietärer "Magie" hat.

Die wichtigste Eigenschaft des Flash-Speichers ist, dass er viele Male schnell und in kleinen Einheiten gelesen werden kann, aber das Schreiben ist viel schwieriger. Eine Zelle kann ohne einen speziellen Löschvorgang nicht wieder beschrieben werden und kann nur in großen Blöcken gelöscht werden - zum Beispiel 512 KB. Der Löschzyklus ist langsam und verschleißt schließlich den Block. Die Anzahl der Löschzyklen, die ein Block verträgt, hängt von der zugrunde liegenden Technologie ab - dazu später mehr.

Die Einschränkungen beim Schreiben sind der Grund für die Komplexität von Solid-State-Speichern. Das ist der Grund, warum einige Geräte eine stabile, gleichbleibende Leistung bieten und andere nicht. Das Geheimnis liegt in der proprietären Firmware, den Treibern und anderen Kleinigkeiten, die ein Solid-State-Gerät zum Laufen bringen. Damit Schreibvorgänge gut funktionieren und die Blöcke des Flash-Speichers nicht vorzeitig abgenutzt werden, muss das Gerät in der Lage sein, Seiten zu verschieben und eine Speicherbereinigung und das sogenannte wear leveling durchzuführen. Der Begriff " Write Amplification" (Schreibverstärkung) wird verwendet, um die zusätzlichen Schreibvorgänge zu beschreiben, die durch das Verschieben von Daten von einem Ort zum anderen und das mehrfache Schreiben von Daten und Metadaten aufgrund von partiellen Blockschreibvorgängen entstehen.

Speicherbereinigung

Die Speicherbereinigung ist wichtig zu verstehen. Um einige Blöcke frisch und bereit für neue Schreibvorgänge zu halten, fordert das Gerät Blöcke zurück. Dazu braucht es etwas freien Platz auf dem Gerät. Entweder hat das Gerät intern Speicherplatz reserviert, den du nicht sehen kannst, oder du musst selbst Speicherplatz reservieren, indem du ihn nicht ganz auffüllst. In jedem Fall muss sich der Garbage Collector mehr anstrengen, um einige Blöcke sauber zu halten, und der Schreibverstärkungsfaktor steigt.

Das führt dazu, dass viele Geräte langsamer werden, wenn sie voll sind. Wie viel langsamer, ist von Hersteller zu Hersteller und von Modell zu Modell unterschiedlich und hängt von der Architektur des Geräts ab. Manche Geräte sind so konstruiert, dass sie auch dann noch leistungsstark sind, wenn sie ziemlich voll sind, aber im Allgemeinen wird eine 100 GB große Datei auf einer 160 GB großen SSD anders verarbeitet als auf einer 320 GB großen SSD. Die Verlangsamung wird dadurch verursacht, dass man auf den Abschluss von Löschvorgängen warten muss, wenn es keine freien Blöcke gibt. Ein Schreibvorgang in einen freien Block dauert ein paar hundert Mikrosekunden, aber ein Löschvorgang ist viel langsamer - in der Regel ein paar Millisekunden.

Optimierung der RAID-Leistung

Speicher-Engines speichern ihre Daten und/oder Indizes oft in einzelnen großen Dateien, was bedeutet, dass RAID in der Regel die praktikabelste Option für die Speicherung einer großen Datenmenge ist. RAID kann die Redundanz, die Größe der Speicherung, das Caching und die Geschwindigkeit verbessern. Aber wie bei den anderen Optimierungen, die wir uns angeschaut haben, gibt es viele Varianten von RAID-Konfigurationen, und es ist wichtig, dass du eine wählst, die für deine Bedürfnisse geeignet ist.

Wir werden hier nicht auf jeden RAID-Level eingehen und auch nicht im Detail erklären, wie die verschiedenen RAID-Levels Daten speichern. Stattdessen konzentrieren wir uns darauf, wie RAID-Konfigurationen die Anforderungen eines Datenbankservers erfüllen. Dies sind die wichtigsten RAID-Level:

RAID 0

RAID 0 ist die billigste und leistungsstärkste RAID-Konfiguration, zumindest wenn man Kosten und Leistung auf einfache Weise misst (wenn du zum Beispiel die Datenwiederherstellung mit einbeziehst, wird es teurer). Da es keine Redundanz bietet, ist RAID 0 unserer Meinung nach für eine Produktionsdatenbank nicht geeignet, aber wenn du wirklich Kosten sparen willst, kann es in Entwicklungsumgebungen eine gute Wahl sein, in denen ein kompletter Serverausfall nicht zu einem Vorfall wird.

Beachte, dass RAID 0 keine Redundanz bietet, auch wenn das R in der Abkürzung RAID für "redundant" steht. Die Wahrscheinlichkeit, dass ein RAID 0-Verbund fehlschlägt, ist sogar höher als die Wahrscheinlichkeit, dass eine einzelne Festplatte fehlschlägt, nicht niedriger!

RAID 1

RAID 1 bietet für viele Szenarien eine gute Leseleistung und dupliziert deine Daten auf mehreren Festplatten, sodass eine gute Redundanz gegeben ist. RAID 1 ist beim Lesen ein bisschen schneller als RAID 0. Es eignet sich gut für Server, die mit Logging und ähnlichen Arbeitslasten zu tun haben, da für sequentielle Schreibvorgänge selten viele Festplatten benötigt werden (im Gegensatz zu zufälligen Schreibvorgängen, die von einer Parallelisierung profitieren können). Es ist auch eine typische Wahl für Low-End-Server, die Redundanz benötigen, aber nur zwei Festplatten haben.

RAID 0 und RAID 1 sind sehr einfach und können oft gut in Software implementiert werden. Mit den meisten Betriebssystemen kannst du problemlos Software-RAID 0- und RAID 1-Volumes erstellen.

RAID 5

Früher war RAID 5 für Datenbanksysteme ziemlich unheimlich, vor allem wegen der Leistungseinbußen. Jetzt, wo SSDs alltäglich werden, ist es eine praktikable Option. Dabei werden die Daten auf viele Festplatten mit verteilten Paritätsblöcken verteilt, so dass bei einem Ausfall einer Festplatte die Daten anhand der Paritätsblöcke wiederhergestellt werden können. Wenn zwei Festplatten ausfallen, schlägt der gesamte Datenträger unwiederbringlich fehl. Gemessen an den Kosten pro Speichereinheit ist dies die wirtschaftlichste redundante Konfiguration, da du nur den Speicherplatz einer Festplatte im gesamten Array verlierst.

Das größte "Problem" bei RAID 5 ist, wie sich der Verbund verhält, wenn eine Festplatte fehlschlägt. Das liegt daran, dass die Daten durch Auslesen aller anderen Festplatten rekonstruiert werden müssen. Dies beeinträchtigt die Leistung von HDD erheblich, weshalb allgemein davon abgeraten wurde. Noch schlimmer war es, wenn du viele Festplatten hattest. Wenn du versuchst, den Server während des Rebuilds online zu halten, solltest du nicht erwarten, dass der Rebuild oder die Leistung des Arrays gut sind. Zu den weiteren Leistungseinbußen gehörten die eingeschränkte Skalierbarkeit aufgrund der Paritätsblöcke - RAID 5 lässt sich nicht gut über 10 Festplatten hinaus skalieren - und Caching-Probleme. Eine gute RAID 5-Leistung hängt stark vom Cache des RAID-Controllers ab, der mit den Anforderungen des Datenbankservers in Konflikt geraten kann. Wie wir bereits erwähnt haben, bieten SSDs eine wesentlich bessere Leistung in Bezug auf IOPS und Durchsatz, und die Probleme mit der schlechten Leistung beim zufälligen Lesen und Schreiben sind ebenfalls verschwunden.

Einer der mildernden Faktoren für RAID 5 ist, dass es so beliebt ist. Infolgedessen sind RAID-Controller oft stark für RAID 5 optimiert und trotz der theoretischen Grenzen können intelligente Controller, die den Cache gut nutzen, bei einigen Arbeitslasten fast so gut abschneiden wie RAID 10-Controller. Das könnte daran liegen, dass die RAID 10-Controller weniger stark optimiert sind, aber unabhängig vom Grund haben wir diese Erfahrung gemacht.

RAID 6

Das größte Problem bei RAID 5 war, dass der Verlust von zwei Festplatten katastrophal war. Je mehr Festplatten du in deinem Verbund hast, desto höher ist die Wahrscheinlichkeit eines Festplattenausfalls. RAID 6 hilft, die Ausfallwahrscheinlichkeit zu verringern, indem es eine zweite Paritätsplatte hinzufügt. So kannst du zwei Festplattenausfälle überstehen und den Verbund trotzdem wiederherstellen. Der Nachteil ist, dass die Berechnung der zusätzlichen Parität die Schreibvorgänge langsamer macht als bei RAID 5.

RAID 10

RAID 10 ist eine sehr gute Wahl für die Datenspeicherung. Es besteht aus gespiegelten Paaren, die gestreift sind, und skaliert daher sowohl Lese- als auch Schreibvorgänge gut. Im Vergleich zu RAID 5 ist es schnell und einfach wiederherzustellen. Es lässt sich auch recht gut in Software implementieren.

Der Leistungsverlust, wenn eine Festplatte ausfällt, kann immer noch beträchtlich sein, weil dieser Stripe zu einem Engpass werden kann. Je nach Arbeitslast kann die Leistung um bis zu 50 % sinken. Eine Sache, auf die du achten solltest, sind RAID-Controller, die eine "verkettete Spiegelung" für RAID 10 verwenden. Das ist suboptimal, weil es kein Striping gibt: Die Daten, auf die du am häufigsten zugreifst, werden möglicherweise auf nur einem Festplattenpaar gespeichert, anstatt auf mehrere verteilt zu sein.

RAID 50

RAID 50 besteht aus RAID-5-Arrays, die gestriped sind, und kann ein guter Kompromiss zwischen der Wirtschaftlichkeit von RAID 5 und der Leistung von RAID 10 sein, wenn du viele Festplatten hast. Dies ist vor allem für sehr große Datensätze nützlich, z. B. für Data Warehouses oder extrem große OLTP-Systeme.

Tabelle 4-1 fasst die verschiedenen RAID-Konfigurationen zusammen.

Tabelle 4-1. Vergleich der RAID-Levels

Level	Synopsis	Redundanz	Erforderliche Festplatten	Schneller lesen	Schneller schreibt
RAID 0	Billig, schnell, gefährlich	Nein	N	Ja	Ja
RAID 1	Schnell gelesen, einfach, sicher	Ja	2 (normalerweise)	Ja	Nein
RAID 5	Billig und schnell mit SSDs	Ja	N + 1	Ja	Hängt ab
RAID 6	Wie RAID 5, aber widerstandsfähiger	Ja	N + 2	Ja	Hängt ab
RAID 10	Teuer, schnell, sicher	Ja	2N	Ja	Ja
RAID 50	Für sehr große Datenspeicher	Ja	2(N + 1)	Ja	Ja

RAID-Ausfall, Wiederherstellung und Überwachung

RAID-Konfigurationen (mit Ausnahme von RAID 0) bieten Redundanz. Das ist wichtig, aber man unterschätzt leicht die Wahrscheinlichkeit von gleichzeitigen Festplattenausfällen. Du solltest nicht denken, dass RAID eine Garantie für die Sicherheit deiner Daten ist.

RAID macht Backups nicht überflüssig - oder reduziert sie sogar. Wenn ein Problem auftritt, hängt die Wiederherstellungszeit von deinem Controller, dem RAID-Level, der Größe des Arrays, der Festplattengeschwindigkeit und davon ab, ob du den Server online lassen musst, während du das Array wiederherstellst.

Es besteht die Möglichkeit, dass Festplatten genau zur gleichen Zeit fehlschlagen. Zum Beispiel kann eine Stromspitze oder eine Überhitzung leicht zwei oder mehr Festplatten zerstören. Häufiger ist es jedoch, dass zwei Festplatten kurz hintereinander ausfallen. Viele solcher Probleme können unbemerkt bleiben. Eine häufige Ursache ist die Beschädigung der physischen Datenträger, auf denen Daten gespeichert sind, auf die nur selten zugegriffen wird. Dies kann monatelang unbemerkt bleiben, bis du versuchst, die Daten zu lesen, oder eine andere Festplatte fehlschlägt und der RAID-Controller versucht, die beschädigten Daten zu verwenden, um den Verbund wiederherzustellen. Je größer die Festplatte ist, desto wahrscheinlicher ist dies.

Deshalb ist es wichtig, deine RAID-Arrays zu überwachen. Die meisten Controller bieten eine Software an, die den Status des Arrays meldet, und du musst das im Auge behalten, weil du sonst von einem Festplattenausfall nichts mitbekommst. Du könntest die Chance verpassen, die Daten wiederherzustellen und das Problem erst entdecken, wenn ein zweites Laufwerk fehlschlägt und es dann zu spät ist. Du solltest ein Überwachungssystem so konfigurieren, dass es dich benachrichtigt, wenn ein Laufwerk oder ein Datenträger den Status "beschädigt" oder "fehlgeschlagen" annimmt.

Du kannst das Risiko einer latenten Beschädigung verringern, indem du deine Arrays in regelmäßigen Abständen aktiv auf Konsistenz überprüfst. Background Patrol Read, eine Funktion einiger Controller, die auf beschädigte Medien prüft und sie repariert, während alle Laufwerke online sind, kann ebenfalls dazu beitragen, solche Probleme zu vermeiden. Wie bei der Wiederherstellung kann die Überprüfung von extrem großen Arrays langsam sein.

Du kannst auch ein Hot-Spare-Laufwerk hinzufügen, das unbenutzt ist und als Standby-Laufwerk konfiguriert ist, damit der Controller es automatisch zur Wiederherstellung verwendet. Das ist eine gute Idee, wenn du auf jeden Server angewiesen bist. Bei Servern, die nur wenige Festplatten haben, ist es teuer, weil die Kosten für eine ungenutzte Festplatte proportional höher sind, aber wenn du viele Festplatten hast, ist es fast töricht, kein Hot Spare zu haben. Erinnere dich daran, dass die Wahrscheinlichkeit eines Festplattenausfalls mit zunehmender Anzahl von Festplatten schnell steigt.

Zusätzlich zur Überwachung der Laufwerke auf Ausfälle solltest du auch die Backup-Batterie des RAID-Controllers und die Richtlinien für den Schreibcache überwachen. Wenn die Batterie fehlschlägt, deaktivieren die meisten Controller standardmäßig den Schreibcache, indem sie die Cache-Richtlinie auf Durchschreiben statt auf Zurückschreiben ändern. Dies kann zu einem starken Leistungsabfall führen. Bei vielen Controllern durchläuft die Batterie außerdem regelmäßig einen Lernprozess, bei dem der Cache ebenfalls deaktiviert wird. Im Verwaltungsprogramm deines RAID-Controllers solltest du sehen und konfigurieren können, wann der Lernzyklus stattfindet, damit du nicht unvorbereitet getroffen wirst. Neuere RAID-Controller vermeiden dies, indem sie einen Flash-gestützten Cache verwenden, der NVRAM nutzt, um unbestätigte Schreibvorgänge zu speichern, anstatt eines batteriegepufferten Caches. Dadurch wird der gesamte Lernzyklus vermieden.

Du könntest auch einen Benchmark-Test durchführen, bei dem die Cache-Richtlinie auf Write-Through eingestellt ist, damit du weißt, was du erwarten kannst. Am besten planst du deine Batterie-Lernzyklen zu Zeiten mit geringem Datenverkehr, also nachts oder am Wochenende. Wenn die Leistung zu irgendeinem Zeitpunkt mit Durchschreiben zu sehr leidet, kannst du auch auf einen anderen Server ausweichen, bevor dein Lernzyklus beginnt. Als allerletzten Ausweg kannst du deine Server neu konfigurieren, indem du die Variablen innodb_flush_log_at_trx_commit und sync_binlog auf eine geringere Haltbarkeit einstellst. Dadurch wird die Festplattenauslastung während des Durchschreibens verringert und die Leistung kann akzeptabel sein; dies sollte aber wirklich nur als letzter Ausweg geschehen. Die Verringerung der Haltbarkeit hat einen großen Einfluss darauf, wie viele Daten du bei einem Datenbankabsturz verlieren kannst und wie gut du sie wiederherstellen kannst.

RAID-Konfiguration und Caching

kannst du den RAID-Controller in der Regel selbst konfigurieren, indem du sein Setup-Dienstprogramm während der Boot-Sequenz des Rechners aufrufst oder es über die Eingabeaufforderung ausführst. Obwohl die meisten Controller viele Optionen bieten, konzentrieren wir uns auf die Chunk-Größe für Striped-Arrays und den Controller-Cache (auch bekannt als RAID-Cache; wir verwenden die Begriffe synonym).

Die RAID Stripe Chunk-Größe

Die optimale Stripe-Chunk-Größe ist abhängig von der Arbeitslast und der Hardware. Theoretisch ist es gut, eine große Chunk-Größe für zufällige E/A zu haben, denn das bedeutet, dass mehr Lesevorgänge von einem einzigen Laufwerk ausgeführt werden können.

Um zu sehen, warum das so ist, betrachte die Größe eines typischen Random-I/O-Vorgangs für deine Arbeitslast. Wenn die Chunk-Größe mindestens so groß ist und die Daten nicht über die Grenze zwischen den Chunks hinausgehen, muss nur ein einziges Laufwerk am Lesevorgang teilnehmen. Wenn die Chunk-Größe jedoch kleiner ist als die zu lesende Datenmenge, führt kein Weg daran vorbei, mehr als ein Laufwerk in den Lesevorgang einzubeziehen.

So viel zur Theorie. In der Praxis funktionieren viele RAID-Controller nicht gut mit großen Chunks. Zum Beispiel könnte der Controller die Größe des Chunks als Cache-Einheit in seinem Cache verwenden, was eine Verschwendung wäre. Der Controller könnte auch die Chunk-Größe, die Cache-Größe und die Read-Unit-Größe (die Datenmenge, die er in einem einzigen Vorgang liest) aufeinander abstimmen. Wenn die Leseeinheit zu groß ist, ist der Cache möglicherweise weniger effektiv und es werden viel mehr Daten gelesen, als wirklich benötigt werden, selbst bei kleinen Anfragen.

Es ist auch schwer zu wissen, ob sich ein bestimmtes Datenstück über mehrere Laufwerke erstrecken wird. Selbst wenn die Chunk-Größe 16 KB beträgt, was der Seitengröße von InnoDB entspricht, kannst du nicht sicher sein, dass alle Lesevorgänge an 16 KB-Grenzen ausgerichtet werden. Das Dateisystem kann die Datei fragmentieren und wird die Fragmente normalerweise an der Blockgröße des Dateisystems ausrichten, die oft 4 KB beträgt. Manche Dateisysteme sind vielleicht schlauer, aber darauf solltest du dich nicht verlassen.

Der RAID-Cache

Der RAID-Cache ist ein (relativ) kleiner Speicher, der physisch auf einem Hardware-RAID-Controller installiert ist. Er kann genutzt werden, um Daten auf dem Weg zwischen den Festplatten und dem Hostsystem zu puffern. Hier sind einige der Gründe, warum eine RAID-Karte den Cache nutzen kann:

Caching liest

Nachdem der Controller einige Daten von den Festplatten gelesen und an das Hostsystem gesendet hat, kann er die Daten speichern; so kann er künftige Anfragen nach denselben Daten erfüllen, ohne erneut auf die Festplatte zugreifen zu müssen.

Das ist normalerweise eine sehr schlechte Nutzung des RAID-Caches. Warum? Weil das Betriebssystem und der Datenbankserver ihre eigenen, viel größeren Caches haben. Wenn es in einem dieser Caches einen Cache-Treffer gibt, werden die Daten im RAID-Cache nicht verwendet. Umgekehrt ist die Wahrscheinlichkeit eines Treffers im RAID-Cache verschwindend gering, wenn ein Fehler in einem der übergeordneten Caches auftritt. Weil der RAID-Cache so viel kleiner ist, wurde er mit ziemlicher Sicherheit auch schon geleert und mit anderen Daten gefüllt. Wie auch immer du es betrachtest, es ist Speicherverschwendung, Lesevorgänge im RAID-Cache zu speichern.

Zwischenspeichern von Vorauslesedaten

Wenn der RAID-Controller merkt, dass Daten nacheinander angefordert werden, kann er sich dazu entschließen, ein Read-Ahead-Read durchzuführen, d.h. er holt sich die Daten, von denen er annimmt, dass sie bald benötigt werden, im Voraus. Allerdings muss er die Daten irgendwo ablegen, bis sie angefordert werden. Dafür kann es den RAID-Cache nutzen. Die Auswirkung auf die Leistung kann sehr unterschiedlich sein und du solltest überprüfen, ob sie tatsächlich hilfreich ist. Read-ahead-Operationen sind möglicherweise nicht hilfreich, wenn der Datenbankserver seine eigenen intelligenten Read-ahead-Operationen durchführt (wie es InnoDB tut), und sie können die wichtige Pufferung synchroner Schreibvorgänge beeinträchtigen.

Schreiben zwischenspeichern

Der RAID-Controller kann Schreibvorgänge in seinem Cache zwischenspeichern und sie für einen späteren Zeitpunkt planen. Das hat zwei Vorteile: Erstens kann er dem Hostsystem viel schneller "Erfolg" melden, als wenn er die Schreibvorgänge auf den physischen Festplatten tatsächlich durchführen müsste, und zweitens kann er Schreibvorgänge sammeln und effizienter durchführen.

Interne Vorgänge

Einige RAID-Vorgänge sind sehr komplex - vor allem RAID 5-Schreibvorgänge, bei denen Paritätsbits berechnet werden müssen, die im Falle eines Ausfalls zur Wiederherstellung der Daten verwendet werden können. Für diese Art von internen Vorgängen muss der Controller etwas Speicher verwenden. Das ist einer der Gründe, warum RAID 5 bei manchen Controllern schlecht funktioniert: Um eine gute Leistung zu erzielen, müssen viele Daten in den Cache eingelesen werden. Einige Controller können die Schreibvorgänge und die RAID 5-Paritätsvorgänge nicht in einem ausgewogenen Verhältnis zwischenspeichern.

Im Allgemeinen ist der Speicher des RAID-Controllers eine knappe Ressource, die du sinnvoll nutzen solltest. Ihn für Lesevorgänge zu nutzen, ist in der Regel eine Verschwendung, aber für Schreibvorgänge ist er ein wichtiger Weg, um die E/A-Leistung zu verbessern. Bei vielen Controllern kannst du wählen, wie du den Speicher zuweisen möchtest. Du kannst zum Beispiel wählen, wie viel davon für das Zwischenspeichern von Schreibvorgängen und wie viel für Lesevorgänge verwendet werden soll. Bei RAID 0, RAID 1 und RAID 10 solltest du wahrscheinlich 100 % des Controllerspeichers für die Zwischenspeicherung von Schreibvorgängen reservieren. Bei RAID 5 solltest du einen Teil des Speichers des Controllers für die internen Vorgänge reservieren. Das ist im Allgemeinen ein guter Rat, aber er gilt nicht immer - unterschiedliche RAID-Karten erfordern unterschiedliche Konfigurationen.

Wenn du den RAID-Cache zum Zwischenspeichern von Schreibvorgängen nutzt, kannst du bei vielen Controllern einstellen, wie lange die Schreibvorgänge verzögert werden sollen (eine Sekunde, fünf Sekunden usw.). Eine längere Verzögerung bedeutet, dass mehr Schreibvorgänge gruppiert und optimal auf die Festplatten übertragen werden können. Der Nachteil ist, dass deine Schreibvorgänge "stoßweise" erfolgen. Das ist nicht weiter schlimm, es sei denn, deine Anwendung stellt gerade dann eine Reihe von Schreibanfragen, wenn der Cache des Controllers voll ist und er auf die Festplatte übertragen werden soll. Wenn es nicht genug Platz für die Schreibanfragen deiner Anwendung gibt, muss sie warten. Eine kürzere Verzögerung bedeutet, dass du mehr Schreibvorgänge hast und diese weniger effizient sind, aber sie glättet die Unregelmäßigkeiten und sorgt dafür, dass mehr Platz im Cache frei bleibt, um Bursts der Anwendung zu verarbeiten. (Wir vereinfachen hier - Controller haben oft komplexe, herstellerspezifische Ausgleichsalgorithmen, daher versuchen wir nur, die grundlegenden Prinzipien zu vermitteln).

Der Schreibcache ist sehr hilfreich für synchrone Schreibvorgänge, z. B. für fsync() Aufrufe der Transaktionsprotokolle und die Erstellung von Binärprotokollen mit sync_binlog. Du solltest ihn jedoch nur aktivieren, wenn dein Controller über eine Battery Backup Unit (BBU) oder eine andere nichtflüchtige Speicherung verfügt. Wenn du Schreibvorgänge ohne BBU zwischenspeicherst, werden deine Datenbank und sogar dein Transaktionsdateisystem im Falle eines Stromausfalls wahrscheinlich beschädigt. Wenn du jedoch eine BBU hast, kann die Aktivierung des Schreibcaches die Leistung bei Arbeitslasten, die viele Log-Flushes durchführen, wie z. B. das Flushen des Transaktionslogs beim Commit einer Transaktion, um den Faktor 20 oder mehr erhöhen.

Ein letzter Aspekt ist, dass viele Festplatten über eigene Schreibcaches verfügen, die fsync() Operationen "vortäuschen" können, indem sie dem Controller vorgaukeln, dass die Daten auf physische Medien geschrieben wurden. Festplatten, die direkt angeschlossen sind (und nicht an einen RAID-Controller), können ihre Caches manchmal vom Betriebssystem verwalten lassen, aber auch das funktioniert nicht immer. Normalerweise werden diese Caches für fsync() geleert und für synchrone E/A umgangen, aber auch hier kann die Festplatte lügen. Du solltest entweder sicherstellen, dass diese Caches auf fsync() geleert werden, oder sie deaktivieren, da sie nicht batteriegepuffert sind. Festplatten, die vom Betriebssystem oder der RAID-Firmware nicht richtig verwaltet werden, haben in vielen Fällen zu Datenverlusten geführt.

Aus diesem und anderen Gründen ist es immer eine gute Idee, einen echten Crashtest durchzuführen (buchstäblich den Stromstecker aus der Wand zu ziehen), wenn du neue Hardware installierst. Das ist oft die einzige Möglichkeit, um subtile Fehlkonfigurationen oder heimtückisches Festplattenverhalten zu entdecken. Ein praktisches Skript dafür findest du online.

Um zu testen, ob du dich wirklich auf die BBU deines RAID-Controllers verlassen kannst, solltest du das Netzkabel für eine realistische Zeitspanne aus der Steckdose ziehen. Manche Geräte halten ohne Strom nicht so lange durch, wie sie eigentlich sollten. Auch hier kann ein einziges defektes Glied deine gesamte Kette von Komponenten zur Speicherung unbrauchbar machen.

Netzwerk-Konfiguration

Genau wie Latenz und Durchsatz begrenzende Faktoren für eine Festplatte sind, sind Latenz und Bandbreite begrenzende Faktoren für eine Netzwerkverbindung. Das größte Problem für die meisten Anwendungen ist die Latenzzeit. Eine typische Anwendung führt viele kleine Netzwerkübertragungen durch, und die geringe Verzögerung bei jeder Übertragung summiert sich.

Ein Netzwerk, das nicht richtig funktioniert, ist auch ein großer Leistungsengpass. Paketverluste sind ein häufiges Problem. Selbst 1 % Verlust reicht aus, um eine erhebliche Leistungsminderung zu verursachen, weil verschiedene Schichten im Protokollstapel versuchen, die Probleme mit Strategien wie Warten und erneutem Senden von Paketen zu beheben, was zusätzliche Zeit kostet. Ein weiteres häufiges Problem ist die fehlerhafte oder langsame DNS-Auflösung.¹

DNS ist eine so große Achillesferse, dass die Aktivierung von skip_name_resolve eine gute Idee für Produktionsserver ist. Eine fehlerhafte oder langsame DNS-Auflösung ist für viele Anwendungen ein Problem, aber für MySQL ist es besonders schwerwiegend. Wenn MySQL eine Verbindungsanfrage erhält, führt es sowohl einen Forward als auch einen Reverse DNS Lookup durch. Es gibt viele Gründe, warum das schief gehen kann. Wenn das passiert, werden Verbindungen verweigert, der Verbindungsaufbau zum Server wird verlangsamt und es kommt zu Störungen, bis hin zu Denial-of-Service-Angriffen. Wenn du die Option skip_name_resolve aktivierst, führt MySQL überhaupt keine DNS-Abfragen durch. Das bedeutet aber auch, dass deine Benutzerkonten nur IP-Adressen, "localhost" oder IP-Adressen-Wildcards in der Spalte host enthalten dürfen. Ein Benutzerkonto, das einen Hostnamen in der Spalte host hat, kann sich nicht anmelden.

In der Regel ist es jedoch wichtiger, deine Einstellungen so anzupassen, dass du mit vielen Verbindungen und kleinen Abfragen effizient umgehen kannst. Eine der häufigsten Anpassungen ist die Änderung deines lokalen Portbereichs. Linux-Systeme haben eine Reihe von lokalen Ports, die verwendet werden können. Wenn die Verbindung zurück zu einem Anrufer hergestellt wird, wird ein lokaler Port verwendet. Wenn du viele gleichzeitige Verbindungen hast, können dir die lokalen Ports ausgehen.

Hier ist ein System, das mit Standardwerten konfiguriert ist:

$ cat /proc/sys/net/ipv4/ip_local_port_range
32768 61000

Manchmal musst du diese Werte vielleicht in einen größeren Bereich ändern. Zum Beispiel:

$ echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range

Das TCP-Protokoll ermöglicht es einem System, eingehende Verbindungen in eine Warteschlange zu stellen, wie einen Eimer. Wenn die Warteschlange voll ist, können die Clients keine Verbindung mehr herstellen. Du kannst wie folgt mehr Verbindungen in die Warteschlange stellen:

$ echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog

Bei Datenbankservern, die nur lokal genutzt werden, kannst du die Zeitspanne verkürzen, die nach dem Schließen eines Sockets verstreicht, falls die Gegenstelle nicht mehr funktioniert und ihre Seite der Verbindung nicht schließt. Der Standardwert ist auf den meisten Systemen eine Minute, was ziemlich lang ist:

$ echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout

Die meiste Zeit können diese Einstellungen auf den Standardwerten belassen werden. Normalerweise musst du sie nur ändern, wenn etwas Ungewöhnliches passiert, z. B. eine extrem schlechte Netzwerkleistung oder eine sehr große Anzahl von Verbindungen. Wenn du im Internet nach "TCP-Variablen" suchst, findest du eine Menge guter Informationen über diese und viele andere Variablen.

Auswahl eines Dateisystems

Die Wahl deines Dateisystems hängt stark von deinem Betriebssystem ab. Bei vielen Systemen, wie z. B. Windows, hast du nur eine oder zwei Möglichkeiten, und nur eine (NTFS) ist wirklich praktikabel. GNU/Linux hingegen unterstützt viele Dateisysteme.

Viele Leute wollen wissen, welches Dateisystem die beste Leistung für MySQL unter GNU/Linux bietet oder, noch genauer, welches der zur Auswahl stehenden Systeme das beste für InnoDB ist. Die Benchmarks zeigen, dass die meisten von ihnen in vielerlei Hinsicht sehr nahe beieinander liegen, aber die Leistung des Dateisystems zu betrachten, lenkt nur ab. Die Leistung des Dateisystems hängt stark von der Arbeitslast ab, und kein Dateisystem ist ein Allheilmittel. Die meiste Zeit wird ein bestimmtes Dateisystem nicht wesentlich besser oder schlechter sein als ein anderes Dateisystem. Die Ausnahme ist, wenn du an die Grenzen eines Dateisystems stößt, z. B. beim Umgang mit Gleichzeitigkeit, bei der Arbeit mit vielen Dateien, bei der Fragmentierung und so weiter.

Insgesamt ist es am besten, wenn du ein Journaling-Dateisystem wie ext4, XFS oder ZFS verwendest. Andernfalls kann die Überprüfung des Dateisystems nach einem Absturz sehr lange dauern.

Wenn du ext3 oder seinen Nachfolger ext4 verwendest, hast du drei Optionen für die Art und Weise, wie die Daten gejourned werden, die du in den Einhängeoptionen von /etc/fstab angeben kannst:

data=writeback

Diese Option bedeutet, dass nur Metadaten-Schreibvorgänge protokolliert werden. Die Schreibvorgänge in den Metadaten werden nicht mit den Daten synchronisiert. Dies ist die schnellste Konfiguration und kann in der Regel sicher mit InnoDB verwendet werden, da es über ein eigenes Transaktionsprotokoll verfügt. Die Ausnahme ist, dass ein Absturz genau zum richtigen Zeitpunkt eine Beschädigung einer .frm-Datei in einer Vor-8.0-Version von MySQL verursachen kann.

Hier ist ein Beispiel dafür, wie diese Konfiguration Probleme verursachen kann. Angenommen, ein Programm beschließt, eine Datei zu erweitern, um sie größer zu machen. Die Metadaten (die Dateigröße) werden protokolliert und geschrieben, bevor die Daten tatsächlich in die (nun größere) Datei geschrieben werden. Das hat zur Folge, dass der Schwanz der Datei - der neu erweiterte Bereich - Müll enthält.

data=ordered

Diese Option protokolliert ebenfalls nur die Metadaten, sorgt aber für eine gewisse Konsistenz, indem sie die Daten vor den Metadaten schreibt, damit sie konsistent bleiben. Sie ist nur geringfügig langsamer als die Option writeback und viel sicherer, wenn es zu einem Absturz kommt. Nehmen wir an, dass ein Programm eine Datei erweitern möchte, dann spiegeln die Metadaten der Datei die neue Größe erst dann wider, wenn die Daten in den neu erweiterten Bereich geschrieben wurden.

data=journal

Diese Option sorgt für ein atomares Journalverhalten, indem die Daten in das Journal geschrieben werden, bevor sie an den endgültigen Speicherort geschrieben werden. Sie ist in der Regel unnötig und hat einen viel höheren Overhead als die beiden anderen Optionen. In einigen Fällen kann sie jedoch die Leistung verbessern, da das Dateisystem durch das Journaling die Schreibvorgänge an den endgültigen Speicherort der Daten verzögern kann.

Ungeachtet des Dateisystems gibt es einige spezielle Optionen, die du am besten deaktivierst, weil sie keinen Nutzen bringen und einen ziemlichen Overhead verursachen können. Die bekannteste ist die Aufzeichnung der Zugriffszeit, die auch dann einen Schreibzugriff erfordert, wenn du eine Datei oder ein Verzeichnis gerade liest. Um diese Option zu deaktivieren, füge die Einhängeoptionen noatime,nodiratime zu deiner /etc/fstab hinzu. Das kann die Leistung je nach Arbeitslast und Dateisystem um 5-10 % steigern (in anderen Fällen macht es vielleicht keinen großen Unterschied). Hier ist eine Beispielzeile der /etc/fstab für die erwähnten ext3-Optionen:

/dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1

Du kannst auch das Read-Ahead-Verhalten des Dateisystems einstellen, weil es möglicherweise redundant ist. InnoDB zum Beispiel macht seine eigene Read-Ahead-Vorhersage. Die Deaktivierung oder Einschränkung von Read-ahead ist besonders auf dem UFS von Solaris von Vorteil. Wenn du innodb_​flush_​method=​O_DIRECT verwendest, wird Read-Ahead automatisch deaktiviert.

Einige Dateisysteme unterstützen keine Funktionen, die du vielleicht brauchst. Zum Beispiel könnte die Unterstützung für direkte E/A wichtig sein, wenn du die O_DIRECT Flush-Methode für InnoDB verwendest. Außerdem können manche Dateisysteme besser mit einer großen Anzahl von zugrunde liegenden Laufwerken umgehen als andere; XFS ist in dieser Hinsicht oft besser als ext3, zum Beispiel. Wenn du Logical Volume Manager (LVM)-Snapshots für die Initialisierung von Replikaten oder Backups verwenden willst, solltest du sicherstellen, dass dein gewähltes Dateisystem und die LVM-Version gut zusammenarbeiten.

Tabelle 4-2 fasst die Eigenschaften einiger gängiger Dateisysteme zusammen.

Tabelle 4-2. Gemeinsame Merkmale von Dateisystemen

Dateisystem	Operationssystem	Journaling	Große Verzeichnisse
ext3	GNU/Linux	Optional	Optional/teilweise
ext4	GNU/Linux	Ja	Ja
Journaled File System (JFS)	GNU/Linux	Ja	Nein
NTFS	Windows	Ja	Ja
ReiserFS	GNU/Linux	Ja	Ja
UFS (Solaris)	Solaris	Ja	Abstimmbar
UFS (FreeBSD)	FreeBSD	Nein	Optional/teilweise
UFS2	FreeBSD	Nein	Optional/teilweise
XFS	GNU/Linux	Ja	Ja
ZFS	GNU/Linux, Solaris, FreeBSD	Ja	Ja

Wir empfehlen normalerweise die Verwendung des XFS-Dateisystems. Das ext3-Dateisystem hat einfach zu viele schwerwiegende Einschränkungen, wie z. B. eine einzige Mutex pro Inode und schlechtes Verhalten, wie z. B. das Flushen aller schmutzigen Blöcke im gesamten Dateisystem auf fsync() anstatt nur der schmutzigen Blöcke einer Datei. Das ext4-Dateisystem ist eine akzeptable Wahl, obwohl es in bestimmten Kernelversionen zu Leistungsengpässen gekommen ist, die du untersuchen solltest, bevor du dich dafür entscheidest.

Wenn du ein Dateisystem für eine Datenbank in Betracht ziehst, solltest du darauf achten, wie lange es schon verfügbar ist, wie ausgereift es ist und wie sehr es sich in Produktionsumgebungen bewährt hat. Die Bits des Dateisystems sind die niedrigste Stufe der Datenintegrität, die du in einer Datenbank hast.

Auswahl eines Zeitplanungsprogramms für die Warteschlange

Unter GNU/Linux bestimmt das Zeitplannungsprogramm die Reihenfolge, in der Anfragen an ein Blockgerät an das darunter liegende Gerät gesendet werden. Die Standardeinstellung ist Completely Fair Queuing, oder cfq. Für den gelegentlichen Gebrauch auf Laptops und Desktops ist das in Ordnung, da es hilft, eine E/A-Aushungerung zu verhindern, aber für Server ist es schrecklich. Bei der Arbeitslast, die MySQL erzeugt, führt es zu sehr schlechten Antwortzeiten, weil es einige Anfragen unnötigerweise in der Warteschlange aufhält.

Mit folgendem Befehl kannst du sehen, welche Zeitplannungsprogramme verfügbar sind und welches aktiv ist:

$ cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

Du solltest sda durch den Gerätenamen der Festplatte ersetzen, an der du interessiert bist. In unserem Beispiel zeigen die eckigen Klammern an, welches Zeitplannungsprogramm für dieses Gerät verwendet wird. Die beiden anderen Optionen sind für Hardware der Serverklasse geeignet und funktionieren in den meisten Fällen gleich gut. Das Zeitplannungsprogramm noop eignet sich für Geräte, die ihre eigene Zeitplanung im Hintergrund durchführen, wie z. B. Hardware-RAID-Controller und SANs (Storage Area Networks), und deadline ist sowohl für RAID-Controller als auch für direkt angeschlossene Festplatten geeignet. Unsere Benchmarks zeigen nur einen geringen Unterschied zwischen diesen beiden. Die Hauptsache ist, dass du etwas anderes als cfq verwendest, denn das kann zu erheblichen Leistungsproblemen führen.

Speicher und Tausch

MySQL arbeitet am besten, wenn ihm eine große Menge an Speicher zugewiesen wird. Wie wir in Kapitel 1 gelernt haben, nutzt InnoDB den Speicher als Cache, um Plattenzugriffe zu vermeiden. Das bedeutet, dass die Leistung des Speichersystems einen direkten Einfluss darauf hat, wie schnell Abfragen bedient werden. Eine der besten Möglichkeiten, um einen schnelleren Speicherzugriff zu gewährleisten, ist bis heute, den eingebauten Speicherallokator (glibc) durch einen externen wie tcmalloc oder jemalloc zu ersetzen. Zahlreiche Benchmarks² haben gezeigt, dass beide im Vergleich zu glibc eine bessere Leistung und eine geringere Speicherfragmentierung bieten.

Swapping findet statt, wenn das Betriebssystem einen Teil des virtuellen Speichers auf die Festplatte schreibt, weil es nicht genug physischen Speicher dafür hat. Swapping ist für Prozesse, die auf dem Betriebssystem laufen, transparent. Nur das Betriebssystem weiß, ob sich eine bestimmte virtuelle Speicheradresse im physischen Speicher oder auf der Festplatte befindet.

Wenn du SSDs verwendest, ist der Leistungsverlust nicht mehr so groß wie bei HDDs. Trotzdem solltest du Swapping vermeiden - und sei es nur, um unnötige Schreibvorgänge zu vermeiden, die die Lebensdauer der Festplatte verkürzen könnten. Du kannst auch in Erwägung ziehen, keinen Swap zu verwenden. Damit verzichtest du zwar auf das Potenzial, aber du kommst in eine Situation, in der ein Mangel an Speicher zum Abbruch des Prozesses führen kann.

Unter GNU/Linux kannst du das Swapping mit vmstat überwachen (wir zeigen einige Beispiele im nächsten Abschnitt). Du musst dir die Swap-I/O-Aktivität ansehen, die in den Spalten si und so angezeigt wird, und nicht die Swap-Nutzung, die in der Spalte swpd angezeigt wird. Die Spalte swpd kann Prozesse anzeigen, die zwar geladen sind, aber nicht genutzt werden, was nicht wirklich problematisch ist. Die Werte der Spalten si und so sollten 0 lauten und auf jeden Fall weniger als 10 Blöcke pro Sekunde betragen.

In extremen Fällen kann eine zu hohe Speicherzuweisung dazu führen, dass dem Betriebssystem der Auslagerungsspeicher ausgeht. Wenn das passiert, kann der daraus resultierende Mangel an virtuellem Speicher MySQL zum Absturz bringen. Aber selbst wenn der Swap-Speicher nicht erschöpft ist, kann sehr aktives Swapping dazu führen, dass das gesamte Betriebssystem nicht mehr reagiert, so dass du dich nicht einmal mehr einloggen und den MySQL-Prozess beenden kannst. Manchmal kann sich der Linux-Kernel sogar komplett aufhängen, wenn ihm der Swap-Speicherplatz ausgeht. Wir empfehlen dir, deine Datenbanken ohne Swap Space zu betreiben. Die Festplatte ist immer noch um eine Größenordnung langsamer als der Arbeitsspeicher, und das vermeidet alle hier erwähnten Kopfschmerzen.

Eine weitere Sache, die bei extremem Druck auf den virtuellen Speicher häufig passiert, ist, dass der OOM-Prozess (Out-of-Memory) einsetzt und etwas beendet. Das ist häufig MySQL, aber es kann auch ein anderer Prozess wie SSH sein, der dazu führt, dass das System nicht mehr über das Netzwerk erreichbar ist. Du kannst dies verhindern, indem du den Wert oom_adj oder oom_score_adj für den SSH-Prozess einstellst. Wenn du mit dedizierten Datenbankservern arbeitest, empfehlen wir dir dringend, alle wichtigen Prozesse wie MySQL und SSH zu identifizieren und den OOM-Killer-Score proaktiv anzupassen, um zu verhindern, dass diese Prozesse als erste zur Beendigung ausgewählt werden.

Du kannst die meisten Swapping-Probleme lösen, indem du deine MySQL-Puffer richtig konfigurierst, aber manchmal beschließt das virtuelle Speichersystem des Betriebssystems, MySQL trotzdem auszulagern, was manchmal damit zusammenhängt, wie nonuniform memory access (NUMA) funktioniert³ in Linux. Das passiert in der Regel, wenn das Betriebssystem viele Ein- und Ausgabedaten von MySQL sieht und deshalb versucht, den Dateicache zu vergrößern, um mehr Daten zu speichern. Wenn der Speicher nicht ausreicht, muss etwas ausgelagert werden, und dieses Etwas könnte MySQL selbst sein. Einige ältere Linux-Kernel-Versionen haben auch kontraproduktive Prioritäten, die Dinge auslagern, wenn sie nicht ausgelagert werden sollten.

Betriebssysteme erlauben normalerweise eine gewisse Kontrolle über virtuellen Speicher und E/A. Wir erwähnen ein paar Möglichkeiten, sie unter GNU/Linux zu kontrollieren. Die grundlegendste ist, den Wert von /proc/sys/vm/swappiness auf einen niedrigen Wert zu ändern, z. B. 0 oder 1. Dadurch wird der Kernel angewiesen, nur dann zu swappen, wenn der Bedarf an virtuellem Speicher extrem ist. Hier ein Beispiel, wie du den aktuellen Wert überprüfen kannst:

$ cat /proc/sys/vm/swappiness
60

Der angezeigte Wert von 60 ist die Standardeinstellung für die Auslagerungsfähigkeit (der Bereich reicht von 0 bis 100). Das ist eine sehr schlechte Voreinstellung für Server. Er ist nur für Laptops geeignet. Server sollten auf 0 eingestellt werden:

$ echo 0 > /proc/sys/vm/swappiness

Eine andere Möglichkeit besteht darin, die Art und Weise zu ändern, wie die Speicher-Engines Daten lesen und schreiben. Zum Beispiel entlastet die Verwendung von innodb_flush_method=O_DIRECT den E/A-Druck. Direkte E/A wird nicht zwischengespeichert, so dass das Betriebssystem dies nicht als Grund sieht, die Größe des Dateicaches zu erhöhen. Dieser Parameter funktioniert nur für InnoDB.

Eine andere Möglichkeit ist, die MySQL-Konfigurationsoption memlock zu verwenden, die MySQL im Speicher sperrt. Dadurch wird Swapping vermieden, aber es kann gefährlich sein: Wenn nicht genügend sperrbarer Speicher übrig ist, kann MySQL abstürzen, wenn es versucht, mehr Speicher zuzuweisen. Probleme können auch entstehen, wenn zu viel Speicher gesperrt wird und nicht mehr genug für das Betriebssystem übrig ist.

Viele der Tricks sind spezifisch für eine bestimmte Kernelversion, also sei vorsichtig, vor allem wenn du ein Upgrade durchführst. Bei manchen Workloads ist es schwer, das Betriebssystem zu einem vernünftigen Verhalten zu bewegen, und dein einziger Ausweg könnte darin bestehen, die Puffergrößen auf suboptimale Werte zu senken.

Status des Betriebssystems

Dein Betriebssystem bietet Werkzeuge, mit denen du herausfinden kannst, was das Betriebssystem und die Hardware tun. In diesem Abschnitt zeigen wir dir anhand von Beispielen, wie du zwei weit verbreitete Tools nutzen kannst: iostat und vmstat. Wenn dein System keines dieser Tools bietet, ist es wahrscheinlich, dass es etwas Ähnliches bietet. Unser Ziel ist es also nicht, dich zu einem Experten im Umgang mit iostat oder vmstat zu machen, sondern dir einfach zu zeigen, worauf du achten musst, wenn du versuchst, Probleme mit diesen Tools zu diagnostizieren.

Zusätzlich zu diesen Tools stellt dein Betriebssystem vielleicht noch andere zur Verfügung, wie z.B. mpstat oder sar. Wenn du dich für andere Teile deines Systems interessierst, z. B. für das Netzwerk, solltest du stattdessen Tools wie ifconfig (das unter anderem anzeigt, wie viele Netzwerkfehler aufgetreten sind) oder netstat verwenden.

Standardmäßig erstellen vmstat und iostat nur einen Bericht mit den Durchschnittswerten verschiedener Zähler seit dem Start des Servers, was nicht sehr nützlich ist. Du kannst jedoch beiden Tools ein Intervall-Argument mitgeben. Dadurch erzeugen sie inkrementelle Berichte, die zeigen, was der Server gerade macht, was viel relevanter ist. (Die erste Zeile zeigt die Statistiken seit dem Start des Systems; du kannst diese Zeile einfach ignorieren).

Wie man die vmstat-Ausgabe liest

Schauen wir uns unter zunächst ein Beispiel für vmstat an. Um alle fünf Sekunden einen neuen Bericht mit den Größenangaben in Megabyte auszudrucken, verwendest du den folgenden Befehl:

$ vmstat -SM 5
procs -------memory------- -swap- -----io---- ---system---- ------cpu-----
 r  b swpd free buff cache  si so    bi    bo     in     cs us sy id wa st
11  0    0 2410    4 57223   0  0  9902 35594 122585 150834 10  3 85  1  0
10  2    0 2361    4 57273   0  0 23998 35391 124187 149530 11  3 84  2  0

Du kannst vmstat mit Strg-C beenden. Die Ausgabe, die du siehst, hängt von deinem Betriebssystem ab, also musst du vielleicht die Handbuchseite lesen, um es herauszufinden.

Wie bereits erwähnt, zeigt die erste Zeile der Werte die Durchschnittswerte seit dem Start des Servers an, obwohl wir eine inkrementelle Ausgabe angefordert haben. Die zweite Zeile zeigt, was gerade passiert, und die folgenden Zeilen zeigen, was in Abständen von fünf Sekunden passiert. Die Spalten sind nach einer der folgenden Überschriften gruppiert:

procs

Die Spalte r zeigt, wie viele Prozesse auf CPU-Zeit warten. Die Spalte b zeigt, wie viele Prozesse sich im ununterbrochenen Ruhezustand befinden, was im Allgemeinen bedeutet, dass sie auf E/A warten (Festplatte, Netzwerk, Benutzereingaben usw.).

Speicher

Die Spalte swpd zeigt, wie viele Blöcke auf die Festplatte ausgelagert werden (paged). Die restlichen drei Spalten zeigen, wie viele Blöcke free (unbenutzt) sind, wie viele für Puffer (buff) verwendet werden und wie viele für die cache des Betriebssystems genutzt werden.

tauschen

Diese Spalten zeigen die Swap-Aktivität an: wie viele Blöcke pro Sekunde das Betriebssystem ein- (von der Festplatte) und auslagert (auf die Festplatte). Sie sind viel wichtiger zu überwachen als die Spalte swpd. Wir möchten, dass si und so die meiste Zeit auf 0 stehen und wir möchten auf keinen Fall mehr als 10 Blöcke pro Sekunde sehen. Auch Bursts sind schlecht.

io

Diese Spalten zeigen, wie viele Blöcke pro Sekunde von (bi) Blockgeräten eingelesen und auf (bo) geschrieben werden. Dies spiegelt normalerweise die Festplatten-E/A wider.

System

Diese Spalten zeigen die Anzahl der Interrupts pro Sekunde (in) und die Anzahl der Kontextwechsel pro Sekunde (cs).

cpu

Diese Spalten zeigen den prozentualen Anteil der Gesamt-CPU-Zeit an, der für die Ausführung von Benutzer- (Nicht-Kernel-) Code, System- (Kernel-) Code, Leerlauf und Warten auf E/A aufgewendet wird. Eine mögliche fünfte Spalte (st) zeigt den Prozentsatz, der von einer virtuellen Maschine "gestohlen" wurde, wenn du Virtualisierung verwendest. Dies bezieht sich auf die Zeit, in der etwas auf der virtuellen Maschine ausgeführt werden konnte, der Hypervisor sich aber entschieden hat, stattdessen etwas anderes auszuführen. Wenn die virtuelle Maschine nichts ausführen will und der Hypervisor stattdessen etwas anderes ausführt, zählt das nicht als gestohlene Zeit.

Die Ausgabe von vmstat ist systemabhängig, daher solltest du die Manpage deines Systems vmstat(8) lesen, wenn dein System anders aussieht als das Beispiel, das wir gezeigt haben.

Wie man die iostat-Ausgabe liest

Kommen wir nun zu iostat. Standardmäßig zeigt es einige der gleichen Informationen zur CPU-Auslastung an wie vmstat. Normalerweise sind wir aber nur an den E/A-Statistiken interessiert, also verwenden wir den folgenden Befehl, um nur die erweiterten Gerätestatistiken anzuzeigen:

$ iostat -dxk 5
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s 
sda 0.00 0.00 1060.40 3915.00 8483.20 42395.20 

avgrq-sz avgqu-sz await r_await w_await svctm %util
 20.45 3.68 0.74 0.57 0.78 0.20 98.22

Wie bei vmstat zeigt der erste Bericht die Durchschnittswerte seit dem Hochfahren des Servers an (wir lassen ihn in der Regel weg, um Platz zu sparen), und die folgenden Berichte zeigen inkrementelle Durchschnittswerte. Es gibt eine Zeile pro Gerät.

Es gibt verschiedene Optionen, um Spalten ein- oder auszublenden. Die offizielle Dokumentation ist etwas verwirrend, und wir mussten uns mit dem Quellcode beschäftigen, um herauszufinden, was wirklich angezeigt wird. Hier ist, was jede Spalte anzeigt:

rrqm/s und wrqm/s

Die Anzahl der zusammengefassten Lese- und Schreibanfragen in der Warteschlange pro Sekunde. Zusammengeführt bedeutet, dass das Betriebssystem mehrere logische Anfragen aus der Warteschlange genommen und zu einer einzigen Anfrage an das eigentliche Gerät zusammengefasst hat.

r/s und w/s

Die Anzahl der Lese- und Schreibanfragen, die pro Sekunde an das Gerät gesendet werden.

rkB/s und wkB/s

Die Anzahl der gelesenen und geschriebenen Kilobytes pro Sekunde.

avgrq-sz

Die Größe der Anfrage in Sektoren.

avgqu-sz

Die Anzahl der Anfragen, die in der Warteschlange des Geräts warten.

await

Die Anzahl der Millisekunden, die in der Plattenwarteschlange verbracht wurden.

r_await und w_await

Die durchschnittliche Zeit in Millisekunden für Leseanfragen, die an das zu bedienende Gerät gestellt werden, sowohl für Lese- als auch für Schreibanfragen. Darin enthalten ist die Zeit, die die Anfragen in der Warteschlange verbracht haben, und die Zeit, in der sie bedient wurden.

svctm

Die Anzahl der Millisekunden, die für die Bearbeitung von Anfragen aufgewendet wurden, ohne die Zeit in der Warteschlange.

%util⁴

Der Prozentsatz der Zeit, in der mindestens eine Anfrage aktiv war. Das ist ein sehr verwirrender Name. Es handelt sich nicht um die Auslastung des Geräts, wenn du mit der Standarddefinition von Auslastung in der Warteschlangentheorie vertraut bist. Ein Gerät mit mehr als einer Festplatte (z. B. ein RAID-Controller) sollte in der Lage sein, eine höhere Gleichzeitigkeit als 1 zu unterstützen, aber %util wird niemals 100% überschreiten, es sei denn, es liegt ein Rundungsfehler in der Berechnung vor. Im Gegensatz zu dem, was in der Dokumentation steht, ist sie daher kein guter Indikator für die Sättigung des Geräts, es sei denn, es handelt sich um eine einzelne physische Festplatte.

Anhand der Ausgabe kannst du einige Fakten über das E/A-Subsystem eines Rechners ableiten. Eine wichtige Kennzahl ist die Anzahl der gleichzeitig bearbeiteten Anfragen. Da die Lese- und Schreibvorgänge pro Sekunde erfolgen und die Servicezeit in Tausendstelsekunden angegeben wird, kannst du mithilfe des Little'schen Gesetzes die folgende Formel für die Anzahl der gleichzeitig bearbeiteten Anfragen ableiten:

concurrency = (r/s + w/s) * (svctm/1000)

Setzt man die oben genannten Zahlen in die Gleichzeitigkeitsformel ein, ergibt sich eine Gleichzeitigkeit von etwa 0,995. Das bedeutet, dass das Gerät während des Stichprobenintervalls im Durchschnitt weniger als eine Anfrage auf einmal bearbeitet hat.

Andere hilfreiche Tools

Wir haben vmstat und iostat gezeigt, weil sie weit verbreitet sind und vmstat in der Regel standardmäßig auf vielen Unix-ähnlichen Betriebssystemen installiert ist. Jedes dieser Tools hat jedoch seine Grenzen, z. B. verwirrende Maßeinheiten, Stichproben in Intervallen, die nicht mit den Aktualisierungen der Statistiken durch das Betriebssystem übereinstimmen, und die Unmöglichkeit, alle Metriken auf einmal zu sehen. Wenn diese Tools deine Anforderungen nicht erfüllen, solltest du dich für dstat oder collectl interessieren.

Wir verwenden auch gerne mpstat, um die CPU-Statistiken zu beobachten. Damit erhältst du einen viel besseren Überblick über das Verhalten der einzelnen CPUs, anstatt sie alle zusammen zu gruppieren. Manchmal ist das sehr wichtig, wenn du ein Problem diagnostizieren willst. Auch blktrace kann hilfreich sein, wenn du die Festplatten-E/A-Nutzung untersuchst.

Percona hat seinen eigenen Ersatz für iostat geschrieben: pt-diskstats. Es ist Teil des Percona Toolkits. Es behebt einige der Kritikpunkte an iostat, z. B. die Art und Weise, wie Lese- und Schreibvorgänge zusammengefasst dargestellt werden, und die fehlende Sichtbarkeit der Gleichzeitigkeit. Außerdem ist es interaktiv und tastengesteuert, sodass du die Ansicht vergrößern und verkleinern, die Aggregation ändern, Geräte herausfiltern und Spalten ein- und ausblenden kannst. Es ist eine großartige Möglichkeit, eine Stichprobe von Festplattenstatistiken zu erstellen, die du mit einem einfachen Shell-Skript erfassen kannst, auch wenn du das Tool nicht installiert hast. Du kannst Stichproben der Festplattenaktivität erfassen und sie per E-Mail versenden oder für eine spätere Analyse speichern.

Perf, der Linux-Profiler, ist ein unschätzbares Werkzeug, um die Vorgänge auf Betriebssystemebene zu untersuchen. Mit Perf kannst du allgemeine Informationen über das Betriebssystem abrufen, zum Beispiel warum der Kernel die CPU so stark beansprucht. Du kannst auch bestimmte Prozess-IDs untersuchen, damit du siehst, wie MySQL mit dem Betriebssystem interagiert. Die Untersuchung der Systemleistung ist ein sehr tiefes Thema, daher empfehlen wir dir Systems Performance, Second Edition von Brendan Gregg (Pearson) als hervorragende weiterführende Lektüre.

Zusammenfassung

Hardware für MySQL auszuwählen und zu konfigurieren und MySQL für die Hardware zu konfigurieren, ist keine mystische Kunst. Im Allgemeinen brauchst du die gleichen Fähigkeiten und Kenntnisse, die du auch für die meisten anderen Zwecke brauchst. Es gibt jedoch einige MySQL-spezifische Dinge, die du wissen solltest.

Was wir den meisten Leuten empfehlen, ist, ein gutes Gleichgewicht zwischen Leistung und Kosten zu finden. Erstens verwenden wir aus vielen Gründen gerne Commodity-Server. Wenn du z. B. Probleme mit einem Server hast und ihn für eine Diagnose außer Betrieb nehmen musst oder ihn einfach gegen einen anderen Server austauschen willst, ist das mit einem Server für 5.000 Dollar viel einfacher als mit einem, der 50.000 Dollar oder mehr kostet. Außerdem ist MySQL in der Regel besser für handelsübliche Hardware geeignet - sowohl was die Software selbst als auch die typischen Arbeitslasten angeht.

Die vier grundlegenden Ressourcen, die MySQL benötigt, sind CPU-, Speicher-, Festplatten- und Netzwerkressourcen. Das Netzwerk erweist sich nur selten als ernsthafter Engpass, aber CPUs, Speicher und Festplatten schon. Das Gleichgewicht zwischen Geschwindigkeit und Menge hängt wirklich von der Arbeitslast ab, und du solltest ein Gleichgewicht zwischen schnell und viel anstreben, soweit es dein Budget zulässt. Je mehr Gleichzeitigkeit du erwartest, desto mehr CPUs solltest du einsetzen, um deine Arbeitslast zu bewältigen.

Die Beziehung zwischen CPUs, Arbeitsspeicher und Festplatten ist kompliziert, und Probleme in einem Bereich zeigen sich oft an anderer Stelle. Bevor du Ressourcen auf ein Problem wirfst, solltest du dich fragen, ob du stattdessen Ressourcen auf ein anderes Problem werfen solltest. Brauchst du mehr E/A-Kapazität oder einfach nur mehr Speicher, wenn du an der Grenze bist? Die Antwort hängt von der Größe der Arbeitsmenge ab, d. h. von der Menge der Daten, die in einem bestimmten Zeitraum am häufigsten benötigt werden.

Solid-State-Geräte sind großartig, um die Serverleistung insgesamt zu verbessern, und sollten jetzt generell der Standard für Datenbanken sein, insbesondere für OLTP-Workloads. Der einzige Grund, weiterhin HDDs zu verwenden, sind Systeme mit extrem eingeschränktem Budget oder solche, bei denen du eine unglaublich große Menge an Speicherplatz benötigst - in der Größenordnung von Petabytes in einer Data-Warehousing-Situation.

Was das Betriebssystem angeht, gibt es nur ein paar wichtige Dinge, die du beachten musst, vor allem in Bezug auf die Speicherung, das Netzwerk und die Verwaltung des virtuellen Speichers. Wenn du GNU/Linux verwendest, wie es die meisten MySQL-Nutzer tun, empfehlen wir dir, das XFS-Dateisystem zu verwenden und das Zeitplannungsprogramm für die Auslagerung und die Festplattenwarteschlange auf Werte zu setzen, die für einen Server angemessen sind. Es gibt einige Netzwerkparameter, die du eventuell ändern musst, und du möchtest vielleicht einige andere Dinge anpassen (z. B. SELinux deaktivieren), aber diese Änderungen sind eine Frage der Präferenz.

¹ Beliebtes Haiku: Es ist nicht DNS. Auf keinen Fall ist es DNS. Es war DNS.

² Vergleiche dazu findest du in den Blog-Beiträgen "Auswirkung von Speicherallokatoren auf die MySQL-Leistung" und "MySQL (oder Percona) Speicherauslastungstests".

³ Mehr dazu findest du in diesem Blogbeitrag.

⁴ Software-RAID, wie z. B. MD/RAID, zeigt möglicherweise nicht die Auslastung des RAID-Verbunds selbst an.