Eine umfassendere Referenz zur MySQL-Optimierung

In diesem Artikel wurden einige allgemeine Optimierungsmethoden für MySQL zusammengestellt und eine einfache Zusammenfassung zur Weitergabe erstellt. Ziel ist es, Unternehmen, die keinen Vollzeit-MySQL-DBA haben, bei der Durchführung grundlegender Optimierungsarbeiten zu helfen Sie können durch Hinzufügen geeigneter Indizes erzielt werden. Komplexere Indizes erfordern eine detaillierte Analyse.

1. Hardware-Layer-bezogene Optimierung

1.1. CPU-bezogen

In den BIOS-Einstellungen des Servers können Sie die anpassen Die folgenden Konfigurationen dienen dazu, die Leistung der CPU zu maximieren oder klassische NUMA-Probleme zu vermeiden:

1. Wählen Sie den Modus „Performance Per Watt Optimized“ (DAPC), um die Leistung der CPU zu maximieren. Das Ausführen von DB erfordert normalerweise eine hohe Rechenleistung Wenn Sie viele Dienste nutzen, denken Sie nicht an Energieeinsparungen.

2. Deaktivieren Sie Optionen wie C1E und C States, um die CPU-Effizienz zu verbessern.

3. Wählen Sie „Maximale Leistung“ aus Speicherfrequenz (Beste Leistung);

4. Aktivieren Sie im Speichereinstellungsmenü Node Interleaving, um NUMA-Probleme zu vermeiden

Folgendes Sortieren nach dem Ausmaß der IOPS-Leistungsverbesserung sind einige Maßnahmen, die für Festplatten-E/A optimiert werden können:

1. Verwenden Sie SSD- oder PCIe-SSD-Geräte, um eine mindestens hundert- oder sogar zehntausendfache IOPS-Verbesserung zu erzielen;

2. Der Kauf einer Array-Karte, die sowohl mit CACHE- als auch mit BBU-Modulen ausgestattet ist, kann die IOPS erheblich steigern (bezieht sich hauptsächlich auf mechanische Festplatten, außer SSD oder PCIe SSD). Gleichzeitig ist es notwendig, den Zustand regelmäßig zu überprüfen Status der CACHE- und BBU-Module, um sicherzustellen, dass bei einem Unfall keine Daten verloren gehen.

3. Wenn eine Array-Karte vorhanden ist, stellen Sie die Array-Schreibstrategie auf WB oder sogar auf FORCE WB (falls vorhanden). Wenn es sich um einen Dual-Power-Schutz handelt oder wenn die Anforderungen an die Datensicherheit nicht besonders hoch sind, ist die Verwendung der WT-Strategie strengstens untersagt. Und die Closed-Array-Read-Ahead-Strategie ist grundsätzlich nutzlos und von geringem Nutzen.

4. Verwenden Sie so oft wie möglich RAID-10 anstelle von RAID-5 mechanische Festplatte, Versuchen Sie, möglichst eine Hochgeschwindigkeitsfestplatte zu wählen, wählen Sie beispielsweise eine 15-kRPM-Festplatte anstelle einer 7,2-kRPM-Festplatte, die nicht ein paar Dollar wert ist

　2. Bezogen auf die Systemschicht Optimierung

　2.1. Optimierung der Dateisystemebene

Auf der Dateisystemebene können die folgenden Maßnahmen die IOPS-Leistung erheblich verbessern:

1. Nutzen Sie die Frist der beiden E/A-Planer /noop und niemals cfq verwenden (es ist nicht geeignet, DB-Dienste auszuführen);

2. Verwenden Sie das xfs-Dateisystem, verwenden Sie niemals ext3; aber wenn das Geschäftsvolumen groß ist, müssen Sie es verwenden xfs;

3. Den Dateisystem-Mount-Parametern wurden mehrere Optionen hinzugefügt: noatime, nodiratime und nobarrier (nobarrier gilt nur für das xfs-Dateisystem);

　2.2

Legen Sie geeignete wichtige Kernel-Parameter fest. Der Zweck besteht darin, die Tendenz zum Auslagern zu reduzieren und große Schwankungen im Speicher und bei der Festplatten-E/A zu verhindern, die zu sofortiger Spitzenlast führen:

