Referenz zur MySQL-Optimierungslösung

Probleme, die bei der Optimierung auftreten können

Die Optimierung wird nicht immer in einer einfachen Umgebung durchgeführt, sondern kann auch ein komplexes System betreffen, das in Produktion genommen wurde.

Optimierungsmethoden bergen große Risiken, die Sie jedoch nicht erkennen und vorhersehen können!

Jede Technologie kann ein Problem lösen, aber es besteht immer das Risiko, ein anderes Problem hervorzurufen!

Für eine Optimierung ist die Lösung der dadurch verursachten Probleme nur dann effektiv, wenn sie in einem akzeptablen Rahmen gehalten werden.

Den Status Quo beizubehalten oder ihn sogar noch zu verschlechtern, ist ein Versagen!

Dieser Artikel stellt einige gängige MySQL-Optimierungsmethoden vor und bietet eine einfache Zusammenfassung, die Unternehmen, die keinen Vollzeit-MySQL-DBA haben, bei der Durchführung grundlegender Optimierungsarbeiten helfen soll. Was die spezifische SQL-Optimierung betrifft, können die meisten davon durch das Hinzufügen geeigneter Indizes erreicht werden. Komplexere Optimierungen erfordern eine spezifische Analyse.

1. Optimierung auf Hardwareebene

1.1 、 CPU-bezogen

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

1. Wählen Sie den DAPC-Modus (Performance Per Watt Optimized), um die CPU-Leistung zu maximieren. Denken Sie nicht an Energiesparen, wenn Sie DB-Dienste ausführen, die normalerweise eine hohe Rechenleistung erfordern.

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

3. Wählen Sie „Maximale Leistung“ für die Speicherfrequenz.

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

1.2 Disk-E/A-bezogen

Im Folgenden sind einige Maßnahmen aufgeführt, mit denen sich der Festplatten-E/A optimieren lässt, sortiert nach dem Ausmaß der IOPS-Leistungsverbesserung:

1. Verwenden Sie SSD- oder PCIe-SSD-Geräte, um die IOPS um mindestens das Hundert- oder sogar Zehntausendfache zu erhöhen;

2. Der Kauf von Array-Karten mit CACHE- und BBU-Modulen kann die IOPS erheblich steigern (hauptsächlich mechanische Festplatten, ausgenommen SSDs oder PCIe-SSDs. Gleichzeitig ist es notwendig, den Zustand der CACHE- und BBU-Module regelmäßig zu überprüfen, um sicherzustellen, dass im Falle eines Unfalls keine Daten verloren gehen);

3. Wenn eine Array-Karte vorhanden ist, stellen Sie die Array-Schreibrichtlinie auf WB oder sogar FORCE WB ein (wenn ein Dual-Power-Schutz vorhanden ist oder die Datensicherheitsanforderungen nicht besonders hoch sind). Die Verwendung der WT-Richtlinie ist strengstens verboten. Und die Strategie des Vorlesens geschlossener Arrays ist im Grunde nutzlos;

4. Wählen Sie nach Möglichkeit RAID-10 statt RAID-5.

5. Wenn Sie eine mechanische Festplatte verwenden, wählen Sie möglichst eine Hochgeschwindigkeitsfestplatte, z. B. eine 15-KRPM-Festplatte statt einer 7,2-KRPM-Festplatte. Sie können ein paar Dollar sparen.

2. Optimierung auf Systemebene

2.1. Optimierung der Dateisystemebene

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

1. Verwenden Sie Deadline-/Noop-E/A-Scheduler und niemals cfq (es ist nicht zum Ausführen von DB-Diensten geeignet).

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

3. Den Mount-Parametern des Dateisystems wurden die folgenden Optionen hinzugefügt: noatime, nodiratime, nobarrier (nobarrier ist eindeutig für das xfs-Dateisystem);

2.2. Optimierung anderer Kernelparameter

Der Zweck des Festlegens geeigneter Werte für wichtige Kernelparameter besteht darin, die Swapping-Tendenz zu verringern und große Schwankungen beim Speicher und bei der Festplatten-E/A zu verhindern, die zu sofortigen Spitzenlasten führen würden:

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

