Optimierung von Datenbankabfragen am Beispiel eines B2B-Service für Bauherren

Wie lässt sich die Anzahl der Datenbankabfragen um das Zehnfache steigern, ohne auf einen produktiveren Server zu wechseln und gleichzeitig die Systemfunktionalität aufrechtzuerhalten? Ich erzähle Ihnen, wie wir mit dem Leistungsabfall unserer Datenbank umgegangen sind und wie wir SQL-Abfragen optimiert haben, um möglichst viele Benutzer zu bedienen und die Kosten für Rechenressourcen nicht zu erhöhen.

Ich erstelle einen Service zur Verwaltung von Geschäftsprozessen in Bauunternehmen. Etwa 3 Unternehmen arbeiten mit uns zusammen. Mehr als 10 Menschen arbeiten täglich 4-10 Stunden lang mit unserem System. Es löst verschiedene Probleme der Planung, Benachrichtigung, Warnung, Validierung ... Wir verwenden PostgreSQL 9.6. Wir haben etwa 300 Tabellen in der Datenbank und täglich gehen bis zu 200 Millionen Anfragen (zehntausend verschiedene) ein. Im Durchschnitt haben wir 10-3 Anfragen pro Sekunde, in den aktivsten Momenten mehr als 4 Anfragen pro Sekunde. Die meisten Abfragen sind OLAP. Es gibt viel weniger Hinzufügungen, Änderungen und Löschungen, was bedeutet, dass die OLTP-Last relativ gering ist. Ich habe alle diese Zahlen angegeben, damit Sie den Umfang unseres Projekts einschätzen und verstehen können, wie nützlich unsere Erfahrung für Sie sein kann.

Bild eins. Lyrisch

Als wir mit der Entwicklung begannen, haben wir nicht wirklich darüber nachgedacht, welche Belastung auf die Datenbank zukommen würde und was wir tun würden, wenn der Server nicht mehr ziehen würde. Bei der Gestaltung der Datenbank haben wir allgemeine Empfehlungen befolgt und versucht, uns nicht selbst ins Bein zu schießen, sind aber über allgemeine Ratschläge wie „Verwenden Sie das Muster nicht“ hinausgegangen Entitätsattributwerte wir sind nicht reingegangen. Wir haben das Design auf der Grundlage der Prinzipien der Normalisierung entwickelt, um Datenredundanz zu vermeiden und uns nicht um die Beschleunigung bestimmter Abfragen zu kümmern. Sobald die ersten Benutzer eintrafen, stießen wir auf ein Leistungsproblem. Darauf waren wir wie üblich völlig unvorbereitet. Die ersten Probleme erwiesen sich als einfach. In der Regel wurde alles durch das Hinzufügen eines neuen Index gelöst. Aber es kam eine Zeit, in der einfache Patches nicht mehr funktionierten. Als wir erkannten, dass es uns an Erfahrung mangelt und es für uns immer schwieriger wird, die Ursache der Probleme zu verstehen, haben wir Spezialisten engagiert, die uns bei der korrekten Einrichtung des Servers und der Verbindungsüberwachung geholfen haben und uns gezeigt haben, wo wir suchen können Statistiken.

Bild zwei. Statistisch

Wir haben also etwa 10 verschiedene Abfragen, die pro Tag in unserer Datenbank ausgeführt werden. Von diesen 10 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 Abfragen zu optimieren, daher beschlossen wir herauszufinden, wohin wir unsere Bemühungen richten sollten, um die Leistung der Datenbank korrekt zu verbessern. Nach mehreren Iterationen begannen wir, Anfragen in Typen zu unterteilen.

TOP-Anfragen

Dies sind die schwersten Abfragen, die die meiste Zeit (Gesamtzeit) in Anspruch nehmen. Hierbei handelt es sich um Abfragen, die entweder sehr oft aufgerufen werden oder deren Ausführung sehr lange dauert (lange und häufige Abfragen wurden in den ersten Iterationen des Kampfes um Geschwindigkeit optimiert). Daher verbringt der Server die meiste Zeit mit der Ausführung. Darüber hinaus ist es wichtig, die Top-Anfragen nach Gesamtausführungszeit und separat nach E/A-Zeit zu trennen. Die Methoden zur Optimierung solcher Abfragen unterscheiden sich geringfügig.

