Optimieren und Optimieren der Leistung von MySQL 8.0 auf einem CentOS 7-Server

Einführung

Bevor wir beginnen, müssen Sie die Faktoren verstehen, die sich auf die MySQL-Leistung auswirken, damit Sie lernen können, den MySQL-Server auf die richtige Weise zu optimieren und zu optimieren und die richtigen Ergebnisse zu erzielen. Die Hauptfaktoren sind:


  • Datenvolumen, das abgerufen wird>
  • Verfügbare Ressourcen, d. H. CPU, Speicher (RAM)
  • Wird vom MySQL-Server ausgeführt

Ursachen für eine schlechte Leistung des MySQL-Servers sind:

  • Schlechtes Datenbankdesign
  • Hardware-Engpässe
  • Schlechte Codierung
  • Schlechte Indizierung
  • Ineffiziente Konfiguration

In diesem Artikel wird veranschaulicht, wie die Leistung von MySQL 8.0 unter CentOS 7 Linux VPS optimiert und optimiert wird.

Voraussetzungen:

  • CentOS 7 VPS-Setup
  • Installierter MySQL 8.0 Server
  • Benutzer mit Sudo-Berechtigungen erstellt

Installation von mysqltuner und Tuning-Primer

Installieren Sie mysqltuner

Mysqltuner ist ein leistungsstarkes Perl-Skript zur Optimierung von MySQL, das einen Überblick über den Integritätsstatus des MySQL-Servers gibt und spezifische Empfehlungen für eine verbesserte, verbesserte Leistung, Stabilität und Effizienz enthält.

Führen Sie die folgenden Befehle aus, um mysqltuner zu installieren

Installieren Sie zuerst epel-release, da sich das mysqltuner-Paket im epel-release-Repository befindet

$ sudo yum install -y epel-release

Dann installieren Sie mysqltuner

$ sudo yum install -y mysqltuner

Tuning-Primer installieren

Tuning Primer ist ein Shell-Skript, das Informationen von MySQL-Server-Interna abruft und Empfehlungen zur Anpassung der Servervariablen gibt.

Führen Sie die folgenden Befehle aus, um den Tuning-Primer zu installieren:

$ sudo yum install bc -y
$ git-Klon https://github.com/RootService/tuning-primer.git

Besonderer Hinweis: Wenn git nicht installiert ist, können Sie es mit installieren

$ sudo yum installiere git -y

Wechseln Sie in das Tuning-Primer-Verzeichnis und machen Sie das Tuning-Primer-Skript wie folgt ausführbar:

$ cd Tuning-Primer
$ sudo chmod + x tuning-primer.sh

Optimierung und Optimierung des MySQL-Servers

Die Optimierung des MySQL-Servers erfolgt in der MySQL-Konfigurationsdatei.

/etc/my.cnf

Führen Sie mysqltuner aus.

$ sudo mysqltuner

Geben Sie den MySQL-Benutzer mit allen Berechtigungen (in unserem Fall “root”) und Passwort nach Aufforderung ein

[linuxuser @ centos7-hostadvice4 ~] $ sudo mysqltuner
Bitte geben Sie Ihr MySQL-Administrator-Login ein: root
Bitte geben Sie Ihr MySQL-Administratorkennwort ein: >> MySQLTuner 1.6.0 – Major Hayden
>> Fehlerberichte, Funktionsanfragen und Downloads unter http://mysqltuner.com/
>> Führen Sie mit ‘–help’ zusätzliche Optionen und Ausgabefilter aus
[-] Versionsprüfung für MySQLTuner-Skript übersprungen
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
[!!] Derzeit wird MySQL Version 8.0.11 nicht unterstützt
[OK] Betrieb mit 64-Bit-Architektur

——– Storage Engine-Statistiken ——————————————-
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
[-] Status: + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MRG_MYISAM
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
[-] Daten in InnoDB-Tabellen: 16 KB (Tabellen: 1)
[OK] Insgesamt fragmentierte Tabellen: 0
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.

