MySQL-Serie 15: Allgemeine MySQL-Konfiguration und Leistungsstresstest

1. Allgemeine MySQL-Konfiguration

Alle folgenden Konfigurationsparameter basieren auf einem Server mit 32 GB Arbeitsspeicher.

1. Öffnen Sie einen unabhängigen Tablespace

innodb_file_per_table = 1

2. Die Obergrenze der Anzahl gleichzeitiger Sitzungen, die der MySQL-Dienst zulässt, beträgt standardmäßig 151. Wenn die Fehlermeldung „Zu viele Verbindungen“ häufig auftritt, müssen Sie diesen Wert erhöhen.

max_Verbindungen = 8000

3. Die Anzahl der Verbindungen, die das Betriebssystem in der Abhörwarteschlange aufrechterhalten kann

back_log = 300

4. Die maximal zulässige Anzahl von Fehlern für jede Clientverbindung. Wenn diese Zahl überschritten wird, verhindert der MySQL-Server die Verbindungsanforderung dieses Hosts, bis der MySQL-Server neu gestartet wird oder die relevanten Informationen dieses Hosts über den Befehl „Flush Hosts“ gelöscht werden.

max_connect_errors = 1000

5. Die Anzahl der von allen Threads geöffneten Tabellen

Open_files_limit = 10240

6. Die maximale Datengröße, die von jeder Verbindung übertragen wird, beträgt 1 G, was ein Vielfaches von 1024 sein muss. Sie wird im Allgemeinen auf den Wert des größten BLOB eingestellt.

max_allowed_packet = 32M

7. Maximale Verbindungszeit für Anfragen

Wartezeitüberschreitung = 10

8. Der Sortierpuffer wird verwendet, um die Sortierung durch ORDER BY- und GROUP BY-Warteschlangen zu handhaben

Sortierpuffergröße = 16 M

9. Minimale Puffergröße für vollständigen Tabellenscan ohne Index

Join-Puffergröße = 16 M

10. Abfragepuffergröße

Abfrage-Cachegröße = 128 M

11. Geben Sie die Puffergröße an, die von einer einzelnen Abfrage verwendet werden kann. Der Standardwert ist 1M

query_cache_limit = 4 M

12. Legen Sie die Standardtransaktionsisolationsstufe fest

transaction_isolation = WIEDERHOLBARES LESEN

13. Die vom Thread verwendete Heap-Größe. Dieser Wert begrenzt die rekursive Tiefe der gespeicherten Prozedur und die Komplexität der SQL-Anweisung, die im Speicher verarbeitet werden kann. Diese Speichermenge wird für jede Verbindung reserviert.

Thread-Stapel = 512 KB

14. Binäres Logging aktivieren

log_bin

15. Binäres Protokollformat: zeilenbasiert

binlog_format = Zeile

16. InnoDB verwendet einen Pufferpool zum Speichern von Indizes und Rohdaten. Sie können diese Variable auf 80 % der physischen Speichergröße des Servers einstellen.

innodb_buffer_pool_size = 6G

17. Die Anzahl der IO-Threads, die zum Synchronisieren von IO-Operationen verwendet werden

innodb_file_io_threads = 4

18. Die empfohlene Einstellung für die Anzahl der im Innodb-Kern zulässigen Threads beträgt die doppelte Anzahl der CPUs plus die Anzahl der Festplatten.

innodb_thread_concurrency = 16

19. Die Größe des Puffers, der zum Puffern von Protokolldaten verwendet wird

innodb_log_buffer_size = 16 M

20. Die Größe jeder Protokolldatei in der Protokollgruppe

innodb_log_file_size = 512 M

21. Gesamtzahl der Dateien in der Protokollgruppe

innodb_log_files_in_group = 3

22. Die Zeit, die eine InnoDB-Transaktion auf eine InnoDB-Zeilensperre wartet, bevor eine SQL-Anweisung zurückgesetzt wird

innodb_lock_wait_timeout = 120

23. Die Schwellendauer langsamer Abfragedatensätze beträgt standardmäßig 10 Sekunden

lange_Abfragezeit = 2

24. Datensatzabfragen, die keine Indizes verwenden

