7 Schlüssel für eine bessere MySQL-Leistung

Peter Zaitsev ist Mitbegründer und CEO von  Percona .

Wir messen Anwendungen unter anderem anhand der Leistung. Eine der Metriken für die Anwendungsleistung ist die Benutzererfahrung, die im Allgemeinen bedeutet: "Musste der Benutzer länger als eine angemessene Zeit warten, um das zu erhalten, was er wollte?"

Diese Metrik kann in verschiedenen Szenarien unterschiedliche Bedeutungen haben. Bei einer mobilen Einkaufs-App können die Antwortzeiten nicht mehr als ein paar Sekunden betragen. Auf der HR-Seite eines Mitarbeiters können Antworten einige Sekunden länger dauern.

Wir haben viel Forschung darüber, wie sich die Leistung auf das Benutzerverhalten auswirkt:

  • 79 Prozent der Kunden kehren seltener zu einer langsamen Website zurück
  • 47 Prozent der Verbraucher erwarten, dass eine Webseite innerhalb von 2 Sekunden oder weniger geladen wird
  • 40 Prozent der Benutzer verlassen eine Website, wenn das Laden länger als drei Sekunden dauert
  • Eine Verzögerung der Ladezeit von einer Sekunde um eine Sekunde kann zu einem Konvertierungsverlust von 7 Prozent und zu 11 Prozent weniger Seitenaufrufen führen

Unabhängig vom Standard ist es wichtig, eine gute Leistung für Anwendungen aufrechtzuerhalten. Andernfalls beschweren sich Benutzer (oder gehen schlimmer noch zu einer anderen Anwendung). Einer der Faktoren, die die Anwendungsleistung beeinflussen, ist die Datenbankleistung. Die Interaktion zwischen Anwendungen, Websites und Datenbanken ist entscheidend für die Festlegung der Anwendungsleistung.

Eine zentrale Komponente dieser Interaktion ist, wie Anwendungen die Datenbank abfragen und wie die Datenbank auf Anforderungen reagiert. In jeder Hinsicht ist MySQL eines der beliebtesten Datenbankverwaltungssysteme. Immer mehr Unternehmen setzen in ihren Produktionsumgebungen auf MySQL (und andere Open Source-Datenbanken) als Datenbanklösung.

Es gibt viele Methoden zum Konfigurieren von MySQL, mit denen sichergestellt werden kann, dass Ihre Datenbank schnell und mit minimaler Verschlechterung der Anwendungsleistung auf Anfragen reagiert.

Im Folgenden finden Sie einige wichtige Tipps, mit denen Sie die Leistung Ihrer MySQL-Datenbank optimieren können.

MySQL-Optimierungsschlüssel Nr. 1: Erfahren Sie, wie Sie ihn verwenden EXPLAIN

Die beiden wichtigsten Entscheidungen, die Sie mit einer Datenbank treffen, sind das Entwerfen, wie Beziehungen zwischen Anwendungsentitäten Tabellen zugeordnet werden (das Datenbankschema), und das Entwerfen, wie Anwendungen die benötigten Daten in dem von ihnen benötigten Format erhalten (Abfragen).

Komplizierte Anwendungen können komplizierte Schemata und Abfragen haben. Wenn Sie die Leistung und Skalierbarkeit erzielen möchten, die Ihre Anwendungen erfordern, können Sie sich nicht nur auf die Intuition verlassen, um zu verstehen, wie Abfragen ausgeführt werden.

Anstatt zu raten und zu hoffen, sollten Sie lernen, wie Sie den EXPLAINBefehl verwenden. Dieser Befehl zeigt Ihnen, wie eine Abfrage ausgeführt wird, und gibt Ihnen einen Einblick in die zu erwartende Leistung und in die Skalierung der Abfrage bei sich ändernder Datengröße.

Es gibt eine Reihe von Tools wie MySQL Workbench EXPLAIN, mit denen Sie die Ausgabe für sich visualisieren können. Sie müssen jedoch die Grundlagen verstehen, um einen Sinn daraus zu ziehen.

Es gibt zwei verschiedene Formate, in denen der EXPLAINBefehl eine Ausgabe bereitstellt: das altmodische Tabellenformat und ein moderneres, strukturiertes JSON-Dokument, das wesentlich detaillierter ist (siehe unten):

mysql> EXPLAIN format = json wähle avg (k) aus sbtest1 aus, wobei id zwischen 1000 und 2000 \ G liegt

*************************** 1. Reihe ******************** *******

