Ako zvýšiť 10-násobok počtu dopytov do databázy bez prechodu na produktívnejší server a zachovať funkčnosť systému? Poviem vám, ako sme sa vysporiadali s poklesom výkonu našej databázy, ako sme optimalizovali SQL dotazy, aby slúžili čo najväčšiemu počtu používateľov a nepredražovali výpočtové zdroje.
Robím službu pre riadenie obchodných procesov v stavebných firmách. Spolupracuje s nami asi 3 tisíc firiem. Viac ako 10 tisíc ľudí pracuje s naším systémom denne 4-10 hodín. Rieši rôzne problémy plánovania, notifikácie, varovania, validácie... Používame PostgreSQL 9.6. V databáze máme okolo 300 tabuliek a každý deň dostaneme až 200 miliónov dopytov (10 tisíc rôznych). V priemere máme 3-4 tisíc požiadaviek za sekundu, v najaktívnejších momentoch viac ako 10 tisíc požiadaviek za sekundu. Väčšina dopytov je OLAP. Existuje oveľa menej pridaní, úprav a vymazaní, čo znamená, že zaťaženie OLTP je relatívne malé. Všetky tieto čísla som uviedol, aby ste mohli posúdiť rozsah nášho projektu a pochopiť, aké užitočné môžu byť pre vás naše skúsenosti.
Obrázok jedna. Lyrický
Keď sme začínali s vývojom, v skutočnosti sme nepremýšľali o tom, aké zaťaženie by dopadlo na databázu a čo by sme urobili, keby server prestal ťahať. Pri navrhovaní databázy sme sa riadili všeobecnými odporúčaniami a snažili sme sa nestreliť si do nohy, ale išli sme nad rámec všeobecných rád ako „nepoužívajte vzor nevstúpili sme. Navrhovali sme na princípoch normalizácie, vyhýbali sme sa redundancii dát a nestarali sme sa o zrýchlenie niektorých dopytov. Hneď po príchode prvých používateľov sme narazili na problém s výkonom. Ako obvykle, boli sme na to úplne nepripravení. Prvé problémy sa ukázali byť jednoduché. Spravidla sa všetko vyriešilo pridaním nového indexu. Ale prišiel čas, keď jednoduché záplaty prestali fungovať. Uvedomili sme si, že nám chýbajú skúsenosti a je pre nás čoraz ťažšie pochopiť, čo spôsobuje problémy, najali sme špecialistov, ktorí nám pomohli správne nastaviť server, pripojiť monitorovanie a ukázali nám, kde hľadať .
Obrázok dva. Štatistické
Takže máme asi 10 tisíc rôznych dopytov, ktoré sa v našej databáze vykonávajú denne. Z týchto 10 tisíc sú príšery, ktoré sú spustené 2-3 milióny krát s priemerným časom vykonávania 0.1 až 0.3 ms a existujú dopyty s priemerným časom vykonávania 30 sekúnd, ktoré sa volajú 100 krát za deň.
Nebolo možné optimalizovať všetkých 10 XNUMX dopytov, preto sme sa rozhodli zistiť, kam nasmerovať naše úsilie, aby sa výkon databázy správne zlepšil. Po niekoľkých iteráciách sme začali požiadavky deliť na typy.
TOP žiadosti
Toto sú najťažšie dopyty, ktoré zaberajú najviac času (celkový čas). Ide o dopyty, ktoré sa volajú buď veľmi často, alebo dopyty, ktorých vykonanie trvá veľmi dlho (dlhé a časté dotazy boli optimalizované v prvých iteráciách boja o rýchlosť). Výsledkom je, že server trávi najviac času ich vykonávaním. Okrem toho je dôležité oddeliť najvyššie požiadavky podľa celkového času vykonania a samostatne podľa času IO. Metódy optimalizácie takýchto dopytov sa mierne líšia.
Bežnou praxou všetkých firiem je pracovať s TOP požiadavkami. Je ich málo, optimalizácia čo i len jedného dotazu môže uvoľniť 5 – 10 % zdrojov. Ako však projekt dospieva, optimalizácia TOP dopytov sa stáva čoraz menej triviálnou úlohou. Všetky jednoduché metódy už boli vypracované a „najťažšia“ požiadavka zaberá „iba“ 3 – 5 % zdrojov. Ak TOP dopyty celkovo zaberú menej ako 30-40% času, potom ste sa s najväčšou pravdepodobnosťou už snažili, aby fungovali rýchlo a je čas prejsť na optimalizáciu dopytov z ďalšej skupiny.
Zostáva zodpovedať otázku, koľko top dopytov by sa malo zaradiť do tejto skupiny. Väčšinou beriem aspoň 10, ale nie viac ako 20. Snažím sa, aby sa čas prvého a posledného v TOP skupine nelíšil viac ako 10-krát. To znamená, že ak čas vykonania dotazu prudko klesne z 1. miesta na 10., potom vezmem TOP-10, ak je pokles postupnejší, potom zväčším veľkosť skupiny na 15 alebo 20.
Strední roľníci
To všetko sú požiadavky, ktoré prídu hneď po TOP, s výnimkou posledných 5-10%. V optimalizácii týchto dopytov je zvyčajne príležitosť výrazne zvýšiť výkon servera. Tieto požiadavky môžu vážiť až 80 %. Ale aj keď ich podiel prekročil 50%, je čas pozrieť sa na nich pozornejšie.
Chvost
Ako už bolo spomenuté, tieto dopyty prichádzajú na konci a zaberajú 5-10 % času. Môžete na ne zabudnúť, iba ak nepoužívate nástroje na automatickú analýzu dopytov, ich optimalizácia môže byť tiež lacná.
Ako hodnotiť jednotlivé skupiny?
Používam SQL dotaz, ktorý pomáha vykonať takéto hodnotenie pre PostgreSQL (som si istý, že podobný dotaz možno napísať pre mnoho iných DBMS)
SQL dotaz na odhad veľkosti skupín TOP-MEDIUM-TAIL
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
Výsledkom dotazu sú tri stĺpce, z ktorých každý obsahuje percento času potrebného na spracovanie dotazov z tejto skupiny. Vo vnútri žiadosti sú dve čísla (v mojom prípade je to 20 a 800), ktoré oddeľujú požiadavky z jednej skupiny od druhej.
Takto sa približne porovnávajú podiely žiadostí v čase, keď sa optimalizačné práce začali, a teraz.
Diagram ukazuje, že podiel TOP žiadostí prudko klesol, ale zvýšili sa „strední roľníci“.
Najprv medzi TOP požiadavky patrili do očí bijúce hrubé chyby. Postupom času vymizli detské choroby, klesal podiel TOP požiadaviek a bolo treba vynaložiť stále väčšie úsilie na urýchlenie náročných požiadaviek.
Na získanie textu žiadostí používame nasledujúcu žiadosť
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
Tu je zoznam najčastejšie používaných techník, ktoré nám pomohli urýchliť TOP dopyty:
- Prepracovanie systému, napríklad prepracovanie logiky upozornení pomocou sprostredkovateľa správ namiesto pravidelných dotazov na databázu
- Pridanie alebo zmena indexov
- Prepisovanie ORM dotazov na čistý SQL
- Prepisovanie logiky lenivého načítania údajov
- Ukladanie do vyrovnávacej pamäte prostredníctvom denormalizácie údajov. Napríklad máme tabuľkové spojenie Doručenie -> Faktúra -> Žiadosť -> Žiadosť. To znamená, že každá dodávka je spojená s aplikáciou prostredníctvom iných tabuliek. Aby sme neprepojili všetky tabuľky v každej požiadavke, duplikovali sme prepojenie na požiadavku v tabuľke Delivery.
- Ukladanie statických tabuliek s referenčnými knihami a zriedkavo meniacich sa tabuliek do pamäte programu.
Zmeny niekedy predstavovali pôsobivý redizajn, ale poskytovali 5-10% zaťaženia systému a boli opodstatnené. Postupom času sa výfuk zmenšoval a bolo potrebné stále vážnejšie prerábať.
Potom sme obrátili našu pozornosť na druhú skupinu žiadostí – skupinu stredných roľníkov. Je v ňom oveľa viac dotazov a zdalo sa, že analýza celej skupiny zaberie veľa času. Ukázalo sa však, že optimalizácia väčšiny dopytov je veľmi jednoduchá a mnohé problémy sa opakovali desiatky krát v rôznych variáciách. Tu sú príklady niektorých typických optimalizácií, ktoré sme aplikovali na desiatky podobných dopytov a každá skupina optimalizovaných dopytov uvoľnila databázu o 3 – 5 %.
- Namiesto kontroly prítomnosti záznamov pomocou COUNT a skenovania celej tabuľky sa začalo používať EXISTS
- Zbavili ste sa DISTINCT (všeobecný recept neexistuje, ale niekedy sa ho môžete ľahko zbaviť zrýchlením požiadavky 10-100 krát).
Napríklad namiesto dotazu na výber všetkých ovládačov z veľkej tabuľky dodávok (DORUČENIE)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDzadal dotaz na relatívne malom stole PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Zdalo by sa, že sme použili korelovaný poddotaz, ale zrýchli sa viac ako 10-krát.
- V mnohých prípadoch sa od COUNT úplne upustilo a
- namiesto
UPPER(s) LIKE JOHN%’použitie
s ILIKE “John%”
Každá konkrétna požiadavka bola niekedy zrýchlená 3-1000 krát. Napriek pôsobivému výkonu sa nám spočiatku zdalo, že nemá zmysel optimalizovať dopyt, ktorý trvá 10 ms, je jedným z 3. stovky najťažších dopytov a zaberá stotiny percenta z celkového času načítania databázy. Aplikovaním rovnakého receptu na skupinu dopytov rovnakého typu sme však získali späť niekoľko percent. Aby sme nestrácali čas manuálnym prezeraním všetkých stoviek dopytov, napísali sme niekoľko jednoduchých skriptov, ktoré používali regulárne výrazy na nájdenie dopytov rovnakého typu. V dôsledku toho nám automatické vyhľadávanie skupín dopytov umožnilo ďalej zlepšovať našu výkonnosť s miernym úsilím.
Výsledkom je, že už tri roky pracujeme na rovnakom hardvéri. Priemerná denná záťaž je cca 30%, v špičkách dosahuje 70%. Počet žiadostí, ako aj počet používateľov sa zvýšil približne 10-krát. A to všetko vďaka neustálemu monitorovaniu práve týchto skupín TOP-MEDIUM požiadaviek. Akonáhle sa v skupine TOP objaví nová požiadavka, okamžite ju analyzujeme a snažíme sa ju urýchliť. Skupinu MEDIUM kontrolujeme raz týždenne pomocou skriptov analýzy dopytov. Ak narazíme na nové dopyty, ktoré už vieme optimalizovať, rýchlo ich zmeníme. Niekedy nájdeme nové optimalizačné metódy, ktoré je možné aplikovať na viacero dotazov naraz.
Podľa našich predpovedí súčasný server vydrží zvýšenie počtu používateľov o ďalších 3-5 krát. Pravda, máme ešte jedno eso v rukáve – stále sme nepreniesli SELECT dopyty do zrkadla, ako sa odporúča. Nerobíme to však vedome, pretože pred zapnutím „ťažkého delostrelectva“ chceme najskôr úplne vyčerpať možnosti „inteligentnej“ optimalizácie.
Kritický pohľad na vykonanú prácu môže navrhnúť použitie vertikálneho škálovania. Kúpte si výkonnejší server namiesto toho, aby ste strácali čas špecialistov. Server nemusí stáť tak veľa, najmä preto, že sme ešte nevyčerpali limity vertikálneho škálovania. Len počet žiadostí sa však zvýšil 10-krát. V priebehu niekoľkých rokov sa funkčnosť systému zvýšila a v súčasnosti existuje viac typov požiadaviek. Vďaka ukladaniu do vyrovnávacej pamäte sa funkčnosť, ktorá existovala, vykonáva v menšom počte požiadaviek a efektívnejšie. To znamená, že môžete pokojne vynásobiť ďalšími 5, aby ste získali skutočný koeficient zrýchlenia. Takže podľa najkonzervatívnejších odhadov môžeme povedať, že zrýchlenie bolo 50-krát alebo viac. Vertikálne otáčanie servera by stálo 50-krát viac. Najmä ak vezmeme do úvahy, že po vykonaní optimalizácie to funguje stále a účet za prenajatý server prichádza každý mesiac.
Zdroj: hab.com