Optimierung von Datenbankabfragen am Beispiel eines B2B-Services für Bauunternehmer

Wie kann man die Anzahl der Datenbankabfragen um das Zehnfache steigern, ohne auf einen leistungsstärkeren Server umzuziehen und die Funktionalität des Systems aufrechtzuerhalten? Ich werde erklären, wie wir mit dem Leistungsabfall unserer Datenbank umgegangen sind, wie wir SQL-Abfragen 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. Täglich nutzen über 10.000 Personen unser System für 4–10 Stunden. Es löst verschiedene Aufgaben wie Planung, Benachrichtigungen, Warnungen, Validierungen... Wir verwenden PostgreSQL 9.6. Unsere Datenbank enthält etwa 300 Tabellen, und täglich kommen bis zu 200 Millionen Anfragen (10.000 unterschiedliche) hinzu. Im Durchschnitt haben wir 3-4.000 Anfragen pro Sekunde, zu den aktivsten Zeiten mehr als 10.000 Anfragen pro Sekunde. Der Großteil der Anfragen ist OLAP. Hinzufügungen, Modifikationen und Löschungen sind deutlich weniger, d.h. die OLTP-Belastung ist relativ gering. Ich habe all diese Zahlen angegeben, damit Sie den Umfang unseres Projekts verstehen und einschätzen können, wie nützlich unsere Erfahrungen für Sie sein könnten.

Bild eins. Lyrisch

Als wir mit der Entwicklung begannen, dachten wir kaum darüber nach, welche Last auf der Datenbank liegen würde und was wir tun würden, wenn der Server nicht mehr mithalten kann. Bei der Planung der Datenbank hielten wir uns an allgemeine Empfehlungen und versuchten, uns nicht selbst in die Quere zu kommen, aber weiter als zu allgemeinen Ratschlägen wie ‚verwendet kein Muster‘ Entity Attribute Values sind wir nicht gegangen. Wir haben nach den Prinzipien der Normalisierung entworfen, um Datenredundanz zu vermeiden, und haben uns nicht um die Beschleunigung einzelner Abfragen gekümmert. Sobald die ersten Benutzer kamen, standen wir vor dem Leistungsproblem. Wie so oft waren wir darauf absolut nicht vorbereitet. Die ersten Probleme waren einfach. In der Regel wurde alles durch das Hinzufügen eines neuen Indexes gelöst. Aber es kam der Zeitpunkt, an dem einfache Lösungen nicht mehr funktionierten. Als wir erkannten, dass uns die Erfahrung fehlte und es uns immer schwerer fiel, die Ursachen der Probleme zu verstehen, stellten wir Spezialisten ein, die uns halfen, den Server richtig einzurichten, das Monitoring zu verbinden, und die uns zeigten, wohin wir schauen sollten, um die Statistik.

Bild zwei. Statistisch

Wir haben also etwa 10.000 verschiedene Abfragen, die täglich in unserer Datenbank ausgeführt werden. Von diesen 10.000 gibt es Monster, die 2-3 Millionen Mal mit einer durchschnittlichen Ausführungszeit von 0,1-0,3 ms ausgeführt werden, und es gibt Abfragen mit einer durchschnittlichen Ausführungszeit von 30 Sekunden, die 100 Mal am Tag aufgerufen werden.

Es war nicht möglich, alle 10.000 Abfragen zu optimieren, daher haben wir beschlossen, herauszufinden, wo wir unsere Anstrengungen lenken sollten, um die Datenbankleistung richtig zu steigern. Nach mehreren Iterationen fingen wir an, die Abfragen in Typen zu unterteilen.

TOP-Abfragen

Dies sind die schwersten Abfragen, die am meisten Zeit in Anspruch nehmen (Gesamtzeit). Es handelt sich um Abfragen, die entweder sehr häufig aufgerufen werden oder die sehr lange ausgeführt werden (langsame und häufige Abfragen wurden bereits in den ersten Iterationen des Geschwindigkeitskampfes optimiert). Insgesamt benötigt der Server am meisten Zeit für deren Ausführung. Dabei ist es wichtig, die Top-Abfragen nach der gesamten Ausführungszeit und separat nach IO-Zeit zu trennen. Die Methoden zur Optimierung solcher Abfragen sind etwas unterschiedlich.

