Zusammenfassung mehrerer wichtiger Methoden zur Berechnung und Optimierung des Leistungsindex für MySQL

1 QPS-Berechnung (Anzahl der Abfragen pro Sekunde)

Für Datenbanken basierend auf der MyISAM-Engine

MySQL> zeigt globalen Status wie „Fragen“ an;
+---------------+------------+
| Variablenname | Wert |
+---------------+------------+
| Fragen | 2009191409 |
+---------------+------------+
1 Zeile im Satz (0,00 Sek.)

mysql> globalen Status wie „Uptime“ anzeigen;
+---------------+--------+
| Variablenname | Wert |
+---------------+--------+
| Betriebszeit | 388402 |
+---------------+--------+
1 Zeile im Satz (0,00 Sek.)

QPS = Fragen/Betriebszeit = 5172, die durchschnittliche QPS von MySQL seit dem Start. Wenn Sie die QPS innerhalb eines bestimmten Zeitraums berechnen möchten, können Sie die Intervallzeit t2-t1 während der Spitzenzeit ermitteln und dann den Q-Wert bei t2 bzw. t1 berechnen, QPS = (q2-q1)/(t2-t1).

Für DBs basierend auf der InnnoDB-Engine

mysql> globalen Status wie „com_update“ anzeigen;
+---------------+----------+
| Variablenname | Wert |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> globalen Status wie „com_select“ anzeigen;
+---------------+------------+
| Variablenname | Wert |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 Zeile im Satz (0,00 Sek.)


mysql> globalen Status wie „com_delete“ anzeigen;
+---------------+--------+
| Variablenname | Wert |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 Zeile im Satz (0,00 Sek.)

mysql> globalen Status wie „Uptime“ anzeigen;

+---------------+--------+
| Variablenname | Wert |
+---------------+--------+
| Betriebszeit | 388816 |
+---------------+--------+
1 Zeile im Satz (0,00 Sek.)

QPS = (com_update + com_insert + com_delete + com_select) / uptime = 3076. Die QPS-Abfragemethode innerhalb eines bestimmten Zeitraums ist dieselbe wie oben.

2 TPS-Berechnung (Transaktionen pro Sekunde)

mysql> globalen Status wie „com_commit“ anzeigen;

+---------------+---------+
| Variablenname | Wert |
+---------------+---------+
| Com_commit | 7424815 |
+---------------+---------+
1 Zeile im Satz (0,00 Sek.)

mysql> globalen Status wie „com_rollback“ anzeigen;
+---------------+---------+
| Variablenname | Wert |
+---------------+---------+
| Com_rollback | 1073179 |
+---------------+---------+
1 Zeile im Satz (0,00 Sek.)

mysql> globalen Status wie „Uptime“ anzeigen;
+---------------+--------+
| Variablenname | Wert |
+---------------+--------+
| Betriebszeit | 389467 |
+---------------+--------+
1 Zeile im Satz (0,00 Sek.)

TPS = (com_commit + com_rollback) / Betriebszeit = 22

3 Threadverbindungen und Trefferquote

mysql> globalen Status wie „threads_%“ anzeigen;
+---------------------+------+
| Variablenname | Wert |
+---------------------+------+
| Threads_cached | 480 | //Stellt dar, wie viele inaktive Threads sich derzeit im Thread-Cache befinden| Threads_connected | 153 | //Stellt die Anzahl der aktuell hergestellten Verbindungen dar. Da eine Verbindung einen Thread erfordert, kann dies auch als die Anzahl der aktuell verwendeten Threads angesehen werden| Threads_created | 20344 | //Stellt die Anzahl der seit dem letzten Dienststart erstellten Threads dar| Threads_running | 2 | //Stellt die Anzahl der aktuell aktiven (nicht schlafenden) Threads dar+-------------------+-------+
4 Zeilen im Satz (0,00 Sek.)

mysql> globalen Status wie „Verbindungen“ anzeigen;
+---------------+-----------+
| Variablenname | Wert |
+---------------+-----------+
| Verbindungen | 381487397 |
+---------------+-----------+
1 Zeile im Satz (0,00 Sek.)

Thread-Cache-Trefferquote = 1-Erstellte Threads/Verbindungen = 99,994 %

Wir legen die Anzahl der Thread-Caches festmysql> zeigt Variablen wie „%thread_cache_size%“ an;
+---------------------+------+
| Variablenname | Wert |
+---------------------+------+
| Thread-Cache-Größe | 500 |
+---------------------+------+
1 Zeile im Satz (0,00 Sek.)

