Optimiséieren Datebank Ufroen mam Beispill vun engem B2B Service fir Builder

Wéi wuessen 10 Mol d'Zuel vun Ufroen an d'Datebank ouni op e méi produktive Server ze plënneren an d'Systemfunktionalitéit z'erhalen? Ech wäert Iech soen wéi mir de Réckgang vun der Leeschtung vun eiser Datebank behandelt hunn, wéi mir SQL Ufroen optimiséiert hunn fir sou vill Benotzer wéi méiglech ze déngen an net d'Käschte vun de Rechenressourcen ze erhéijen.

Ech maachen e Service fir Geschäftsprozesser an Baufirmen ze managen. Ongeféier 3 dausend Firmen schaffen mat eis. Méi wéi 10 dausend Leit schaffen all Dag fir 4-10 Stonnen mat eisem System. Et léist verschidde Problemer vu Planung, Notifikatioun, Warnung, Validatioun ... Mir benotzen PostgreSQL 9.6. Mir hunn ongeféier 300 Dëscher an der Datebank a bis zu 200 Millioune Ufroen (10 dausend verschidden) ginn all Dag kritt. Am Duerchschnëtt hu mir 3-4 dausend Ufroen pro Sekonn, an den aktivsten Momenter méi wéi 10 Tausend Ufroen pro Sekonn. Déi meescht Ufroe sinn OLAP. Et gi vill manner Ergänzunge, Ännerungen an Läschen, dat heescht datt d'OLTP-Laascht relativ liicht ass. Ech hunn all dës Zuelen zur Verfügung gestallt fir datt Dir d'Skala vun eisem Projet beurteelt an ze verstoen wéi nëtzlech eis Erfahrung fir Iech ka sinn.

Eng Foto. Lyresch

Wann mir d'Entwécklung ugefaang hunn, hu mir net wierklech geduecht iwwer wéi eng Laascht op d'Datebank géif falen a wat mir maache wann de Server ophält ze zéien. Beim Design vun der Datebank hu mir allgemeng Empfehlungen gefollegt a probéiert eis net an de Fouss ze schéissen, awer sinn iwwer allgemeng Berodung gaang wéi "benotzt d'Muster net Entitéit Attributer Wäerter mir sinn net an. Mir hunn op Basis vun de Prinzipien vun der Normaliséierung entworf, Datenredundanz vermeit an et ass egal fir verschidde Ufroen ze beschleunegen. Soubal déi éischt Benotzer ukomm sinn, hu mir e Performanceproblem begéint. Mir ware wéi gewinnt komplett onpreparéiert dofir. Déi éischt Problemer hu sech als einfach erausgestallt. Als Regel gouf alles geléist andeems en neien Index derbäigesat gouf. Awer et ass eng Zäit komm wou einfache Patches opgehalen hunn ze schaffen. Mir realiséieren datt mir Erfahrung feelen an et gëtt ëmmer méi schwéier fir eis ze verstoen wat d'Problemer verursaacht, hu mir Spezialisten ugestallt, déi eis gehollef hunn de Server korrekt opzestellen, d'Iwwerwaachung ze verbannen, an eis gewisen hunn wou mir kucken fir ze kréien Statistiken.

Bild zwee. Statistesch

Also hu mir ongeféier 10 dausend verschidde Ufroen déi pro Dag op eiser Datebank ausgefouert ginn. Vun dësen 10 dausend ginn et Monsteren déi 2-3 Millioune Mol mat enger Moyenne Ausféierungszäit vun 0.1-0.3 MS ausgefouert ginn, an et ginn Ufroe mat enger Moyenne Ausféierungszäit vun 30 Sekonnen déi 100 Mol am Dag genannt ginn.

Et war net méiglech all 10 Tausend Ufroen ze optimiséieren, also hu mir beschloss erauszefannen, wou mir eis Efforte riichten fir d'Performance vun der Datebank korrekt ze verbesseren. No e puer Iteratiounen hu mir ugefaang Ufroen an Typen opzedeelen.

TOP Ufroen

Dëst sinn déi schwéierst Ufroen déi am meeschten Zäit huelen (Gesamtzäit). Dëst sinn Ufroen déi entweder ganz dacks genannt ginn oder Ufroen déi ganz laang daueren fir auszeféieren (laang a heefeg Ufroe goufen an den éischten Iteratiounen vum Kampf fir Geschwindegkeet optimiséiert). Als Resultat verbréngt de Server déi meescht Zäit op hir Ausféierung. Ausserdeem ass et wichteg Top Ufroen ze trennen duerch total Ausféierungszäit an getrennt duerch IO Zäit. D'Methoden fir sou Ufroen ze optimiséieren si liicht anescht.

