Optimalizace databázových dotazů na příkladu B2B služby pro stavitele

Jak 10násobně zvýšit počet dotazů do databáze bez přechodu na produktivnější server a zachovat funkčnost systému? Řeknu vám, jak jsme se vypořádali s poklesem výkonu naší databáze, jak jsme optimalizovali SQL dotazy, aby sloužily co největšímu počtu uživatelů a nezvyšovaly náklady na výpočetní prostředky.

Dělám službu pro řízení obchodních procesů ve stavebních firmách. Spolupracuje s námi asi 3 tisíce firem. Více než 10 tisíc lidí pracuje s naším systémem každý den po dobu 4-10 hodin. Řeší různé problémy plánování, upozornění, varování, validace... Používáme PostgreSQL 9.6. V databázi máme asi 300 tabulek a denně přijímáme až 200 milionů dotazů (10 tisíc různých). V průměru máme 3-4 tisíce požadavků za sekundu, v nejaktivnějších okamžicích více než 10 tisíc požadavků za sekundu. Většina dotazů je OLAP. Existuje mnohem méně přidání, úprav a odstranění, což znamená, že zatížení OLTP je relativně malé. Všechna tato čísla jsem uvedl, abyste mohli posoudit rozsah našeho projektu a pochopit, jak užitečné pro vás mohou být naše zkušenosti.

Obrázek jedna. Lyrický

Když jsme začínali s vývojem, moc jsme nepřemýšleli o tom, jaká zátěž dopadne na databázi a co bychom dělali, kdyby se server přestal stahovat. Při navrhování databáze jsme se řídili obecnými doporučeními a snažili jsme se nestřílet do nohy, ale šli jsme nad rámec obecných rad jako „nepoužívejte vzor Hodnoty atributu entity nešli jsme dovnitř. Navrhovali jsme na principech normalizace, vyvarovali se redundance dat a nestarali jsme se o zrychlení některých dotazů. Jakmile dorazili první uživatelé, narazili jsme na problém s výkonem. Jako obvykle jsme na to nebyli připraveni. První problémy se ukázaly být jednoduché. Vše se zpravidla vyřešilo přidáním nového indexu. Ale přišla doba, kdy jednoduché záplaty přestaly fungovat. Uvědomili jsme si, že nám chybí zkušenosti a že je pro nás stále obtížnější pochopit, co je příčinou problémů, najali jsme specialisty, kteří nám pomohli server správně nastavit, připojit monitoring a ukázali nám, kde hledat statistika.

Obrázek dva. Statistický

Máme tedy asi 10 tisíc různých dotazů, které jsou v naší databázi provedeny denně. Z těchto 10 tisíc jsou monstra, která se spouštějí 2-3 miliony krát s průměrnou dobou provádění 0.1-0.3 ms, a jsou dotazy s průměrnou dobou provádění 30 sekund, které jsou volány 100krát za den.

Nebylo možné optimalizovat všech 10 tisíc dotazů, a tak jsme se rozhodli vymyslet, kam nasměrovat naše úsilí, abychom výkon databáze vylepšili správně. Po několika iteracích jsme začali požadavky rozdělovat do typů.

TOP požadavky

Toto jsou nejtěžší dotazy, které zaberou nejvíce času (celkový čas). Jedná se o dotazy, které jsou buď volány velmi často, nebo dotazy, jejichž provedení trvá velmi dlouho (dlouhé a časté dotazy byly optimalizovány v prvních iteracích boje o rychlost). Výsledkem je, že server tráví nejvíce času jejich prováděním. Kromě toho je důležité oddělit nejvyšší požadavky podle celkové doby provedení a samostatně podle doby IO. Metody optimalizace takových dotazů se mírně liší.

