Einführung in MySQL-Optimierungsideen

Der Inhalt dieses Artikels ist eine Einführung in MySQL-Optimierungsideen. Er hat einen gewissen Referenzwert. Ich hoffe, dass er für Sie hilfreich ist.

Maßnahmen zur Lösung von Problemen auf Datenbankebene

Allgemeine Ideen zur Notfalloptimierung:
Im Falle einer plötzlichen Verzögerung der Geschäftsverarbeitung kann die normale Geschäftsverarbeitung nicht durchgeführt werden! Ein Szenario, das sofort gelöst werden muss!

1、show processlist

2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 结果集>30;
show index from table;

3、通过执行计划判断，索引问题(有没有、合不合理)或者语句本身问题

4、show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session

Allgemeine Tuning-Ideen:
Zum Beispiel ist das Geschäft jeden Tag um 10-11 Uhr extrem langsam, aber es kann immer noch verwendet werden wird gut sein.

1. Überprüfen Sie das Slowlog, analysieren Sie das Slowlog und analysieren Sie die langsamen Abfrageanweisungen.

2. Überprüfen Sie alle langsamen Anweisungen einzeln nach einer bestimmten Priorität.

3. Analysieren Sie Top-SQL, führen Sie EXPLAIN-Debugging durch und überprüfen Sie die Ausführungszeit der Anweisung.

4. Passen Sie den Index oder die Anweisung selbst an.

1. Systemebene

CPU:
vmstat, sar top, htop, nmon, mpstat

Speicher:
kostenlos, ps - aux,

IO-Geräte (Festplatte, Netzwerk):
iostat, ss, netstat, iptraf, iftop, lsof,

vmstat-Befehlsbeschreibung:
Procs: r zeigt an, wie viele Prozesse warten auf CPU-Zeit. b Zeigt die Anzahl der Prozesse im ununterbrochenen Ruhezustand an. Warten auf E/A

Speicher: swpd zeigt die Anzahl der auf die Festplatte ausgelagerten Datenblöcke an. Die Anzahl der nicht verwendeten Datenblöcke, Benutzerpuffer-Datenblöcke und vom Betriebssystem verwendeten Datenblöcke

Swap: Die Anzahl der Datenblöcke, die das Betriebssystem pro Sekunde von der Festplatte zum Speicher und vom Speicher zur Festplatte austauscht. s1 und s0 sind vorzugsweise 0

Io: Die Anzahl der Datenblöcke, die pro Sekunde auf Gerät b0 geschrieben werden, wenn b1 vom Gerät gelesen wird. Reflektiert Festplatten-E/A

System: Zeigt die Anzahl der Interrupts (in) und Kontextwechsel (cs) an, die pro Sekunde auftreten

Cpu: Zeigt die Anzahl an, die zum Ausführen von Benutzercode und Systemcode verwendet wird , Leerlauf, CPU-Zeit beim Warten auf E/A

iostat-Befehlsbeschreibung
Beispielbefehl: iostat -dk 1 5
iostat -d -k -x 5 (Gerätenutzung anzeigen (%util) und Antwortzeit (warten))

tps: Die Anzahl der Übertragungen pro Sekunde des Geräts. „Eine Übertragung“ bedeutet „eine E/A-Anfrage“. Mehrere logische Anfragen können zu einer „einzelnen I/O-Anfrage“ zusammengefasst werden.

iops: Wenn die Hardware das Werk verlässt, definiert der Hersteller eine maximale Anzahl von IOs pro Sekunde. Die Größe der „One Transfer“-Anfrage ist unbekannt.

kB_read/s: Die pro Sekunde vom Gerät gelesene Datenmenge (auf dem Laufwerk ausgedrückt)

KB_wrtn/s: Die pro Sekunde auf das Gerät geschriebene Datenmenge (auf dem Laufwerk ausgedrückt);

kB_read: die Gesamtmenge der gelesenen Daten;

kB_wrtn: die Gesamtmenge der geschriebenen Daten;

