So optimieren Sie Ihre Datenbank

Der Grund, warum die Datenbank zu langsam läuft

Der erste Punkt ist, dass die Hardware zu alt ist

Für Hardware sind wir hauptsächlich Konzentrieren Sie sich auf CPU, Speicher und einige Faktoren wie Netzwerkkarten, Computerraumnetzwerke usw. Aufgrund der Länge des Artikels werde ich sie nicht einzeln vorstellen Es wird Gelegenheit geben, in Zukunft darüber zu sprechen.

Werfen wir zunächst einen Blick auf die CPU-Auslastungseigenschaften von MySQL:

5.1 kann 4 Kerne nutzen, 5.5 kann 24 Kerne nutzen und 5.6 kann 64 Kerne nutzen

Zum Beispiel MySQL5.6 kann mehr als 48 Kerne verwenden, und wenn es gut läuft, kann es 64 Kerne verwenden (zwischen 48 Kernen und 64 Kernen, die offizielle Ankündigung ist 48 Kerne, und in meinem tatsächlichen Test kann es 64 Kerne erreichen).

MySQL 5.6 kann 48 Kerne+ verwenden

MySQL 5.1 kann zuvor bis zu 4 Kerne verwenden

Jetzt sind die allgemeinen Produktionsumgebungsserver 32 Kerne oder höher.

Daher empfehle ich jedem, MySQL5.5 oder MySQL5.6 zu verwenden, es sei denn, der Server Ihres Unternehmens verwendet einen sehr alten Server mit nur 4 Kernen oder 1 Kern.

Da es vor 5.1 (dasselbe wie 5.0) im internen Code fest codiert war und auf der Innobase-Speicher-Engine basierte, hatte die Datenbank eine schlechte Hardwareauslastung. Nach der Weiterentwicklung zur InnoDB-Engine wurde es viel besser.

Jede Verbindung ist ein Thread (kein Thread-Pool), und jede Abfrage kann nur einen Kern verwenden.

Außerdem kann in MySQL jede Abfrage nur eine CPU verwenden.

Oracle verwendet paralleles SQL und parallele Abfragen. Diese Art von Funktion gibt es in MySQL nicht.

Kein Ausführungsplan-Cache (keine SQL-Ausführungsplan-Vorkompilierung)

Zweitens gibt es in MySQL keine SQL-Vorkompilierung. Daher gibt es in der Speicherstruktur von Oracle keine Struktur wie den Bibliothekscache. Daher verfügt MySQL nur über Hard-Parsing, es gibt kein Soft-Parsing, geschweige denn Soft-Parsing.

MySQL wird mit zunehmender Anzahl von Verbindungen einen Leistungsabfall erfahren

Dies ist ebenfalls ein Fehler von MySQL, aber mit der Weiterentwicklung der MySQL-Versionen sind viele Lösungen entstanden.

Zum Beispiel: der offiziell gestartete Thread-Pool (TP). Es soll das Problem einer zu hohen Anzahl gleichzeitiger Verbindungen lösen. Dies ist jedoch eine zusätzliche Komponente von MySQL, und für den Kauf des offiziellen TP ist zusätzliches Geld erforderlich.

Darüber hinaus gibt es in China eine Person namens Lou Fangxin, die eine OneSQL-Middleware entwickelt hat, um ähnliche Probleme zu lösen.

Es gibt einen Ergebnis-Cache, aber er ist nutzlos

MySQL verfügt auch über einen Ergebnis-Cache ähnlich dem in Oracle, genannt Query Cache, aber es ist eine relativ nutzlose Funktion und wird selten verwendet.

Da es sich bei den meisten tatsächlichen Produktionsumgebungen um OLTP-Systeme handelt, kommt es häufig zu Aktualisierungs- und Änderungsvorgängen. Dieser Abfragecache wird in einer Umgebung verwendet, in der Daten häufig aktualisiert und geändert werden, was die Leistung von MySQL erheblich beeinträchtigt. Daher ist es generell sehr sparsam zu verwenden.

Heutzutage verwendet MySQL grundsätzlich die InnoDB-Speicher-Engine. Die bisherigen MyISAM-Engines werden selten verwendet. (Was ist eine Speicher-Engine? Wenn Sie das nicht wissen, können Sie gg)