——– Sicherheitsempfehlungen ——————————————-
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
[OK] Es gibt kein anonymes Konto in allen Datenbankbenutzern
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
FEHLER 1054 (42S22) in Zeile 1: Unbekannte Spalte ‘Passwort’ in ‘where-Klausel’
[OK] Allen Datenbankbenutzern sind Kennwörter zugewiesen
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
FEHLER 1064 (42000) in Zeile 1: Sie haben einen Fehler in Ihrer SQL-Syntax. Überprüfen Sie das Handbuch, das Ihrer MySQL-Serverversion entspricht, auf die richtige Syntax für die Verwendung in der Nähe von ‘(Benutzer) ODER CAST (Kennwort als Binär) = PASSWORT (OBER (Benutzer)) ODER CAST (Kennwort als B’ in Zeile 1)
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
[!!] Es gibt keine grundlegende Liste der Passwortdateien !
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
mysql: [Warnung] Die Verwendung eines Kennworts auf der Befehlszeilenschnittstelle kann unsicher sein.
Verwendung des nicht initialisierten Werts zusätzlich (+) in der Zeile / bin / mysqltuner 1934, <>
Zeile 2 (Nr. 1)
(W nicht initialisiert) Ein undefinierter Wert wurde so verwendet, als ob er bereits vorhanden wäre
definiert. Es wurde interpretiert als "" oder eine 0, aber vielleicht war es ein Fehler.
Um diese Warnung zu unterdrücken, weisen Sie Ihren Variablen einen definierten Wert zu.

Um Ihnen zu helfen, herauszufinden, was undefiniert war, wird Perl versuchen, es Ihnen zu sagen
der Name der Variablen (falls vorhanden), die nicht definiert war. In manchen Fällen
Dies ist nicht möglich, daher wird auch angegeben, welche Operation Sie verwendet haben
undefinierter Wert in. Beachten Sie jedoch, dass Perl Ihr Programm optimiert
Außerdem muss der in der Warnung angezeigte Vorgang nicht unbedingt angezeigt werden
buchstäblich in Ihrem Programm. Zum Beispiel, "das $ foo" ist gewöhnlich
optimiert in "Das " . $ foo, und die Warnung bezieht sich auf die
Verkettungsoperator (.), obwohl es keinen gibt. im
Ihr Programm.

Verwendung des nicht initialisierten Werts in der Division (/) in der Zeile / bin / mysqltuner 1934, <>
Zeile 2 (Nr. 1)
Verwendung des nicht initialisierten Wertes $ mystat {"Qcache_lowmem_prunes"} in numerischer Gleichung (==)
at / bin / mysqltuner line 1949, <> Zeile 2 (Nr. 1)

——– Leistungsmetriken ————————————————-
[-] Bis zu: 1h 37m 3s (12 q [0,002 qps], 20 conn, TX: 20K, RX: 2K)
[-] Liest / schreibt: 100% / 0%
[-] Die binäre Protokollierung ist aktiviert (GTID-MODUS: AUS)
[-] Gesamtpuffer: 168,0 Mio. global + 1,1 Mio. pro Thread (maximal 151 Threads)
[OK] Maximal erreichte Speicherauslastung: 169,1 MB (4,78% des installierten Arbeitsspeichers)
[OK] Maximal mögliche Speichernutzung: 341,4 MB (9,65% des installierten Arbeitsspeichers)
[OK] Langsame Abfragen: 0% (0/12)
[OK] Höchste Auslastung der verfügbaren Verbindungen: 0% (1/151)
[!!] Abgebrochene Verbindungen: 40,00% (8/20)
Verwendung des nicht initialisierten Werts $ myvar {"query_cache_size"} in numerischen lt (<) beim
/ bin / mysqltuner Zeile 2281, <> Zeile 2 (Nr. 1)
[!!] Der Abfrage-Cache ist deaktiviert
[OK] Auf der Festplatte erstellte temporäre Tabellen: 0% (0 auf der Festplatte / 2 insgesamt)
[OK] Trefferquote im Thread-Cache: 95% (1 erstellt / 20 Verbindungen)
[OK] Trefferquote im Tabellen-Cache: 83% (118 offen / 142 geöffnet)
[OK] Verwendetes offenes Dateilimit: 0% (2 / 10K)
[OK] Sofort erworbene Tabellensperren: 100% (2 Sofort- / 2 Sperren)
[OK] Binlog-Cache-Speicherzugriff: 0% (0 Speicher / 0 Gesamt)

——– MyISAM-Metriken —————————————————–
[!!] Verwendeter Schlüsselpuffer: 18,2% (1M verwendet / 8M Cache)
[!!] MyISAM-Indexgröße kann nicht berechnet werden – Skript als Root-Benutzer erneut ausführen

