10 Leistungskonfigurationselemente, die nach der Installation von MySQL angepasst werden müssen

In diesem Blog besprechen wir zehn Leistungseinstellungen, deren Anpassung nach der Installation der MySQL-Datenbank empfohlen wird.

Wenn wir ein MySQL-Leistungsaudit durchführen müssen, überprüfen wir normalerweise die MySQL-Konfiguration und geben Empfehlungen für Verbesserungen. In den meisten Fällen empfehlen wir, nach der Installation nur einige wenige grundlegende MySQL-Leistungsoptimierungsparameter zu ändern, auch wenn Hunderte von Optionen verfügbar sind. Der Zweck dieses Artikels besteht darin, Ihnen eine Liste der wichtigsten Parametereinstellungen zu geben und Ihnen zu erklären, wie Sie diese anpassen.

Bevor Sie mit der Anpassung beginnen

Auch erfahrenen Leuten können Fehler unterlaufen, die große Probleme verursachen können. Bevor Sie die in diesem Artikel empfohlenen Konfigurationselemente anwenden, beachten Sie bitte Folgendes:

• Ändern Sie jeweils nur eine Einstellung! Nur so können Sie überprüfen, ob Ihr Setup funktioniert hat.
• Die meisten Konfigurationselemente können zur Laufzeit mit dem Befehl SET GLOBAL geändert werden. Diese Methode ist sehr komfortabel und falls nach der Änderung Probleme auftreten, können die ursprünglichen Einstellungen sofort wiederhergestellt werden. Aber am Ende müssen Sie diese Änderung noch in die Konfigurationsdatei schreiben, um sie dauerhaft zu machen.
• Manchmal werden die Parameter in der Konfigurationsdatei auch nach einem Neustart von MySQL nicht wirksam. An dieser Stelle müssen Sie überlegen: Verwenden Sie die richtige Konfigurationsdatei? Haben Sie diesen Parameter an der richtigen Stelle eingefügt? (Alle Konfigurationen in diesem Artikel gehören zum Abschnitt [mysqld])
• Wenn der Start der Datenbank nach einer Konfigurationsänderung nicht gelingt, muss dann geprüft werden, ob die richtige Einheit verwendet wird? Beispielsweise ist innodb_buffer_pool_size in Bytes angegeben, während max_connection keine Einheiten hat.
• Doppelte Einstellungen sind in der Konfigurationsdatei nicht zulässig. Wenn Sie Änderungen an Ihrer Konfiguration verfolgen möchten, verwenden Sie die Versionskontrolle.
• Machen Sie keine naiven Berechnungen wie: „Mein neuer Server hat doppelt so viel RAM wie mein alter, also kann ich alle meine Konfigurationsoptionen auf das Doppelte ihrer ursprünglichen Werte setzen.“

Grundeinstellungen

Hier erläutern wir hauptsächlich drei sehr wichtige MySQL-Leistungskonfigurationselemente. Sie sollten diese Parameter häufig sehen. Wenn Sie sich nicht anpassen, werden wahrscheinlich Probleme auftreten.

innodb_buffer_pool_size:

Dies ist die erste Konfiguration, die Sie sich nach der Installation einer MySQL-Installation ansehen sollten, die die InnoDB-Speicher-Engine verwendet. Der Pufferpool wird zum Zwischenspeichern von Daten und Indizes verwendet. Ihm sollte so viel Speicher wie möglich zugewiesen werden, um sicherzustellen, dass die meisten Lesevorgänge aus dem Speicher und nicht von der Festplatte ausgeführt werden. Typische Einstellungen sind 5–6 GB (8 GB RAM), 20–25 GB (32 GB RAM), 100–120 GB (128 GB RAM).

innodb_log_file_size:

Diese Option legt die Größe des Redo-Protokolls fest. Das Redo-Protokoll wird verwendet, um sicherzustellen, dass geschriebene Daten schnell geschrieben und gespeichert werden können, und kann auch zur Wiederherstellung nach einem Absturz verwendet werden. Vor MySQL 5.1 war diese Option schwierig anzupassen, da Sie die Größe des Redo-Logs zur Verbesserung der Leistung erhöhen, gleichzeitig aber auch zur schnellen Wiederherstellung nach einem Absturz reduzieren wollten. Glücklicherweise wurde die Leistung der Wiederherstellung nach einem Absturz seit MySQL 5.5 erheblich verbessert, und Sie können jetzt eine schnelle Schreibleistung erzielen und gleichzeitig die Anforderungen an eine schnelle Wiederherstellung nach einem Absturz erfüllen. Bis MySQL 5.5 war die Gesamtgröße des Redo-Protokolls auf 4 GB begrenzt (standardmäßig mit 2 Protokolldateien). Dies wurde in MySQL 5.6 hinzugefügt.