Déi üblech Praxis vun all Firmen ass mat TOP Ufroen ze schaffen. Et gi wéineg vun hinnen; souguer eng Ufro optimiséieren kann 5-10% vun de Ressourcen befreien. Wéi och ëmmer, wéi de Projet reift, gëtt d'Optimiséierung vun TOP Ufroen eng ëmmer méi net-trivial Aufgab. All einfache Methode si scho ausgeschafft, an déi "schwéier" Ufro hëlt "nëmmen" 3-5% vun de Ressourcen. Wann TOP Ufroen am Ganzen manner wéi 30-40% vun der Zäit daueren, dann hues de héchstwahrscheinlech schonn Efforte gemaach fir se séier ze schaffen an et ass Zäit fir weider ze optimiséieren Ufroe vun der nächster Grupp.
Et bleift d'Fro ze beäntweren wéivill Top Ufroe sollen an dëser Grupp abegraff sinn. Ech huelen normalerweis op d'mannst 10, awer net méi wéi 20. Ech probéieren ze garantéieren datt d'Zäit vun der éischter an der leschter an der TOP Grupp net méi wéi 10 Mol ënnerscheet. Dat ass, wann d'Ausféierungszäit vun der Ufro schaarf vun der 1. Plaz op den 10. fällt, dann huelen ech TOP-10, wann de Réckgang méi graduell ass, erhéijen ech d'Gruppgréisst op 15 oder 20.

Mëttelbaueren

Dat sinn alles Ufroen déi direkt nom TOP kommen, mat Ausnam vun de leschten 5-10%. Normalerweis, bei der Optimisatioun vun dësen Ufroen läit d'Méiglechkeet fir d'Serverleistung staark ze erhéijen. Dës Ufroe kënne bis zu 80% weien. Awer och wann hiren Undeel 50% iwwerschratt ass, dann ass et Zäit se méi suergfälteg ze kucken.

Schwanz

Wéi scho gesot, dës Ufroe kommen um Enn an huelen 5-10% vun der Zäit. Dir kënnt se nëmmen vergiessen wann Dir keng automatesch Ufroanalyse Tools benotzt, da kann se optimiséieren och bëlleg sinn.

Wéi all Grupp ze bewäerten?

Ech benotzen eng SQL Ufro déi hëlleft esou eng Bewäertung fir PostgreSQL ze maachen (Ech si sécher datt eng ähnlech Ufro fir vill aner DBMSs geschriwwe ka ginn)

SQL Ufro fir d'Gréisst vun TOP-MEDIUM-TAIL Gruppen ze schätzen

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

D'Resultat vun der Ufro ass dräi Kolonnen, déi jidderee de Prozentsaz vun der Zäit enthält fir Ufroen aus dëser Grupp ze veraarbecht. Bannen an der Demande ginn et zwou Zuelen (a mengem Fall ass et 20 an 800) déi Ufroe vun enger Grupp vun engem aneren trennen.

Dëst ass wéi d'Aktie vun den Ufroen ongeféier vergläicht wéi d'Optimisatiounsaarbecht ugefaang huet an elo.

D'Diagramm weist datt den Undeel vun den TOP Ufroe staark erofgaang ass, awer d'"Mëttelbaueren" sinn eropgaang.
Am Ufank hunn déi TOP Ufroe blatant Feeler abegraff. Mat der Zäit si Kannerkrankheeten verschwonnen, den Undeel vun den TOP-Ufroen ass erofgaang, a méi a méi Efforte hu misse gemaach ginn fir schwéier Ufroen ze beschleunegen.

Fir den Text vun Ufroen ze kréien benotze mir déi folgend Ufro

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

Hei ass eng Lëscht vun de meescht benotzten Techniken déi eis gehollef hunn TOP Ufroen ze beschleunegen:

• Redesign vum System, zum Beispill d'Wiederaarbecht vun der Notifikatiounslogik mat engem Message Broker anstatt periodesch Ufroen an d'Datebank
• Indizes addéieren oder änneren
• Iwwerschreiwe ORM Ufroen op pure SQL
• Rewriting lazy Date Luede Logik
• Caching duerch Datenormaliséierung. Zum Beispill hu mir eng Tabellverbindung Liwwerung -> Rechnung -> Ufro -> Applikatioun. Dat ass, all Liwwerung ass mat enger Applikatioun duerch aner Dëscher verbonnen. Fir net all Dëscher an all Ufro ze verbannen, hu mir de Link op d'Ufro an der Liwwerungstabell duplizéiert.
• Caching statesch Dëscher mat Referenzbicher a selten Dëscher am Programmminne änneren.

Heiansdo hunn d'Ännerungen eng beandrockend Neigestaltung gemaach, awer si hunn 5-10% vun der Systemlaascht geliwwert a waren gerechtfäerdegt. Mat der Zäit gouf den Auspuff ëmmer méi kleng, a méi a méi eeschte Redesign war erfuerderlech.

Duerno hu mir eis op déi zweet Grupp vun Ufroen opmierksam gemaach - de Grupp vu Mëttelbaueren. Et gi vill méi Ufroen dran an et huet geschéngt datt et vill Zäit dauert fir de ganze Grupp ze analyséieren. Wéi och ëmmer, déi meescht Ufroe ware ganz einfach ze optimiséieren, a vill Probleemer goufen Dutzende Mol a verschiddene Variatiounen widderholl. Hei sinn Beispiller vun e puer typesch Optimisatiounen, déi mir op Dosende vun ähnlechen Ufroen applizéiert hunn an all Grupp vun optimiséierten Ufroen huet d'Datebank ëm 3-5% ofgelueden.