1. Lösungen für Probleme auf Systemebene

Glauben Sie, dass es besser ist, eine höhere oder eine niedrigere Last zu haben?
In der tatsächlichen Produktion wird allgemein angenommen, dass es kein Problem gibt, solange die CPU 90 % nicht überschreitet.

Folgende Sondersituationen sind natürlich nicht ausgeschlossen:
Problem 1: Hohe CPU-Last, geringe IO-Last
Unzureichender Speicher

Schlechte Festplattenleistung

SQL-Problem- ----->Gehen Sie zur Datenbankebene, um das SQL-Problem weiter zu beheben

Es liegt ein Problem mit IO vor (die Festplatte ist kritisch, das RAID-Design ist nicht gut, das RAID ist beeinträchtigt). , gesperrt, und die tps pro Zeiteinheit sind zu hoch)

tps ist zu hoch: viele kleine Daten-IOs, viele vollständige Tabellenscans

Problem 2: hohe IO-Last, geringe CPU-Last
Viele kleine IO-Schreibvorgänge:

Autocommit, das eine große Anzahl kleiner IOs generiert

IO/PS ist ein fester Wert der Festplatte Wenn das Gerät das Werk verlässt, definiert der Hersteller eine maximale Anzahl an IOs pro Sekunde.

Eine große Anzahl großer IO-Schreibvorgänge

Die Wahrscheinlichkeit von SQL-Problemen ist relativ hoch

Problem 3: IO- und CPU-Lasten sind sehr hoch
Die Hardware ist nicht genug oder es liegt ein Problem mit SQL vor

5. Grundlegende Optimierung

1. Optimierungsideen

Lage der Problempunkte:
Hardware-- > System--> Anwendung--> Architektur (hohe Verfügbarkeit, Lese-/Schreibtrennung, Unterdatenbank und Untertabelle)

Verarbeitungsrichtung:
Klare Optimierungsziele , Kompromisse zwischen Leistung und Sicherheit eingehen und Probleme verhindern, bevor sie auftreten

1. Hardwareoptimierung

Host-Aspekt: ​​
Je nach Datenbanktyp, Host-CPU-Auswahl, Speicherkapazität Auswahl, Festplattenauswahl

Speicher- und Festplattenressourcen ausgleichen

Zufällige E/A und sequentielle E/A

BBU (Battery Backup Unit) der Host-RAID-Karte ist ausgeschaltet

CPU-Auswahl:
Zwei Schlüsselfaktoren der CPU: Anzahl der Kerne und Hauptfrequenz

Wählen Sie je nach Geschäftstyp:

CPU-intensiv: mehr Berechnungen, OLTP erfordert mehr CPUs und Kerne mit hoher Hauptfrequenz

IO-intensiv: Abfragevergleich erfordert mehr OLAP-Kerne und die Hauptfrequenz ist nicht unbedingt hoch

Speicherauswahl:
Datenbank vom Typ OLAP erfordert mehr Speicher, was mit der Ebene der Datenerfassung zusammenhängt.

Der allgemeine Speicher für OLTP-Daten beträgt das Zwei- bis Vierfache der Anzahl der CPU-Kerne, und es gibt keine Best Practice.

Speicher:
Wählen Sie je nach Art der gespeicherten Daten unterschiedliche Speichergeräte aus.

Konfigurieren Sie einen angemessenen RAID-Level (Raid 5, Raid 10, Hot-Spare-Festplatte)

Für das Betriebssystem müssen keine allzu speziellen Entscheidungen getroffen werden. Es ist am besten, redundante (raid1) (ssd, sas, sata)

RAID-Karte: Auswahl der Host-RAID-Karte:
Implementierung zu verwenden Systemfestplattenredundanz (RAID1)

Speicher- und Festplattenressourcen ausgleichen

Zufällige E/A und sequentielle E/A

BBU (Batterie) der Host-RAID-Karte (Backup-Einheit) schließen

