13 Tipps zum Tunen und Optimieren von MySQL- und Mariadb-Datenbanken | 13 Detailed answer

MySQL und MariaDB sind die am weitesten verbreiteten relationalen Datenbankmanagementsysteme (RDMS) für Webhosting und CMS-Systeme wie Joomla, WordPress, Drupal und Typo 3. In diesem Artikel werde ich erklären, wie man MySQL optimiert und beschleunigt. MariaDB-Datenbankserver.

Speichern Sie MySQL-Daten auf separaten Partitionen

Wenn es um Optimierung und Sicherung geht, ist es immer die beste Idee, Ihre Datenbankdaten auf einem separaten Volume zu speichern. Die Volumes sind speziell für schnelle Speichervolumes wie SSD, NVMe ausgelegt. Selbst wenn das System abstürzt, ist die Datenbank sicher. Da das Partitionsvolume aus schnellen Speichervolumes besteht, ist die Leistung schneller.

Legen Sie die maximale Anzahl von MySQL-Verbindungen fest

MySQL / MariaDB verwendet nur einen Operator max_verbindungen die zeigt, wie viele gleichzeitige Verbindungen auf dem Server unterstützt werden. Zu viele Verbindungen führen zu hohem Speicherverbrauch und hoher CPU-Last. Für kleine Websites können Links zwischen 100-200 eingestellt werden, größere benötigen 500-800 und mehr. Was max_verbindungen kann dynamisch mit einer SQL-Abfrage geändert werden. In diesem Beispiel habe ich es auf 200 gesetzt.

$ mysql -u root -p

mysql> set world max_connections=200;

Ausgabe:

Aktivieren Sie die langsame Protokollierung von MySQL-Abfragen

Das Protokollieren lang andauernder Abfragen erleichtert die Fehlerbehebung bei Datenbankproblemen. Die Protokollierung langsamer Abfragen kann aktiviert werden, indem diese Zeilen zur MySQL/MariaDB-Konfigurationsdatei hinzugefügt 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 langsame Anforderungsprotokoll aktiviert

Die zweite Variable gibt das Protokolldateiverzeichnis an

Die dritte Variable definiert die Zeit zum Abschließen der MySQL-Abfrage

Starten Sie den Dienst mysql / mariadb neu und überprüfen Sie das Protokoll

$ systemctl restart mysql

$ systemctl restart mariadb

$ tail -f /var/lib/mysql/mysql-slow-query.log

Legen Sie das maximale von MySQL unterstützte Paket fest

Die Daten werden in MySQL-Paketen verteilt. Max_allowed_packet gibt die maximale Größe von Paketen an, die gesendet werden können. Wenn Sie max_allowed_packet zu niedrig einstellen, können Anfragen zu langsam sein. Wir empfehlen, den Paketwert auf die größte Paketgröße einzustellen.

Legen Sie die Kapazität der temporären Tabelle fest

Tmp_table_size ist der maximale Speicherplatz der internen Tabelle. Wenn die Tabellengröße das angegebene Limit überschreitet, wird sie in eine MyISAM-Tabelle auf der Festplatte konvertiert. In MySQL / MariaDB können Sie diese Variablen hinzufügen, um die temporäre Tabellengröße in der Konfigurationsdatei festzulegen. Wir empfehlen, diesen Wert auf 64 MB professional Server und professional GB festzulegen.

[mysqld]

tmp_table_size=64M

Starten Sie den MySQL-Dienst neu

$ systemctl restart mysql

$ systemctl restart mariadb

Stellen Sie die maximale Kapazität der Speicherkarte ein.

Max_heap_table_size ist eine Variable, die verwendet wird, um die maximale Kapazität von Heap-Tabellen in MySQL zu konfigurieren. Die maximale Inhaltsgröße der Speichertabelle muss mit dem Inhalt der temporären Tabelle übereinstimmen, damit sie nicht auf die Platte geschrieben wird. Wir empfehlen, diesen Wert auf 64 MB professional Serverspeicher einzustellen. Fügen Sie der MySQL-Konfigurationsdatei die folgende Zeile hinzu und starten Sie den Dienst neu.

[mysqld]

max_heap_table_size=64M

Starten Sie den Datenbankserver neu, um die Änderungen zu übernehmen.

$ systemctl restart mysql

$ systemctl restart mariadb

Deaktivieren Sie MySQL-Reverse-DNS-Lookups

Wenn eine neue Verbindung eintrifft, führt MySQL/MariaDB eine DNS-Suche durch, um die IP-Adresse des Benutzers aufzulösen. Dies kann zu Verzögerungen führen, wenn die DNS-Konfiguration ungültig ist oder ein Problem mit dem DNS-Server vorliegt. Um DNS-Lookups zu deaktivieren, fügen Sie die folgende Zeile zur MySQL-Konfigurationsdatei hinzu und starten Sie den MySQL-Dienst neu.

[mysqld]

skip-name-resolve

