{"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 Datenbankabfragen am Beispiel eines B2B-Services f\u00fcr Bauunternehmer","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Wie kann man die Anzahl der Datenbankabfragen um das Zehnfache steigern, ohne auf einen leistungsst\u00e4rkeren Server umzuziehen und die Funktionalit\u00e4t des Systems aufrechtzuerhalten? Ich werde erkl\u00e4ren, wie wir mit dem Leistungsabfall unserer Datenbank umgegangen sind, wie wir SQL-Abfragen 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. T\u00e4glich nutzen \u00fcber 10.000 Personen unser System f\u00fcr 4\u201310 Stunden. Es l\u00f6st verschiedene Aufgaben wie Planung, Benachrichtigungen, Warnungen, Validierungen... Wir verwenden PostgreSQL 9.6. Unsere Datenbank enth\u00e4lt etwa 300 Tabellen, und t\u00e4glich 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\u00dfteil der Anfragen ist OLAP. Hinzuf\u00fcgungen, Modifikationen und L\u00f6schungen 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\u00e4tzen k\u00f6nnen, wie n\u00fctzlich unsere Erfahrungen f\u00fcr Sie sein k\u00f6nnten.<\/p>\n

Bild eins. Lyrisch<\/h3>\n

\nAls wir mit der Entwicklung begannen, dachten wir kaum dar\u00fcber nach, welche Last auf der Datenbank liegen w\u00fcrde und was wir tun w\u00fcrden, 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\u00e4gen wie \u201averwendet kein Muster\u2018 Entity Attribute Values<\/a><\/noindex> 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\u00fcmmert. 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\u00fcgen eines neuen Indexes gel\u00f6st. Aber es kam der Zeitpunkt, an dem einfache L\u00f6sungen 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<\/a><\/noindex>.<\/p>\n

Bild zwei. Statistisch<\/h3>\n

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

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

TOP-Abfragen<\/h4>\n

\nDies sind die schwersten Abfragen, die am meisten Zeit in Anspruch nehmen (Gesamtzeit). Es handelt sich um Abfragen, die entweder sehr h\u00e4ufig aufgerufen werden oder die sehr lange ausgef\u00fchrt werden (langsame und h\u00e4ufige Abfragen wurden bereits in den ersten Iterationen des Geschwindigkeitskampfes optimiert). Insgesamt ben\u00f6tigt der Server am meisten Zeit f\u00fcr deren Ausf\u00fchrung. Dabei ist es wichtig, die Top-Abfragen nach der gesamten Ausf\u00fchrungszeit und separat nach IO-Zeit zu trennen. Die Methoden zur Optimierung solcher Abfragen sind etwas unterschiedlich.<\/p>\n

Die g\u00e4ngige 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\u00f6tigt '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\u00e4chsten Gruppe zu beginnen.
\nEs 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\u00fchrungszeit der ersten und der letzten in der TOP-Gruppe nicht um mehr als den Faktor 10 unterscheidet. Das hei\u00dft, wenn die Ausf\u00fchrungszeiten der Abfragen von Platz 1 auf Platz 10 stark sinken, nehme ich TOP-10, wenn das Sinken allm\u00e4hlicher ist, erh\u00f6he ich die Gruppengr\u00f6\u00dfe auf 15 oder 20.
\n\"Optimierung
\n<\/p>\n

Durchschnittliche Abfragen (medium)<\/h4>\n

\nDas 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\u00f6glichkeit, die Serverleistung erheblich zu steigern. Diese Abfragen k\u00f6nnen bis zu 80% ausmachen. Aber selbst wenn ihr Anteil \u00fcber 50% liegt, ist es Zeit, ihnen mehr Aufmerksamkeit zu schenken.<\/p>\n

Schwanz (tail)<\/h4>\n

\nWie bereits erw\u00e4hnt, kommen diese Anfragen am Ende und ben\u00f6tigen 5-10% der Zeit. Man kann sie vergessen, es sei denn, Sie nutzen automatische Analysewerkzeuge f\u00fcr Anfragen, dann kann auch ihre Optimierung kosteng\u00fcnstig ausfallen.<\/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 f\u00fcr viele andere DBMS eine \u00e4hnliche Abfrage geschrieben werden kann).<\/p>\n

SQL-Abfrage zur Bewertung der Gr\u00f6\u00dfe der 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 sind drei Spalten, von denen jede den Prozentsatz der Zeit enth\u00e4lt, der f\u00fcr 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.<\/p>\n
So ungef\u00e4hr verh\u00e4lt es sich mit den Anteilen der Anfragen zu Beginn der Optimierungsarbeiten und heute.<\/p>\n
\"Optimierung
\n
\nAus dem Diagramm ist ersichtlich, dass der Anteil der TOP-Anfragen stark gesunken ist, w\u00e4hrend die \u201eMittelm\u00e4\u00dfigen\u201c gestiegen sind.
\nAnfangs 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. <\/p>\n
Um die Texte 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 h\u00e4ufigsten Methoden, die uns geholfen haben, die TOP-Anfragen zu beschleunigen:<\/p>\n