Setzen Sie beim Start innodb_log_file_size = 512 M (also 1 GB Redo-Log), um ausreichend Schreibspeicherplatz bereitzustellen. Wenn Sie wissen, dass Ihre Anwendung viele Schreibvorgänge erfordert, können Sie die Anzahl weiter erhöhen.

max_Verbindungen:

Wenn bei Ihnen häufig die Fehlermeldung „Zu viele Verbindungen“ auftritt, liegt das daran, dass „max_connections“ zu klein ist. Dieser Fehler tritt häufig auf, weil die Anwendung die Verbindung zur Datenbank nicht ordnungsgemäß schließt. Sie müssen die Anzahl der Verbindungen auf einen höheren Wert als den Standardwert 151 einstellen. Ein großer Nachteil einer zu hohen Einstellung von „max_connections“ (z. B. 1000 oder mehr) besteht darin, dass der Server bei der Ausführung von 1000 oder mehr Transaktionen langsam werden oder sogar nicht mehr reagieren kann. Die Verwendung von Verbindungspooling auf der Anwendungsseite oder Threadpooling auf der MySQL-Seite kann zur Lösung dieses Problems beitragen.

InnoDB-Einstellungen

Seit MySQL 5.5 ist InnoDB die Standardspeicher-Engine und wird viel häufiger verwendet als andere Speicher-Engines. Deshalb ist eine sorgfältige Konfiguration wichtig.

innodb_file_per_table:

Dieses Konfigurationselement bestimmt, ob InnoDB einen gemeinsam genutzten Tabellenbereich (innodb_file_per_table = OFF) zum Speichern von Daten und Indizes verwendet oder für jede Tabelle eine separate ibd-Datei verwendet (innodb_file_per_table = ON). Wenn Sie für jede Tabelle eine Datei verwenden, wird der Tabellenspeicherplatz freigegeben, wenn Sie die Tabelle löschen, abschneiden oder neu erstellen. Einige erweiterte Funktionen, wie z. B. die Komprimierung, erfordern auch die Verwendung unabhängiger Tablespaces. Allerdings bringt diese Option keine Leistungsverbesserungen.

In MySQL 5.6 und späteren Versionen ist dieses Konfigurationselement standardmäßig aktiviert, sodass Sie in den meisten Fällen nichts tun müssen. Bei früheren MySQL-Versionen müssen Sie es vor dem Start auf „ON“ setzen, da es nur neu erstellte Tabellen betrifft.

innodb_flush_log_at_trx_commit:

Der Standardwert ist 1, was bedeutet, dass InnoDB ACID-Funktionen vollständig unterstützt. Dies ist beispielsweise auf einem Masterknoten die beste Einstellung, bei dem Ihnen die Datensicherheit am wichtigsten ist. Auf langsamen Festplattensystemen kann dies jedoch zu erheblichem Mehraufwand führen, da bei jeder Übertragung der Änderungen in das Redo-Protokoll ein zusätzlicher fsync-Vorgang erforderlich ist. Die Einstellung 2 ist weniger zuverlässig, da festgeschriebene Transaktionen nur einmal pro Sekunde in das Redo-Protokoll geschrieben werden. Für einen Master kann dies in manchen Fällen jedoch immer noch akzeptabel sein und ist für einen Slave in einer Replikationsbeziehung ein guter Wert. Das Setzen auf 0 ist schneller, aber es ist wahrscheinlich, dass bei einem Absturz einige Daten verloren gehen. Dies ist nur eine gute Einstellung für Slaves.

innodb_flush_method:

Diese Einstellung legt fest, wie Daten und Protokolle auf die Festplatte geschrieben werden. Wenn die Serverhardware über einen RAID-Controller, einen Ausschaltschutz und einen Write-Back-Cache-Mechanismus verfügt, ist O_DIRECT der am häufigsten verwendete Wert. In den meisten anderen Szenarien wird der Standardwert fdatasync verwendet. Sysbench ist ein gutes Tool, das Ihnen bei der Auswahl zwischen diesen beiden Werten hilft.