2. Setzen Sie vm.dirty_background_ratio auf 5-10 und vm.dirty_ratio auf etwa das Doppelte dieses Werts, um sicherzustellen, dass fehlerhafte Daten kontinuierlich auf die Festplatte geschrieben werden können, und um sofortige E/A-Schreibvorgänge zu vermeiden, die zu langen Wartezeiten führen (ähnlich wie innodb_max_dirty_pages_pct in MySQL).

3. Setzen Sie net.ipv4.tcp_tw_recycle und net.ipv4.tcp_tw_reuse 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 online kursieren, habe ich nach dem Testen festgestellt, dass sie nur geringe Auswirkungen auf die OLTP-Umgebung mit gemischten Lese- und Schreibvorgängen haben (in leseempfindlichen Szenarien sollte dies effektiver sein). Möglicherweise liegt jedoch ein Problem mit meiner Testmethode vor. Sie können nach eigenem Ermessen überlegen, ob Sie sie anpassen möchten.

3. MySQL-Schichtbezogene Optimierung

3.1. Informationen zur Versionsauswahl

Die offizielle Version nennen wir ORACLE MySQL. Dazu gibt es nicht viel zu sagen und ich glaube, die meisten Leute werden sich dafür entscheiden.

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

Eine weitere wichtige Zweigversion ist MariaDB. Es ist eigentlich nicht richtig, MariaDB als Zweigversion zu bezeichnen, da ihr Ziel darin besteht, Oracle MySQL zu ersetzen. Es enthält hauptsächlich zahlreiche Verbesserungen auf Quellcodeebene in der ursprünglichen MySQL-Serverebene und ist außerdem eine sehr zuverlässige und hervorragende Zweigversion. Dies führte jedoch auch zu neuen Funktionen wie GTID, die mit der offiziellen Version nicht kompatibel sind (MySQL 5.7 und höher unterstützt auch das dynamische Online-Aktivieren oder Deaktivieren des GTID-Modus). In Anbetracht der Tatsache, dass die meisten Leute immer noch die offizielle Version verwenden, wird MariaDB nicht als vorrangig empfohlen.

3.2. Vorschläge zu den wichtigsten Parameteroptionen

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

1. Wenn Sie sich für die Percona- oder MariaDB-Version entscheiden, wird dringend empfohlen, die Thread-Pool-Funktion zu aktivieren, damit die Leistung bei hoher Parallelität nicht erheblich abnimmt. Darüber hinaus gibt es die extra_port Funktion, die sehr praktisch ist und in kritischen Momenten Leben retten kann. Ein weiteres wichtiges Feature ist die Funktion QUERY_RESPONSE_TIME, die uns auch ein intuitives Verständnis der allgemeinen SQL-Antwortzeitverteilung vermittelt.

2. Setzen Sie default-storage-engine=InnoDB, was bedeutet, dass standardmäßig die InnoDB-Engine verwendet wird. Es wird dringend empfohlen, die MyISAM-Engine nicht zu verwenden. Die InnoDB-Engine kann definitiv mehr als 99 % der Geschäftsszenarien erfüllen.

3. Passen Sie die innodb_buffer_pool_size an. Wenn es sich um eine einzelne Instanz handelt und die meisten Tabellen InnoDB-Engine-Tabellen sind, sollten Sie sie auf etwa 50 % bis 70 % des physischen Speichers einstellen.

4. Legen Sie die Werte von innodb_flush_log_at_trx_commit und sync_binlog entsprechend dem tatsächlichen Bedarf fest. Wenn ein Datenverlust erforderlich ist, werden beide auf 1 gesetzt. Wenn ein geringer Datenverlust akzeptabel ist, können Sie die Werte auf 2 bzw. 10 einstellen. Wenn Ihnen der Datenverlust überhaupt nichts ausmacht (beispielsweise ist bei einem Slave das Schlimmste, was passieren kann, dass die Daten neu erstellt werden müssen), können Sie sie alle auf 0 setzen. Die drei Einstellungen wirken sich auf die Leistung der Datenbank folgendermaßen aus: hoch, mittel und niedrig. Die erste Einstellung macht die Datenbank am langsamsten, während die letzte Einstellung die Datenbank am langsamsten macht.

