Einführung in das MySQL-Tuning

1 Einleitung

Lassen Sie uns nicht über die Frequenz der CPU und die Größe des Speichers sprechen (dies ist genauso wichtig wie der Index, aber nicht der Inhalt dieses Artikels). ) und die Suchzeit der Festplatte. Wenn Sie an MySQL-Optimierung denken, müssen Sie zumindest den EXPLAIN-Ausführungsplan, langsame SQL-Protokolle, alte Profilbefehle, die neue performance_schema-Leistungsansicht und zugehörige Tabellen mit aktuellen Transaktions- und Speichernutzungsinformationen in information_schema kennen und auch Diagnoseinformationen zum Innodb-Status der Engine anzeigen B. einige TPS-, QPS- und IOPs-Indikatoren in Metrix. (Verwandte Empfehlungen: „MySQL-Tutorial“)

Die oben genannten Tools sind für die Optimierung vorbereitet, und die Datenbank bietet viele große und kleine Funktionen für eine hohe Verfügbarkeit. Die großen sind: Replikation , Gruppenkopie, Partition, Dateiverknüpfung: Das heißt, Protokolle und Datendateien können jeweils auf verschiedenen Festplatten abgelegt werden. Zu den kleinen gehören: Berechnung von Spalten, Berechnung von Hash für Spalten, Indexzusammenführung, Index-Pushdown, MRR, BKA, Loose Index und andere Algorithmen sowie Füllfaktoren usw.

Natürlich gibt es keinen Ansichtsindex und keine verteilte partitionierte Ansicht, und Join unterstützt nur verschachtelte. Dies ist ein Manko von MySQL. Der Algorithmus von SQL Server Join unterstützt drei Typen, Loop-While-Hash, was dies erheblich verbessert Geschwindigkeit der Verbindung. MySQL verfügt nicht über viele Funktionen zur Verbesserung der Leistung. Andere basieren auf Erfahrungswerten. Verwenden Sie keine Funktionen in Unterabfragen. Versuchen Sie, Unterabfragen in Join-Abfragen umzuwandeln. Oder die Zeitspalte ist langsam. Join |order by|group darf es nicht zulassen, dass eine temporäre Tabelle auf der Festplatte generiert wird. Dies hängt natürlich mit dem Speicher, dem schmalen Tabellendesign und dem breiten Tabellendesign usw. zusammen hängt von der Art Ihres Unternehmens ab.

Es gibt zwei Möglichkeiten, mit der Optimierung zu beginnen: eine zur Laufzeit, also die Optimierung auf dem laufenden Server, und die andere während des Entwicklungsprozesses. Egal welches, performance_schema wird benötigt.

Zwei performance_schema-Erklärungen

Die Leistungsansicht ist in jeder Datenbank zu finden und besteht aus einer Reihe von Speichertabellen, die mit dm_* beginnen. MySQL sind die verschiedenen Tabellen in der performance_schema-Bibliothek. Schauen wir uns zunächst die Eingangstabellen an:

SELECT * FROM setup_timers; -- 计时定义表
select * from setup_actors; -- 那些用户需要收集信息
select * from Setup_objects; -- 那些对象需要收集信息，比如mysql表，
select * from setup_consumers; -- 那些仪器的分类需要收集
select * from setup_instruments; -- 收集仪器，每一个功能点都会有仪器的事件，开始和结束，然后开启那个仪器，就会收集那个仪器的数据

Schauen wir uns zunächst an, wie das geht performance_schema-Schalter aktivieren:

show variables like 'performance_schema' -- 这是一个read only变量

Wenn es AUS ist, muss es in der Konfigurationsdatei aktiviert werden.

Dann stellen wir diese Eintragstabellen einzeln vor.

1, setup_actors-Tabelle

kann von allen Benutzern gesammelt werden.

2, Setup_objects

　

Diese Objekte können gesammelt werden, unabhängig davon, ob es sich um Tabellen oder Trigger usw. handelt . Was das Deaktivieren der beiden Spaltensteuerelemente betrifft, sind die aktivierten und zeitgesteuerten Felder auf Nein gesetzt, was bei diesen Tabellen der Fall ist.

3 setup_consumers

　　

Klassifizierung von Ereignissen, Stufen sind Schritte, eine Aussage in der Server Die Schritte und Ergebnisse des Ausführungsprozesses sind die gleichen wie bei der Profilmethode, da sie später entfernt wird. Transaktion ist die Ereignissammlung der Transaktion usw.

4 setup_instruments　

Dies ist das Hauptinstrument zur Ereignisüberwachung, wie folgt:

5 Schließlich gibt es setup_timers, das zusammen mit performance_timers verwendet wird, um die Zeittypen dieser Instrumentenkategorien wie folgt zu definieren:

CYCLE: CPU-Uhr , TIMER_FREQUENCY gibt an, wie viele Sekunden es gibt, TIMER_RESOLUTION gibt an, um wie viel es sich jedes Mal erhöht und schließlich wie oft es abgerufen wird.

3 Verwenden Sie performance_schema, um Priorfile-Daten zu erhalten

Öffnen Sie das entsprechende Instrument:

Sehen wir uns die obige Instrumentenklassifizierungstabelle an Die Informationen in setup_consumers und die Zeilen zur Phase sind alle NEIN, daher müssen wir sie in JA ändern. Gleichzeitig müssen wir die Informationen in der Anweisungsüberwachungstabelle abrufen, also müssen wir dies auch tun Anweisungen einschalten:

UPDATE setup_consumers SET ENABLED = 'YES'
       WHERE NAME LIKE '%stage%';
UPDATE setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

Dann schalte das Instrument der Bühne ein

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';   -- 开启所有执行步骤的监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

Die Ausführung basiert auf SQL

 select * from quartz.TestOne

Fragen Sie die Abfrage-ID dieser Anweisung ab:

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%quartz%';

那么id就是509

然后执行性能监控表：

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=509

 

内容和老版本的profile结果一样。

主要看下stage/sql/Sending data这一行，这一行是主要io相关的事件，一般情况下，sql慢了，而这一行数值比较大，那肯定硬盘读数据慢了或者有锁冲突。

那么就是用error log，有死锁，mysql会将死锁信息打入error日志，show engine innodb status只是全局的一些信息，如果要想看详细的再去监控对应的instrument。

而且目前mysql8多支持NOWAIT和skiplocked两个语句，用法还是select.. from 表明 for update/for nowait等，非常灵活的解决了死锁的处理方式，当然你也可以让其事务隔离级别为脏读级别，但是并不能解决更多的业务类型，设置死锁超时也是一个可行的办法。

Das obige ist der detaillierte Inhalt vonEinführung in das MySQL-Tuning. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!