• Amplaz d'Präsenz vun records ze kontrolléieren mat COUNT an engem vollen Dësch Scan, EXISTS ugefaang ze benotzen
• Gitt vun DISTINCT lass (et gëtt keen allgemengt Rezept, awer heiansdo kënnt Dir et einfach lass kréien andeems Dir d'Ufro ëm 10-100 Mol beschleunegt).

  Zum Beispill, amplaz vun enger Ufro fir all Chauffeuren aus enger grousser Tabelle vu Liwwerungen ze wielen (DELIVERY)

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

  eng Ufro op eng relativ kleng Dësch PERSOUN gemaach

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Et géif schéngen datt mir eng korreléiert Ënnerquery benotzt hunn, awer et gëtt e Geschwindegkeet vu méi wéi 10 Mol.

• A ville Fäll war COUNT ganz opginn an
  duerch Berechnung vun ongeféier Wäert ersat
• anstatt

  UPPER(s) LIKE JOHN%’ 
  

  benotzen

  s ILIKE “John%”
  

All spezifesch Ufro gouf heiansdo 3-1000 Mol beschleunegt. Trotz der beandrockender Leeschtung, huet et eis am Ufank geschéngt datt et kee Sënn war fir eng Ufro ze optimiséieren déi 10 ms brauch fir ze kompletéieren, ass eng vun den 3. Awer andeems mir datselwecht Rezept op eng Grupp vu Ufroe vum selwechten Typ applizéieren, hu mir e puer Prozent zréckgewonnen. Fir keng Zäit ze verschwenden fir all Honnerte vu Ufroen manuell ze iwwerpréiwen, hu mir e puer einfache Skripte geschriwwen déi regulär Ausdréck benotzt hunn fir Ufroe vum selwechten Typ ze fannen. Als Resultat hunn automatesch Sichgruppen vu Ufroen eis erlaabt eis Leeschtung mat bescheidenen Effort weider ze verbesseren.

Als Resultat hu mir fir dräi Joer un der selwechter Hardware geschafft. Déi duerchschnëttlech deeglech Belaaschtung ass ongeféier 30%, a Spëtzen erreecht et 70%. D'Zuel vun den Ufroen, wéi och d'Zuel vun de Benotzer, ass ongeféier 10 Mol eropgaang. An dat alles dank der konstanter Iwwerwaachung vun deene selwechte Gruppen vun TOP-MEDIUM Ufroen. Soubal eng nei Ufro am TOP-Grupp erscheint, analyséiere mir se direkt a probéieren se ze beschleunegen. Mir iwwerpréiwen de MEDIUM Grupp eemol d'Woch mat Query Analyse Scripten. Wa mir op nei Ufroe kommen, déi mir scho wësse wéi se optimiséieren, änneren mir se séier. Heiansdo fanne mir nei Optimisatiounsmethoden, déi op e puer Ufroe gläichzäiteg applizéiert kënne ginn.

Laut eise Prognosen wäert den aktuellen Server eng Erhéijung vun der Zuel vun de Benotzer ëm 3-5 Mol widderstoen. Richteg, mir hunn nach een Ace op der Hülse - mir hunn nach ëmmer keng SELECT Ufroen op de Spigel transferéiert, wéi recommandéiert. Awer mir maachen dat net bewosst, well mir wëllen d'Méiglechkeeten vun der "Smart" Optimisatioun fir d'éischt komplett ausschalten, ier Dir déi "schwéier Artillerie" ofschalten.
E kritesche Bléck op d'Aarbecht gemaach ka proposéiere fir vertikal Skala ze benotzen. Kaaft e méi mächtege Server anstatt d'Zäit vu Spezialisten ze verschwenden. De Server kann net sou vill kaschten, besonnesch well mir d'Limite vun der vertikaler Skaléierung nach net erschöpft hunn. Allerdéngs ass nëmmen d'Zuel vun Demanden 10 Mol eropgaang. Am Laf vun e puer Joer ass d'Funktionalitéit vum System eropgaang an elo ginn et méi Aarte vun Ufroen. Dank Caching gëtt d'Funktionalitéit déi existéiert a manner Ufroen a méi effizient Ufroe gemaach. Dëst bedeit datt Dir sécher mat enger anerer 5 multiplizéieren kënnt fir den realen Beschleunigungskoeffizient ze kréien. Also, no de konservativen Schätzungen, kënne mir soen datt d'Beschleunegung 50 Mol oder méi war. Vertikal Schwéngung vun engem Server géif 50 Mol méi kaschten. Besonnesch bedenkt datt eemol d'Optimisatioun duerchgefouert gëtt et déi ganzen Zäit funktionnéiert, an d'Rechnung fir de gelounte Server kënnt all Mount.

Source: will.com