Dieser Abfrage-Cache muss nicht in der InnoDB-Engine aktiviert werden, da es sich um eine transaktionale Speicher-Engine handelt wird bei Verwendung von InnoDB verwendet. Bei hohen Transaktionsverarbeitungsfunktionen kommt es auf jeden Fall zu häufigen Datenaktualisierungen und -änderungen.

Sehen wir uns noch einmal die Speichernutzungseigenschaften von MySQL an

Der Server mit 64-Bit-Betriebssystem kann Speicher ((2^64-1)/1024/1024/1024)G

In einer Hochgeschwindigkeitsumgebung wird Speicher-Caching grundsätzlich verwendet, um die E/A-Auswirkungen auf die Festplatte zu reduzieren.

Normalerweise wird der Speicher entsprechend 15 % bis 20 % der tatsächlichen Daten geplant Da die Daten besonders heiß sind, muss ein größerer Anteil berücksichtigt werden, um Daten zwischenzuspeichern

Diese 15 % bis 20 % der Daten werden normalerweise als heiße Daten bezeichnet. (Dies ist auch ein allgemeiner Erfahrungswert)

Wenn Sie beispielsweise schätzen, dass das Gesamtdatenvolumen Ihres MySQL etwa 500 G beträgt, dann kann der von MySQL bereitgestellte Speicher 75 G (500 * 0,15) betragen Möglicherweise ist ein Server mit ca. 128 GB Speicher erforderlich.

Darüber hinaus verfügen einige Unternehmen über besonders heiße und große Mengen an heißen Daten (es ist möglich, dass der Bereich von 15 % bis 20 % deutlich überschritten wird), wie beispielsweise QQ Farm.

Ich glaube, jeder hat schon einmal Spiele zum Essensdiebstahl gespielt, wie zum Beispiel QQ Farm, Happy Farm und dergleichen. (Es gibt auch eine 12306-Website zum Buchen von Tickets).

Diese Art von Geschäft ist in unserer Branche von großer Bedeutung: Wenn die Daten heiß sind, handelt es sich im Grunde genommen um 100 % heiße Daten. Zum Beispiel: wenn jeder auf QQ spielt Bauernhof. Sie kommen jeden Tag hierher, um zu spielen, und ab und zu kommen sie auch hoch, um etwas Essen zu stehlen. Viele Leute stehen mitten in der Nacht auf und stehlen etwas Essen, wenn sie auf die Toilette gehen.

Daher muss die Speicherkonfiguration der MySQL-Datenbank für diese Art von Unternehmen erhöht werden. 15-20 % reichen nicht aus.

Zusammenfassung: 15–20 % des allgemeinen Geschäfts werden für die Planung wichtiger Daten wie User Center, Bestellungen und andere gängige Geschäfte verwendet. Für einige andere Spezialgeschäfte muss die spezifische Situation im Detail analysiert werden.

Die Zuweisung von Anleitungen kann basierend auf der Antwortzeit der Anfrage erfolgen

Wenn wir eine solche groß angelegte Online-Architektur durchführen – große Datenbanken planen und entwerfen,

Die Antwortzeit von SQL-Abfragen ist auch ein sehr wichtiger Indikator.

In einem so großen System müssen Millionen oder sogar Dutzende Millionen Benutzer gleichzeitig online Geschäfte abwickeln. Die Antwortzeit von SQL-Abfragen (Abfragen) muss streng kontrolliert werden Das System muss die Antwortzeit der Abfrage innerhalb des Zeitlimits steuern.

Für unsere Kernbibliothek benötige ich beispielsweise, dass die Antwortzeit (durchschnittliche Antwort) von Query unter 30 ms liegt. Wenn es 30 ms überschreitet, gehen wir davon aus, dass die Datenbank möglicherweise ihre Auslastungsgrenze erreicht hat und die Datenbank erweitert werden muss.

Darüber hinaus ist eine langfristige Indikatorüberwachung dieser Abfrageantwortzeit erforderlich.

