Vorgehensweise bei der Verwendung der MySQL-Systemdatenbank zur Diagnose der Leistungsbelastung

Ein Meister sagte einmal, man müsse die Datenbank, die man verwaltet, so gut kennen wie seine Frau. Ich persönlich denke, dass dazu zwei Aspekte des Verständnisses gehören:

1. In Bezug auf die Stabilität wird mehr Wert auf Maßnahmen auf hoher Ebene wie hohe Verfügbarkeit, Trennung von Lesen und Schreiben, Lastausgleich, Notfallwiederherstellungsmanagement usw. gelegt (genau wie bei der Gewährleistung der Stabilität des Lebens).

2. Auf Instanzebene müssen Sie auf Speicher, IO, Netzwerk, Hot Tables, Hot Indexes, Top SQL, Deadlocks, Blockierungen und SQL mit historischer abnormaler Ausführung (wie Details zur Lebensqualität) achten. Die Performance_Data-Bibliothek und die Sys-Bibliothek von MySQL bieten sehr umfangreiche Systemprotokolldaten, die uns helfen können, die Details besser zu verstehen. Hier ist eine kurze Liste einiger häufig verwendeter Daten.

Die Sys-Bibliothek kapselt einige Tabellen in performance_data auf eine besser lesbare Weise, sodass die Quelle dieser Daten weiterhin die Daten in der Performance-Data-Bibliothek sind.

Hier ist eine grobe Liste einiger häufig verwendeter Systemdaten, die Ihnen helfen können, die Ressourcenzuweisung während des MySQL-Betriebs auf Instanzebene klarer zu verstehen.

Informationen im Status

Die MySQL-Statusvariable liefert nur allgemeine Informationen. Sie können aus der Statusvariable nicht den detaillierten Ressourcenverbrauch ableiten, z. B. wo sich die IO- oder Speicher-Hotspots befinden oder wo sich die Datenbank- und Tabellen-Hotspots befinden. Wenn Sie die spezifischen Detailinformationen wissen möchten, benötigen Sie die Daten in der Systembibliothek.

Voraussetzung ist die Aktivierung performance_schema , da die Ansichten der Sys-Bibliothek auf der Bibliothek performance_schema basieren.

Speichernutzung:

Speicher-/innodb_buffer_pool-Nutzung

Zusammenfassung der innodb_buffer_pool-Nutzung: Es ist bekannt, dass die aktuelle Instanz über einen Pufferpool von 262144*16/1024 = 4096 MB verfügt und 23260*16/1024 363 MB verwendet wurden

Detaillierte Informationen zum von innodb_buffer_pool belegten Speicher, der anhand der Bibliotheks-/Tabellendimensionen gezählt werden kann

FESTLEGEN DER SITZUNGSTRANSAKTIONSISOLATIONSSTUFE: LESEN UNCOMMITTED;
WÄHLEN 
  Datenbankname,
  SUMME(komprimierte Größe)/1024/1024 AS zugewiesener Speicher,
  SUMME(Datengröße)/1024/1024 AS Datenspeicher,
  SUMME(is_hashed)*16/1024 AS is_hashed_memory,
  SUMME(ist_alt)*16/1024 AS ist_alter_Speicher
AUS 
(
 WÄHLEN 
  Fall wenn INSTR(TABLE_NAME,'.')>0 dann ersetzen(SUBSTRING(TABLE_NAME,1,INSTR(TABLE_NAME,'.')-1),'`','')
  sonst endet 'system_database' als database_name,
  Fall wenn INSTR(TABLE_NAME,'.')>0 dann ersetze(SUBSTRING(TABLE_NAME,INSTR(TABLE_NAME,'.')+1),'`','')
  ELSE 'system_obj' END AS Tabellenname,
  if(komprimierte_Größe = 0, 16384, komprimierte_Größe) AS komprimierte_Größe,
  Datengröße,
  wenn(is_hashed = 'JA',1,0) is_hashed,
  if(ist_alt = 'JA',1,0) ist_alt
 VON information_schema.innodb_buffer_page
 Wobei TABLE_NAME NICHT NULL ist
)
GROUP BY Datenbankname
ORDER BY zugewiesener_Speicher DESC
GRENZE 10;

Lese- und Schreibstatistiken für Datenbanken und Tabellen, Hot-Data-Statistiken auf logischer Ebene

Die Zieltabelle ist performance_schema.table_io_waits_summary_by_table . Einige Artikel sagen, es handele sich um logische IO. Tatsächlich hat es nichts mit logischer IO zu tun. Die Bedeutung der Felder in dieser Tabelle basiert auf den Statistiken der Anzahl der in die Tabelle gelesenen und geschriebenen Zeilen. Was die tatsächlichen Statistiken auf logischer E/A-Ebene betrifft, weiß ich derzeit nicht, welche verfügbare Systemtabelle ich abfragen soll. Mit dieser Bibliothek lässt sich anschaulich darstellen, wie die statistischen Ergebnisse in dieser Tabelle berechnet werden.