Die übliche Praxis aller Unternehmen besteht darin, mit TOP-Anfragen zu arbeiten. Davon gibt es nur wenige; die Optimierung auch nur einer Abfrage kann 5–10 % der Ressourcen freisetzen. Mit zunehmender Reife des Projekts wird die Optimierung von TOP-Abfragen jedoch zu einer zunehmend nicht trivialen Aufgabe. Alle einfachen Methoden sind bereits ausgearbeitet und die „schwerste“ Anfrage beansprucht „nur“ 3-5 % der Ressourcen. Wenn die TOP-Abfragen insgesamt weniger als 30–40 % der Zeit in Anspruch nehmen, haben Sie höchstwahrscheinlich bereits Anstrengungen unternommen, damit sie schnell funktionieren, und es ist an der Zeit, mit der Optimierung der Abfragen der nächsten Gruppe fortzufahren.
Es bleibt die Frage zu beantworten, wie viele Top-Anfragen in diese Gruppe aufgenommen werden sollten. Normalerweise nehme ich mindestens 10, aber nicht mehr als 20. Ich versuche sicherzustellen, dass die Zeit des Ersten und Letzten in der TOP-Gruppe nicht mehr als das Zehnfache unterscheidet. Das heißt, wenn die Abfrageausführungszeit stark vom 10. auf den 1. Platz sinkt, dann nehme ich TOP-10, wenn der Rückgang eher allmählich erfolgt, erhöhe ich die Gruppengröße auf 10 oder 15.

Mittelbauern

Dies sind alle Anfragen, die unmittelbar nach TOP kommen, mit Ausnahme der letzten 5–10 %. Normalerweise liegt in der Optimierung dieser Abfragen die Möglichkeit, die Serverleistung erheblich zu steigern. Diese Anfragen können bis zu 80 % wiegen. Aber selbst wenn ihr Anteil über 50 % liegt, ist es an der Zeit, sie genauer unter die Lupe zu nehmen.

Schwanz

Wie bereits erwähnt, kommen diese Abfragen am Ende und nehmen 5–10 % der Zeit in Anspruch. Sie können sie nur vergessen, wenn Sie keine Tools zur automatischen Abfrageanalyse verwenden. Dann kann ihre Optimierung auch kostengünstig sein.

Wie wird jede Gruppe bewertet?

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

SQL-Abfrage zur Schätzung 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              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN 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, die jeweils den Prozentsatz der Zeit enthalten, die für die Verarbeitung von Abfragen dieser Gruppe benötigt wird. Innerhalb der Anfrage gibt es zwei Zahlen (in meinem Fall sind es 20 und 800), die Anfragen einer Gruppe von einer anderen unterscheiden.

So vergleichen sich ungefähr die Anteile der Anfragen zum Zeitpunkt des Beginns der Optimierungsarbeiten und heute.

Das Diagramm zeigt, dass der Anteil der TOP-Anfragen stark zurückgegangen ist, der Anteil der „Mittelbauern“ jedoch zugenommen hat.
Zu den TOP-Anfragen gehörten zunächst eklatante Patzer. Mit der Zeit verschwanden Kinderkrankheiten, der Anteil der TOP-Anfragen nahm ab und es mussten immer mehr Anstrengungen unternommen werden, um schwierige Anfragen zu beschleunigen.

Um den Text der Anfragen zu erhalten, verwenden wir die folgende Anfrage

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 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

Hier ist eine Liste der am häufigsten verwendeten Techniken, die uns dabei geholfen haben, TOP-Anfragen zu beschleunigen:

• Neugestaltung des Systems, beispielsweise Überarbeitung der Benachrichtigungslogik mithilfe eines Nachrichtenbrokers anstelle regelmäßiger Abfragen der Datenbank
• Indizes hinzufügen oder ändern
• Umschreiben von ORM-Abfragen in reines SQL
• Neuschreiben der Logik zum verzögerten Laden von Daten
• Caching durch Datendenormalisierung. Wir haben zum Beispiel eine Tabellenverbindung Lieferung -> Rechnung -> Anfrage -> Antrag. Das heißt, jede Lieferung ist über andere Tabellen einer Anwendung zugeordnet. Um nicht alle Tabellen in jeder Anfrage zu verknüpfen, haben wir den Link zur Anfrage in der Lieferungstabelle dupliziert.
• Statische Tabellen mit Nachschlagewerken zwischenspeichern und Tabellen selten im Programmspeicher ändern.

Manchmal kamen die Änderungen einem beeindruckenden Redesign gleich, aber sie verursachten 5-10 % der Systemlast und waren gerechtfertigt. Im Laufe der Zeit wurde der Auspuff immer kleiner und es waren immer größere Neukonstruktionen erforderlich.

Dann richteten wir unsere Aufmerksamkeit auf die zweite Gruppe von Forderungen – die Gruppe der Mittelbauern. Es enthält viel mehr Abfragen und es schien, als würde die Analyse der gesamten Gruppe viel Zeit in Anspruch nehmen. Allerdings erwiesen sich die meisten Abfragen als sehr einfach zu optimieren und viele Probleme wiederholten sich Dutzende Male in verschiedenen Variationen. Hier sind Beispiele einiger typischer Optimierungen, die wir auf Dutzende ähnlicher Abfragen angewendet haben und jede Gruppe optimierter Abfragen die Datenbank um 3–5 % entlastet hat.