Netzwerkausrüstung:
Verwenden Sie Datenverkehr zur Unterstützung höherer Netzwerkausrüstung (Switches, Router, Netzwerkkabel, Netzwerkkarten, HBA-Karten)

Hinweis: Die obige Planung sollte bei der anfänglichen Konzeption des Systems berücksichtigt werden .

1. Server-Hardware-Optimierung

1. Physische Statusleuchte:

2. Eigenes Verwaltungsgerät: Fernbedienungskarte (FENCE-Gerät: ipmi ilo idarc) , Ein-/Ausschalten, Hardwareüberwachung.

3. Überwachungssoftware und -geräte von Drittanbietern (SNMP, Agent) überwachen physische Einrichtungen

4. EMC2 (von HP übernommen), Hitachi (hds), IBM Low-End-OEM-HDS, High-End-Speicher ist eine eigene Technologie, Huawei-Speicher

1. Systemoptimierung

CPU:
Grundsätzlich sind keine Anpassungen erforderlich, Sie können sich nur auf die Hardwareauswahl konzentrieren.

Speicher:
Grundsätzlich sind keine Anpassungen erforderlich, Sie können sich nur auf die Hardwareauswahl konzentrieren.

SWAP:
MySQL versucht, die Verwendung von Swap zu vermeiden. Der Standard-Swap auf dem Server von Alibaba Cloud ist 0 🎜 >

Dieser Parameter bestimmt, ob Linux dazu tendiert, Swap zu verwenden oder den Dateisystem-Cache freizugeben. In Situationen mit knappem Speicher führen niedrigere Werte tendenziell dazu, den Dateisystem-Cache freizugeben. Natürlich kann dieser Parameter nur die Wahrscheinlichkeit der Verwendung von Swap verringern, aber nicht verhindern, dass Linux Swap verwendet.

Ändern Sie den MySQL-Konfigurationsparameter innodb_flush_method und aktivieren Sie den O_DIRECT-Modus. In diesem Fall umgeht der Pufferpool von InnoDB den Dateisystem-Cache direkt, um auf die Festplatte zuzugreifen, das Redo-Protokoll verwendet jedoch weiterhin den Dateisystem-Cache. Es ist erwähnenswert, dass sich das Redo-Protokoll im Überschreibmodus befindet. Auch wenn der Dateisystem-Cache verwendet wird, nimmt er nicht zu viel in Anspruch.

IO-Planungsstrategie:

Anpassung der Systemparameter

Linux-Systemkernel-Parameteroptimierung:

Benutzereinschränkungsparameter (MySQL muss die folgende Konfiguration nicht festlegen):

Anwendungsoptimierung

Geschäftsanwendungen und Datenbankanwendungen sind unabhängig, Firewall: iptables, Selinux und andere nutzlose Dienste (geschlossen):

Starten Sie nicht den Runlevel 3 der grafischen Oberfläche, wenn Sie einen Server mit grafischer Oberfläche installieren. Überlegen Sie außerdem, ob unser Geschäft in Zukunft real sein wird. Benötigen Sie MySQL oder verwenden Sie eine andere Art von Datenbank? Der höchste Punkt bei der Verwendung einer Datenbank besteht darin, keine Datenbank zu verwenden.
1. 6. Datenbankoptimierung

SQL-Optimierungsrichtung:

Ausführungsplan, Index, SQL-Umschreibung

Architekturoptimierungsrichtung:

Hochverfügbarkeitsarchitektur, Hochleistungsarchitektur, Sub- Untertabelle der Bibliothek

Datenbankparameteroptimierung

Anpassung:

Instanz insgesamt (erweiterte Optimierung, Erweiterung)

1. Verbindungsschicht (grundlegende Optimierung)
Richten Sie vernünftige Verbindungs-Clients und Verbindungsmethoden ein

Dieser Artikel finden Sie hier. Weitere spannende Inhalte finden Sie in der Spalte
MySQL-Tutorial-Video

auf der chinesischen PHP-Website!

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