Dies ist ein kumulativer Wert basierend auf der Anzahl der in der Tabelle gelesenen und geschriebenen Zeilen. Ich persönlich denke, dass es nicht sehr aussagekräftig ist, nur diesen Wert zu betrachten. Es ist notwendig, die Differenz regelmäßig zu erfassen und zu berechnen, um sie als Referenz aussagekräftig zu machen.

Die folgende Tabelle zeigt den Lese- und Schreibstatus auf Bibliotheksebene.

Lese- und Schreibstatistiken von Bibliotheken und Tabellen sowie Hot-Data-Statistiken auf physischer IO-Ebene

Hotspot-Daten werden basierend auf der physischen IO-Dimension gezählt, um zu bestimmen, welche Bibliotheken und Tabellen wie viel physische IO verbrauchen. Die Daten in der ursprünglichen Systemtabelle stellen hier einen kumulierten statistischen Wert dar. Der extremste Fall ist, dass eine Tabelle 0 Zeilen hat, aber eine große Anzahl physischer Lese- und Schreib-IOs vorhanden ist.

FESTLEGEN DER SITZUNGSTRANSAKTIONSISOLATIONSSTUFE: LESEN UNCOMMITTED;
WÄHLEN 
 Datenbankname,
 IFNULL(cast(Summe(total_read) als signiert),0) AS total_read,
 IFNULL(cast(Summe(geschriebene_Gesamtsumme) als signiert),0) AS geschriebene_Gesamtsumme,
 IFNULL(cast(Summe(Gesamt) AS SIGNED),0) AS total_read_written
AUS
(
 WÄHLEN 
  Teilzeichenfolge (REPLACE(Datei, '@@datadir/', ''),1,instr(REPLACE(Datei, '@@datadir/', ''),'/')-1) AS Datenbankname,
  Anzahl der Lesevorgänge,
  Fall 
   wenn instr(total_read,'KiB')>0 dann ersetze(total_read,'KiB','')/1024
   wenn instr(total_read,'MiB')>0 dann ersetze(total_read,'MiB','')/1024
   wenn instr(total_read,'GiB')>0 dann ersetze(total_read,'GiB','')*1024
  ENDE AS total_read,
  Fall 
   wenn instr(total_written,'KiB')>0 dann replace(total_written,'KiB','')/1024
   wenn instr(total_written,'MiB')>0 dann ersetze(total_written,'MiB','')
   wenn instr(total_written,'GiB')>0 dann ersetze(total_written,'GiB','')*1024
  END AS total_written,
  Fall 
   wenn instr(total,'KiB')>0 dann replace(total,'KiB','')/1024
   wenn instr(total,'MiB')>0 dann ersetze(total,'MiB','')
   wenn instr(total,'GiB')>0 dann replace(total,'GiB','')*1024
  END AS insgesamt
 von sys.io_global_by_file_by_bytes 
 WO DATEI WIE '%@@datadir%' UND instr(REPLACE(Datei, '@@datadir/', ''),'/')>0 
)T
GROUP BY Datenbankname
ORDER BY insgesamt gelesene_geschriebene DESC;

PS: Mir persönlich gefällt MySQLs benutzerdefinierte format_***-Funktion nicht. Die ursprüngliche Absicht dieser Funktion ist gut und sie formatiert einige Daten (Zeit, Speicherplatz usw.) in einen besser lesbaren Modus. Es werden jedoch keine Einheitenparameter unterstützt. Meistens möchten wir es in einer festen Einheit anzeigen. Wenn Sie beispielsweise eine Zeit formatieren, kann sie je nach Größe der Einheit in Mikrosekunden, Millisekunden, Sekunden, Minuten oder Tagen angezeigt werden. Möchte man beispielsweise die Uhrzeit einheitlich in Sekunden formatieren, wird dies leider nicht unterstützt. Manche Daten lassen sich nicht einfach so auf einen Blick darstellen, sondern müssen zur Archivierung und Analyse sogar ausgelesen werden. Daher ist dies nicht zu empfehlen und wird hier auch nicht verwendet.

TOP SQL-Statistiken

Sie können die wichtigsten SQL-Anweisungen nach Ausführungszeit, Blockierungszeit, Anzahl der zurückgegebenen Zeilen usw. zählen.
Darüber hinaus können Sie nach der Zeit der letzten Anzeige filtern und die Top-SQL zählen, die in einem bestimmten Zeitraum kürzlich aufgetreten sind.