Protokollabfragen ohne Verwendung von Indizes

my.cnf Beispiel:

[mysqld]
Datenverzeichnis=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolische Links = 0
innodb_file_per_table = 1
innodb_buffer_pool_size = 6442450944 #Wenn der Speicher nicht ausreicht, wird ein Fehler gemeldet innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16 M
innodb_log_file_size = 512 M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
log_bin = /var/lib/mysql/mariadb-bin
binlog_format = Zeile
langsames_Abfrageprotokoll
lange_Abfragezeit = 2
Protokollabfragen ohne Verwendung von Indizes
transaction_isolation = WIEDERHOLBARES LESEN
Abfrage-Cachegröße = 128 M
query_cache_limit = 4 M
max_verbindungen = 8000
back_log = 300
max_connect_errors = 1000
Open_files_limit = 10240
max_allowed_packet = 32M
Wartezeitüberschreitung = 10
Sortierpuffergröße = 16 M
Join-Puffergröße = 16 M
Thread-Stapel = 512 KB

2. MySQL-Leistungsstresstest

Gängige Testtools:

• mysql.lap
• Sysbench
• tpcc-mysql
• MySQL Benchmark Suite
• MySQL Super-Smack
• MeineBank

Einführung in das MySQLSlap-Tool

​mysqlslap stammt aus dem MariaDB-Paket. Beim Testen wird standardmäßig ein mysqlslap-Schema generiert, eine Testtabelle t1 erstellt, Testdaten abgefragt und eingefügt und die mysqlslap-Bibliothek automatisch generiert. Wenn sie bereits vorhanden ist, wird sie zuerst gelöscht. Verwenden Sie --only-print, um den eigentlichen Testvorgang auszudrucken. Nach Abschluss des gesamten Tests bleibt keine Spur in der Datenbank zurück.

Allgemeine Optionen:

• --auto-generate-sql, -a Testtabellen und -daten automatisch generieren, was bedeutet, dass das vom mysqlslap-Tool generierte SQL-Skript zum Testen des gleichzeitigen Drucks verwendet wird
• --auto-generate-sql-load-type=Typ der Testanweisung. Gibt an, ob es sich bei der zu testenden Umgebung um einen Lesevorgang, einen Schreibvorgang oder eine Mischung aus beiden handelt. Zu den Werten zählen: Lesen, Schlüssel, Schreiben, Aktualisieren und Gemischt (Standard)
• --auto-generate-sql-add-auto-increment bedeutet, dass den generierten Tabellen automatisch auto_increment-Spalten hinzugefügt werden. Diese Funktion wird seit Version 5.1.18 unterstützt.
• --number-char-cols=N, -x N Die Anzahl der Zeichenspalten in der automatisch generierten Testtabelle, Standard 1
• --number-int-cols=N, -y N Die Anzahl der numerischen Spalten in der automatisch generierten Testtabelle, Standard 1
• --number-of-queries=N Gesamtzahl der Testabfragen (Anzahl gleichzeitiger Clients × Anzahl der Abfragen pro Client)
• --query=name,-q Verwenden Sie ein benutzerdefiniertes Skript, um den Test auszuführen. Sie können beispielsweise eine benutzerdefinierte gespeicherte Prozedur oder eine SQL-Anweisung aufrufen, um den Test auszuführen.
• --create-schema stellt den Namen der benutzerdefinierten Testbibliothek dar, das Testschema, und das Schema in MySQL ist die Datenbank
• --commint=N Commit nach wie vielen DMLs
• --compress, -C Komprimiert Nachrichten, wenn sowohl Server als auch Client die Komprimierung unterstützen
• --concurrency = N, -c N gibt die Parallelität an, d. h. wie viele Clients gleichzeitig die Ausführung von select simulieren; es können mehrere Werte angegeben werden, wobei Kommas oder die durch den Parameter --delimiter angegebenen Werte als Trennzeichen dienen.
• --engine=Enginename, -e Enginename stellt die zu testende Engine dar. Es können mehrere Engines vorhanden sein, die durch Trennzeichen getrennt sind.
• --iterations=N, -i N Die Anzahl der Iterationen der Testausführung, die angibt, wie oft der Test in verschiedenen gleichzeitigen Umgebungen ausgeführt werden soll.
• --only-print Druckt nur Testanweisungen, ohne sie tatsächlich auszuführen
• --detach=N Trennen und erneutes Verbinden nach der Ausführung von N Anweisungen
• --debug-info, -T Druckt Speicher- und CPU-bezogene Informationen

