Wie kann man unter der Anzahl der Datenbankabfragen zehnmal wachsen, ohne auf einen effizienteren Server zu wechseln und das System am Laufen zu halten? Ich werde Ihnen sagen, wie wir mit dem Leistungsabfall unserer Datenbank zu kämpfen hatten, wie wir SQL-Abfragen optimiert haben, um so viele Benutzer wie möglich zu bedienen und die Kosten für Computerressourcen nicht zu erhöhen.
Ich mache einen Service für das Management von Geschäftsprozessen in Bauunternehmen. Rund dreitausend Unternehmen arbeiten mit uns zusammen. Täglich arbeiten mehr als 10.000 Menschen 4-10 Stunden mit unserem System. Es löst verschiedene Aufgaben der Planung, Warnungen, Warnungen, Validierungen ... Wir verwenden PostgreSQL 9.6. Wir haben ungefähr 300 Tabellen in der Datenbank und jeden Tag werden bis zu 200 Millionen Anfragen (zehntausend verschiedene) an sie gesendet. Im Durchschnitt haben wir 3-4.000 Anfragen pro Sekunde, in den aktivsten Momenten mehr als 10.000 Anfragen pro Sekunde. Die meisten Anfragen sind OLAP. Es gibt viel weniger Hinzufügungen, Änderungen und Löschungen, dh die OLTP-Last ist relativ gering. Ich habe all diese Zahlen angegeben, damit Sie den Umfang unseres Projekts bewerten und verstehen können, wie nützlich unsere Erfahrung für Sie sein kann.
Das erste Bild. Lyrisch
Als wir mit der Entwicklung begannen, haben wir nicht wirklich darüber nachgedacht, welche Art von Last in der Datenbank sein wird und was wir tun werden, wenn der Server nicht mehr zieht. Beim Entwerfen der Datenbank haben wir die allgemeinen Empfehlungen befolgt und versucht, uns nicht in den Fuß zu schießen, aber über allgemeine Tipps wie „Verwenden Sie nicht das Muster für
Entitätsattributwerte , sind wir nicht gegangen. Entwickelt nach den Prinzipien der Normalisierung, um Datenredundanz zu vermeiden, und kümmerte sich nicht darum, bestimmte Abfragen zu beschleunigen. Sobald die ersten Benutzer ankamen, stießen wir auf ein Leistungsproblem. Darauf waren wir wie immer völlig unvorbereitet. Die ersten Probleme waren einfach. In der Regel wurde alles durch Hinzufügen eines neuen Index entschieden. Aber es kam eine Zeit, in der einfache Patches nicht mehr funktionierten. Nachdem wir festgestellt haben, dass nicht genügend Erfahrung vorhanden ist und es immer schwieriger wird, die Ursache der Probleme zu verstehen, haben wir Spezialisten eingestellt, die uns bei der korrekten Einrichtung des Servers und der Verbindungsüberwachung halfen und zeigten, wo nach
Statistiken gesucht werden muss .
Das zweite Bild. Statistisch
Wir haben also ungefähr 10.000 verschiedene Abfragen, die pro Tag in unserer Datenbank ausgeführt werden. Von diesen 10 Tausend gibt es Monster, die 2-3 Millionen Mal mit einer durchschnittlichen Laufzeit von 0,1 bis 0,3 ms laufen, und es gibt Abfragen mit einer durchschnittlichen Laufzeit von 30 Sekunden, die 100 Mal am Tag aufgerufen werden.
Es war nicht möglich, alle 10.000 Abfragen zu optimieren. Daher haben wir uns entschlossen, herauszufinden, wohin die Bemühungen gerichtet werden müssen, um die Datenbankleistung korrekt zu verbessern. Nach mehreren Iterationen begannen wir, Anforderungen in Typen zu unterteilen.
TOP-Anfragen
Dies sind die schwierigsten Abfragen, die am meisten Zeit in Anspruch nehmen (Gesamtzeit). Dies sind Abfragen, die entweder sehr häufig aufgerufen werden oder sehr lange dauern (lange und häufige Abfragen wurden bereits bei den ersten Iterationen des Kampfes um Geschwindigkeit optimiert). Infolgedessen verbringt der Server insgesamt die meiste Zeit mit seiner Ausführung. Darüber hinaus ist es wichtig, die obersten Anforderungen nach der Gesamtausführungszeit und getrennt nach der E / A-Zeit zu trennen. Die Möglichkeiten zur Optimierung solcher Abfragen unterscheiden sich geringfügig.
Die übliche Praxis aller Unternehmen besteht darin, mit TOP-Anfragen zu arbeiten. Es gibt nur wenige davon. Durch die Optimierung einer einzigen Anfrage können 5-10% der Ressourcen freigesetzt werden. Mit zunehmendem Alter des Projekts wird die Optimierung von TOP-Abfragen jedoch zu einer zunehmend nicht trivialen Aufgabe. Alle einfachen Methoden wurden bereits ausgearbeitet, und selbst die „schwierigste“ Anfrage nimmt „nur“ 3-5% der Ressourcen weg. Wenn 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, die Abfragen der nächsten Gruppe zu optimieren.
Es bleibt die Frage zu beantworten, wie viele Top-Abfragen in diese Gruppe aufgenommen werden sollen. Normalerweise nehme ich nicht weniger als 10, aber nicht mehr als 20. Ich versuche sicherzustellen, dass sich die Zeit des ersten und letzten in der TOP-Gruppe nicht mehr als 10 Mal unterscheidet. Das heißt, wenn die Ausführungszeit der Abfrage stark von 1 auf 10 sinkt, nehme ich TOP-10, wenn der Fall glatter ist, erhöhe ich die Gruppengröße auf 15 oder 20.
Die mittleren Bauern (mittel)
Dies sind alles Anfragen, die unmittelbar nach TOP gehen, mit Ausnahme der letzten 5-10%. In der Optimierung dieser speziellen Anforderungen liegt normalerweise die Fähigkeit, die Serverleistung erheblich zu steigern. Diese Abfragen können bis zu 80% „wiegen“. Aber selbst wenn ihr Anteil 50% überschritten hat, ist es Zeit, sie genauer zu betrachten.
Schwanz
Wie gesagt, diese Anfragen gehen am Ende und dauern 5-10% der Zeit. Sie können sie nur vergessen, wenn Sie keine automatischen Abfrageanalyse-Tools verwenden. Ihre Optimierung kann auch kostengünstig sein.
Wie bewerte ich jede Gruppe?
Ich verwende eine SQL-Abfrage, mit deren Hilfe eine solche Bewertung für PostgreSQL vorgenommen werden kann (ich bin sicher, dass Sie für viele andere DBMS eine ähnliche Abfrage schreiben können).
SQL-Abfrage zum Schätzen der Größe von TOP-MEDIUM-TAIL-GruppenSELECT 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 Abfrageergebnis besteht aus drei Spalten, von denen jede einen Prozentsatz der Zeit enthält, die für die Verarbeitung von Anforderungen aus dieser Gruppe aufgewendet wird. Innerhalb der Abfrage gibt es zwei Nummern (in meinem Fall 20 und 800), die Anforderungen von einer Gruppe von einer anderen trennen.
Auf diese Weise korrelieren die Anteile der Anforderungen zum Zeitpunkt des Beginns der Optimierungsarbeit nun grob.
Das Diagramm zeigt, dass der Anteil der TOP-Anfragen stark zurückgegangen ist, die „Mittelbauern“ jedoch gewachsen sind.
Anfänglich trafen TOP-Fehler auf TOP-Abfragen. Im Laufe der Zeit verschwanden Kinderkrankheiten, der Anteil der TOP-Anfragen wurde reduziert, und es mussten weitere Anstrengungen unternommen werden, um schwierige Anfragen zu beschleunigen.
Um den Text der Anfragen zu erhalten, verwenden wir eine solche 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
Hier ist eine Liste der am häufigsten verwendeten Tricks, die uns geholfen haben, TOP-Abfragen zu beschleunigen:
- Neugestaltung von Systemen, z. B. Verarbeitung der Benachrichtigungslogik auf dem Nachrichtenbroker anstelle von regelmäßigen Datenbankabfragen
- Hinzufügen oder Ändern von Indizes
- Schreiben Sie ORM-Abfragen in reinem SQL neu
- Schreiben Sie die Logik zum Laden fauler Daten neu
- Caching durch Daten-Denormalisierung. Zum Beispiel haben wir eine Verknüpfung zwischen den Tabellen Lieferung -> Rechnung -> Anfrage -> Anfrage. Das heißt, jede Lieferung ist der Anwendung über andere Tabellen zugeordnet. Um nicht alle Tabellen in jeder Anforderung zu verknüpfen, haben wir den Link zur Anwendung in der Übermittlungstabelle dupliziert.
- Zwischenspeichern statischer Tabellen mit Verzeichnissen und seltenes Ändern von Tabellen im Programmspeicher.
Manchmal führten die Änderungen zu einer beeindruckenden Neugestaltung, aber sie machten 5-10% des Entladens des Systems aus und waren gerechtfertigt. Mit der Zeit wurde der Auspuff immer weniger und die Neugestaltung musste immer ernster werden.
Dann machten wir auf die zweite Gruppe von Anfragen aufmerksam - die Gruppe der Mittelbauern. Es hat viel mehr Anfragen und es schien, als würde es viel Zeit in Anspruch nehmen, die gesamte Gruppe zu analysieren. Die meisten Abfragen erwiesen sich jedoch als sehr einfach für die Optimierung, und viele Probleme wurden Dutzende Male in verschiedenen Variationen wiederholt. Hier sind Beispiele für einige typische Optimierungen, die wir auf Dutzende ähnlicher Abfragen angewendet haben, und jede Gruppe optimierter Abfragen hat die Datenbank um 3-5% entladen.
- Anstatt mit COUNT und einem vollständigen Tabellenscan zu prüfen, ob Datensätze vorhanden sind, wurde EXISTS verwendet.
- Wir haben DISTINCT losgeworden (es gibt kein allgemeines Rezept, aber manchmal können Sie es leicht loswerden, indem Sie die Anfrage 10-100 Mal beschleunigen).
Anstatt beispielsweise abzufragen, alle Treiber in einer großen Liefertabelle auszuwählen (LIEFERUNG)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
beantragte einen relativ kleinen PERSON-Tisch
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 korrelierende Unterabfrage verwendet haben, aber sie ergibt eine Beschleunigung von mehr als dem Zehnfachen.
- In vielen Fällen zählen COUNT und
ersetzt durch die Berechnung des ungefähren Wertes
- statt
UPPER(s) LIKE JOHN%'
verwenden
s ILIKE “John%”
Jede spezifische Anfrage wurde manchmal um das 3-1000-fache beschleunigt. Trotz der beeindruckenden Leistung schien es uns zunächst sinnlos, die Abfrage zu optimieren, die 10 ms lang ausgeführt wurde und in den dreihundert der schwersten Abfragen und in der gesamten Ladezeit der Datenbank Hundertstel Prozent dauerte. Aber wenn wir dasselbe Rezept auf eine Gruppe ähnlicher Anfragen anwenden, haben wir mehrere Prozent zurückgewonnen. Um keine Zeit damit zu verschwenden, alle Hunderte von Abfragen manuell anzuzeigen, haben wir mehrere einfache Skripte geschrieben, die mithilfe regulärer Ausdrücke ähnliche Abfragen fanden. Die automatische Suche nach Abfragegruppen ermöglichte es uns daher, unsere Leistung durch bescheidene Anstrengungen weiter zu verbessern.
Aus diesem Grund arbeiten wir seit drei Jahren an derselben Hardware. Die durchschnittliche tägliche Belastung beträgt ca. 30%, bei Spitzenwerten 70%. Die Anzahl der Anfragen sowie die Anzahl der Benutzer hat sich um das Zehnfache erhöht. Und das alles dank der ständigen Überwachung dieser Gruppen von TOP-MEDIUM-Abfragen. Sobald eine neue Anfrage in der TOP-Gruppe erscheint, analysieren wir sie sofort und versuchen, sie zu beschleunigen. Wir überprüfen die MEDIUM-Gruppe einmal pro Woche mithilfe von Abfrageanalyse-Skripten. Wenn Sie auf neue Anforderungen stoßen, die wir bereits optimieren können, ändern wir diese schnell. Manchmal finden wir neue Optimierungsmethoden, die auf mehrere Abfragen gleichzeitig angewendet werden können.
Nach unseren Prognosen wird der aktuelle Server einer Erhöhung der Benutzeranzahl um das weitere 3-5-fache standhalten. Wir haben zwar noch einen Trumpf im Ärmel, aber wir haben SELECT-Abfragen noch nicht wie empfohlen in den Spiegel übersetzt. Dies tun wir jedoch nicht bewusst, da wir zunächst die Möglichkeiten der „intelligenten“ Optimierung vollständig ausschöpfen wollen, bevor wir die „schwere Artillerie“ einschalten.
Ein kritischer Blick auf die geleistete Arbeit kann die Verwendung einer vertikalen Skalierung vorschlagen. Kaufen Sie einen leistungsstärkeren Server, anstatt die Zeit von Spezialisten zu verschwenden. Der Server kostet möglicherweise nicht so viel, zumal die Grenzen der vertikalen Skalierung noch nicht ausgeschöpft sind. Allerdings hat sich nur die Anzahl der Anfragen verzehnfacht. Seit einigen Jahren hat die Funktionalität des Systems zugenommen und jetzt gibt es mehr Arten von Anfragen. Die Funktionalität, die durch das Caching verursacht wurde, wird von weniger Anforderungen ausgeführt, außerdem von effizienteren Anforderungen. Sie können also sicher mit weiteren 5 multiplizieren, um den tatsächlichen Beschleunigungskoeffizienten zu erhalten. Nach den konservativsten Schätzungen können wir also sagen, dass die Beschleunigung das 50-fache oder mehr betrug. Ein 50-maliges vertikales Schütteln des Servers würde mehr kosten. Vor allem, wenn man bedenkt, dass die Optimierung immer durchgeführt wird und jeden Monat eine Rechnung für einen gemieteten Server ausgestellt wird.