——– InnoDB-Metriken —————————————————–
[-] InnoDB ist aktiviert.
[OK] InnoDB-Pufferpool / Datengröße: 128,0 MB / 16,0 KB
[OK] InnoDB-Pufferpoolinstanzen: 1
[!!] InnoDB Verwendeter Puffer: 11,08% (908 verwendet / 8192 insgesamt)
[OK] InnoDB-Lesepuffer-Effizienz: 94,07% (12243 Treffer / 13015 insgesamt)
[!!] InnoDB Schreibpuffereffizienz: 0,00% (0 Treffer / 1 insgesamt)
[OK] InnoDB-Protokoll wartet: 0,00% (0 wartet / 12 Schreibvorgänge)

——– AriaDB-Metriken —————————————————–
[-] AriaDB ist deaktiviert.

——– Replikationsmetriken ————————————————-
[-] Keine Replikations-Slaves für diesen Server.
[-] Dies ist ein eigenständiger Server..

——– Empfehlungen —————————————————–
Generelle Empfehlungen:
MySQL wurde innerhalb der letzten 24 Stunden gestartet – Empfehlungen sind möglicherweise ungenau
Reduzieren oder beseitigen Sie nicht geschlossene Verbindungen und Netzwerkprobleme
Zu justierende Variablen:
query_cache_size (>= 8M)
[linuxuser @ centos7-hostadvice4 ~] $

Konzentrieren Sie sich auf den letzten Teil der Ausgabe im Abschnitt „Empfehlungen“ und überprüfen Sie die Variablen, die erhöht werden müssen.

Erstellen Sie eine Sicherungskopie der MySQL-Konfigurationsdatei und bearbeiten Sie die eigentliche Konfigurationsdatei.

$ sudo cp /etc/my.cnf /etc/my.cnf.bak
$ sudo vim /etc/my.cnf

Passen Sie die Variable der Datei gemäß den Empfehlungen an. Sie können die Standardvariablenwerte auskommentieren und die Variablenwerte auf das Doppelte des Standardwerts erhöhen. Tatsächlich kann es einige Tage dauern, bis die besten Werte für Ihren Server ermittelt sind.

Starten Sie nach dem Vornehmen der Änderungen den MySQL-Server neu.

$ sudo systemctl starte mysql neu

Besonderer Hinweis: Wenn der Neustart fehlschlägt, gehen Sie zurück und kommentieren Sie die neuen Werte aus. Beginnen Sie mit der Anpassung nacheinander und prüfen Sie, welche Variablenanpassung den Serverausfall verursacht.

Wenn der Server erfolgreich neu geladen wird, führen Sie mysqltuner erneut aus und prüfen Sie, ob weitere Empfehlungen zur Anpassung der Variablenwerte vorliegen.

Führen Sie einen ähnlichen Vorgang zum Einstellen des Primers durch.

Tuning-Primer ausführen

$ sudo Tuning-Primer
[linuxuser @ centos7-hostadvice4 tuning-primer] $ sudo ./tuning-primer.sh
— MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 —
– Von: Matthew Montgomery –
– Von: Markus Kohlmeyer –

MySQL Version 8.0.11 x86_64

Betriebszeit = 0 Tage 0 Std. 2 Min. 52 Sek
Durchschn. qps = 0
Fragen insgesamt = 4
Verbundene Threads = 1

Warnung: Der Server wurde mindestens 48 Stunden lang nicht ausgeführt.
Es ist möglicherweise nicht sicher, diese Empfehlungen zu verwenden

Weitere Informationen dazu finden Sie hier
Laufzeitvariablen wirken sich auf den Leistungsbesuch aus:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
Besuchen Sie http://www.mysql.com/products/enterprise/advisors.html
Informationen zum Enterprise Monitoring and Advisory Service von MySQL

LANGSAME FRAGEN
Das langsame Abfrageprotokoll ist NICHT aktiviert.
Aktuelle long_query_time = 10.000000 Sek.
Sie haben 0 von 25, die länger als 10.000000 Sekunden dauern. fertigstellen
Ihre long_query_time ist möglicherweise zu hoch. Ich setze dies normalerweise unter 5 Sekunden.