1. Stellen Sie vm.swappiness ein auf etwa 5-10 oder sogar auf 0 setzen (achten Sie darauf, ihn für RHEL 7 und höher auf 0 zu setzen, es sei denn, Sie lassen zu, dass OOM-Kill auftritt), um die Wahrscheinlichkeit der Verwendung von SWAP zu verringern

　2. Stellen Sie ein vm.dirty_background_ratio auf 5-10 und vm .dirty_ratio auf etwa das Doppelte, um sicherzustellen, dass schmutzige Daten kontinuierlich auf die Festplatte geleert werden können, um sofortige E/A-Schreibvorgänge und ernsthafte Wartezeiten zu vermeiden (ähnlich wie innodb_max_dirty_pages_pct in MySQL); >

3. Setzen Sie net .ipv4.tcp_tw_recycle und net.ipv4.tcp_tw_reuse beide auf 1, um TIME_WAIT zu reduzieren und die TCP-Effizienz zu verbessern;

4. Was die beiden Parameter read_ahead_kb und nr_requests betrifft, die übertragen werden Nach dem Testen im Netzwerk habe ich festgestellt, dass die Auswirkungen von OLTP-Umgebungen mit gemischtem Schreib- und Schreibzugriff nicht groß sind (in leseempfindlichen Szenarien sollte es effektiver sein), aber möglicherweise stimmt etwas mit meiner Testmethode nicht, Sie können entscheiden, ob dies der Fall ist um es selbst anzupassen;

3. MySQL-Layer-bezogene Optimierung

3.1. Zur Versionsauswahl

Dazu gibt es nichts zu sagen. Ich glaube, dass sich die meisten Menschen dafür entscheiden werden.

Ich persönlich empfehle dringend, die Percona-Zweigversion zu wählen. Es handelt sich um eine relativ ausgereifte und hervorragende MySQL-Zweigversion, die viele Verbesserungen in Bezug auf Leistung, Zuverlässigkeit und Verwaltung mit sich bringt. Es ist grundsätzlich vollständig mit der offiziellen ORACLE-MySQL-Version kompatibel und seine Leistung wurde um etwa 20 % oder mehr verbessert. Daher empfehle ich es zuerst und verwende es seit 2008.

Eine weitere wichtige Zweigversion ist MariaDB. Es ist eigentlich unangemessen zu sagen, dass MariaDB eine Zweigversion ist, da ihr Ziel darin besteht, ORACLE MySQL zu ersetzen. Es führt hauptsächlich viele Verbesserungen auf Quellcodeebene gegenüber der ursprünglichen MySQL Server-Ebene durch und ist außerdem eine sehr zuverlässige und hervorragende Zweigversion. Dies hat jedoch auch zu neuen Funktionen geführt, die durch GTID dargestellt werden und nicht mit der offiziellen Version kompatibel sind (ab MySQL 5.7 wird auch das dynamische Ein- und Ausschalten des GTID-Modus online unterstützt, da die überwiegende Mehrheit der Benutzer weiterhin folgen wird). die offizielle Version. Daher wird MariaDB zunächst nicht empfohlen.

3.2. Vorschläge zum Anpassen der wichtigsten Parameteroptionen

Es wird empfohlen, die folgenden Schlüsselparameter anzupassen, um eine bessere Leistung zu erzielen (Sie können zum Generieren den von dieser Website bereitgestellten my.cnf-Generator verwenden die Konfigurationsdatei-Vorlage):

1. Wenn Sie sich für die Percona- oder MariaDB-Version entscheiden, wird dringend empfohlen, die Thread-Pool-Funktion zu aktivieren, damit die Leistung in Situationen mit hoher Parallelität nicht wesentlich abnimmt. Darüber hinaus gibt es die Funktion extra_port, die sehr praktisch ist und in kritischen Momenten Leben retten kann. Eine weitere wichtige Funktion ist die Funktion QUERY_RESPONSE_TIME, die uns auch ein intuitives Gefühl für die gesamte SQL-Antwortzeitverteilung gibt