Anhand von Threads_connected können Sie abschätzen, wie groß der Wert für thread_cache_size sein sollte. Im Allgemeinen ist 250 eine gute Obergrenze. Wenn der Speicher groß genug ist, können Sie den Wert für thread_cache_size auch auf den gleichen Wert wie thread_connected setzen.

Oder indem Sie den Wert „threads_created“ beobachten. Wenn der Wert groß ist oder weiter wächst, können Sie den Wert „thread_cache_size“ entsprechend erhöhen. Im Ruhezustand belegt jeder Thread etwa 256 KB Speicher. Wenn also ausreichend Speicher vorhanden ist, wird durch eine zu kleine Einstellung nicht viel Speicher gespart, es sei denn, der Wert übersteigt mehrere Tausend.

4 Tabellen-Cache

mysql> globalen Status wie „open_tables%“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Offene_Tische | 2228 |
+---------------+-------+
1 Zeile im Satz (0,00 Sek.)

Wir richten den Cache für offene Tabellen und den Cache für Tabellendefinitionen ein

mysql> Variablen wie „table_open_cache“ anzeigen;
+------------------+--------+
| Variablenname | Wert |
+------------------+--------+
| Tabelle_öffnen_Cache | 16384 |
+------------------+--------+
1 Zeile im Satz (0,00 Sek.)

mysql> Variablen wie „table_defi%“ anzeigen;
+------------------------+----------+
| Variablenname | Wert |
+------------------------+----------+
| Tabellendefinitionscache | 2000 |
+------------------------+----------+
1 Zeile im Satz (0,00 Sek.)

Für MyISAM:

Jedes Mal, wenn MySQL eine Tabelle öffnet, liest es einige Daten in den Cache table_open_cache. Wenn MySQL die entsprechenden Informationen in diesem Cache nicht finden kann, liest es sie direkt von der Festplatte. Daher sollte dieser Wert groß genug eingestellt werden, um ein erneutes Öffnen und erneutes Parsen der Tabellendefinition zu vermeiden. Normalerweise wird er auf das 10-fache von max_connections eingestellt, aber es ist am besten, ihn unter 10000 zu halten.

Eine weitere Grundlage besteht darin, es entsprechend dem Wert des Status open_tables festzulegen. Wenn Sie feststellen, dass sich der Wert von open_tables jede Sekunde stark ändert, müssen Sie möglicherweise den Wert von table_open_cache erhöhen.

table_definition_cache wird normalerweise einfach auf die Anzahl der im Server vorhandenen Tabellen eingestellt, es sei denn, es gibt Zehntausende von Tabellen.

Für InnoDB:

Anders als bei MyISAM besteht in InnoDB keine direkte Verbindung zwischen geöffneter Tabelle und geöffneter Datei, d. h. wenn die frm-Tabelle geöffnet wird, wird die entsprechende ibd-Datei möglicherweise geschlossen.

Daher verwendet InnoDB nur table_definiton_cache und nicht table_open_cache.

Die frm-Datei wird in table_definition_cache gespeichert, während idb durch innodb_open_files bestimmt wird (vorausgesetzt innodb_file_per_table ist aktiviert). Am besten ist es, innodb_open_files groß genug einzustellen, damit der Server alle .ibd-Dateien gleichzeitig geöffnet halten kann.

5 Maximale Anzahl Verbindungen

mysql> globalen Status wie „Max_used_connections“ anzeigen;
+----------------------+----------+
| Variablenname | Wert |
+----------------------+----------+
| Max_genutzte_Verbindungen | 1785 |
+----------------------+----------+
1 Zeile im Satz (0,00 Sek.)

Wir setzen die max_connections Größe

mysql> Variablen wie „max_connections%“ anzeigen;
+-----------------+--------+
| Variablenname | Wert |
+-----------------+--------+
| max_Verbindungen | 4000 |
+-----------------+--------+
1 Zeile im Satz (0,00 Sek.)

Normalerweise sollte die Größe von max_connections größer als der Statuswert von max_used_connections eingestellt werden. Der Statuswert von max_used_connections gibt an, ob die Serververbindung in einem bestimmten Zeitraum einen Spitzenwert aufweist. Wenn dieser Wert größer als der Wert von max_connections ist, bedeutet dies, dass der Client mindestens einmal abgelehnt wurde. Er kann einfach so eingestellt werden, dass er die folgenden Bedingungen erfüllt: max_used_connections/max_connections=0,8

6 Innodb Cache Trefferquote

