Performance Optimierung der MySQL und MariaDB

Aktualisiert: Freitag, 06. Januar 2023 19:29

Geschrieben von Mazin Shanyoor

Die Optimierung der MySQL und der MariaDB ist die effektivste Methode zur Steigerung der Performance von CMS und Shop-Systemen. Die mögliche Steigerung der Geschwindigkeit beim Ausspielen der Daten aus der MySQL bzw. MariaDB ist ein wesentlicher SEO-Vorteil und ist ein wichtiger Performance-Parameter bei der optimalen Ausnutzung von Hard- und Software-Konfigurationen.

Die einfachste Optimierung liegt in der Hardware. Dazu gehört eine schnelle CPU, genügend Arbeitsspeicher, um die Last zu verarbeiten, und ein schnelles Speichersystem (wie eine SSD-Festplatte).

Aber die beste Hardware verpufft, wenn man die MySQL-Konfiguration nicht an die Möglichkeiten und die Leistung der Hardware anpasst.

Anpassung und Optimieren der MySQL- und MariaDB-Server-Variablen an die Hardware-Konfiguration

Diese Variablen innodb_buffer_pool_size,  query_cache_size und sort_buffer_size sollten unbedingt angepasst und optimiert werden.

innodb_buffer_pool_size

Diese Variable bestimmt die Größe des InnoDB-Buffer-Pools, der zum Cachen von Daten und Indizes im Arbeitsspeicher verwendet wird. Der optimale Wert für die Variable innodb_buffer_pool_size hängt von Ihrer Last und Hardware ab. Sie sollte groß genug sein, um so viele Daten und Indizes wie möglich aufzunehmen, um die Anzahl der von MySQL auszuführenden Festplatten-I/Os zu reduzieren. Wenn Sie sie jedoch zu groß einstellen, kann sie zu viel Speicher verbrauchen und zu wenig Speicher für andere Prozesse auf dem Server übrig lassen.

Als allgemeine Regel sollte innodb_buffer_pool_size auf etwa 70-80% des gesamten verfügbaren Speichers auf Ihrem Server eingestellt werden. Wenn man zum Beispiel einen Server mit 32 GB Arbeitsspeicher hat, sollte innodb_buffer_pool_size auf etwa 23-26 GB gesetzt werden.

query_cache_size

Der optimale Wert für die Variable query_cache_size hängt von Ihrer Last und Hardware ab. Sie sollte groß genug sein, um die Ergebnisse häufig ausgeführter SELECT-Abfragen im Arbeitsspeicher zu speichern, um die Anzahl der von MySQL auszuführenden Festplatten-I/Os zu reduzieren. Wenn diese jedoch zu groß eingestellt wird, kann sie zu viel Speicher verbrauchen und zu wenig Speicher für andere Prozesse auf dem Server übrig lassen.

Als allgemeine Regel gilt, dass man query_cache_size auf etwa 10-30 MB pro 1 GB Arbeitsspeicher auf dem Server einstellen sollte. Wenn der Server mit 32 GB Arbeitsspeicher ausgestattet ist, kann query_cache_size auf etwa 320-960 MB einstellt werden. Es ist wichtig zu beachten, dass der Query-Cache nicht immer effektiv ist und in manchen Fällen sogar die Leistung verringern kann.

Wenn eine Applikation viele kleine verschiedene SELECT-Abfragen benötigt, die bei jeder Ausführung unterschiedliche Ergebnisse liefern, ist der Abfrage-Cache möglicherweise nicht sehr nützlich. In diesen Fällen ist es sinnvoller, die Variable query_cache_size auf 0 zu setzen, um den Abfragecache zu deaktivieren.

sort_buffer_size

Die sort_buffer_size ist eine MySQL-Server-Systemvariable, die bestimmt, wie viel Speicher jeder von dem Server durchgeführte Sortieroperation zugewiesen wird. Der Wert dieser Variable wird in Bytes zugewiesen. Es ist möglich, den Wert von sort_buffer_size zu ändern, um den MySQL-Server für eine bestimmte Last anzupassen. Eine Erhöhung des Werts kann jedoch auch den von MySQL verwendeten Speicher erhöhen, was in manchen Fällen nicht erwünscht ist. Standardmäßig ist die sort_buffer_size-Variable auf 256KB festgelegt. Dies sollte für die meisten Lasten ausreichen, aber wenn in der Applikation große Sortieroperationen durchführt werden (große ORDER BY- oder GROUP BY-Abfragen), muss möglicherweise der Wert dieser Variable erhöht werden.