　2. Setzen Sie default-storage-engine=InnoDB, was bedeutet, dass die InnoDB-Engine verwendet wird Standardmäßig wird dringend empfohlen, die MyISAM-Engine nicht mehr zu verwenden.

3. Passen Sie die Größe von innodb_buffer_pool_size an Die meisten davon sind InnoDB-Engine-Tabellen. Erwägen Sie die Einstellung auf etwa 50 % bis 70 % des physischen Speichers.

4. Stellen Sie die Werte von innodb_flush_log_at_trx_commit und sync_binlog entsprechend den tatsächlichen Anforderungen ein. Wenn keine Daten verloren gehen können, setzen Sie beide auf 1. Wenn ein geringer Datenverlust zulässig ist, können sie auf 2 bzw. 10 eingestellt werden. Und wenn es keinen Grund gibt, sich darum zu kümmern, ob die Daten verloren gehen (zum Beispiel werden sie auf dem Slave trotzdem wiederhergestellt), dann kann alles auf 0 gesetzt werden. Der Grad, in dem die Leistung der Datenbank durch diese drei Einstellungswerte beeinflusst wird, ist: hoch, mittel und niedrig, das heißt, der erste macht die Datenbank am langsamsten und der letzte ist das Gegenteil 🎜>

5. Setzen Sie innodb_file_per_table = 1. Bei Verwendung unabhängiger Tabellenbereiche kann ich mir wirklich keine Vorteile vorstellen.

6. Setzen Sie innodb_data_file_path = ibdata1:1G:autoextend Verwenden Sie nicht den Standardwert von 10 Millionen, da sonst die Transaktionen stark beeinträchtigt werden

8. Setzen Sie long_query_time = 1. In Version 5.5 und höher kann es auf weniger als 1 eingestellt werden. Es wird empfohlen, es auf 0,05 (50 Millisekunden) festzulegen, um die langsam ausgeführten SQLs für die spätere Analyse und Fehlerbehebung aufzuzeichnen.

9. Passen Sie max_connection (maximale Anzahl von Verbindungen) und max_connection_error (maximale Anzahl von Fehlern) entsprechend den tatsächlichen Geschäftsanforderungen an. Es wird empfohlen, sie auf mehr als 100.000 festzulegen, während die Parameter open_files_limit, innodb_open_files, table_open_cache und table_definition_cache können auf etwa das Zehnfache von max_connection eingestellt werden. Ein häufiges Missverständnis besteht darin, tmp_table_size und max_heap_table_size relativ groß zu setzen Stellen Sie sie daher nicht zu groß ein, da dies sonst leicht zu OOM führt. Einige andere Optionen auf Verbindungssitzungsebene wie sort_buffer_size, join_buffer_size usw. müssen ebenfalls nicht festgelegt werden zu groß;

11. Da empfohlen wurde, die MyISAM-Engine nicht mehr zu verwenden, können Sie key_buffer_size auf etwa 32 MB festlegen, und es wird dringend empfohlen, die Abfrage-Cache-Funktion auszuschalten

3.3. Bezüglich Schema-Design-Spezifikationen und SQL-Nutzungsvorschlägen

Nachfolgend sind einige allgemeine Schema-Design-Spezifikationen aufgeführt, die zur Verbesserung der MySQL-Effizienz beitragen können, und Vorschläge zur SQL-Nutzung:

1. Alle InnoDB-Tabellen sind mit einer automatischen Inkrementierungsspalte als Primärschlüssel konzipiert. Dies gilt für die meisten Szenarien. Es gibt nicht viele InnoDB-Tabellen, die wirklich rein schreibgeschützt sind Verwenden Sie TokuDB;

2. Wählen Sie unter der Voraussetzung, dass die Feldlänge den Anforderungen entspricht, die kleinstmögliche Länge. Versuchen Sie außerdem, den Feldattributen NOT NULL-Einschränkungen hinzuzufügen, was die Leistung bis zu einem gewissen Grad verbessern kann.

3. Versuchen Sie, so oft wie möglich TEXT/BLOB-Typen zu verwenden Teilen Sie sie in Untertabellen auf und führen Sie sie nicht mit der Haupttabelle zusammen, um eine schlechte Leseleistung bei SELECT * zu vermeiden.