5. Setzen Sie innodb_file_per_table = 1 und verwenden Sie einen separaten Tablespace. Mir fällt wirklich kein Vorteil ein, der sich bei der Verwendung eines gemeinsam genutzten Tablespace ergibt.

6. Setzen Sie innodb_data_file_path = ibdata1:1G:autoextend. Verwenden Sie nicht den Standardwert 10M, da dies sonst bei vielen gleichzeitigen Transaktionen stark beeinträchtigt wird.

7. Setzen Sie innodb_log_file_size=256M und innodb_log_files_in_group=2, was grundsätzlich mehr als 90 % der Szenarien abdecken kann;

8. Setzen Sie long_query_time = 1. In Versionen über 5.5 kann der Wert auf weniger als 1 gesetzt werden. Es wird empfohlen, den Wert auf 0,05 (50 Millisekunden) zu setzen, um die SQL-Anweisungen aufzuzeichnen, die langsam ausgeführt werden, um sie anschließend analysieren und beheben zu können.

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 über 100.000 festzulegen. Die Parameter open_files_limit, innodb_open_files, table_open_cache und table_definition_cache können auf etwa das Zehnfache der Größe von max_connection festgelegt werden.

10. Ein häufiger Fehler besteht darin, tmp_table_size und max_heap_table_size auf einen relativ großen Wert einzustellen. Ich habe Einstellungen von 1G gesehen. Diese beiden Optionen werden für jede Verbindungssitzung zugewiesen. Stellen Sie sie also nicht zu groß ein, da dies sonst leicht zu OOM führt. Andere Optionen auf Verbindungssitzungsebene wie sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size usw. sollten ebenfalls nicht zu groß eingestellt werden.

11. Da empfohlen wird, die MyISAM-Engine nicht mehr zu verwenden, können Sie die key_buffer_size auf etwa 32 M einstellen. Außerdem wird dringend empfohlen, die Abfrage-Cache-Funktion zu deaktivieren.

3.3. Schema-Design-Spezifikationen und SQL-Nutzungsvorschläge

Hier sind einige allgemeine Schema-Designspezifikationen und Vorschläge zur SQL-Verwendung, die zur Verbesserung der MySQL-Effizienz beitragen können:

1. Alle InnoDB-Tabellen sind mit einer Auto-Increment-Spalte für nicht geschäftliche Zwecke als Primärschlüssel konzipiert. Dies gilt für die meisten Szenarien. Nicht viele InnoDB-Tabellen sind wirklich schreibgeschützt. In diesem Fall ist es kostengünstiger, TokuDB zu verwenden.

2. Wenn die Feldlänge den Anforderungen entspricht, wählen Sie eine möglichst kleinere Länge. Versuchen Sie außerdem, den Feldattributen NOT NULL-Einschränkungen hinzuzufügen, um die Leistung bis zu einem gewissen Grad zu verbessern.

3. Vermeiden Sie die Verwendung von TEXT/BLOB-Typen so weit wie möglich. Bei Bedarf wird empfohlen, sie in Untertabellen aufzuteilen und sie nicht mit der Haupttabelle zusammenzufassen, um eine schlechte Leseleistung bei Verwendung von SELECT * zu vermeiden.

4. Wählen Sie beim Lesen von Daten nur die erforderlichen Spalten aus und verwenden Sie nicht jedes Mal SELECT *, um schwerwiegende Probleme beim zufälligen Lesen zu vermeiden, insbesondere beim Lesen einiger TEXT/BLOB-Spalten.

5. Beim Erstellen eines Indexes für eine VARCHAR(N)-Spalte genügt es normalerweise, einen Präfixindex von etwa 50 % (oder sogar weniger) ihrer Länge zu erstellen, um mehr als 80 % der Abfrageanforderungen zu erfüllen. Es ist nicht erforderlich, einen Index voller Länge für die gesamte Spalte zu erstellen.