Dies ist die Kernbibliothek. Wenn es andere, weniger wichtige Hilfsbibliotheken gibt, z. B. Protokollierungsbibliotheken, oder einige Bibliotheken, deren Leistungsanforderungen nicht zu hoch sind, können wir die Abfrageantwortzeit auf 1 oder 2 Sekunden reduzieren Sekunden.

Bestimmen Sie den Schwellenwert dieser Abfrageantwortzeit entsprechend der Bedeutung des Unternehmens.

Dies ist ein sehr wichtiges Leitprinzip: Planen Sie Ihre Leistungskapazität basierend auf der Antwortzeit auf Abfragen.

Es gibt zwei Arten von Kapazität: Leistungskapazität und Raumkapazität. Die Speicherplatzkapazität ist sehr einfach, das heißt, wie viele SIZE-Daten platziert werden und wie viele T.

Leistungsfähigkeit ist wichtiger und bestimmt, ob sie Ihrem geschäftlichen Druck und Ihrer Belastung gewachsen ist.

Jeder sollte bedenken: Wenn das Unternehmen, mit dem Sie zusammenarbeiten möchten, Millionen aktiver Benutzer und nicht Hunderte von Benutzern umfasst, ist die Leistung entscheidend und die Erfüllung der Anforderungen des Unternehmens ist das Wichtigste.

Egal wie großartig Ihre Funktionen sind, egal wie gut Ihr Produkt ist, die Leistung ist unübertroffen und alles andere ist Unsinn. Hunderte von Menschen können Ihr gesamtes System und Projekt in wenigen Sekunden lahmlegen Dann seid ihr Leute. Die Firma war geblendet.

Die Benutzer, die so hart gearbeitet haben, werden ebenfalls in großer Zahl verloren gehen, und die Verluste werden hoch sein.

Leistung ist das Fundament. Die gesamte Architektur macht nur dann Sinn, wenn die Leistung dem standhält. Wenn die Leistung nicht zufriedenstellend ist, ist es sinnlos, später über Hochverfügbarkeit nachzudenken.

Merkmale der Festplattenauslastung von MySQL

Binlog, Redo-Log, Undo-Log sequentielles IO

MySQL verfügt über verschiedene IO-Typen.

binlog, redolog, undolog, das sind alles sequentielle IO-Schreibvorgänge.

Es besteht keine große Notwendigkeit, solche Dinge auf einer SSD zu speichern. Auch das sequentielle Schreiben auf einer SSD ist etwas verschwenderisch Leben. Es muss auf SSD platziert werden. Es reicht aus, es auf eine herkömmliche SAS-Festplatte zu legen. Es ist nicht erforderlich, eine SSD einzubauen.

SSD wird zum Speichern von Datendateien verwendet. Da die meisten E/A-Vorgänge in der Datendatei zufällige E/A-Vorgänge sind, ist es für SSD sehr vorteilhaft, zufällige E/A-Vorgänge auszuführen. SSD-Solid-State-Festplatte und herkömmliche SAS-Festplatte werden zur Speicherung gemischt. Darüber hinaus sollten Sie keine SSDs für Backup-Festplatten verwenden.

Zufällige Datendatei-E/A und sequentielle E/A kombiniert

Sequentielle E/A ist immer schneller. Was beim Datenbankdesign darüber entscheidet, ob Sie ein großartiger DBA oder ein großartiger Architekt sind, hängt davon ab, ob Sie ein Unternehmen so weit wie möglich als sequenzielles IO entwerfen und gleichzeitig zufälliges IO reduzieren können. Zum Beispiel: Wenn ich ein Freundschaftsbeziehungsgeschäft entwerfe, hoffe ich, dass eine Abfrage die Freundschaftsbeziehung durch sequentielle E/A herausnehmen kann. Wie entwirft man sie?

In MySQLs InnoDB können wir eine Funktion von InnoDB nutzen: Clustered-Index-Tabellen. (Ähnlich wie Oracles IOT).

Mit dieser Funktion können die Freundesdaten des Benutzers so weit wie möglich auf einer Seite oder auf mehreren angrenzenden Seiten gesammelt werden. Beim Lesen kann eine sequentielle Lese-E/A durchgeführt werden, wodurch die Leistung erheblich verbessert wird.