FESTLEGEN DER SITZUNGSTRANSAKTIONSISOLATIONSSTUFE: LESEN UNCOMMITTED;

WÄHLEN 
 Schemaname,
 Digest_Text,
 zähle_sterne,
 avg_timer_wait/1000000000000 AS avg_timer_wait,
 max_timer_wait/1000000000000 AS max_timer_wait,
 sum_lock_time/count_star/1000000000000 AS avg_lock_time ,
 Summe der betroffenen Zeilen/Anzahl der Sterne AS Durchschnitt der betroffenen Zeilen,
 sum_rows_sent/count_star AS avg_rows_sent ,
 Summe der untersuchten Zeilen/Anzahl der Sterne AS avg_rows_examined,
 summieren_erstellt_zeitweilige_Diskettentabellen/Anzahl_Sterne AS avg_erstellt_zeitweilige_Diskettentabellen,
 sum_created_tmp_tables/count_star AS avg_create_tmp_tables,
 Summe_Auswahl_vollständiger_Join/Anzahl_Sterne AS Durchschnitt_Auswahl_vollständiger_Join,
 Summe_Auswahl_vollständiger_Bereich_Join/Anzahl_Sterne AS Durchschnitt_Auswahl_vollständiger_Bereich_Join,
 Summe_Auswahlbereich/Anzahl_Stern AS Durchschnitt_Auswahlbereich,
 Summe_Auswahl_Bereich_Check/Anzahl_Stern AS Durchschnitt_Auswahl_Bereich,
 zuerst_gesehen,
 zuletzt gesehen
VON performance_schema.events_statements_summary_by_digest
WO zuletzt gesehen>Datum_Hinzufügen(JETZT(), Intervall -1 STUNDE)
BESTELLEN BIS 
max_timer_wait
--avg_timer_wait
-- Summe der betroffenen Zeilen/Anzahl der Sterne 
-- Summe_Sperrzeit/Anzahl_Sterne
--avg_lock_time
--avg_rows_sent
BESCHREIBUNG
Grenze 10;

Es ist zu beachten, dass diese Statistik auf den von MySQL verbrauchten Ressourcen basiert, um eine Transaktion auszuführen, nicht eine Anweisung. Der Autor war zunächst eine Weile verwirrt. Lassen Sie mich Ihnen ein einfaches Beispiel geben.

Als Referenz ist hier eine gespeicherte Prozedur, die Daten in einer Schleife schreibt. Die aufrufende Methode call create_test_data(N)， , um N Testdaten zu schreiben.
Beispielsweise schreibt call create_test_data(1000000) 1 Million Testdaten. Dieser Ausführungsprozess dauert mehrere Minuten. Laut der Testinstanz des Autors ist die Dimension von avg_timer_wait definitiv ein TOP SQL.

Bei der Abfrage habe ich jedoch nie festgestellt, dass der Aufruf dieser gespeicherten Prozedur als TOP SQL aufgeführt wurde. Später habe ich versucht, eine Transaktion innerhalb der gespeicherten Prozedur hinzuzufügen, und konnte dann erfolgreich das gesamte TOP SQL erfassen.

Daher basieren die Statistiken in performance_schema.events_statements_summary_by_digest auf Transaktionen und nicht auf der Ausführungszeit eines Stapels.

CREATE DEFINER=`root`@`%` VERFAHREN `create_test_data`(
 IN `loopcnt` INT
)
SPRACHE SQL
NICHT DETERMINISTISCH
ENTHÄLT SQL
SQL-SICHERHEITSDEFINIERER
KOMMENTAR ''
BEGINNEN
 -- TRANSAKTION STARTEN; 
  während loopcnt>0 mache
   in test_mrr(rand_id,create_date) Werte einfügen (RAND()*100000000,now(6));
   setze loopcnt=loopcnt-1;
  Ende während;
 -- begehen;
ENDE

Ein weiterer interessanter Punkt ist, dass diese Systemtabelle eine der wenigen ist, die das Abschneiden unterstützt. Natürlich befindet sie sich auch intern in einem kontinuierlichen Sammelprozess.

Statistiken fehlgeschlagener SQL-Ausführungen

Ich dachte immer, dass das System SQL-Anweisungen, deren Ausführung fehlschlug oder die falsch analysiert wurden, nicht aufzeichnen würde. Ich wollte beispielsweise die Anweisungen zählen, deren Ausführung aufgrund eines Timeouts fehlschlug. Später stellte ich fest, dass MySQL diese Informationen vollständig aufzeichnete.

Anweisungen mit Ausführungsfehlern werden hier detailliert aufgezeichnet, einschließlich endgültiger Ausführungsfehler (z. B. Zeitüberschreitung), Syntaxfehler und während der Ausführung generierter Warnungen. Verwenden Sie sum_errors>0 or sum_warnings>0去performance_schema.events_statements_summary_by_digest .