Obvyklou praxí všech firem je práce s TOP požadavky. Je jich málo, optimalizace byť jednoho dotazu může uvolnit 5-10 % zdrojů. Jak však projekt dospívá, stává se optimalizace TOP dotazů stále méně triviálním úkolem. Všechny jednoduché metody již byly vypracovány a „nejtěžší“ požadavek zabírá „pouze“ 3–5 % zdrojů. Pokud TOP dotazy celkem zabírají méně než 30-40 % času, pak jste se s největší pravděpodobností již snažili, aby fungovaly rychle, a je čas přejít k optimalizaci dotazů z další skupiny.
Zbývá odpovědět na otázku, kolik top dotazů by mělo být zahrnuto do této skupiny. Většinou beru minimálně 10, maximálně však 20. Snažím se, aby se čas prvního a posledního v TOP skupině nelišil maximálně 10x. To znamená, že pokud doba provádění dotazu prudce klesne z 1. místa na 10., pak beru TOP-10, pokud je pokles pozvolnější, pak zvětším velikost skupiny na 15 nebo 20.

Střední rolníci

To jsou všechny požadavky, které přicházejí bezprostředně po TOP, s výjimkou posledních 5-10%. Obvykle se v optimalizaci těchto dotazů skrývá příležitost výrazně zvýšit výkon serveru. Tyto požadavky mohou vážit až 80 %. Ale i když jejich podíl přesáhl 50 %, pak je čas se na ně podívat pozorněji.

Ocas

Jak již bylo zmíněno, tyto dotazy přicházejí na konci a zabírají 5–10 % času. Zapomenout na ně můžete pouze v případě, že nepoužíváte nástroje pro automatickou analýzu dotazů, pak může být levná i jejich optimalizace.

Jak hodnotit jednotlivé skupiny?

Používám SQL dotaz, který pomáhá provést takové hodnocení pro PostgreSQL (jsem si jistý, že podobný dotaz lze napsat pro mnoho dalších DBMS)

SQL dotaz pro odhad velikosti skupin 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ýsledkem dotazu jsou tři sloupce, z nichž každý obsahuje procento času potřebného ke zpracování dotazů z této skupiny. Uvnitř požadavku jsou dvě čísla (v mém případě je to 20 a 800), která oddělují požadavky z jedné skupiny od druhé.

Takto se zhruba porovnávají podíly požadavků v době zahájení optimalizačních prací a nyní.

Diagram ukazuje, že podíl TOP požadavků prudce klesl, ale „střední rolníci“ se zvýšili.
Zpočátku mezi TOP požadavky patřily nehorázné hrubé chyby. Postupem času mizely dětské nemoci, klesal podíl TOP žádostí a bylo třeba stále více usilovat o urychlení obtížných požadavků.

K získání textu žádostí používáme následující žádost

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

Zde je seznam nejčastěji používaných technik, které nám pomohly urychlit TOP dotazy:

• Přepracování systému, například přepracování logiky oznámení pomocí zprostředkovatele zpráv namísto pravidelných dotazů do databáze
• Přidání nebo změna indexů
• Přepisování ORM dotazů na čisté SQL
• Přepisování líné logiky načítání dat
• Ukládání do mezipaměti prostřednictvím denormalizace dat. Máme například tabulkové spojení Doručení -> Faktura -> Žádost -> Žádost. To znamená, že každá dodávka je spojena s aplikací prostřednictvím jiných tabulek. Aby nedošlo k propojení všech tabulek v každém požadavku, duplikovali jsme odkaz na požadavek v tabulce Delivery.
• Ukládání statických tabulek s referenčními knihami a zřídka se měnících tabulek do paměti programu.

Někdy změny představovaly působivý redesign, ale poskytovaly 5–10 % zatížení systému a byly oprávněné. Postupem času se výfuk zmenšoval a zmenšoval a bylo vyžadováno stále vážnější přepracování.