Die Struktur der Freundschaftsbeziehungstabelle ist wie folgt (die Prämissentabelle ist die InnoDB-Engine):

owner_id freund_id (Freund-ID)

Die beiden oben genannten Felder werden verwendet als Primärschlüssel, der Primärschlüssel von InnoDB Es handelt sich um einen Clustered-Index, daher muss das Lesen dieser beiden Felder mit sequentiellen E/A erfolgen.

In der Vergangenheit wurde in einigen Büchern zum Datenbankdesign immer erwähnt, dass jede Tabelle eine Spezifikation für einen automatisch inkrementierten Primärschlüssel hinzufügen muss. Tatsächlich ist die Spezifikation tot, aber die Antwort ist lebendig. Die Freundschaftsbeziehung, die ich gegeben habe Ein Beispiel oben ist: Anstatt einen automatisch inkrementierten Primärschlüssel zu verwenden, werden zwei Geschäftsfelder, die über Geschäftsattribute verfügen und häufig gelesen werden, als Primärschlüssel verwendet, was zu einer besseren Leistung führt.

Merken Sie sich daher beim Lernen nicht die Normen und Vorschriften in diesen Büchern. Stattdessen sollten Sie die Prinzipien von etwas wirklich verstehen, beispielsweise die internen Prinzipien von InnoDB gut erlernen und dann in der tatsächlichen Arbeit arbeiten , können Sie die Prinzipien unterstützen und Prinzipien verwenden, um Schlussfolgerungen zu ziehen.

Die Prinzipien von InnoDB sind ein riesiges Stück Wissen und erfordern ein Lernen im Laufe der Zeit. Sie können meinem offiziellen Konto mehr Aufmerksamkeit schenken und einige Artikel über InnoDB werden nacheinander veröffentlicht.

OLTP-Geschäft erfordert mehr zufällige E/A

Sie können Speicher zum Zwischenspeichern verwenden und dadurch zufällige E/A reduzieren

OLAP-Geschäft erfordert mehr sequentielle E/A

Speicher Der Cache ist von geringem Nutzen

Vor MySQL 5.6 wurde die Seitenänderung nicht unterstützt und der Standardwert war 16 KB.

MySQL5.6 kann nach MySQL5.6 geändert werden. Dieser Parameter ist innodb_page_size, aber MySQL5.6 kann nur auf 8K oder 4K geändert werden und kann nicht auf 32K oder 64K erhöht werden. 7 oder höher.

Bei OLAP-Systemen tragen größere Seiten zur Verbesserung der Leistung bei, da OLAP-Systeme relativ große Abfragen haben und viele Daten scannen.

Zweiter Punkt: Das Datenbankdesign ist nicht gut

Zum Beispiel werden viele Datenbankfunktionen verwendet, wie Trigger, Partitionen, viele gespeicherte Prozeduren, Funktionen usw .

Wir sagen oft: „Klein ist schön“, was bedeutet, dass Einfachheit das Beste ist. Wenn Sie alle Funktionen der Datenbank nutzen, wird die Leistung der Datenbank natürlich verlangsamt und die Wahrscheinlichkeit möglicher Fehler und zugrunde liegender Ausfälle steigt.

Daher muss jeder verstehen, dass ein gutes Datenbankprojektdesign klein, schön, schlank und einfach ist. Darüber hinaus ist die Datenbank nur ein Teil des Gesamtprojekts. Dinge wie Trigger und gespeicherte Prozeduren können durchaus mithilfe von Anwendungscode im Gesamtprojekt implementiert werden.

Wenn wir also MySQL verwenden, nutzen wir einfach seine leistungsstarken Funktionen wie Tabellen, Indizes, Transaktionen usw., anstatt alle seine Funktionen nutzen zu müssen.

Ein weiterer Punkt ist, dass vor MySQL 5.6 Unterabfragen in der Hauptdatenbank der Produktionsumgebung nicht zulässig waren.

Die Leistung von Unterabfragen vor MySQL 5.6 war besonders schlecht. (Syntax wird unterstützt, aber die SQL-Leistung ist sehr schlecht).