6. Im Allgemeinen ist die Leistung von Unterabfragen relativ schlecht. Es wird empfohlen, sie in JOIN-Ausdrücke umzuwandeln.

7. Bei der Abfrage mehrerer Tabellen sollten die Typen der zugehörigen Felder möglichst konsistent sein und alle sollten über Indizes verfügen.

8. Wenn Sie mehrere Tabellen abfragen, verwenden Sie die Tabelle mit dem kleinsten Ergebnissatz (beachten Sie, dass sich dies auf den gefilterten Ergebnissatz bezieht, nicht notwendigerweise auf die Tabelle mit der kleinsten Datenmenge) als treibende Tabelle.

9. Wenn mehrere Tabellen verbunden und sortiert werden, muss sich das Sortierfeld in der treibenden Tabelle befinden, sonst kann die Sortierspalte den Index nicht verwenden.

10. Verwenden Sie mehr zusammengesetzte Indizes und weniger unabhängige Indizes. Erstellen Sie insbesondere keine unabhängigen Indizes für Spalten mit geringer Kardinalität (z. B. wenn die Gesamtzahl der eindeutigen Werte in der Spalte weniger als 255 beträgt).

11. Für SQL mit Paging-Funktion wird empfohlen, zuerst den Primärschlüssel zuzuordnen und dann den Ergebnissatz zurückzugeben. Dies ist wesentlich effizienter.

3.4 Weitere Vorschläge

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

1. Im Allgemeinen überschreitet die physische Größe einer einzelnen Tabelle nicht 10 GB, die Anzahl der Zeilen in einer einzelnen Tabelle überschreitet nicht 100 Millionen und die durchschnittliche Länge einer Zeile überschreitet nicht 8 KB. Wenn die Maschinenleistung ausreicht, kann MySQL diese Datenmenge vollständig verarbeiten und es besteht kein Grund zur Sorge über Leistungsprobleme. Diese Empfehlung ist hauptsächlich auf die hohen Kosten von ONLINE DDL zurückzuführen.

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 nicht eine große Menge an SWAP verwendet wird.

3. In der Vergangenheit bestand der Zweck des Ausführens mehrerer Instanzen auf einer einzigen Maschine darin, die Nutzung der Rechenressourcen zu maximieren. Wenn eine einzelne Instanz bereits die meisten Rechenressourcen verbrauchen konnte, bestand keine Notwendigkeit, mehrere Instanzen auszuführen.

4. Verwenden Sie pt-duplicate-key-checker regelmäßig, um doppelte Indizes zu überprüfen und zu löschen. Verwenden Sie das Tool pt-index-usage regelmäßig, um selten verwendete Indizes zu überprüfen und zu löschen.

5. Sammeln Sie regelmäßig Protokolle langsamer Abfragen und analysieren Sie diese mit dem Tool pt-query-digest. Sie können das Anemometer-System zur Verwaltung langsamer Abfragen kombinieren, um langsame Abfragen zu analysieren und anschließend Optimierungsarbeiten durchzuführen.

6. Sie können pt-kill verwenden, um SQL-Anfragen abzubrechen, die zu lange dauern. In der Percona-Version gibt es eine Option innodb_kill_idle_transaction, die diese Funktion ebenfalls erreichen kann.

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

8. Verwenden Sie pt-table-checksum und pt-table-sync regelmäßig, um Datenunterschiede zwischen der MySQL-Master-Slave-Replikation zu überprüfen und zu beheben.

Fazit: Für diese Optimierungsreferenz habe ich in den meisten Fällen anwendbare Szenarien vorgestellt. Wenn Ihr Anwendungsszenario von dem in diesem Artikel beschriebenen abweicht, wird empfohlen, Anpassungen basierend auf der tatsächlichen Situation vorzunehmen, anstatt sie mechanisch anzuwenden. Ihre Zweifel und Kritik sind uns willkommen, den üblichen Widerstand lehnen wir jedoch gedankenlos ab.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an