Testbeispiel:

1) Single-Thread-Test

[root@centos7 ~]# mysqlslap -a -uroot -p
Passwort eingeben: 
Vergleichsindex
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 0,004 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 0,004 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 0,004 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 1
        Durchschnittliche Anzahl der Anfragen pro Kunde: 0

2) Multithread-Tests, verwenden Sie –concurrency, um gleichzeitige Verbindungen zu simulieren

[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Passwort eingeben: 
Vergleichsindex
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 3,384 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 3,384 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 3,384 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 500
        Durchschnittliche Anzahl der Anfragen pro Kunde: 0

3) Testen Sie zum Vergleich gleichzeitig die Leistung verschiedener Speicher-Engines

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Passwort eingeben: 
Vergleichsindex
        Läuft für den Motor Myisam
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 0,192 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 0,187 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 0,202 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 500
        Durchschnittliche Anzahl der Anfragen pro Kunde: 2

Vergleichsindex
        Wird für die Engine InnoDB ausgeführt
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 0,355 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 0,350 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 0,364 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 500
        Durchschnittliche Anzahl der Anfragen pro Kunde: 2


Benutzerzeit 0,33, Systemzeit 0,58
Maximale Resident Set-Größe 22892, Integrale Resident Set-Größe 0
Nicht-physische Seitenfehler 46012, Physische Seitenfehler 0, Swaps 0
Blöcke ein 0 aus 0, Nachrichten ein 0 aus 0, Signale 0
Freiwillige Kontextwechsel 31896, Unfreiwillige Kontextwechsel 0

4) Führen Sie einen Test mit 500 und 1000 gleichzeitigen Abfragen und insgesamt 5000 Abfragen durch

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --Anzahl der Abfragen 5000 --debug-info
Passwort eingeben: 
Vergleichsindex
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 3,378 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 3,378 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 3,378 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 500
        Durchschnittliche Anzahl der Anfragen pro Kunde: 10

Vergleichsindex
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 3,101 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 3,101 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 3,101 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 1000
        Durchschnittliche Anzahl der Anfragen pro Kunde: 5


Benutzerzeit 0,84, Systemzeit 0,64
Maximale Resident Set-Größe 83068, Integrale Resident Set-Größe 0
Nicht-physische Seitenfehler 139977, Physische Seitenfehler 0, Swaps 0
Blöcke ein 0 aus 0, Nachrichten ein 0 aus 0, Signale 0
Freiwillige Kontextwechsel 31524, Unfreiwillige Kontextwechsel 3

5) Iteratives Testen

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Passwort eingeben: 
Vergleichsindex
        Durchschnittliche Anzahl von Sekunden zum Ausführen aller Abfragen: 3,307 Sekunden
        Mindestanzahl von Sekunden zum Ausführen aller Abfragen: 3,184 Sekunden
        Maximale Anzahl von Sekunden zum Ausführen aller Abfragen: 3,421 Sekunden
        Anzahl der Clients, die Abfragen ausführen: 500
        Durchschnittliche Anzahl der Anfragen pro Kunde: 10


Benutzerzeit 2,18, Systemzeit 1,58
Maximale Resident Set-Größe 74872, Integrale Resident Set-Größe 0
Nicht-physische Seitenfehler 327732, Physische Seitenfehler 0, Swaps 0
Blöcke ein 0 aus 0, Nachrichten ein 0 aus 0, Signale 0
Freiwillige Kontextwechsel 73904, Unfreiwillige Kontextwechsel 3

Oben finden Sie den detaillierten Inhalt der MySQL-Reihe 15: Allgemeine MySQL-Konfiguration und Leistungsstresstests. Weitere Informationen zu allgemeinen MySQL-Konfigurationen und Leistungsstresstests finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!