mysql> globalen Status wie „innodb_buffer_pool_read%“ anzeigen;
+------------------------------------------+------------------+
| Variablenname | Wert |
+------------------------------------------+------------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 268720 | //Anzahl der vorab gelesenen Seiten | Innodb_buffer_pool_read_ahead_evicted | 0 |   
| Innodb_buffer_pool_read_requests | 480291074970 | //Anzahl der Lesevorgänge aus dem Pufferpool | Innodb_buffer_pool_reads | 29912739 | //Gibt die Anzahl der von der physischen Festplatte gelesenen Seiten an+---------------------------------------+--------------+
5 Zeilen im Satz (0,00 Sek.)

Pufferpool-Trefferquote = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads) = 99,994 %

Wenn dieser Wert unter 99,9 % liegt, wird empfohlen, den Wert von innodb_buffer_pool_size zu erhöhen. Dieser Wert wird im Allgemeinen auf 75 % bis 85 % der Gesamtspeichergröße eingestellt, oder Sie berechnen den vom Betriebssystem benötigten Cache + den für jede MySQL-Verbindung benötigten Speicher (z. B. Sortierpuffer und temporäre Tabellen) + MyISAM-Schlüsselcache und geben den verbleibenden Speicher an innodb_buffer_pool_size weiter. Er sollte jedoch nicht zu groß eingestellt werden, da dies zu häufigem Speicheraustausch, langen Aufwärm- und Herunterfahrzeiten und anderen Problemen führt.

7 MyISAM-Schlüsselpuffer-Trefferquote und Puffernutzung

mysql> globalen Status wie „key_%“ anzeigen;
+------------------------+-----------+
| Variablenname | Wert |
+------------------------+-----------+
| Schlüsselblöcke nicht geleert | 0 |
| Key_blocks_unbenutzt | 106662 |
| Verwendete Schlüsselblöcke | 107171 |
| Schlüsselleseanforderungen | 883825678 |
| Schlüssellesungen | 133294 |
| Schlüsselschreibanforderungen | 217310758 |
| Schlüssel_schreibvorgänge | 2061054 |
+------------------------+-----------+
7 Zeilen im Satz (0,00 Sek.)

mysql> Variablen wie „%key_cache_block_size%“ anzeigen;
+----------------------+----------+
| Variablenname | Wert |
+----------------------+----------+
| Schlüsselcacheblockgröße | 1024 |
+----------------------+----------+
1 Zeile im Satz (0,00 Sek.)


mysql> Variablen wie „%key_buffer_size%“ anzeigen;
+-----------------+------------+
| Variablenname | Wert |
+-----------------+------------+
| Schlüsselpuffergröße | 134217728 |
+-----------------+------------+
1 Zeile im Satz (0,00 Sek.)

Puffernutzung = 1-(ungenutzte Schlüsselblöcke*Größe des Schlüsselcacheblocks/Größe des Schlüsselpuffers) = 18,6 %

Lesetrefferrate = 1 Schlüssellesungen / Schlüsselleseanforderungen = 99,98 %

Schreibtrefferrate = 1-Schlüsselschreibvorgänge / Schlüsselschreibanforderungen = 99,05 %

Es ist ersichtlich, dass die Auslastungsrate des Puffers nicht hoch ist. Wenn alle Schlüsselpuffer nach langer Zeit nicht aufgebraucht sind, können Sie eine Reduzierung der Puffergröße in Betracht ziehen.

Die Trefferquote für den Schlüsselcache ist möglicherweise nicht sehr aussagekräftig, da sie anwendungsabhängig ist. Einige Anwendungen funktionieren gut mit einer Trefferquote von 95 %, während andere 99,99 % benötigen. Erfahrungsgemäß ist also die Anzahl der Cachefehler pro Sekunde wichtiger. Angenommen, eine eigenständige Festplatte kann 100 zufällige Lesevorgänge pro Sekunde ausführen, dann verursachen 5 Cachefehler pro Sekunde möglicherweise keine I/O-Auslastung, 80 pro Sekunde können jedoch Probleme verursachen.

Cachefehler pro Sekunde = Key_reads/Betriebszeit = 0,33

8 Temporäre Tabellennutzung

mysql> globalen Status wie „Created_tmp%“ anzeigen;
+-------------------------+----------+
| Variablenname | Wert |
+-------------------------+----------+
| Temporäre Datenträgertabellen erstellt | 19226325 |
| Erstellte temporäre Dateien | 117 |
| Temporäre Tabellen erstellt | 56265812 |
+-------------------------+----------+
3 Zeilen im Satz (0,00 Sek.)

mysql> Variablen wie „%tmp_table_size%“ anzeigen;
+-----------------+----------+
| Variablenname | Wert |
+-----------------+----------+
| tmp_tabellengröße | 67108864 |
+-----------------+----------+
1 Zeile im Satz (0,00 Sek.)