Die optimale Größe der sort_buffer_size hängt von der Art der Sortieroperationen ab, die vom MySQL-Server durchgeführt werden, sowie von der verfügbaren Hardware (insbesondere, dem verfügbaren Arbeitsspeicher).
In der Regel gilt: Je größer der Wert von sort_buffer_size, desto schneller werden Sortieroperationen ausgeführt. Allerdings wird auch mehr RAM (Arbeitsspeicher) verwendet, wodurch möglicherweise weniger Speicher für andere Zwecke zur Verfügung steht.

Eine gute Regel ist es, den Wert von sort_buffer_size auf einen Bruchteil des verfügbaren Arbeitsspeichers festzulegen. Zum Beispiel kann der Wert auf 256 MB festgesetzt werden, wenn der Arbeitsspeicher 4 GB hat.

Es ist jedoch wichtig zu beachten, dass es keine feste Regel gibt, die für alle Situationen gilt. Um die optimale Größe für sort_buffer_size zu bestimmen, müssen möglicherweise mehrere Tests durchführt werden, um die Leistung des MySQL-Servers beurteilen zu können.

Verwenden der geeigneten Speicher-Engine für die MySQL-Tabellen

Verschiedene Speicher-Engines sind für verschiedene Lasten optimiert. Zum Beispiel ist die InnoDB-Engine besser für transaktionale Lasten, während die MyISAM-Engine besser für schreibgeschützte oder überwiegend schreibgeschützte Lasten ist.

Optimiertes indizieren der MySQL-Tabellen

Indizes können die Leistung der Abfragen auf die Datenbank erheblich verbessern, fügen aber auch Overhead für Schreibvorgänge hinzu. Es sollten daher nur Indizes auf Spalten erstellt werden, die in WHERE-Klauseln, JOIN-Bedingungen oder GROUP BY/ORDER BY-Klauseln Verwendung finden.

In welcher Datei befinden sich die MySQL-Server-Variablen

Die MySQL- und MariaDB-Server-Variablen werden in der Regel in einer Konfigurationsdatei mit dem Namen my.cnf (oder my.ini unter Windows) gespeichert. Diese Datei befindet sich in der Regel im Verzeichnis /etc/mysql/ oder /etc/mysql/mysql.conf.d/ auf Linux-Systemen und im Verzeichnis C:\ProgramData\MySQL\MySQL Server 8.0\ auf Windows-Systemen.

In der Datei my.cnf können die Werte der MySQL- und MariaDB-Server-Variablen geändert werden. Die Datei ist in der Regel in Abschnitte unterteilt, und es können die Variablen, die man ändern möchte, im entsprechenden Abschnitt platziert werden. Zum Beispiel kann die Variable innodb_buffer_pool_size im Abschnitt [mysqld] wie folgt platziert werden:

[mysqld]
innodb_buffer_pool_size = 23G

Kleine, aber nützliche Hinweise für die MSQL- und MariaDB-Optimierung

• Es ist eine gute Idee, die MySQL-Leistung zu überwachen und gegebenenfalls Anpassungen vorzunehmen. Es sollten Tools wie das MySQL Slow Query Log und der Befehl EXPLAIN verwendet werden, um Leistungsprobleme zu identifizieren und zu beheben.
• Die Datei my.cnf ist nur einer von mehreren Orten, an denen die MySQL-Server-Variablen festlegt werden kann. Die Variablen können auch zur Laufzeit mit der Anweisung SET oder mit dem Kommandozeilen-Client mysql festlegt werden.
• Die Änderungen an der MySQL- und MariaDB-Konfigurationsdatei werden erst wirksam, nachdem der MySQL-Server neu gestartet wurde (systemctl restart mysql oder service mysql restart).