Wenn Sie beispielsweise jetzt Oracle verwenden und Oracle auf MySQL migrieren möchten, wird die Verwendung der MySQL5.6-Version empfohlen. MySQL5.6 hat große Verbesserungen bei der Unterabfrageunterstützung und Leistung gebracht.

Die Leistung der MySQL 5.6-Unterabfrage wird erheblich verbessert.

Der dritte Punkt: Das Schreiben von Programmen ist schrecklich

Ich denke, Studenten, die DBAs waren, sollten dies in kleinen und mittleren Unternehmen erlebt haben variiert zusammen.

Besonders wenn Sie auf viele Programmierer treffen, die gerade erst in die Branche eingestiegen sind (frische Absolventen), ist es wahrscheinlicher, dass diese Programmierer, die gerade erst in die Branche eingestiegen sind, auch einige sehr dringende Bedürfnisse übernehmen werden. Es ist schwer vorstellbar, dass ein Programm in einer solchen Umgebung entwickelt wird.

Natürlich ist es nicht die Schuld unserer Programmierer, wir können ihnen keine Vorwürfe machen.

Der Hauptgrund für mein oben erwähntes Phänomen ist die inländische Entwicklungsumgebung. Ich kann nichts dagegen tun. Der Entwicklungsbedarf ist dringend (Produkte werden jeden Tag aktiviert) und die Programmierer sind damit beschäftigt, zur Arbeit zu eilen (Langfristige Überstunden). Sie können nur mit der Umsetzung von Geschäftsprogrammen beschäftigt sein, es bleibt keine Zeit, das Programm zu optimieren.

Natürlich ist es in diesem Umfeld eine Chance für uns DBAs. Schlechtes SQL und komplexes SQL, das von Programmierern geschrieben wurde, führten dazu, dass das System langsam war oder sogar abstürzte. Dann griff unser DBA ein, um dieses schlechte SQL und langsame SQL zu optimieren und zu transformieren, und das System normalisierte sich wieder und wurde immer stabiler. Auch das ist etwas, das sehr erfüllend ist und von Kollegen und Führungskräften respektiert wird.

Gleichzeitig können DBAs auch die Ausbildung von Programmierern verstärken, um deren Fähigkeit zu verbessern, schnell gutes SQL zu schreiben. Lassen Sie sie weniger Zeit aufwenden und SQL-Anweisungen mit besserer Leistung und reibungsloserer Leistung schreiben. Auf diese Weise kann auch die Belastung des DBA verringert werden.

Ich persönlich bevorzuge es, mit Programmierern über Schulungen zu sprechen. Erstens kann jeder durch den Austausch von Technologie etwas gewinnen. Zweitens kann es eine gute Beziehung aufbauen und es einfacher machen, über alle Angelegenheiten zu sprechen, die verhandelt werden müssen in der Zukunft. Das ist besser, als sie zum Abendessen einzuladen.

Wir haben hauptsächlich die folgenden Lösungen für schlecht geschriebene Programme:

Um Anwendungen dazu zu bringen, Datenbankverbindungspools zu verwenden, insbesondere in großen, auf JAVA basierenden Anwendungen mit hoher Parallelität, muss dies der Fall sein Verbindung verwenden Pooling.

Der Vorteil der Verwendung eines Verbindungspools besteht darin, dass die Anzahl der Verbindungen für die Anwendung begrenzt werden kann. Außerdem ist es nicht erforderlich, jede zusätzliche Verbindung zu erstellen , weil das Erstellen einer neuen Verbindung dem Erstellen eines Threads bei MySQL entspricht.

Ich habe gerade auch erwähnt, dass es bei MySQL zu Leistungseinbußen kommen wird, wenn die Anzahl der Verbindungen zunimmt.

Studenten, die Programmcode geschrieben haben, sollten auch wissen, dass Sie auf unserem normalen PC-Notebook (normalerweise 4CORE) 400 Threads erstellen und jeder Thread 1+1+1+1+ ausführt. Einfache Aufgabe, wieder schlafen und prüfen Sie, ob Ihr PC feststeckt oder nicht. Sie werden feststellen, dass die CPU Ihres PCs fast voll ist. Wenn Sie es wagen, 600 Threads zu erstellen, wird Ihre Maschine bald neu gestartet. Dies liegt daran, dass die CPU aufgrund des Thread-Overheads voll ausgelastet ist.