Es ist ersichtlich, dass insgesamt 56.265.812 temporäre Tabellen erstellt wurden, von denen 19.226.325 Festplatten-E/A betrafen, was etwa 0,34 entspricht. Dies zeigt, dass die Datenmenge, die mit Sortier- und Verknüpfungsanweisungen in Datenbankanwendungen verbunden ist, zu groß ist und SQL optimiert oder der Wert von tmp_table_size erhöht werden muss. Ich habe ihn auf 64 M eingestellt. Das Verhältnis sollte innerhalb von 0,2 kontrolliert werden.

9 Binlog-Cache-Nutzung

mysql> Status wie „Binlog_cache%“ anzeigen; 
+----------------------+----------+
| Variablenname | Wert |
+----------------------+----------+
| Binlog_cache_disk_use | 15 |
| Binlog_cache_use | 95978256 |
+----------------------+----------+
2 Zeilen im Satz (0,00 Sek.)

mysql> Variablen wie „binlog_cache_size“ anzeigen;
+---------------------+---------+
| Variablenname | Wert |
+---------------------+---------+
| binlog_cache_size | 1048576 |
+---------------------+---------+
1 Zeile im Satz (0,00 Sek.)

Binlog_cache_disk_use gibt an, wie oft temporäre Dateien zum Zwischenspeichern von Binärprotokollen aufgrund von unzureichendem Speicher verwendet werden, der durch unsere binlog_cache_size vorgegeben ist.

Binlog_cache_use gibt an, wie oft binlog_cache_size zum Zwischenspeichern verwendet wird

Wenn der entsprechende Wert von Binlog_cache_disk_use relativ groß ist, können wir in Betracht ziehen, den entsprechenden Wert von binlog_cache_size entsprechend zu erhöhen

10 Einstellung der Innodb-Protokollpuffergröße

mysql> Variablen wie „%innodb_log_buffer_size%“ anzeigen;
+------------------------+---------+
| Variablenname | Wert |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 Zeile im Satz (0,00 Sek.)
mysql> Status wie „innodb_log_waits“ anzeigen;
+------------------+--------+
| Variablenname | Wert |
+------------------+--------+
| Innodb_log_waits | 0 |
+------------------+--------+
1 Zeile im Satz (0,00 Sek.)

Ich habe innodb_log_buffer_size auf 8M gesetzt, was groß genug sein sollte; Innodb_log_waits gibt die Anzahl der Wartezeiten aufgrund unzureichenden Protokollpuffers an. Wenn dieser Wert nicht 0 ist, können Sie den Wert von innodb_log_buffer_size entsprechend erhöhen.

11. Beurteilung des Tabellenscan-Status

mysql> globalen Status wie „Handler_read%“ anzeigen;
+----------------------+--------------+
| Variablenname | Wert |
+----------------------+--------------+
| Handler_read_first | 19180695 |
| Handler_read_key | 30303690598 |
| Zuletzt gelesener Handler | 290721 |
| Handler_read_next | 51169834260 |
| Handler_read_prev | 1267528402 |
| Handler_read_rnd | 219230406 |
| Handler_read_rnd_next | 344713226172 |
+----------------------+--------------+
7 Zeilen im Satz (0,00 Sek.)

Handler_read_first: Die Anzahl der Indexscans. Der Wert dieses Werts kann nicht sagen, ob die Systemleistung gut oder schlecht ist.

Handler_read_key: Die Anzahl der Abfragen über den Schlüssel. Je größer der Wert, desto besser die Systemleistung.

Handler_read_next: Die Anzahl der Sortierungen anhand des Indexes
Handler_read_prev: Diese Option gibt an, wie oft Daten aus der Datendatei in umgekehrter Reihenfolge des Index abgerufen werden sollen, wenn ein Indexscan durchgeführt wird, normalerweise ORDER BY ... DESC

Handler_read_rnd: Je größer der Wert, desto wahrscheinlicher ist es, dass es im System eine große Anzahl von Operationen gibt, die keine Indizes zum Sortieren verwenden, oder dass bei Joins keine Indizes verwendet werden.

Handler_read_rnd_next: Die Anzahl der Scans der Datendatei. Je größer der Wert, desto wahrscheinlicher ist es, dass viele vollständige Tabellenscans durchgeführt werden oder dass der Index nicht ordnungsgemäß erstellt wird und der erstellte Index nicht gut verwendet wird.

12 Innodb_buffer_pool_wait_free