innodb_log_buffer_size:

Mit dieser Einstellung wird die Größe des Puffers festgelegt, der zum Zwischenspeichern von Transaktionen verwendet wird, die noch nicht festgeschrieben wurden. Der Standardwert (1 MB) ist normalerweise ausreichend, aber sobald Transaktionen große Blob-/Textfelder haben, füllt sich dieser Puffer schnell und verursacht eine zusätzliche E/A-Last. Überprüfen Sie den Wert der Statusvariable innodb_log_waits. Wenn er nicht 0 ist, müssen Sie innodb_log_buffer_size erhöhen.

Andere Einstellungen

Abfrage-Cachegröße:

Jeder weiß, dass der Abfragecache einen Engpass darstellt, selbst wenn die Parallelität nicht hoch ist. Die beste Einstellung besteht darin, den Abfragecache (query_cache_size = 0) am ersten Tag der Nutzung zu deaktivieren. Diese Option ist nach MySQL 5.6 standardmäßig deaktiviert. Wir können die Abfragegeschwindigkeit auf andere Weise verbessern: durch das Entwerfen guter Indizes, die Erhöhung der Lese-/Schreibtrennung oder die Verwendung eines zusätzlichen Caches (z. B. Memcache oder Redis). Wenn Sie MySQL mit aktiviertem Abfrage-Cache haben und noch nie Probleme hatten, ist der Abfrage-Cache wahrscheinlich von Vorteil für Sie und Sie sollten vorsichtig vorgehen, wenn Sie ihn deaktivieren möchten.

log_bin:

Wenn ein Knoten der primäre Knoten in einer Replikationsbeziehung sein soll, muss das Binärprotokoll aktiviert werden. Gleichzeitig müssen Sie eine global eindeutige Server-ID festlegen. Wenn es sich um eine Einzelinstanzdatenbank handelt und Sie die Daten zu einem früheren Zeitpunkt wiederherstellen möchten (mit der neuesten Sicherung wiederherstellen und dann mit Binlog wiederherstellen), sind Binärprotokolle erforderlich. Sobald ein Binärprotokoll erstellt wurde, wird es dauerhaft gespeichert. Wenn Sie also keinen Speicherplatz verbrauchen möchten, sollten Sie PURGE BINARY LOGS verwenden, um alte Binärprotokolldateien zu bereinigen, oder die Option expire_logs_days festlegen, um anzugeben, wie viele Tage zum automatischen Bereinigen abgelaufener Binärprotokolle benötigt werden.

Das Protokollieren von Binärdateien verbraucht Ressourcen. Daher kann es in einer Master-Slave-Replikationsumgebung deaktiviert werden, wenn die Standby-Datenbank Binlog nicht benötigt.

skip_name_resolve:

Wenn ein Client eine Verbindung herstellt, führt der Server eine Hostnamenauflösung durch, und wenn DNS langsam ist, dauert der Verbindungsaufbau lange. Es wird daher empfohlen, beim Booten die Option „Skip-Name-Resolve“ festzulegen, um DNS-Lookups zu deaktivieren. Die einzige Einschränkung besteht darin, dass die GRANT-Anweisung nur mit IP-Adressen verwendet werden kann. Daher ist beim Hinzufügen dieser Option zu einem vorhandenen System äußerste Vorsicht geboten.

abschließend

Natürlich gibt es auch noch andere Einstellungen, die Sie je nach Auslastung und Hardwarebedingungen zur Optimierung verwenden können: Kleiner Speicher, Hochgeschwindigkeitsfestplatten, hohe Parallelität und schreibintensive Auslastungen erfordern beispielsweise eine spezielle Optimierung. Der Zweck dieses Artikels besteht jedoch darin, mehrere Konfigurationselemente zur MySQL-Leistungsoptimierung bereitzustellen, sodass Sie schnell eine sinnvolle MySQL-Konfigurationsdatei konfigurieren und verstehen können, welche Parameter für Sie wichtig sind, ohne viel Zeit mit dem Lesen offizieller Dokumente zu verbringen.

Oben sind die Details der 10 Leistungskonfigurationselemente aufgeführt, die nach der Installation von MySQL angepasst werden müssen. Weitere Informationen zu den Leistungskonfigurationselementen von MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!