Eine SQL-Datenbank an ihrem Working Set und teuren Abfragen dimensionieren
Die Datenbankdimensionierung ist mehr als der Vergleich der Größe eines Datenverzeichnisses mit dem RAM eines Servers. Eine kleine Datenbank kann durch wiederholte Scans oder Sperrkonkurrenz schlecht abschneiden, während eine größere eine fokussierte Arbeitslast effizient bewältigen kann. Untersuchen Sie die Arbeit, die die Anwendung von der Datenbank verlangt, bevor Sie die nächste Ressourcenzuweisung wählen.
Das Wichtigste in Kürze
Identifizieren Sie teure und wiederholte Abfragen, prüfen Sie deren Pläne sicher, messen Sie Speicher und Parallelität gemeinsam und ändern Sie die Abfrage oder Ressource, die tatsächlich nützliche Arbeit begrenzt.
Beschreiben Sie die Arbeitslast, bevor Sie eine Größe wählen
Listen Sie Datenbank-Engine, Anwendung, Haupttabellen und wichtige Nutzerabläufe auf. Ein Berichtsjob, der historische Daten scannt, verhält sich anders als eine kleine Suche hinter jeder Webanfrage. Erfassen Sie normales Anfragevolumen, gleichzeitige Jobs, Datenwachstum und zeitkritische Vorgänge. Beziehen Sie Hintergrundaufgaben, Importe und Exporte ein: Sie können die geschäftigsten Zeiten erzeugen, selbst wenn der öffentliche Datenverkehr ruhig ist. Halten Sie Zugangsdaten und personenbezogene Daten aus den Dimensionierungsnotizen heraus, die Sie weitergeben.
Schätzen Sie den Speicher über die aktuellen Tabellendaten hinaus. Indizes, Transaktionsprotokolle, temporäre Arbeit, Wachstum und Wartung können alle Platz benötigen. Backups erfordern zudem ein bewusst gewähltes Ziel und ein Wiederherstellungsverfahren. Eine Speicherangabe auf einer Hosting-Karte ist die dem Dienst zugewiesene Kapazität, keine Aussage, dass jedes Byte von der Live-Datenbank belegt sein sollte. Prüfen Sie Trends beim freien Speicher und lassen Sie Raum für die Vorgänge, die Ihre Engine und Anwendung tatsächlich ausführen.
Das Working Set sind die Daten und Indizes, die eine Arbeitslast wiederholt berührt. Es kann viel kleiner als die vollständige historische Datenbank sein und sich ändern, wenn ein Bericht oder eine Aktion beliebt wird. Speicher hilft, wenn nützliche Seiten wiederverwendet werden können, aber eine pauschale Regel, dass alle Daten in RAM passen müssen, ist zu grob. Beobachten Sie Lesevorgänge, Cache-Verhalten und Anfragedauer bei repräsentativer Arbeit, statt einen universellen Speicherprozentsatz anzuwenden.
Finden Sie die Abfrage, die nützliche Zeit verbraucht
Beginnen Sie bei einem langsamen Anwendungsablauf und stellen Sie den Bezug zur Datenbankaktivität her. Eine außergewöhnlich langsame Abfrage ist sichtbar, doch eine bescheidene Abfrage, die hunderte Male wiederholt wird, kann insgesamt mehr Arbeit verursachen. Gruppieren Sie ähnliche Abfragen und vergleichen Sie Häufigkeit, Dauer, geprüfte oder zurückgegebene Zeilen sowie die Zeiträume, in denen Nutzer die Verzögerung bemerken. Nutzen Sie die Protokollierungs- und Diagnosemöglichkeiten Ihrer Engine mit angemessenem Zugriff und angemessener Aufbewahrung für potenziell sensible Abfragedaten.
Ein Ausführungsplan beschreibt, wie die Engine Daten voraussichtlich abruft und kombiniert. Lesen Sie Zugriffspfad, geschätzte Zeilen, Joins und Sortierarbeit im Zusammenhang. Ein Scan ist nicht automatisch falsch: Bei einer kleinen Tabelle oder einer breiten Abfrage kann er sinnvoll sein. Die Frage ist, ob die Arbeit dem Ergebnis entspricht, das die Anwendung benötigt. Vergleichen Sie geschätztes und beobachtetes Verhalten, wenn geeignete Werkzeuge verfügbar sind, und notieren Sie das genaue Schema und die Engine-Version.
| Symptom | Mögliche Ursache | Zu erhebende Belege |
|---|---|---|
| Viele verarbeitete Zeilen für ein kleines Ergebnis | Abfrageform oder fehlender nützlicher Index | Plan, Prädikate und geprüfte Zeilen |
| Verzögerung steigt bei gleichzeitigen Schreibvorgängen | Sperr- oder Transaktionskonflikt | Wartende Transaktionen und Transaktionsdauer |
| Wiederholte identische Abfragen | Wiederholung von Abfragen in der Anwendung | Gruppierte Abfragehäufigkeit und Request-Trace |
| Langsame große Sortierungen oder Berichte | Arbeitsdaten übersteigen den verfügbaren Speicher | Temporäre Arbeit, Speicherdruck und Berichtsgröße |
Seien Sie vorsichtig mit Werkzeugen, die eine Anweisung ausführen, während sie sie messen. PostgreSQL EXPLAIN ANALYZE führt die Abfrage aus, und datenändernde Anweisungen können Daten ändern, sofern die Untersuchung dieses Ergebnis nicht bewusst verhindert. Die Analysefunktionen von MySQL erfordern zudem das Verständnis der gewählten Anweisung und Version. Nutzen Sie nach Möglichkeit eine repräsentative Testumgebung. Fügen Sie niemals einen unbekannten Diagnosebefehl allein deshalb in die Produktion ein, weil er das Wort explain enthält.
Ändern Sie jeweils nur eine Ursache
Ein Index kann die für eine nützliche Suche benötigten Zeilen reduzieren, belegt aber auch Speicher und verursacht zusätzlichen Wartungsaufwand bei Schreibvorgängen. Gestalten Sie ihn anhand der tatsächlichen Prädikate und Sortierung und messen Sie dann die relevanten Lese- und Schreibvorgänge. Indizes auf jeder Spalte zu erstellen, erzeugt Kosten, ohne einen Nutzen zu belegen. Große Schema-Operationen benötigen einen eigenen Plan, da Sperrverhalten, temporärer Speicherplatz und Abschlusszeit von Engine, Operation und Arbeitslast abhängen.
Änderungen an der Anwendung können Arbeit entfernen, bevor sie die Datenbank erreicht. Rufen Sie nur benötigte Spalten ab, vermeiden Sie wiederholtes Abrufen derselben Beziehung und verwenden Sie eine bewusste Paginierungsstrategie für große Listen. Halten Sie Transaktionen fokussiert, damit sie keine Ressourcen belegen, während sie auf unabhängige Netzwerkaufrufe warten. Diese Entscheidungen verbessern oft sowohl die durchschnittliche Anfrage als auch die schlimmsten Konfliktphasen. Prüfen Sie sowohl das an Nutzer zurückgegebene Ergebnis als auch die verkürzte Abfragedauer.
Erhöhen Sie Ressourcen, wenn die Belege dies stützen. Mehr RAM kann einem nützlichen Arbeitsspeicherbedarf helfen, während zusätzliche CPU Arbeit unterstützen können, die tatsächlich parallel ausgeführt werden kann. Keine der beiden Änderungen löst automatisch eine lang gehaltene Sperre oder einen unnötigen vollständigen Scan. Vergleichen Sie bei der Wahl des Hostings die genaue Speicher-, Speicherplatz- und Prozessorzuweisung und beziehen Sie das Betriebssystem und andere Dienste ein, die sich die Maschine teilen. Dokumentieren Sie, warum diese Zuweisung zur gemessenen Arbeitslast passt.
Unter Nebenläufigkeit validieren und Wiederherstellung im Plan behalten
- Bewahren Sie Schema, Engine-Version und Ausgangsmessungen auf.
- Verwenden Sie repräsentative Datenverteilungen statt nur winziger Beispieltabellen.
- Vergleichen Sie dieselbe Abfrage und Arbeitslast nach einer kontrollierten Änderung.
- Messen Sie erfolgreiche Vorgänge, Latenz und Fehler während gleichzeitiger Arbeit.
- Überprüfen Sie Backups und ein Wiederherstellungsverfahren vor riskanten Wartungsarbeiten.
Ein Test mit einer Verbindung beschreibt nicht jede Produktionsspitze. Wiederholen Sie die relevante Arbeitslast mit realistischen gleichzeitigen Anfragen und geplanten Aufgaben und stoppen Sie, wenn die vereinbarten Fehler- oder Ressourcengrenzen erreicht sind. Verwenden Sie synthetische oder ordnungsgemäß geschützte Testdaten. Beziehen Sie ohne entsprechende Vereinbarung keinen externen Zahlungsdienst oder Systeme anderer Parteien in eine generierte Arbeitslast ein. Halten Sie den Lastgenerator ausreichend getrennt, damit seine eigenen Grenzen sichtbar sind.
Verwandeln Sie die Diagnose abschließend in einen kurzen Wartungseintrag. Bewahren Sie den Abfrage-Fingerprint, den Plan, die beobachtete Einschränkung, die gewählte Änderung und den Rollback-Pfad auf. Fügen Sie Speicherwachstum und Wiederherstellungsanforderungen zum nächsten Überprüfungstermin hinzu. Dies gibt künftigen Betreuern einen begründeten Ausgangspunkt, wenn sich die Arbeitslast ändert, statt einer rätselhaften Speichereinstellung oder eines Index, den niemand anzufassen wagt.