BINARY UPDATE LOG
Das binäre Aktualisierungsprotokoll ist aktiviert
Die expire_logs_days sind nicht festgelegt.
Das mysqld behält das gesamte Binärprotokoll bei, bis die Befehle RESET MASTER oder PURGE MASTER LOGS manuell ausgeführt werden
Durch Festlegen von expire_logs_days können Sie alte Binärprotokolle automatisch entfernen
Siehe http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html

ARBEITNEHMERGEWINDE
Aktuelle thread_cache_size = 9
Aktuelle threads_cached = 0
Aktuelle threads_per_sec = 0
Historischer threads_per_sec = 0
Ihre thread_cache_size ist in Ordnung

MAX. ANSCHLÜSSE
Aktuelle max_connections = 151
Aktuelle threads_connected = 1
Historische max_used_connections = 1
Die Anzahl der verwendeten Verbindungen beträgt 0% des konfigurierten Maximums.
Sie verwenden weniger als 10% Ihrer konfigurierten max_connections.
Durch Verringern von max_connections kann eine Überbelegung des Speichers vermieden werden
Sehen "SPEICHERNUTZUNG" Abschnitt, um sicherzustellen, dass Sie nicht zu viel zuweisen

INNODB-STATUS
Aktueller InnoDB-Indexraum = 240 K.
Aktueller InnoDB-Datenraum = 2 M.
Aktueller InnoDB-Pufferpool frei = 88%
Aktuelle innodb_buffer_pool_size = 128 M.
Je nachdem, wie viel Speicherplatz Ihre Innodb-Indizes belegen, ist dies möglicherweise sicher
um diesen Wert auf bis zu 2/3 des gesamten Systemspeichers zu erhöhen

SPEICHERNUTZUNG
Maximal zugewiesener Speicher: 153 M.
Konfigurierte maximale Puffer pro Thread: 178 M.
Konfigurierte maximale globale Puffer: 152 M.
Konfiguriertes maximales Speicherlimit: 330 M.
Plus 16 M pro erstellte temporäre Tabelle
Physischer Speicher: 3,45 G.
Die maximale Speichergrenze scheint innerhalb akzeptabler Normen zu liegen

SCHLÜSSELPUFFER
Kein Schlüssel liest?!
Schauen Sie sich ernsthaft die Verwendung einiger Indizes an
Aktueller MyISAM-Indexbereich = 0 Byte
Aktuelle key_buffer_size = 8 M.
Die Schlüssel-Cache-Fehlerrate beträgt 1: 0
Schlüsselpufferfreies Verhältnis = 81%
Ihre key_buffer_size scheint in Ordnung zu sein

QUERY CACHE
Der Abfragecache wird unterstützt, aber nicht aktiviert
Vielleicht sollten Sie die query_cache_size festlegen

SORTIERBETRIEB
Aktuelle sort_buffer_size = 256 K.
Aktuelle read_rnd_buffer_size = 256 K.
Es wurden keine Sortiervorgänge ausgeführt
Der Sortierpuffer scheint in Ordnung zu sein

Tritt bei
Aktuelle join_buffer_size = 260.00 K.
Sie hatten 3 Abfragen, bei denen ein Join einen Index nicht richtig verwenden konnte
Sie sollten aktivieren "Protokollabfragen, die keine Indizes verwenden"
Suchen Sie dann im langsamen Abfrageprotokoll nach nicht indizierten Verknüpfungen.

OPEN FILES LIMIT
Aktuelle open_files_limit = 10000 Dateien
Das open_files_limit sollte normalerweise auf mindestens 2x-3x eingestellt sein
das von table_open_cache, wenn Sie MyISAM stark nutzen.
Ihr open_files_limit-Wert scheint in Ordnung zu sein

TISCHCACHE
Aktueller table_open_cache = 4000 Tabellen
Aktueller table_definition_cache = 2000 Tabellen
Sie haben insgesamt 136 Tabellen
Sie haben 165 offene Tische.
Der Wert table_open_cache scheint in Ordnung zu sein

TEMP-TABELLEN
Aktuelle max_heap_table_size = 16 M.
Aktuelle tmp_table_size = 16 M.
Von 95 temporären Tabellen wurden 0% auf der Festplatte erstellt
Das Verhältnis der erstellten Festplatten-TMP-Tabellen scheint in Ordnung zu sein

TABELLENSCANS
Aktuelle read_buffer_size = 128 K.
Aktuelles Tabellen-Scan-Verhältnis = 793: 1
read_buffer_size scheint in Ordnung zu sein