Die gängige Praxis aller Unternehmen besteht darin, mit den TOP-Abfragen zu arbeiten. Es gibt nicht viele davon; selbst die Optimierung einer einzigen Abfrage kann 5-10% der Ressourcen freisetzen. Allerdings wird die Optimierung der TOP-Abfragen mit dem 'Wachstum' des Projekts immer weniger trivial. Alle einfachen Methoden wurden bereits angewendet, und die 'schwierigste' Abfrage benötigt 'nur' 3-5% der Ressourcen. Wenn die TOP-Abfragen insgesamt weniger als 30-40% der Zeit in Anspruch nehmen, haben Sie wahrscheinlich bereits Anstrengungen unternommen, um sie schnell zu gestalten, und es ist an der Zeit, mit der Optimierung der Abfragen aus der nächsten Gruppe zu beginnen.
Es bleibt die Frage zu beantworten, wie viele obere Abfragen in diese Gruppe aufgenommen werden sollen. Ich nehme normalerweise nicht weniger als 10, aber nicht mehr als 20. Ich achte darauf, dass sich die Ausführungszeit der ersten und der letzten in der TOP-Gruppe nicht um mehr als den Faktor 10 unterscheidet. Das heißt, wenn die Ausführungszeiten der Abfragen von Platz 1 auf Platz 10 stark sinken, nehme ich TOP-10, wenn das Sinken allmählicher ist, erhöhe ich die Gruppengröße auf 15 oder 20.

Durchschnittliche Abfragen (medium)

Das sind alle Abfragen, die unmittelbar hinter den TOP-Abfragen kommen, mit Ausnahme der letzten 5-10%. In der Optimierung dieser Abfragen steckt oft die Möglichkeit, die Serverleistung erheblich zu steigern. Diese Abfragen können bis zu 80% ausmachen. Aber selbst wenn ihr Anteil über 50% liegt, ist es Zeit, ihnen mehr Aufmerksamkeit zu schenken.

Schwanz (tail)

Wie bereits erwähnt, kommen diese Anfragen am Ende und benötigen 5-10% der Zeit. Man kann sie vergessen, es sei denn, Sie nutzen automatische Analysewerkzeuge für Anfragen, dann kann auch ihre Optimierung kostengünstig ausfallen.

Wie bewertet man jede Gruppe?

Ich verwende eine SQL-Abfrage, die hilft, eine solche Bewertung für PostgreSQL vorzunehmen (ich bin sicher, dass für viele andere DBMS eine ähnliche Abfrage geschrieben werden kann).

SQL-Abfrage zur Bewertung der Größe der 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 sind drei Spalten, von denen jede den Prozentsatz der Zeit enthält, der für die Verarbeitung der 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 ungefähr verhält es sich mit den Anteilen der Anfragen zu Beginn der Optimierungsarbeiten und heute.

Aus dem Diagramm ist ersichtlich, dass der Anteil der TOP-Anfragen stark gesunken ist, während die „Mittelmäßigen“ gestiegen sind.
Anfangs waren in den TOP-Anfragen offensichtliche Fehler dabei. Im Laufe der Zeit verschwanden die Kinderkrankheiten, der Anteil der TOP-Anfragen schrumpfte, und es war erforderlich, immer mehr Anstrengungen zu unternehmen, um die schweren Anfragen zu beschleunigen.

Um die Texte 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 häufigsten Methoden, die uns geholfen haben, die TOP-Anfragen zu beschleunigen:

• Neugestaltung des Systems, zum Beispiel die Überarbeitung der Logik für Benachrichtigungen auf einen Message Broker anstelle von regelmäßigen Anfragen an die DB.
• Hinzufügen oder Ändern von Indizes
• Umformulierung von ORM-Anfragen in reines SQL
• Umformulierung der Logik für das Lazy-Loading von Daten
• Caching durch Denormalisierung der Daten. Zum Beispiel haben wir eine Beziehung zwischen den Tabellen Lieferung -> Rechnung -> Anfrage -> Antrag. Das heißt, jede Lieferung ist über andere Tabellen mit einem Antrag verbunden. Um nicht in jeder Anfrage alle Tabellen verknüpfen zu müssen, haben wir die Verknüpfung zum Antrag in der Tabelle Lieferung dupliziert.
• Caching von statischen Tabellen mit Verzeichnissen und weniger häufig ändernden Tabellen im Programmspeicher.

Manchmal führten Änderungen zu einem erheblichen Redesign, was jedoch eine Systementlastung von 5-10 % brachte und gerechtfertigt war. Mit der Zeit wurde der Nutzen immer geringer, und ein ernsthafteres Redesign wurde erforderlich.

Dann richteten wir unsere Aufmerksamkeit auf die zweite Gruppe von Anfragen – die Gruppe der Durchschnittsanfragen. In ihr gab es deutlich mehr Anfragen, und es schien, dass die Analyse der gesamten Gruppe sehr viel Zeit in Anspruch nehmen würde. Dennoch erwiesen sich die meisten Anfragen als sehr einfach zu optimieren, und viele Probleme wiederholten sich in verschiedenen Variationen dutzende Male. Hier sind einige Beispiele für typische Optimierungen, die wir auf Dutzende ähnlicher Anfragen angewendet haben, wobei jede Gruppe optimierter Anfragen die DB um 3-5 % entlastete.

