MySQL und MariaDB sind die am weitesten verbreiteten relationalen Datenbankverwaltungssysteme (RDMS), wenn es um Website-Hosting und CMS-Systeme wie Joomla, WordPress, Drupal und Typo 3 geht. In diesem Artikel werde ich erklären, wie Sie Ihre beschleunigen und optimieren können MySQL- und MariaDB-Datenbankserver.
MySQL-Daten in separaten Partitionen speichern
Wenn es um Optimierung und Absicherung geht, ist es immer die beste Idee, die Datenbankdaten auf einem separaten Volume zu speichern. Die Volumes sind speziell für schnelle Speichervolumes wie SSD, NVMe. Selbst wenn Ihr System ausfällt, haben Sie Ihre Datenbank sicher. Da das Partitionsvolumen aus schnellen Speichervolumen besteht, ist die Leistung schneller.
Legen Sie die maximale Anzahl von MySQL-Verbindungen fest
MySQL/MariaDB verwendet eine Anweisung max_connections die angibt, wie viele gleichzeitige Verbindungen derzeit auf dem Server zulässig sind. Zu viele Verbindungen führen zu einem hohen Speicherverbrauch sowie einer hohen CPU-Last. Für kleine Websites können die Verbindungen auf 100-200 festgelegt werden und größere benötigen möglicherweise 500-800 und mehr. Die max_connections kann über die SQL-Abfrage dynamisch geändert werden. In diesem Beispiel habe ich den Wert auf 200 gesetzt.
$ mysql -u root -p
mysql> set global max_connections=200;
Ausgabe :
MySQL-Protokoll für langsame Abfragen aktivieren
Das Protokollieren von Abfragen, deren Ausführung sehr lange dauert, erleichtert die Fehlerbehebung bei Datenbankproblemen. Das Log für langsame Abfragen kann durch Hinzufügen der folgenden Zeilen in der MySQL/MariaDB-Konfigurationsdatei aktiviert werden.
slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1
Wobei die erste Variable das Protokoll für langsame Abfragen aktiviert
Die zweite Variable definiert das Protokolldateiverzeichnis
Die dritte Variable definiert die Zeit zum Abschließen einer MySQL-Abfrage
Starten Sie den Dienst mysql/mariadb neu und überwachen Sie das Protokoll
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Setzen Sie das maximale von MySQL erlaubte Paket
Daten werden in MySQL in Pakete aufgeteilt. Max_allowed_packet definiert die maximale Größe der Pakete, die gesendet werden können. Eine zu niedrige Einstellung von max_allowed_packet kann dazu führen, dass die Abfrage zu langsam ist. Es wird empfohlen, den Paketwert auf die Größe des größten Pakets einzustellen.
Einrichten der temporären Tabellenkapazität
Tmp_table_size ist der maximale Speicherplatz, der für die eingebaute Speichertabelle verwendet wird. Wenn die Größe der Tabelle die angegebene Grenze überschreitet, wird sie in eine MyISAM-Tabelle auf der Festplatte konvertiert. In MySQL/MariaDB können Sie die folgenden Variablen in der Konfigurationsdatei hinzufügen, um die temporäre Tabellengröße einzurichten. Es wird empfohlen, diesen Wert auf dem Server auf 64 MB pro GB Arbeitsspeicher einzustellen.
[mysqld] tmp_table_size=64M
Starten Sie den MySQL-Dienst neu
$ systemctl restart mysql
$ systemctl restart mariadb
Stellen Sie die maximale Kapazität der Speichertabelle ein.
Max_heap_table_size ist die Variable, die in MySQL verwendet wird, um die maximale Speichertabellenkapazität zu konfigurieren. Die Größe der maximalen Speichertabellenkapazität sollte der temporären Tabellenkapazität entsprechen, um Festplattenschreibvorgänge zu vermeiden. Es wird empfohlen, diesen Wert auf dem Server auf 64 MB pro GB Arbeitsspeicher einzustellen. Fügen Sie die folgende Zeile in die MySQL-Konfigurationsdatei ein und starten Sie den Dienst neu.
[mysqld] max_heap_table_size=64M
Um die Änderungen zu übernehmen, starten Sie den Datenbankserver neu.
$ systemctl restart mysql
$ systemctl restart mariadb
DNS-Reverse-Lookup für MySQL deaktivieren
Wenn eine neue Verbindung empfangen wird, führt MySQL/MariaDB eine DNS-Suche durch, um die IP-Adresse des Benutzers aufzulösen. Dies kann zu einer Verzögerung führen, wenn die DNS-Konfiguration ungültig ist oder ein Problem mit dem DNS-Server vorliegt. Um die DNS-Suche zu deaktivieren, fügen Sie die folgende Zeile in die MySQL-Konfigurationsdatei ein und starten Sie den MySQL-Dienst neu.
[mysqld] skip-name-resolve
Starten Sie den Dienst neu:
$ systemctl restart mysql
$ systemctl restart mariadb
Vermeiden Sie die Verwendung von Swapiness in MySQL
Der Linux-Kernel verschiebt einen Teil des Arbeitsspeichers auf eine spezielle Partition der Festplatte, die als „Auslagerungsspeicher“ bezeichnet wird, wenn dem System der physische Arbeitsspeicher ausgeht. In diesem Zustand schreibt das System Informationen auf die Festplatte, anstatt Speicherplatz freizugeben. Da der Systemspeicher schneller als der Festplattenspeicher ist, wird empfohlen, die Auslagerung zu deaktivieren. Swapiness kann mit dem folgenden Befehl deaktiviert werden.
$ sysctl -w vm.swappiness=0
Ausgabe :
Größe des InnoDB-Pufferpools erhöhen
MySQL/MariaDB verfügt über eine InnoDB-Engine, die über einen Pufferpool verfügt, um Daten im Arbeitsspeicher zwischenzuspeichern und zu indizieren. Pufferpool hilft MySQL/MariaDB-Abfragen vergleichsweise schneller auszuführen. Die Auswahl der richtigen Größe des InnoDB-Pufferpools erfordert einige Kenntnisse über den Systemspeicher. Die beste Idee ist, den Wert der InnoDB-Pufferpoolgröße auf 80 % des Arbeitsspeichers festzulegen.
Beispiel.
- Systemspeicher =4 GB
- Pufferpoolgröße =3,2 GB
Fügen Sie die folgende Zeile in die MySQL-Konfigurationsdatei ein und starten Sie den Dienst neu
[mysqld] Innodb_buffer_pool_size 3.2G
Starten Sie die Datenbank neu:
$ systemctl restart mysql
$ systemctl restart mariadb
Umgang mit der Größe des Abfrage-Cache
Die Abfrage-Cache-Direktive in MySQL/MariaDB wird verwendet, um alle Abfragen zwischenzuspeichern, die sich immer wieder mit denselben Daten wiederholen. Es wird empfohlen, den Wert auf 64 MB einzustellen und die Zeit für kleine Websites zu erhöhen. Es wird nicht empfohlen, den Wert der Abfragecachegröße auf GB zu erhöhen, da dies die Datenbankleistung beeinträchtigen kann. Fügen Sie die folgende Zeile in die my.cnf-Datei ein.
[mysqld] query_cache_size=64M
Inaktive Verbindungen prüfen
Ressourcen werden von inaktiven Verbindungen verbraucht, sodass sie nach Möglichkeit beendet oder aktualisiert werden müssen. Diese Verbindungen verbleiben im „Schlaf“-Zustand und können über einen längeren Zeitraum bestehen bleiben. Überprüfen Sie die im Leerlauf befindlichen Verbindungen mit dem folgenden Befehl.
$ mysqladmin processlist -u root -p | grep “Sleep”
Die Abfrage listet die Prozesse auf, die sich im Ruhezustand befinden. Im Allgemeinen kann das Ereignis in PHP auftreten, wenn mysql_pconnect verwendet wird. Dadurch wird die MySQL-Verbindung geöffnet, die Abfragen ausgeführt, Authentifizierungen entfernt und die Verbindung offen gelassen. Verwendung von wait_timeout Direktive können die ungenutzten Verbindungen unterbrochen werden. Der Standardwert für wait_timeout beträgt 28800 Sekunden, die auf einen minimalen Zeitbereich wie 60 Sekunden verringert werden kann. Fügen Sie die folgende Zeile in der my.cnf-Datei hinzu
[mysqld] wait_timeout=60
MySQL-Datenbank optimieren und reparieren
Wenn der Server unerwartet heruntergefahren wird, besteht die Möglichkeit, dass Tabellen in MySQL/MariaDB abstürzen. Es gibt andere mögliche Gründe für den Absturz der Datenbanktabelle, z. B. wenn auf die Datenbank zugegriffen wird, während der Kopiervorgang ausgeführt wird, das Dateisystem plötzlich abgestürzt ist. In dieser Situation haben wir ein spezielles Tool namens „mysqlcheck“. ” das alle Tabellen in den Datenbanken prüft, repariert und optimiert.
Verwenden Sie den folgenden Befehl, um die Reparatur- und Optimierungsaktivitäten durchzuführen.
Für alle Datenbanken:
$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Für eine bestimmte Datenbank:
$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Ersetzen Sie dbname durch Ihren Datenbanknamen
- Überprüfen Sie die Leistung von MySQL/MariaDB mit Testtools
Es empfiehlt sich, die Leistung der MySQL/MariaDB-Datenbank regelmäßig zu überprüfen. Dies erleichtert das Abrufen des Leistungsberichts und des Verbesserungspunkts. Es gibt viele Tools, unter denen mysqltuner das beste ist.
Führen Sie den folgenden Befehl aus, um das Tool herunterzuladen
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Entpacken Sie die Datei
$ tar xvzf master
Wechseln Sie in das Projektverzeichnis und führen Sie das folgende Skript aus.
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Ausgabe:
Schlussfolgerung
In diesem Artikel haben wir gelernt, wie man MySQL/MariaDB mit verschiedenen Techniken optimiert. Danke fürs Lesen.