TISCHVERRIEGELUNG
Current Lock Wait Ratio = 0: 286
Ihre Tischverriegelung scheint in Ordnung zu sein

[linuxuser @ centos7-hostadvice4 tuning-primer] $

Besonderer Hinweis: Konzentrieren Sie sich mehr auf den rot und gelb hervorgehobenen Text, da er Empfehlungen zur Optimierung und Verbesserung der MySQL-Leistung enthält. .

Passen Sie die Variable der Datei gemäß den Empfehlungen an. Sie können die Standardvariablenwerte auskommentieren und die Variablenwerte wie in den Empfehlungen vorgeschlagen anpassen. .

Starten Sie den MySQL-Server nach den Änderungen neu.

$ sudo systemctl starte mysql neu

Besonderer Hinweis: Wenn der Neustart fehlschlägt, gehen Sie zurück und kommentieren Sie die neuen Werte aus. Beginnen Sie mit der Anpassung nacheinander und prüfen Sie, welche Variablenanpassung den Serverausfall verursacht.

Wenn der Server erfolgreich neu gestartet wurde, führen Sie Tuneprimer erneut aus und prüfen Sie, ob weitere Empfehlungen zur Anpassung der Variablenwerte vorliegen.

Fazit

Es ist sehr wichtig zu beachten, dass die Tuning-Tools mysqltuner oder tuning-primer nicht die Lösungen für einen schlecht funktionierenden MySQL-Server sind, sondern Skripte, die Sie bei der Feinabstimmung des MySQL-Servers unterstützen.

Für beste Leistung,

  • Führen Sie eine gründliche Überprüfung der an den Server gesendeten Abfragen durch und optimieren Sie die Datenbankstruktur / das Datenbankdesign und die SQL-Abfrage
  • Bewerten Sie die Faktoren, die die MySQL-Leistung beeinflussen, wie in der Einführung erläutert.
  • Erwägen Sie ein Upgrade der Hardware des physischen Servers für eine bessere Leistung und optimieren Sie die MySQL-Konfigurationen basierend auf den Hardwarespezifikationen. Wenden Sie sich an HostAdvice, um die besten MySQL-Hosting-Optionen zu erhalten.
  • Erfahren Sie mehr darüber, wie Sie MySQL-Konfigurationen basierend auf Anwendungen optimieren können, die MySQL Server verwenden.

Es wird dringend empfohlen, mysqltuner oder das Tuning-Primer-Tool nach dem Neustart des MySQL-Servers regelmäßig über einen Zeitraum von 48 Stunden auszuführen, um eine aussagekräftige Nutzungsstatistik zu erhalten.

Schauen Sie sich diese Top 3 MySQL-Hosting-Services an:

A2 Hosting

Startpreis:
$ 3,92


Verlässlichkeit
9.3


Preisgestaltung
9.0


Benutzerfreundlich
9.3


Unterstützung
9.3


Eigenschaften
9.3

Bewertungen lesen

Besuchen Sie A2 Hosting

FastComet

Startpreis:
2,95 $


Verlässlichkeit
9.7


Preisgestaltung
9.5


Benutzerfreundlich
9.7


Unterstützung
9.7


Eigenschaften
9.6

Bewertungen lesen

Besuchen Sie FastComet

Hostinger

Startpreis:
0,99 $


Verlässlichkeit
9.3


Preisgestaltung
9.3


Benutzerfreundlich
9.4


Unterstützung
9.4


Eigenschaften
9.2

Bewertungen lesen

Besuchen Sie Hostinger

Verwandte Anleitungen

  • Optimieren und Optimieren der Leistung von MySQL 5.7 auf einem Ubuntu 18.04 VPS oder einem dedizierten Server
    Experte
  • So installieren Sie phpMyAdmin auf einem CentOS 7 VPS oder einem dedizierten Server
    mittlere
  • So sichern Sie Ihre MySQL-Datenbank auf einem Ubuntu 18.04 VPS oder einem dedizierten Server
    mittlere
  • So installieren Sie MYSQL 8.0 und erstellen eine Datenbank auf einem CentOS 7 Linux VPS oder einem dedizierten Server
    mittlere
  • So erstellen Sie neue MySQL-Benutzerkonten und verwalten MySQL-Berechtigungen
    Neuling
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me