Dienst neu starten:

$ systemctl restart mysql

$ systemctl restart mariadb

Vermeiden Sie die Verwendung von MySQL Exchange

Der Linux-Kernel wird auf eine spezielle Festplattenpartition verschoben, die als Teil des Speichers bezeichnet wird Austausch Speicherplatz, wenn dem System der physische Speicher ausgeht. In dieser Situation schreibt das System Informationen, anstatt Speicherplatz freizugeben. Da der Systemspeicher schneller ist als der Festplattenspeicher, empfehlen wir, die Auslagerung zu deaktivieren. Die Ersetzungsoption kann mit dem folgenden Befehl deaktiviert werden.

$ sysctl -w vm.swappiness=0

Ausgabe:

Erhöhen Sie die Größe des InnoDB-Pufferpools

MySQL/MariaDB verfügt über eine InnoDB-Engine mit einem Pufferpool zum Zwischenspeichern und Indizieren von Daten im Arbeitsspeicher. Der Pufferpool hilft MySQL/MariaDB-Abfragen, relativ schneller zu laufen. Die Kenntnis des Systemspeichers ist erforderlich, um die richtige Größe des InnoDB-Pufferpools auszuwählen. Die beste Idee ist, die Größe des InnoDB-Pufferpools auf 80 % des Arbeitsspeichers festzulegen.

Ein Beispiel.

• Systemspeicher = 4 GB
• Puffergröße = 3,2 GB

Fügen Sie der MySQL-Konfigurationsdatei die folgende Zeile hinzu und starten Sie den Dienst neu

[mysqld]

Innodb_buffer_pool_size 3.2G

Datenbank neu starten:

$ systemctl restart mysql

$ systemctl restart mariadb

Größe des Anfragecaches verwalten

Die MySQL/MariaDB-Abfrage-Cache-Direktive wird verwendet, um alle wiederholten Abfragen mit denselben Daten zwischenzuspeichern. Wir empfehlen, den Wert auf 64 MB festzulegen und die Zeit für kleine Seiten zu erhöhen. Es wird nicht empfohlen, die Größe des Abfragecaches auf GB zu erhöhen, da dies die Leistung der Datenbank beeinträchtigen kann. Fügen Sie der Datei my.cnf die folgende Zeile hinzu.

[mysqld]

query_cache_size=64M

Überprüfen Sie inaktive Links

Inaktive Verbindungen verbrauchen Ressourcen und sollten wann immer möglich beendet oder aktualisiert werden. Diese Verknüpfungen bleiben bestehen Traum Dies kann eine Bedingung und eine lange Zeit sein. Überprüfen Sie inaktive Verbindungen mit dem folgenden Befehl.

$ mysqladmin processlist -u root -p | grep Sleep

Die Abfrage listet die ruhenden Prozesse auf. Grundsätzlich kann das Event in PHP bei Verwendung von mysql_pconnect auftreten. Dadurch wird die MySQL-Verbindung geöffnet, die Abfrage ausgeführt, die Anmeldeinformationen entfernt und die Verbindung offen gelassen. Benutze es Wartezeit Gemäß der Richtlinie können nicht verwendete Anschlüsse getrennt werden. Standard Wartezeit Dies sind 28800 Sekunden, die auf ein minimales Zeitintervall von 60 Sekunden reduziert werden können. Fügen Sie der Datei my.cnf die folgende Zeile hinzu

[mysqld]

wait_timeout=60

Optimierung und Reparatur von MySQL-Datenbanken

Wenn der Server unerwartet ausfällt, können die MySQL-/MariaDB-Tabellen beschädigt werden. Es gibt andere mögliche Gründe für das Versagen von Datenbanktabellen, wie z. B.: Wenn der Kopiervorgang verfügbar ist, wenn die Datenbank verfügbar ist, stürzt das Dateisystem plötzlich ab. In dieser Situation haben wir ein spezielles Tool namens mysqlcheck die alle Datenbanktabellen prüft, repariert und optimiert.

Verwenden Sie diesen Befehl, um Reparatur- und Optimierungsaktivitäten durchzuführen.

Für jede Datenbank:

$ 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 den Datenbanknamen

1. Ü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 macht es einfach, einen Leistungsbericht und Verbesserungspunkte zu erhalten. Es gibt viele Tools da draußen und unter ihnen ist mysqltuner das beste.

Führen Sie diesen Befehl aus, um das Tool herunterzuladen

$ wget https://github.com/major/MySQLTuner-perl/tarball/master

Entpacken Sie die Datei

$ tar xvzf grasp

Wechseln Sie in Ihr Projektverzeichnis und führen Sie dieses Skript aus.

$ cd major-MySQLTuner-perl-7aa57fa

$ ./mysqltuner.pl

Ausgabe:

Fazit

In diesem Artikel haben wir gelernt, wie man MySQL/MariaDB mit verschiedenen Techniken optimiert. Vielen Dank fürs Lesen.