4. Wählen Sie beim Lesen von Daten nur die Spalten aus, die Sie benötigen, um schwerwiegende zufällige Leseprobleme zu vermeiden, insbesondere beim Lesen einiger TEXT-/BLOB-Spalten 🎜> 6. Unter normalen Umständen ist die Leistung der Unterabfrage relativ schlecht, und es wird empfohlen, sie auf die Schreibmethode JOIN zu ändern

8. Verwenden Sie bei Abfragen mit mehreren Tabellen die Tabelle mit einem kleinen Ergebnis set (beachten Sie, dass sich dies auf die gefilterte Ergebnismenge bezieht, nicht unbedingt auf das kleine Datenvolumen der gesamten Tabelle) als treibende Tabelle

9. Wenn mehrere Tabellen verbunden und sortiert werden, muss das Sortierfeld vorhanden sein die Fahrtabelle, sonst kann die Sortierspalte den Index nicht verwenden

10. Verwenden Sie mehr zusammengesetzte Indizes und weniger die Verwendung mehrerer unabhängiger Indizes, insbesondere Erstellen Sie keine unabhängigen Indizes für einige Spalten, deren Kardinalität zu klein ist (z. B , die Gesamtzahl der eindeutigen Werte der Spalte beträgt weniger als 255);

11. Für SQL mit ähnlicher Paging-Funktion wird empfohlen, zuerst die Primärschlüsselzuordnung zurückzugeben Die Effizienz wird viel höher sein.

3.4. Weitere Vorschläge zur Verwaltung und Wartung von MySQL sind:

1. Normalerweise ist die physische Größe der Tabelle überschreitet nicht 10 GB, die Anzahl der Zeilen in einer einzelnen Tabelle überschreitet nicht 100 Millionen und die durchschnittliche Zeilenlänge überschreitet nicht 8 KB. Wenn die Maschinenleistung ausreichend ist, kann MySQL diese Datenmenge nicht vollständig verarbeiten Machen Sie sich Sorgen über Leistungsprobleme. Bedenken Sie die höheren Kosten von ONLINE DDL

2. Machen Sie sich keine allzu großen Sorgen darüber, dass der MySQLd-Prozess zu viel Speicher beansprucht. Solange kein OOM-Kill auftritt und viel SWAP verwendet wird, ist es in Ordnung. In der Vergangenheit bestand der Zweck der Ausführung mehrerer Instanzen auf einer einzelnen Maschine darin, die Nutzung der Rechenressourcen zu maximieren. Wenn eine einzelne Instanz bereits die meisten Rechenressourcen verbrauchen kann, besteht keine Notwendigkeit, mehrere Instanzen auszuführen >

4. Verwenden Sie regelmäßig den pt-duplicate-key-checker, um doppelte Indizes zu überprüfen und zu löschen. Verwenden Sie regelmäßig das pt-index-usage-Tool, um Indizes mit sehr geringer Nutzungshäufigkeit zu überprüfen und zu löschen. 5. Sammeln Sie regelmäßig langsame Abfrageprotokolle und analysieren Sie sie mit dem pt-query-digest-Tool das Anemometer-System für die langsame Abfrageverwaltung.

6. Sie können pt-kill verwenden, um langfristige SQL-Anfragen zu beenden die Percona-Version, die diese Funktion auch erreichen kann

7. Verwenden Sie pt-online-schema-change, um die ONLINE-DDL-Anforderungen großer Tabellen zu erfüllen

8. Verwenden Sie regelmäßig pt-table-; Prüfsumme und pt-table-sync zum Überprüfen und Reparieren von Datenunterschieden bei der MySQL-Master-Slave-Replikation

Für diese Optimierungsreferenz habe ich in den meisten Fällen anwendbare Szenarien vorgestellt Ausgehend von der Beschreibung in diesem Artikel wird empfohlen, auf der Grundlage der tatsächlichen Situation vorzugehen und nicht mechanisch anzupassen. Fragen und Anregungen sind willkommen, gewohnheitsmäßiger Widerstand, der nicht über den Kopf geht, wird jedoch abgelehnt.

Das Obige ist eine relativ umfassende MySQL-Optimierungsreferenz. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!