• Anstatt die Existenz von Datensätzen mit COUNT zu überprüfen und die gesamte Tabelle zu scannen, begannen wir EXISTS zu verwenden.
• Wir haben auf DISTINCT verzichtet (es gibt kein allgemeines Rezept, aber manchmal kann man leicht darauf verzichten und die Anfrage um das 10-100-fache beschleunigen).

  Zum Beispiel anstelle einer Anfrage zur Auswahl aller Fahrer aus einer großen Liefertabelle (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  erstellten wir eine Anfrage über die vergleichsweise kleine Tabelle PERSON.

  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 schien zwar, als hätten wir eine korrelierende Unterabfrage verwendet, aber sie sorgte für eine Beschleunigung von mehr als 10-fach.

• In vielen Fällen haben wir COUNT ganz aufgegeben und
  es durch die Berechnung eines ungefähren Wertes ersetzt.
• anstatt

  UPPER(s) LIKE JOHN%’ 
  

  verwenden wir

  s ILIKE “John%”
  

Es gelang uns, jede einzelne Anfrage manchmal um das 3-1000-fache zu beschleunigen. Trotz beeindruckender Ergebnisse schien es uns anfangs, dass eine Optimierung von Anfragen, die in 10 ms ausgeführt werden und zu den 300 schwersten Anfragen gehören, sowie einen minimalen Anteil an der Gesamtbelastung der DB ausmachen, keinen Sinn macht. Doch indem wir dasselbe Rezept auf eine Gruppe ähnlicher Anfragen anwendeten, konnten wir mehrere Prozent zurückgewinnen. Um keine Zeit mit der manuellen Überprüfung aller Hunderten von Anfragen zu verbringen, schrieben wir mehrere einfache Skripte, die mithilfe von regulären Ausdrücken ähnliche Anfragen fanden. Letztendlich ermöglichte uns die automatische Suche nach Anfragegruppen eine noch größere Verbesserung unserer Leistung bei bescheidenem Aufwand.

Insgesamt arbeiten wir nun seit drei Jahren mit derselben Hardware. Die durchschnittliche tägliche Auslastung liegt bei etwa 30 %, in Spitzenzeiten erreicht sie bis zu 70 %. Die Anzahl der Anfragen und der Benutzer ist etwa 10 Mal gestiegen. Und das alles dank der ständigen Überwachung dieser Gruppen von TOP-MEDIUM-Anfragen. Sobald eine neue Anfrage in der TOP-Gruppe auftaucht, analysieren wir sie sofort und versuchen, sie zu beschleunigen. Die MEDIUM-Gruppe überprüfen wir einmal pro Woche mit Skripten zur Analyse der Anfragen. Wenn wir neue Anfragen finden, von denen wir bereits wissen, wie wir sie optimieren können, ändern wir sie schnell. Manchmal entdecken wir neue Optimierungsmöglichkeiten, die wir sofort auf mehrere Anfragen anwenden können.

Nach unseren Prognosen wird der aktuelle Server eine weitere Erhöhung der Benutzeranzahl um das 3- bis 5-Fache aushalten. Allerdings haben wir noch ein Ass im Ärmel – wir haben die SELECT-Anfragen noch nicht auf das Spiegel-Server übertragen, wie es empfohlen wird. Aber das tun wir bewusst nicht, da wir zunächst die Möglichkeiten der „intelligenten“ Optimierung vollständig ausschöpfen möchten, bevor wir die „schwere Artillerie“ einsetzen.
Ein kritischer Blick auf die geleistete Arbeit könnte darauf hinweisen, vertikales Scaling zu nutzen. Einen leistungsstärkeren Server kaufen, anstatt die Zeit der Spezialisten zu verschwenden. Ein Server kann nicht so teuer sein, zumal die Grenzen des vertikalen Scalings bei uns noch nicht ausgeschöpft sind. Allerdings ist die Anzahl der Anfragen nur um das 10-fache gestiegen. Über die Jahre hat sich die Funktionalität des Systems erhöht, und jetzt gibt es mehr Sorten von Anfragen. Die Funktionalität, die vorhanden war, wird durch Caching mit weniger Anfragen, und zwar mit effektiveren Anfragen, ausgeführt. Das bedeutet, dass man noch zusätzlich mit 5 multiplizieren kann, um den realen Beschleunigungsfaktor zu erhalten. Somit kann man bescheiden sagen, dass die Beschleunigung 50 Mal oder mehr beträgt. Ein vertikales Hochstufen des Servers um 50 Mal wäre teurer gewesen. Besonders wenn man bedenkt, dass eine einmal durchgeführte Optimierung immer funktioniert, während die Rechnung für den gemieteten Server jeden Monat kommt.

Quelle: habr.com