• Anstatt mit COUNT und einem vollständigen Tabellenscan zu prüfen, ob Datensätze vorhanden sind, wurde EXISTS verwendet
• DISTINCT wurde entfernt (es gibt kein allgemeines Rezept, aber manchmal kann man es leicht entfernen, indem man die Anfrage um das 10- bis 100-fache beschleunigt).

  Anstelle einer Abfrage zum Beispiel alle Fahrer aus einer großen Liefertabelle auszuwählen (DELIVERY)

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

  Habe eine Abfrage für eine relativ kleine Tabelle PERSON durchgeführt

  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 sieht so aus, als hätten wir eine korrelierte Unterabfrage verwendet, aber sie führt zu einer Beschleunigung um mehr als das Zehnfache.

• In vielen Fällen wurde COUNT ganz aufgegeben und
  durch Berechnung des Näherungswertes ersetzt
• statt

  UPPER(s) LIKE JOHN%’ 
  

  Verwendung

  s ILIKE “John%”
  

Jede einzelne Anfrage wurde manchmal um das 3- bis 1000-fache beschleunigt. Trotz der beeindruckenden Leistung schien es uns zunächst sinnlos, eine Abfrage zu optimieren, deren Abschluss 10 ms dauert, eine der dreihundert schwersten Abfragen ist und Hundertstel Prozent der gesamten Datenbankladezeit in Anspruch nimmt. Aber indem wir dasselbe Rezept auf eine Gruppe von Abfragen desselben Typs anwendeten, konnten wir ein paar Prozent zurückgewinnen. Um keine Zeit damit zu verschwenden, alle Hunderte von Abfragen manuell zu überprüfen, haben wir mehrere einfache Skripte geschrieben, die reguläre Ausdrücke verwenden, um Abfragen desselben Typs zu finden. Dadurch konnten wir durch die automatische Suche nach Abfragegruppen unsere Leistung mit geringem Aufwand weiter verbessern.

Dadurch arbeiten wir nun schon seit drei Jahren an der gleichen Hardware. Die durchschnittliche Tagesbelastung beträgt etwa 30 %, in Spitzenzeiten erreicht sie 70 %. Die Anzahl der Anfragen sowie die Anzahl der Benutzer haben sich etwa verzehnfacht. Und das alles dank der ständigen Überwachung derselben Gruppen von TOP-MEDIUM-Anfragen. Sobald eine neue Anfrage in der TOP-Gruppe auftaucht, analysieren wir diese sofort und versuchen, sie zu beschleunigen. Wir überprüfen die MEDIUM-Gruppe einmal pro Woche mithilfe von Abfrageanalyseskripten. Wenn wir auf neue Anfragen stoßen, die wir bereits optimieren können, ändern wir sie schnell. Manchmal finden wir neue Optimierungsmethoden, die auf mehrere Abfragen gleichzeitig angewendet werden können.

Nach unseren Prognosen wird der aktuelle Server einer Steigerung der Benutzerzahl um das weitere 3- bis 5-fache standhalten. Allerdings haben wir noch ein Ass im Ärmel: Wir haben SELECT-Abfragen immer noch nicht wie empfohlen an den Spiegel übertragen. Wir tun dies aber nicht bewusst, denn wir wollen zunächst die Möglichkeiten der „intelligenten“ Optimierung komplett ausschöpfen, bevor wir die „schwere Artillerie“ angreifen.
Ein kritischer Blick auf die geleistete Arbeit könnte den Einsatz einer vertikalen Skalierung nahelegen. Kaufen Sie einen leistungsstärkeren Server, anstatt die Zeit von Spezialisten zu verschwenden. Der Server dürfte nicht so viel kosten, zumal wir die Grenzen der vertikalen Skalierung noch nicht ausgeschöpft haben. Allerdings stieg nur die Anzahl der Anfragen um das Zehnfache. Im Laufe der Jahre hat sich die Funktionalität des Systems erhöht und mittlerweile gibt es mehr Arten von Anfragen. Dank Caching wird die vorhandene Funktionalität in weniger Anfragen und effizienteren Anfragen ausgeführt. Das bedeutet, dass Sie problemlos mit weiteren 10 multiplizieren können, um den tatsächlichen Beschleunigungskoeffizienten zu erhalten. Nach den konservativsten Schätzungen können wir also sagen, dass die Beschleunigung das 5-fache oder mehr betrug. Das vertikale Schwenken eines Servers würde das 50-fache kosten. Vor allem wenn man bedenkt, dass die einmal durchgeführte Optimierung immer funktioniert und die Rechnung für den gemieteten Server jeden Monat kommt.

Source: habr.com