FESTLEGEN DER SITZUNGSTRANSAKTIONSISOLATIONSSTUFE: LESEN UNCOMMITTED;

wählen 
 Schemaname,
 Digest_Text,
 zähle_sterne,
 zuerst_gesehen,
 zuletzt gesehen
aus performance_schema.events_statements_summary_by_digest
wobei sum_errors>0 oder sum_warnings>0 
Sortieren nach zuletzt gesehen, absteigend;

Indexnutzungsstatistiken

Basierend auf der Systemtabelle performance_schema.table_io_waits_summary_by_index_usage ist ihre statistische Dimension auch „die Anzahl der Zeilen, die von einer bestimmten Indexabfrage zurückgegeben werden.“

Statistiken können basierend darauf gesammelt werden, welche Indizes am häufigsten oder am wenigsten verwendet werden.

Allerdings birgt diese Statistik ein potenzielles Missverständnis:
count_read,count_write,count_fetch,count_insert,count_update,count_delete zählen die Anzahl der betroffenen Zeilen, wenn ein Index verwendet wird, und sum_timer_wait ist die kumulierte Wartezeit auf den Index.

Wenn der Index verwendet wird, aber keine Daten betroffen sind (d. h., die Bedingungen der DML-Anweisung treffen keine Daten), wird count_*** nicht gezählt, aber sum_timer_wait wird gezählt. Dies ist ein irreführender Punkt. Der Index wurde nicht oft getroffen, aber es wird eine große Anzahl von timer_wait generiert. Wenn der Index ähnliche Informationen sieht, kann der Index nicht vorschnell gelöscht werden.

Warteereignisstatistik

Jede Aktion in der MySQL-Datenbank erfordert eine Wartezeit (eine bestimmte Zeitspanne bis zur Ausführung). Insgesamt gibt es mehr als 1.000 Warteereignisse, die zu verschiedenen Kategorien gehören. Jede Version ist anders und nicht alle Warteereignisse sind standardmäßig aktiviert.

Persönlich denke ich, dass Warteereignisse nur als Referenz dienen und keine Problemdiagnose darstellen. Selbst wenn die Datenbank optimiert oder nur gering ausgelastet ist, sammeln sich bei bestimmten Ereignissen im Laufe der Zeit immer noch viele Warteereignisse an.

Die Warteereignisse dieser Ereignisse sind nicht unbedingt alle negativ. Beispielsweise wird das Warten auf Sperren zwangsläufig während des gleichzeitigen Ausführungsprozesses generiert. Die statistischen Ergebnisse dieses Warteereignisses sind ebenfalls kumulativ. Das bloße Betrachten eines direkten Werts hat keine Referenzbedeutung.
Sofern die Daten nicht regelmäßig erhoben und Differenzberechnungen auf Grundlage der tatsächlichen Bedingungen durchgeführt werden, haben sie lediglich einen Referenzwert.

FESTLEGEN DER SITZUNGSTRANSAKTIONSISOLATIONSSTUFE: LESEN UNCOMMITTED;

Wählen Sie SUBSTRING_INDEX (NAME, '/', 1) als Wartetyp, ANZAHL (1) 
VON performance_schema.setup_instruments
GRUPPE NACH 1 
SORTIEREN NACH 2 DESC;


WÄHLEN
Ereignisname,
zähle_sterne,
Summe_Timer_Warten
VON performance_schema.events_waits_summary_global_by_event_name
WO Ereignisname != 'Leerlauf'
Sortiert nach sum_timer_wait desc
Grenze 100;

Schließlich ist es wichtig festzustellen, dass

1. Für die Daten in vielen von MySQL bereitgestellten Systemtabellen (Ansichten) ist das bloße Betrachten des Werts selbst nicht sehr aussagekräftig, da es sich um einen kumulativen Wert handelt, insbesondere avg_***, der als Referenz mit vielen umfassenden Faktoren kombiniert werden muss.
2. Jede Abfrage der Systemtabelle kann einen gewissen Einfluss auf die Systemleistung selbst haben. Sammeln Sie keine Datenstatistiken, wenn dies erhebliche negative Auswirkungen auf das System haben könnte.

Zusammenfassen

Oben ist die Methode, die ich Ihnen vorgestellt habe, um mithilfe der MySQL-Systemdatenbank eine Leistungslastdiagnose durchzuführen. Ich hoffe, sie wird Ihnen hilfreich sein. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und ich werde Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!
Wenn Sie diesen Artikel hilfreich finden, können Sie ihn gerne abdrucken und dabei bitte die Quelle angeben. Vielen Dank!