ERKLÄREN: {

  "Query_block": {

    "Select_id": 1,

    "Cost_info": {

      "Query_cost": "762.40"

    "Tabelle": {

      "Tabellenname": "sbtest1",

      "Access_type": "range",

      "Mögliche_Tasten": [

        "PRIMARY"

      ],

      "Schlüssel": "PRIMARY",

      "Used_key_parts": [

        "Ich würde"

      ],

      "Schlüssellänge": "4",

      "Rows_examined_per_scan": 1874,

      "Rows_produced_per_join": 1874,

      "Gefiltert": "100,00",

      "Cost_info": {

        "Read_cost": "387.60",

        "Eval_cost": "374.80",

        "Prefix_cost": "762.40",

        "Data_read_per_join": "351K"

      },

      "Used_columns": [

        "Ich würde",

        "K"

      ],

      "Attached_condition": "(` sbtest`.`sbtest1`.`id` zwischen 1000 und 2000) "

    }}

  }}

}}

Eine Komponente, die Sie sich ansehen sollten, sind "Abfragekosten". Die Abfragekosten beziehen sich darauf, wie teuer MySQL diese bestimmte Abfrage im Hinblick auf die Gesamtkosten der Abfrageausführung betrachtet, und basieren auf vielen verschiedenen Faktoren.

Einfache Abfragen haben im Allgemeinen Abfragekosten von weniger als 1.000. Abfragen mit Kosten zwischen 1.000 und 100.000 gelten als Abfragen mit mittleren Kosten und sind im Allgemeinen schnell, wenn Sie nur Hunderte solcher Abfragen pro Sekunde ausführen (nicht Zehntausende).  

Abfragen mit einem Preis von mehr als 100.000 sind teure Abfragen. Oft werden diese Abfragen immer noch schnell ausgeführt, wenn Sie ein einzelner Benutzer im System sind. Sie sollten jedoch sorgfältig überlegen, wie häufig Sie solche Abfragen in Ihren interaktiven Anwendungen verwenden (insbesondere, wenn die Anzahl der Benutzer zunimmt).

Natürlich sind dies Leistungszahlen für das Baseballstadion, aber sie demonstrieren das allgemeine Prinzip. Ihr System kann je nach Architektur und Konfiguration Abfrage-Workloads besser oder schlechter verarbeiten.

Der Hauptfaktor für die Abfragekosten ist, ob die Abfrage die Indizes korrekt verwendet. Der EXPLAINBefehl kann Ihnen mitteilen, ob eine Abfrage keine Indizes verwendet (normalerweise aufgrund der Art und Weise, wie die Indizes in der Datenbank erstellt werden oder wie die Abfrage selbst erstellt wurde). Deshalb ist es so wichtig, den Umgang zu lernen EXPLAIN.

MySQL-Optimierungsschlüssel Nr. 2: Erstellen Sie die richtigen Indizes

Ein Index verbessert die Abfrageleistung, indem die Datenmenge in der Datenbank reduziert wird, die von Abfragen gescannt werden muss. Indizes in MySQL werden verwendet, um den Zugriff auf die Datenbank zu beschleunigen und Datenbankbeschränkungen (wie UNIQUEund FOREIGN KEY) durchzusetzen .

Datenbankindizes ähneln Buchindizes. Sie werden an ihrem eigenen Speicherort aufbewahrt und enthalten Informationen, die sich bereits in der Hauptdatenbank befinden. Sie sind eine Referenzmethode oder eine Karte, auf der sich Daten befinden. Indizes ändern keine Daten in einer Datenbank. Sie zeigen einfach auf den Ort der Daten.

Es gibt keine Indizes, die für jede Arbeitslast immer geeignet sind. Sie sollten Indizes immer im Kontext der Abfragen betrachten, die das System ausführt.

Gut indizierte Datenbanken werden nicht nur schneller ausgeführt, sondern selbst ein einzelner fehlender Index kann das Crawlen einer Datenbank verlangsamen. Verwenden Sie EXPLAIN(wie zuvor empfohlen), um fehlende Indizes zu finden und hinzuzufügen. Aber seien Sie vorsichtig: Fügen Sie keine Indizes hinzu, die Sie nicht benötigen! Unnötige Indizes verlangsamen die Datenbanken (siehe meine Präsentation zu Best Practices für die MySQL-Indizierung).

MySQL-Optimierungsschlüssel Nr. 3: Keine Standardeinstellungen!

Wie jede Software verfügt MySQL über viele konfigurierbare Einstellungen, mit denen das Verhalten (und letztendlich die Leistung) geändert werden kann. Und wie bei jeder Software werden viele dieser konfigurierbaren Einstellungen von Administratoren ignoriert und im Standardmodus verwendet.

Um die beste Leistung von MySQL zu erzielen, ist es wichtig, die konfigurierbaren MySQL-Einstellungen zu verstehen und - was noch wichtiger ist - so einzustellen, dass sie für Ihre Datenbankumgebung am besten funktionieren.