Pak jsme obrátili svou pozornost na druhou skupinu žádostí – skupinu středních rolníků. Je v ní mnohem více dotazů a zdálo se, že analýza celé skupiny zabere spoustu času. Ukázalo se však, že optimalizace většiny dotazů je velmi jednoduchá a mnoho problémů se v různých obměnách opakovalo desítkykrát. Zde jsou příklady některých typických optimalizací, které jsme aplikovali na desítky podobných dotazů a každá skupina optimalizovaných dotazů uvolnila databázi o 3–5 %.

• Místo kontroly přítomnosti záznamů pomocí COUNT a úplného skenování tabulky se začalo používat EXISTS
• Zbavte se DISTINCT (obecný recept neexistuje, ale někdy se toho můžete snadno zbavit tak, že požadavek 10-100x urychlíte).

  Například místo dotazu na výběr všech ovladačů z velké tabulky dodávek (DORUČENÍ)

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

  provedl dotaz na relativně malý stůl 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)
  

  Zdálo by se, že jsme použili korelovaný poddotaz, ale zrychlí více než 10krát.

• V mnoha případech bylo COUNT úplně opuštěno a
  nahrazeno výpočtem přibližné hodnoty
• místo

  UPPER(s) LIKE JOHN%’ 
  

  použití

  s ILIKE “John%”
  

Každý konkrétní požadavek byl někdy zrychlen 3-1000krát. Navzdory působivému výkonu se nám zpočátku zdálo, že nemá smysl optimalizovat dotaz, který trvá 10 ms, je jedním z 3. stovky nejtěžších dotazů a zabírá setiny procenta celkového času načítání databáze. Ale použitím stejného receptu na skupinu dotazů stejného typu jsme získali pár procent zpět. Abychom neztráceli čas ručním prověřováním všech stovek dotazů, napsali jsme několik jednoduchých skriptů, které k nalezení dotazů stejného typu používaly regulární výrazy. V důsledku toho nám automatické vyhledávání skupin dotazů umožnilo dále zlepšovat náš výkon s mírným úsilím.

Výsledkem je, že již tři roky pracujeme na stejném hardwaru. Průměrná denní zátěž je asi 30 %, ve špičkách dosahuje 70 %. Počet požadavků, stejně jako počet uživatelů, se zvýšil přibližně 10krát. A to vše díky neustálému sledování těchto stejných skupin TOP-MEDIUM požadavků. Jakmile se v TOP skupině objeví nový požadavek, okamžitě jej rozebíráme a snažíme se jej urychlit. Skupinu MEDIUM kontrolujeme jednou týdně pomocí skriptů analýzy dotazů. Pokud narazíme na nové dotazy, které už umíme optimalizovat, rychle je změníme. Někdy najdeme nové optimalizační metody, které lze aplikovat na několik dotazů najednou.

Podle našich předpovědí současný server ustojí nárůst počtu uživatelů ještě 3-5x. Pravda, máme ještě jedno eso v rukávu – stále jsme nepřenesli SELECT dotazy do mirroru, jak se doporučuje. Ale neděláme to vědomě, protože chceme nejprve zcela vyčerpat možnosti „chytré“ optimalizace, než zapneme „těžké dělostřelectvo“.
Kritický pohled na vykonanou práci může navrhnout použití vertikálního škálování. Kupte si výkonnější server místo plýtvání časem specialistů. Server nemusí stát tolik, zvláště když jsme ještě nevyčerpali limity vertikálního škálování. Pouze počet žádostí se však zvýšil 10krát. V průběhu několika let se funkčnost systému zvýšila a nyní existuje více typů požadavků. Díky ukládání do mezipaměti je funkce, která existovala, prováděna s menším počtem požadavků a efektivnějšími požadavky. To znamená, že můžete bezpečně vynásobit dalšími 5, abyste získali skutečný koeficient zrychlení. Takže podle nejkonzervativnějších odhadů můžeme říci, že zrychlení bylo 50krát nebo více. Vertikální otočení serveru by stálo 50krát více. Zejména s ohledem na to, že jakmile je provedena optimalizace, funguje neustále a účet za pronajatý server přichází každý měsíc.

Zdroj: www.habr.com