Wie können Sie die Anzahl der Datenbankanfragen verzehnfachen, ohne auf einen leistungsstärkeren Server umzuziehen und dabei die Systemstabilität zu gewährleisten? Ich werde Ihnen erzählen, wie wir mit dem Leistungsabfall unserer Datenbank umgegangen sind, wie wir SQL-Anfragen optimiert haben, um möglichst viele Benutzer zu bedienen, ohne die Kosten für Rechenressourcen zu erhöhen.
Ich entwickle einen Service zur Verwaltung von Geschäftsprozessen in Bauunternehmen. Etwa 3.000 Unternehmen arbeiten mit uns zusammen. Über 10.000 Menschen nutzen unser System täglich 4-10 Stunden lang. Es löst verschiedene Aufgaben in den Bereichen Planung, Benachrichtigung, Warnung und Validierung… Wir verwenden PostgreSQL 9.6. In unserer Datenbank gibt es etwa 300 Tabellen, und täglich gehen bis zu 200 Millionen Anfragen (10.000 verschiedene) ein. Im Durchschnitt haben wir 3-4 Tausend Anfragen pro Sekunde, in den aktivsten Momenten über 10.000 Anfragen pro Sekunde. Der Großteil der Anfragen sind OLAP. Neuzugänge, Modifikationen und Löschungen sind deutlich weniger, das heißt, die OLTP-Belastung ist relativ gering. Ich habe diese Zahlen angeführt, damit Sie das Ausmaß unseres Projekts einschätzen und verstehen können, wie wertvoll unsere Erfahrung für Sie sein könnte.
Erster Eindruck. Lyrisch
Als wir mit der Entwicklung begannen, dachten wir nicht wirklich darüber nach, welche Last auf die Datenbank zukommen würde und was wir tun würden, wenn der Server nicht mehr leistungsfähig genug ist. Bei der Gestaltung der Datenbank hielten wir uns an allgemeine Empfehlungen und versuchten, nicht in die falsche Richtung zu steuern. Aber über allgemeine Ratschläge wie "verwenden Sie kein Muster" hinaus, gingen wir nicht hinaus. Wir planen mit den Prinzipien der Normalisierung und vermeiden Datenredundanz, ohne uns um die Beschleunigung bestimmter Abfragen zu kümmern. Sobald die ersten Benutzer kamen, hatten wir mit Leistungsproblemen zu kämpfen. Wie immer waren wir dafür absolut unvorbereitet. Die ersten Probleme waren einfach zu lösen. In der Regel ließ sich alles durch das Hinzufügen eines neuen Index lösen. Aber es kam der Moment, als einfache Lösungen nicht mehr funktionierten. Als wir merkten, dass uns die Erfahrung fehlte und es immer schwieriger wurde zu verstehen, wo die Probleme lagen, stellten wir Spezialisten ein, die uns halfen, den Server richtig einzurichten, Monitoring einzurichten und uns zeigten, wo wir hinschauen sollten, um die .
Zweiter Eindruck. Statistisch
Wir haben also etwa 10.000 verschiedene Anfragen, die täglich in unserer Datenbank verarbeitet werden. Unter diesen 10.000 Anfragen gibt es "Monsteranfragen", die 2-3 Millionen Mal ausgeführt werden, mit einer durchschnittlichen Ausführungszeit von 0,1-0,3 ms, und es gibt Anfragen mit einer durchschnittlichen Ausführungszeit von 30 Sekunden, die 100 Mal pro Tag aufgerufen werden.
Es war nicht möglich, alle 10.000 Anfragen zu optimieren, deshalb haben wir beschlossen, zu analysieren, wo wir unsere Anstrengungen konzentrieren sollten, um die Leistung der Datenbank gezielt zu verbessern. Nach mehreren Iterationen begannen wir, die Anfragen nach Typen zu kategorisieren.
TOP Anfragen
Dies sind die anspruchsvollsten Anfragen, die die meiste Zeit in Anspruch nehmen (Gesamtzeit). Es handelt sich um Anfragen, die entweder sehr häufig aufgerufen werden oder solche, die sehr lange zur Verarbeitung benötigen (lange und häufige Anfragen wurden bereits in den ersten Iterationen der Geschwindigkeitsoptimierung bearbeitet). Insgesamt benötigt der Server somit die meiste Zeit für deren Ausführung. Es ist wichtig, die Top-Anfragen sowohl nach der Gesamt-Ausführungszeit als auch nach der IO-Zeit zu trennen. Die Optimierungsmethoden für solche Anfragen sind unterschiedlichen.
Es ist gängige Praxis unter Unternehmen, mit TOP-Anfragen zu arbeiten. Es sind nicht viele, aber die Optimierung auch nur einer Anfrage kann 5-10 % der Ressourcen freisetzen. Mit dem "Wachsen" eines Projekts wird die Optimierung der TOP-Anfragen jedoch zunehmend zu einer anspruchsvollen Aufgabe. Alle einfachen Methoden sind bereits ausgeschöpft, und selbst die "schwerste" Anfrage beansprucht lediglich 3-5 % der Ressourcen. Wenn die TOP-Anfragen insgesamt weniger als 30-40 % der Zeit in Anspruch nehmen, haben Sie wahrscheinlich bereits Anstrengungen unternommen, um diese schnell zu gestalten, und es ist an der Zeit, sich der Optimierung der Anfragen der nächsten Gruppe zu widmen.
Es bleibt die Frage zu beantworten, wie viele TOP-Anfragen in diese Gruppe aufgenommen werden sollen. Ich nehme normalerweise nicht weniger als 10, aber nicht mehr als 20. Ich achte darauf, dass die Ausführungszeit der ersten und der letzten Anfrage in der TOP-Gruppe sich nicht mehr als zehnmal unterscheidet. Das heißt, wenn die Ausführungszeit der Anfragen vom 1. Platz auf den 10. Platz drastisch sinkt, nehme ich die TOP-10; wenn der Rückgang allmählicher ist, erhöhe ich die Gruppengröße auf 15 oder 20.
Mittelständische Anfragen (medium)
Dies sind alle Abfragen, die unmittelbar nach TOP kommen, mit Ausnahme der letzten 5-10 %. In der Optimierung dieser Abfragen liegt oft die Möglichkeit, die Serverleistung erheblich zu steigern. Diese Abfragen können bis zu 80 % des Gesamtvolumens ausmachen. Wenn ihr Anteil jedoch über 50 % liegt, ist es an der Zeit, ihnen mehr Aufmerksamkeit zu schenken.
Schwanz (tail)
Wie bereits erwähnt, erfolgen diese Abfragen am Ende und benötigen 5-10 % der Zeit. Man kann sie ignorieren, es sei denn, man nutzt automatische Analysewerkzeuge, dann kann deren Optimierung ebenfalls kostengünstig sein.
Wie bewertet man jede Gruppe?
Ich verwende eine SQL-Abfrage, die hilft, eine solche Bewertung für PostgreSQL vorzunehmen (ich bin sicher, dass man für viele andere DBMS ähnliche Abfragen schreiben kann).
SQL-Abfrage zur Bewertung der Größe von TOP-MEDIUM-TAIL-Gruppen
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn 20 AND rn 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER() OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Das Ergebnis der Abfrage zeigt drei Spalten, von denen jede den Prozentsatz der Zeit anzeigt, die für die Verarbeitung von Anfragen aus dieser Gruppe aufgewendet wird. Innerhalb der Abfrage gibt es zwei Zahlen (in meinem Fall 20 und 800), die die Anfragen einer Gruppe von einer anderen trennen.
So stehen die Anteile der Anfragen zum Zeitpunkt des Beginns der Optimierung und jetzt in etwa zueinander.
Aus dem Diagramm geht hervor, dass der Anteil der TOP-Anfragen deutlich gesunken ist, während die „mittelmäßigen“ Anfragen zugenommen haben.
Anfangs wurden in die TOP-Anfragen offenbare Fehler aufgenommen. Mit der Zeit verschwanden die Kinderkrankheiten, und der Anteil der TOP-Anfragen nahm ab. Es war erforderlich, immer mehr Anstrengungen zu unternehmen, um die schweren Anfragen zu beschleunigen.
Um den Text der Anfragen zu erhalten, verwenden wir folgende Abfrage:
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn 20 AND rn 800 -- TAIL
Hier ist eine Liste der am häufigsten verwendeten Techniken, die uns geholfen haben, die TOP-Anfragen zu beschleunigen:
- Neugestaltung des Systems, beispielsweise die Überarbeitung der Logik der Benachrichtigungen auf einen Message Broker anstelle von regelmäßigen Anfragen an die Datenbank.
- Hinzufügen oder Ändern von Indizes.
- Umformulierung von ORM-Anfragen in reines SQL.
- Umstellung der Logik für das Lazy Loading von Daten
- Caching durch Denormalisierung von Daten. Zum Beispiel haben wir eine Beziehung zwischen den Tabellen Lieferung -> Rechnung -> Anfrage -> Antrag. Das heißt, jede Lieferung ist über andere Tabellen mit dem Antrag verbunden. Um nicht in jeder Abfrage alle Tabellen zu verknüpfen, haben wir den Verweis auf den Antrag in der Tabelle Lieferung dupliziert.
- Caching von statischen Tabellen mit Verzeichnissen und seltenen Tabellen im Programmspeicher.
Manchmal führten Änderungen zu einem signifikanten Redesign, brachten jedoch nur 5-10% Entlastung des Systems und waren gerechtfertigt. Mit der Zeit wurde die Ausbeute immer geringer, während ein ernsthafteres Redesign erforderlich wurde.
Dann wendeten wir uns der zweiten Gruppe von Anfragen zu – der Gruppe der Mittelfeldspieler. In dieser Gruppe gibt es deutlich mehr Anfragen, und es schien, als würde die Analyse der gesamten Gruppe sehr viel Zeit in Anspruch nehmen. Dennoch erwiesen sich die meisten Anfragen als sehr einfach zu optimieren, und viele Probleme traten in unterschiedlichen Variationen dutzende Male auf. Hier sind Beispiele einiger typischer Optimierungen, die wir auf Dutzende ähnlicher Anfragen anwendeten, und jede Gruppe optimierter Anfragen entlastete die Datenbank um 3-5%.
- Anstelle der Überprüfung der Verfügbarkeit von Datensätzen mit COUNT und eines vollständigen Tabellenscans verwenden wir jetzt EXISTS.
- Wir haben DISTINCT entfernt (es gibt kein allgemeines Rezept, aber manchmal kann man leicht darauf verzichten und die Abfrage um das 10-100-Fache beschleunigen).
Zum Beispiel anstelle einer Abfrage zur Auswahl aller Fahrer aus einer großen Lieferdatenbank (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDstellten wir die Abfrage auf die vergleichsweise kleine Tabelle PERSON um.
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Es scheint, dass wir eine korrelierte Unterabfrage verwendet haben, aber diese führt zu einer Beschleunigung von mehr als dem 10-fachen.
- In vielen Fällen haben wir COUNT ganz weggelassen und
- statt
UPPER(s) LIKE 'JOHN%'VPS KVM
s ILIKE 'John%'
Wir konnten jede spezifische Anfrage manchmal um das 3- bis 1000-fache beschleunigen. Trotz der beeindruckenden Ergebnisse schien es uns zunächst wenig sinnvoll, eine Anfrage zu optimieren, die in 10 ms ausgeführt wird, zu den 300 schwersten Anfragen gehört und am Gesamtaufwand für die Datenbank nur einen Bruchteil Prozent ausmacht. Doch durch die Anwendung derselben Methode auf eine Gruppe ähnlicher Anfragen konnten wir mehrere Prozentpunkte zurückgewinnen. Um keine Zeit mit der manuellen Durchsicht aller Hunderten von Anfragen zu verlieren, haben wir einige einfache Skripte geschrieben, die mithilfe von regulären Ausdrücken ähnliche Anfragen gefunden haben. Letztendlich ermöglichte die automatische Suche nach Anfragegruppen eine weitere Verbesserung unserer Leistung mit bescheidenem Aufwand.
Insgesamt arbeiten wir seit drei Jahren mit der gleichen Hardware. Die durchschnittliche Auslastung liegt bei etwa 30 %, in Spitzenzeiten erreichen wir bis zu 70 %. Die Anzahl der Anfragen sowie die der Nutzer sind ungefähr um das Zehnfache gestiegen. Das verdanken wir dem kontinuierlichen Monitoring dieser speziellen Anfragen der Gruppe TOP-MEDIUM. Sobald eine neue Anfrage in der TOP-Gruppe auftaucht, analysieren wir sie sofort und versuchen, die Antwortzeiten zu verbessern. Die MEDIUM-Gruppe überprüfen wir wöchentlich mit Hilfe von Analyse-Skripten. Wenn wir auf neue Anfragen stoßen, die wir bereits optimieren können, nehmen wir sofort Anpassungen vor. Manchmal entdecken wir neue Optimierungsmethoden, die wir sofort auf mehrere Anfragen anwenden können.
Laut unseren Prognosen wird der aktuelle Server in der Lage sein, die Anzahl der Benutzer nochmals um das 3- bis 5-fache zu erhöhen. Allerdings haben wir noch einen weiteren Trumpf im Ärmel – wir haben die SELECT-Abfragen noch nicht auf das Spiegel-System umgestellt, wie es empfohlen wird. Aber wir machen das bewusst nicht, da wir zunächst die Möglichkeiten der "intelligenten" Optimierung vollständig ausschöpfen möchten, bevor wir auf die "schwere Artillerie" zurückgreifen.
Ein kritischer Blick auf die geleistete Arbeit kann nahelegen, vertikale Skalierung in Betracht zu ziehen. Statt die Zeit der Spezialisten zu verschwenden, könnte man einen leistungsstärkeren Server erwerben. Server müssen nicht teuer sein, insbesondere da unsere Grenzen für vertikale Skalierung noch nicht erreicht sind. Allerdings ist die Anzahl der Anfragen nur um das Zehnfache gestiegen. Im Laufe der Jahre hat sich die Funktionalität des Systems erheblich erhöht, und die Vielfalt der Anfragen ist gewachsen. Die vorhandene Funktionalität wird durch Caching mit weniger, zudem effizienteren Anfragen durchgeführt. Das bedeutet, dass man getrost den Wert um das Fünffache multiplizieren kann, um den tatsächlichen Beschleunigungsfaktor zu ermitteln. Somit lässt sich konservativ sagen, dass die Beschleunigung bei 50 und mehr liegt. Eine vertikale Aufrüstung des Servers um das 50-fache wäre teurer gewesen. Besonders wenn man bedenkt, dass einmalige Optimierungen dauerhaft wirken, während die Rechnung für den gemieteten Server jeden Monat eintrifft.
Quelle: habr.com