Standardmäßig ist MySQL für eine kleine Entwicklungsinstallation und nicht für den Produktionsmaßstab ausgelegt. In der Regel möchten Sie MySQL so konfigurieren, dass alle verfügbaren Speicherressourcen verwendet werden und die Anzahl der für Ihre Anwendung erforderlichen Verbindungen zulässig ist. 

Hier sind drei MySQL-Einstellungen zur Leistungsoptimierung, die Sie immer genau untersuchen sollten:

innodb_buffer_pool_size: Im Pufferpool werden Daten und Indizes zwischengespeichert. Dies ist der Hauptgrund für die Verwendung eines Systems mit viel RAM als Datenbankserver. Wenn Sie nur die InnoDB-Speicher-Engine ausführen, weisen Sie normalerweise etwa 80 Prozent Ihres Speichers für den Pufferpool zu. Wenn Sie sehr komplizierte Abfragen ausführen oder eine sehr große Anzahl gleichzeitiger Datenbankverbindungen oder eine sehr große Anzahl von Tabellen haben, müssen Sie diesen Wert möglicherweise um eine Stufe verringern, um mehr Speicher für andere Zwecke zuzuweisen.

Wenn Sie die Größe des InnoDB-Pufferpools festlegen, müssen Sie sicherstellen, dass Sie ihn nicht zu groß festlegen, da dies sonst zu einem Austausch führt. Dies beeinträchtigt Ihre Datenbankleistung absolut. Eine einfache Möglichkeit zur Überprüfung besteht darin, die Swap-Aktivität im Diagramm Systemübersicht in Percona Monitoring and Management zu betrachten: 

Percona

Wie diese Grafik zeigt, ist ein gewisser Austausch von Zeit zu Zeit in Ordnung. Wenn Sie jedoch eine anhaltende Auslagerungsaktivität von 1 MB pro Sekunde oder mehr feststellen, müssen Sie die Größe Ihres Pufferpools (oder andere Speichernutzungen) reduzieren.

Machen Sie innodb_buffer_pool_sizesich keine Sorgen, wenn Sie den Wert für auf Anhieb nicht richtig erhalten. Ab MySQL 5.7 können Sie die Größe des InnoDB-Pufferpools dynamisch ändern, ohne den Datenbankserver neu zu starten.  

innodb_log_file_size: Dies ist die Größe einer einzelnen InnoDB-Protokolldatei. Standardmäßig verwendet InnoDB zwei Werte, sodass Sie diese Zahl verdoppeln können, um die Größe des kreisförmigen Redo-Log-Bereichs zu ermitteln, den InnoDB verwendet, um sicherzustellen, dass Ihre Transaktionen dauerhaft sind. Dies optimiert auch das Anwenden von Änderungen auf die Datenbank. Das Setzen innodb_log_file_sizeist eine Frage der Kompromisse. Je größer der von Ihnen zugewiesene Redo-Speicherplatz ist, desto besser ist die Leistung, die Sie für eine schreibintensive Arbeitslast erzielen. Je länger die Zeit für die Wiederherstellung nach einem Absturz ist, wenn Ihr System unter Stromausfall oder anderen Problemen leidet. 

Woher wissen Sie, ob Ihre MySQL-Leistung durch Ihre aktuelle InnoDB-Protokolldateigröße eingeschränkt ist? Sie können erkennen, wie viel des nutzbaren Redo-Log-Speicherplatzes tatsächlich verwendet wird. Am einfachsten ist es, sich das Percona Monitoring and Management InnoDB Metrics-Dashboard anzusehen. In der folgenden Grafik ist die Größe der InnoDB-Protokolldatei nicht groß genug, da der verwendete Speicherplatz sehr nahe an den verfügbaren nutzbaren Redo-Protokollspeicherplatz heranreicht (angezeigt durch die rote Linie). Ihre Protokolldateigröße sollte mindestens 20 Prozent größer sein als der Speicherplatz, der für eine optimale Leistung Ihres Systems benötigt wird. 

Percona

max_connections: Großanwendungen erfordern häufig viel mehr als die Standardanzahl von Verbindungen. Im Gegensatz zu anderen Variablen treten keine Leistungsprobleme auf, wenn Sie dies nicht richtig einstellen. Wenn die Anzahl der Verbindungen nicht für Ihre Anwendungsanforderungen ausreicht, kann Ihre Anwendung einfach keine Verbindung zur Datenbank herstellen (was für Ihre Benutzer nach Ausfallzeiten aussieht). Es ist wichtig, diese Variable richtig zu machen.