mysql> globalen Status wie „Innodb_buffer_pool_wait_free“ anzeigen;
+------------------------------+----------+
| Variablenname | Wert |
+------------------------------+----------+
| Innodb_buffer_pool_wait_free | 0 |
+------------------------------+----------+
1 Zeile im Satz (0,00 Sek.)

Wenn der Wert ungleich 0 ist, bedeutet dies, dass im Pufferpool kein freier Speicherplatz vorhanden ist. Der mögliche Grund ist, dass die innodb_buffer_pool_size zu groß eingestellt ist. Sie können diesen Wert entsprechend reduzieren.

13 Informationen zum Join-Vorgang

mysql> globalen Status wie „select_full_join“ anzeigen;
+------------------+--------+
| Variablenname | Wert |
+------------------+--------+
| Vollständige_Verbindung auswählen | 10403 |
+------------------+--------+
1 Zeile im Satz (0,00 Sek.)

Dieser Wert gibt an, wie oft der Index im Join-Vorgang nicht verwendet wurde. Ein hoher Wert weist darauf hin, dass beim Schreiben der Join-Anweisung Probleme aufgetreten sind.

mysql> globalen Status wie „select_range“ anzeigen;
+---------------+----------+
| Variablenname | Wert |
+---------------+----------+
| Bereich auswählen | 22450380 |
+---------------+----------+
1 Zeile im Satz (0,00 Sek.)

Dieser Wert gibt die Anzahl der Verknüpfungen mit Bereichen in der ersten Tabelle an. Ein großer Wert zeigt an, dass die Verknüpfung richtig geschrieben wurde. Sie können normalerweise das Verhältnis von select_full_join zu select_range überprüfen, um die Leistung von Verknüpfungsanweisungen im System zu bestimmen.

mysql> globalen Status wie „Select_range_check“ anzeigen;
+--------------------+----------+
| Variablenname | Wert |
+--------------------+----------+
| Bereichsprüfung auswählen | 0 |
+--------------------+----------+
1 Zeile im Satz (0,00 Sek.)

Wenn der Wert nicht 0 ist, müssen Sie prüfen, ob der Index der Tabelle sinnvoll ist. Das bedeutet, dass Sie den Index jeder Zeile in Tabelle n in Tabelle n+1 neu auswerten müssen, um zu sehen, ob der Aufwand minimal ist. Die Anzahl der vorgenommenen Verknüpfungen bedeutet, dass Tabelle n+1 keinen sinnvollen Index für diese Verknüpfung hat.

mysql> zeigt globalen Status wie „select_scan“ an;
+---------------+-----------+
| Variablenname | Wert |
+---------------+-----------+
| Scan auswählen | 116037811 |
+---------------+-----------+
1 Zeile im Satz (0,00 Sek.)

select_scan gibt die Anzahl der Verbindungen zum Scannen der ersten Tabelle an. Dies ist in Ordnung, wenn jede Zeile der ersten Tabelle in den Join einbezogen ist. Wenn Sie nicht alle Zeilen zurückgeben möchten, aber keinen Index verwenden, um die benötigten Zeilen zu finden, ist eine hohe Anzahl schlecht.

14 Langsame Abfrage

mysql> globalen Status wie „Slow_queries“ anzeigen;
+---------------+--------+
| Variablenname | Wert |
+---------------+--------+
| Langsame Abfragen | 114111 |
+---------------+--------+
1 Zeile im Satz (0,00 Sek.)

Dieser Wert gibt die Anzahl der langsamen Abfragen seit dem Start von MySQL an, also die Häufigkeit, mit der die Ausführungszeit die long_query_time überschreitet. Anhand des Verhältnisses Slow_queries/uptime lässt sich die Anzahl der langsamen Abfragen pro Zeiteinheit ermitteln und damit die Leistung des Systems bestimmen.

15 Informationen zur Tabellensperre

mysql> globalen Status wie „table_lock%“ anzeigen;
+----------------------+------------+
| Variablenname | Wert |
+----------------------+------------+
| Table_locks_immediate | 1644917567 |
| Auf Tabellensperren gewartet | 53 |
+----------------------+------------+
2 Zeilen im Satz (0,00 Sek.)

Das Verhältnis dieser beiden Werte: Table_locks_waited / Table_locks_immediate tendiert gegen 0. Wenn der Wert größer ist, bedeutet dies, dass das System ernsthaft blockiert ist.

Die obige Zusammenfassung mehrerer wichtiger Berechnungen und Optimierungsmethoden für MySQL-Leistungsindikatoren ist der gesamte Inhalt, den der Herausgeber mit Ihnen teilt. Ich hoffe, er kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.