So verbessern Sie die Leistung von MySQL-Servern mit der richtigen Optimierung

Kluge Speicherzuweisung für MySQL:

Stellen Sie sicher, dass die MySQL-Konfiguration nicht den gesamten physischen Speicher verwendet. Lassen Sie genügend Arbeitsspeicher für das Betriebssystem und andere Dienste. Wenn Sie beispielsweise über 16 GB RAM verfügen und berechnet haben, dass das Betriebssystem etwa 3,2 GB benötigt, weisen Sie etwa 12 bis 13 GB für MySQL zu.

Optimierung der MySQL-Speichernutzung:

Passen Sie, wie bereits erläutert, MySQL-Speicherparameter wie innodb_buffer_pool_size, key_buffer_size und query_cache_size an, um sicherzustellen, dass die gesamte MySQL-Speicherauslastung den verfügbaren Speicher nicht überschreitet.

6. Festlegen des Speichers für den Cache

Cache ist eine wichtige Komponente in MySQL, die zur Verbesserung der Leistung beiträgt, indem häufig abgerufene Daten im Speicher gespeichert werden, wodurch der Zugriff auf langsamere Festplatten reduziert wird. Durch den effektiven Einsatz von Caching können Sie die Abfragegeschwindigkeit verbessern und die Belastung des Speichersystems reduzieren. Die beiden Haupttypen von Caches, die in MySQL verwendet werden, sind MyISAM-Cache und InnoDB-Cache.

Einrichten des Caches für MyISAM und InnoDB

MyISAM-Cache

MyISAM ist eine ältere MySQL-Speicher-Engine und verwendet key_buffer_size als Hauptparameter, um die Größe des Index-Cache festzulegen. Dieser Parameter ist sehr wichtig, da auf zwischengespeicherte Indizes viel schneller zugegriffen werden kann als auf Indizes, die vom Datenträger gelesen werden müssen.

key_buffer_size: Gibt die Cache-Größe für den MyISAM-Index an. Wenn Sie diese Größe zu klein einstellen, kann dies zu häufigen langsamen Festplattenzugriffs führen, während eine zu große Einstellung ein wenig Arbeitsspeicher für andere Anforderungen übrig lassen kann. Als allgemeine Regel gilt, dass key_buffer_size zwischen 25 und 30 % des gesamten physischen Speichers auf einem Server festgelegt werden sollte, der nur MyISAM verwendet.

Beispieleinstellungen in der MySQL-Konfigurationsdatei (my.cnf):

[mysqld]

key_buffer_size = 4G

InnoDB-Cache

InnoDB ist die standardmäßige und am weitesten verbreitete Speicher-Engine in MySQL. InnoDB verwendet innodb_buffer_pool_size, um den Hauptcache einzurichten, in dem die Daten und Indizes der InnoDB-Tabellen gespeichert sind.

innodb_buffer_pool_size: Gibt die Größe des Pufferpools an, der zum Speichern von Daten und Indizes verwendet wird. Diese Einstellung ist entscheidend für die Leistung von InnoDB, da Daten, auf die häufig zugegriffen wird, in einem Pufferpool gespeichert werden, wodurch der Festplattenzugriff reduziert wird. In der Regel sollte innodb_buffer_pool_size zwischen 60 und 80 % des gesamten physischen Speichers auf einem Server festgelegt werden, der nur InnoDB verwendet.

Beispieleinstellungen in der MySQL-Konfigurationsdatei (my.cnf):

[mysqld]

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances: Wenn der Pufferpool groß ist (mehr als 1 GB), können Sie ihn in mehrere Instanzen aufteilen, um die Parallelität zu erhöhen. Wenn innodb_buffer_pool_size beispielsweise auf 12 GB festgelegt ist, können Sie ihn in 4 Instanzen mit jeweils 3 GB aufteilen:

[mysqld]

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances = 4

Abfrage-Cache (Allgemein)

MySQL verfügt auch über einen Abfrage-Cache, der die Ergebnisse häufig wiederholter Abfragen speichert, um die Ausführungszeit nachfolgender Abfragen zu beschleunigen. In der neuesten Version gilt der Abfrage-Cache jedoch als veraltet und wurde in MySQL 8.0 aufgrund von Leistungsproblemen in Umgebungen mit vielen Schreibvorgängen entfernt.

query_cache_size: Wenn Sie eine ältere Version von MySQL verwenden, können Sie die Größe der Cache-Abfrage mit diesem Parameter festlegen. Es wird jedoch empfohlen, für eine bessere Verwaltung Caching oder Cacheproxys auf App-Ebene zu verwenden.

[mysqld]

query_cache_size = 256M