{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/de\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimierung von Datenbankanfragen am Beispiel eines B2B-Dienstes f\u00fcr Bauunternehmen","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Wie k\u00f6nnen Sie die Anzahl der Datenbankanfragen verzehnfachen, ohne auf einen leistungsst\u00e4rkeren Server umzuziehen und dabei die Systemstabilit\u00e4t zu gew\u00e4hrleisten? Ich werde Ihnen erz\u00e4hlen, wie wir mit dem Leistungsabfall unserer Datenbank umgegangen sind, wie wir SQL-Anfragen optimiert haben, um m\u00f6glichst viele Benutzer zu bedienen, ohne die Kosten f\u00fcr Rechenressourcen zu erh\u00f6hen.
\n<\/a><\/noindex>
\nIch entwickle einen Service zur Verwaltung von Gesch\u00e4ftsprozessen in Bauunternehmen. Etwa 3.000 Unternehmen arbeiten mit uns zusammen. \u00dcber 10.000 Menschen nutzen unser System t\u00e4glich 4-10 Stunden lang. Es l\u00f6st verschiedene Aufgaben in den Bereichen Planung, Benachrichtigung, Warnung und Validierung\u2026 Wir verwenden PostgreSQL 9.6. In unserer Datenbank gibt es etwa 300 Tabellen, und t\u00e4glich gehen bis zu 200 Millionen Anfragen (10.000 verschiedene) ein. Im Durchschnitt haben wir 3-4 Tausend Anfragen pro Sekunde, in den aktivsten Momenten \u00fcber 10.000 Anfragen pro Sekunde. Der Gro\u00dfteil der Anfragen sind OLAP. Neuzug\u00e4nge, Modifikationen und L\u00f6schungen sind deutlich weniger, das hei\u00dft, die OLTP-Belastung ist relativ gering. Ich habe diese Zahlen angef\u00fchrt, damit Sie das Ausma\u00df unseres Projekts einsch\u00e4tzen und verstehen k\u00f6nnen, wie wertvoll unsere Erfahrung f\u00fcr Sie sein k\u00f6nnte.<\/p>\n

Erster Eindruck. Lyrisch<\/h3>\n

\nAls wir mit der Entwicklung begannen, dachten wir nicht wirklich dar\u00fcber nach, welche Last auf die Datenbank zukommen w\u00fcrde und was wir tun w\u00fcrden, wenn der Server nicht mehr leistungsf\u00e4hig genug ist. Bei der Gestaltung der Datenbank hielten wir uns an allgemeine Empfehlungen und versuchten, nicht in die falsche Richtung zu steuern. Aber \u00fcber allgemeine Ratschl\u00e4ge wie \"verwenden Sie kein Muster\" hinaus, Entity Attribute Values<\/a><\/noindex> gingen wir nicht hinaus. Wir planen mit den Prinzipien der Normalisierung und vermeiden Datenredundanz, ohne uns um die Beschleunigung bestimmter Abfragen zu k\u00fcmmern. Sobald die ersten Benutzer kamen, hatten wir mit Leistungsproblemen zu k\u00e4mpfen. Wie immer waren wir daf\u00fcr absolut unvorbereitet. Die ersten Probleme waren einfach zu l\u00f6sen. In der Regel lie\u00df sich alles durch das Hinzuf\u00fcgen eines neuen Index l\u00f6sen. Aber es kam der Moment, als einfache L\u00f6sungen 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 Statistiken<\/a><\/noindex>.<\/p>\n

Zweiter Eindruck. Statistisch<\/h3>\n

\nWir haben also etwa 10.000 verschiedene Anfragen, die t\u00e4glich in unserer Datenbank verarbeitet werden. Unter diesen 10.000 Anfragen gibt es \"Monsteranfragen\", die 2-3 Millionen Mal ausgef\u00fchrt werden, mit einer durchschnittlichen Ausf\u00fchrungszeit von 0,1-0,3 ms, und es gibt Anfragen mit einer durchschnittlichen Ausf\u00fchrungszeit von 30 Sekunden, die 100 Mal pro Tag aufgerufen werden.<\/p>\n

Es war nicht m\u00f6glich, 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.<\/p>\n

TOP Anfragen<\/h4>\n

\nDies sind die anspruchsvollsten Anfragen, die die meiste Zeit in Anspruch nehmen (Gesamtzeit). Es handelt sich um Anfragen, die entweder sehr h\u00e4ufig aufgerufen werden oder solche, die sehr lange zur Verarbeitung ben\u00f6tigen (lange und h\u00e4ufige Anfragen wurden bereits in den ersten Iterationen der Geschwindigkeitsoptimierung bearbeitet). Insgesamt ben\u00f6tigt der Server somit die meiste Zeit f\u00fcr deren Ausf\u00fchrung. Es ist wichtig, die Top-Anfragen sowohl nach der Gesamt-Ausf\u00fchrungszeit als auch nach der IO-Zeit zu trennen. Die Optimierungsmethoden f\u00fcr solche Anfragen sind unterschiedlichen.<\/p>\n

Es ist g\u00e4ngige 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\u00f6pft, 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\u00e4chsten Gruppe zu widmen.
\nEs 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\u00fchrungszeit der ersten und der letzten Anfrage in der TOP-Gruppe sich nicht mehr als zehnmal unterscheidet. Das hei\u00dft, wenn die Ausf\u00fchrungszeit der Anfragen vom 1. Platz auf den 10. Platz drastisch sinkt, nehme ich die TOP-10; wenn der R\u00fcckgang allm\u00e4hlicher ist, erh\u00f6he ich die Gruppengr\u00f6\u00dfe auf 15 oder 20.
\n\"Optimierung
\n<\/p>\n

Mittelst\u00e4ndische Anfragen (medium)<\/h4>\n

\nDies sind alle Abfragen, die unmittelbar nach TOP kommen, mit Ausnahme der letzten 5-10 %. In der Optimierung dieser Abfragen liegt oft die M\u00f6glichkeit, die Serverleistung erheblich zu steigern. Diese Abfragen k\u00f6nnen bis zu 80 % des Gesamtvolumens ausmachen. Wenn ihr Anteil jedoch \u00fcber 50 % liegt, ist es an der Zeit, ihnen mehr Aufmerksamkeit zu schenken.<\/p>\n

Schwanz (tail)<\/h4>\n

\nWie bereits erw\u00e4hnt, erfolgen diese Abfragen am Ende und ben\u00f6tigen 5-10 % der Zeit. Man kann sie ignorieren, es sei denn, man nutzt automatische Analysewerkzeuge, dann kann deren Optimierung ebenfalls kosteng\u00fcnstig sein.<\/p>\n

Wie bewertet man jede Gruppe?<\/p>\n

Ich verwende eine SQL-Abfrage, die hilft, eine solche Bewertung f\u00fcr PostgreSQL vorzunehmen (ich bin sicher, dass man f\u00fcr viele andere DBMS \u00e4hnliche Abfragen schreiben kann).<\/p>\n

SQL-Abfrage zur Bewertung der Gr\u00f6\u00dfe von TOP-MEDIUM-TAIL-Gruppen<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER() OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
Das Ergebnis der Abfrage zeigt drei Spalten, von denen jede den Prozentsatz der Zeit anzeigt, die f\u00fcr 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.<\/p>\n
So stehen die Anteile der Anfragen zum Zeitpunkt des Beginns der Optimierung und jetzt in etwa zueinander.<\/p>\n
\"Optimierung
\n
\nAus dem Diagramm geht hervor, dass der Anteil der TOP-Anfragen deutlich gesunken ist, w\u00e4hrend die \u201emittelm\u00e4\u00dfigen\u201c Anfragen zugenommen haben.
\nAnfangs 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. <\/p>\n
Um den Text der Anfragen zu erhalten, verwenden wir folgende Abfrage:<\/b><\/p>\n
SELECT * FROM (\n  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\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Hier ist eine Liste der am h\u00e4ufigsten verwendeten Techniken, die uns geholfen haben, die TOP-Anfragen zu beschleunigen:<\/p>\n