Es kann schwierig sein zu wissen, wie viele Verbindungen Sie für komplexe Anwendungen mit vielen Komponenten benötigen, die auf mehreren Servern ausgeführt werden. Glücklicherweise macht es MySQL sehr einfach zu sehen, wie viele Verbindungen im Spitzenbetrieb verwendet werden. In der Regel möchten Sie sicherstellen, dass zwischen der maximalen Anzahl der von Ihrer Anwendung verwendeten Verbindungen und der maximal verfügbaren Anzahl von Verbindungen eine Lücke von mindestens 30 Prozent besteht. Eine einfache Möglichkeit, diese Zahlen anzuzeigen, ist die Verwendung des MySQL-Verbindungsdiagramms im MySQL-Übersichts-Dashboard in Percona Monitoring and Management. Die folgende Grafik zeigt ein funktionsfähiges System, bei dem eine gute Anzahl zusätzlicher Verbindungen verfügbar ist. 

Percona

Beachten Sie, dass Anwendungen häufig eine übermäßige Anzahl von Verbindungen herstellen, wenn Ihre Datenbank langsam ausgeführt wird. In solchen Fällen sollten Sie das Datenbankleistungsproblem bearbeiten, anstatt einfach mehr Verbindungen zuzulassen. Mehr Verbindungen können das zugrunde liegende Leistungsproblem verschlimmern.

(Hinweis: Wenn Sie die max_connectionsVariable deutlich höher als den Standardwert festlegen , müssen Sie häufig andere Parameter wie die Größe des Tabellencaches und die Anzahl der von MySQL zugelassenen geöffneten Dateien erhöhen. Dies geht jedoch über den Rahmen dieses Artikels hinaus .) 

MySQL-Optimierungsschlüssel Nr. 4: Behalten Sie die Datenbank im Speicher

Wir haben in den letzten Jahren einen Übergang zu Solid State Drives (SSDs) gesehen. Obwohl SSDs viel schneller sind als sich drehende Festplatten, können sie nicht mit der Verfügbarkeit von Daten im RAM mithalten. Dieser Unterschied ergibt sich nicht nur aus der Speicherleistung selbst, sondern auch aus der zusätzlichen Arbeit, die die Datenbank beim Abrufen der Daten vom Festplatten- oder SSD-Speicher leisten muss. 

Mit den jüngsten Hardwareverbesserungen ist es zunehmend möglich, Ihre Datenbank im Speicher abzurufen - unabhängig davon, ob Sie in der Cloud ausgeführt werden oder Ihre eigene Hardware verwalten. 

Die noch bessere Nachricht ist, dass Sie nicht Ihre gesamte Datenbank in den Speicher einpassen müssen, um einen Großteil der Vorteile der In-Memory-Leistung zu erzielen. Sie müssen nur den Arbeitsdatensatz in den Speicher einpassen - die Daten, auf die am häufigsten zugegriffen wird.

Möglicherweise haben Sie einige Artikel gesehen, die bestimmte Zahlen darüber enthalten, welchen Teil der Datenbank Sie im Speicher behalten sollten, und zwar zwischen 10 und 33 Prozent. Tatsächlich gibt es keine "Einheitsgröße" -Nummer. Die Datenmenge, die für den besten Leistungsvorteil in den Speicher passt, hängt von der Arbeitslast ab. Anstatt nach einer bestimmten „magischen“ Nummer zu suchen, sollten Sie überprüfen, wie viel E / A die Datenbank im eingeschwungenen Zustand ausführt (normalerweise einige Stunden nach dem Start). Schauen Sie sich Lesevorgänge an, da Lesevorgänge vollständig eliminiert werden können, wenn sich Ihre Datenbank im Speicher befindet. Schreibvorgänge müssen immer ausgeführt werden, unabhängig davon, wie viel Speicher verfügbar ist. 

Unten sehen Sie die E / A im InnoDB-E / A-Diagramm im InnoDB-Metrik-Dashboard von Percona Monitoring and Management.

Percona

In der obigen Grafik sehen Sie Spitzen von bis zu 2.000 E / A-Vorgängen pro Sekunde, was zeigt, dass (zumindest für einige Teile der Arbeitslast) der Datenbankarbeitssatz nicht gut in den Speicher passt.  

MySQL-Optimierungsschlüssel Nr. 5: Verwenden Sie SSD-Speicher

Wenn Ihre Datenbank nicht in den Speicher passt (und selbst wenn dies der Fall ist), benötigen Sie dennoch schnellen Speicher, um Schreibvorgänge zu verarbeiten und Leistungsprobleme beim Aufwärmen der Datenbank zu vermeiden (direkt nach dem Neustart). Heutzutage bedeutet schneller Speicher SSDs.