Komplexe SQL-Anweisungen

Wie ich gerade sagte, hat das Schreiben von SQL normalerweise viele Probleme. Schließlich sind sie zu beschäftigt, um die Leistung und den Betrieb dieses SQL zu berücksichtigen. In einigen Fällen kann das vom Programmierer gespleißte SQL das gesamte System direkt zum Absturz bringen.

Lassen Sie mich ein einfaches Beispiel geben: Eine unserer Anwendungen stellt 10 Verbindungen zur Datenbank her (maximale Anzahl von Verbindungen = 10). Jede dieser 10 Verbindungen führt gleichzeitig das gleiche komplexe SQL aus komplexes SQL. Es dauert mindestens 10 Minuten. Dann können diese 10 Verbindungen dieses komplexe SQL nur innerhalb von 10 Minuten ausführen, und alle anderen nachfolgenden SQLs werden blockiert.

Die meisten Anwendungen sind 10 Minuten lang nicht verfügbar, oder? Und es kann zu einer Lawine und zum Zusammenbruch des Systems kommen.

Die Optimierung komplexer SQL ist auch eine sehr wichtige Aufgabe für DBAs. Es ist notwendig, diese komplexe SQL, langsame SQL und schlechte SQL durch Überwachungsmethoden herauszufinden und dann den Programmierern (DBA-Anforderungen) Optimierungsvorschläge zu geben um Leistungsvergleiche (Tests) durchzuführen, sodass Programmierer den Code ändern können, sodass das System wirklich reibungslos und parallel laufen kann, wie eine Autobahn ohne Staus.

Manche Leute fragen sich vielleicht, dass die Programmierer unseres Unternehmens nur Bösewichte sind, selbst wenn sie sterben, sie werden ihn nicht optimieren, selbst wenn sie sterben, und sie können nicht kommunizieren. Was sollen wir also tun?

Wir können auch eine dedizierte Slave-Bibliothek (Slave-Bibliothek) erstellen, um damit umzugehen.

Am Beispiel unseres Unternehmens ist unser Hintergrundsystem, das Berichte generiert, zur Abfrage mit der Slave-Datenbank verbunden und stellt keine Verbindung zur Hauptdatenbank her.

Ungültige Logik

Vollständiger Tabellenscan

Zum Beispiel: update t set a = a + 1; Vergessen, die Where-Bedingung hinzuzufügen.

Wenn Sie möchten, dass Ihr System Millionen von Benutzern online unterstützt, müssen Sie ein SQL-Überprüfungssystem (SQL Review) hinzufügen, um SQL mit ungültiger Logik und SQL mit vollständigen Tabellenscans zu eliminieren.

SQL kann erst online freigegeben werden, nachdem es vom DBA überprüft und genehmigt wurde.

Darüber hinaus sollte diese Art von großem Update-SQL stapelweise aktualisiert werden und die große SQL-Aufgabe sollte in kleine auszuführende Aufgaben unterteilt werden. In MySQL erfordert dies besondere Aufmerksamkeit.

Warum stapelweise aktualisieren?

Grund 1. Wie oben erwähnt, kann eine MySQL-Abfrage nur einen CORE verwenden. SQL-Transaktionen sind zu groß und komplex, ihre Ausführung dauert lange, was leicht zu einer Überlastung führt.

Grund 2. In der Online-Umgebung verfügt MySQL im Allgemeinen über eine Master/Slave-Architektur. Wenn im Master eine große Aktualisierungstransaktion mit 1 Million Zeilen stattfindet, bleibt der SLAVE wahrscheinlich dort hängen Der SLAVE ist eine Single-Threaded-Struktur, die zu Synchronisationsverzögerungen führt.

MySQL schreibt SQL und erstellt kleine Transaktions-SQL, die schnell ausgeführt und schnell übermittelt werden kann. Lassen Sie jede Abfrage schneller abschließen und die Verbindung schneller freigeben.

Ist Ihre Datenbank nach der Optimierung basierend auf der oben genannten Freigabe schneller geworden?

Das obige ist der detaillierte Inhalt vonSo optimieren Sie Ihre Datenbank. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!