Príbeh jedného SQL vyšetrovania

V decembri minulého roka som dostal zaujímavú správu o chybe od tímu podpory VWO. Čas načítania jedného z analytických reportov pre veľkého firemného klienta sa zdalo byť neprimerane dlhé. Keďže je to oblasť mojej zodpovednosti, okamžite som sa zameral na riešenie problému.

Pozadie

Aby bolo jasné, o čom hovorím, poviem trochu o VWO. Je to platforma, pomocou ktorej môžete spúšťať rôzne cílené kampane na svojich stránkach: vykonávať A/B experimenty, sledovať návštevníkov a konverzie, robiť analýzu predajného funnelu, zobrazovať tepelné mapy a prehrávať záznamy návštev.

Ale najdôležitejšie na platforme je zostavovanie reportov. Všetky vyššie uvedené funkcie sú medzi sebou prepojené. A pre firemných klientov by obrovské množstvo informácií bolo jednoducho neužitočné bez silnej platformy, ktorá ich prezentuje vo forme pre analýzu.

Pomocou platformy môžete vytvoriť ľubovoľný dotaz na veľkom množstve dát. Tu je jednoduchý príklad:

Zobraziť všetky kliknutia na stránke "abc.com"
OD <dátumu d1> DO <dátumu d2>
pre ľudí, ktorí
používali Chrome ALEBO
(boli v Európe A používali iPhone)

Všimnite si logické operátory. Sú k dispozícii pre klientov v rozhraní dotazu, aby vytvárali dotazy ľubovoľnej zložitosti na získanie výberov.

Pomalý dotaz

Klient, o ktorom hovoríme, sa pokúšal urobiť niečo, čo intuitívne by malo pracovať rýchlo:

Zobraz všetky záznamy relácií
pre používateľov, ktorí navštívili akúkoľvek stránku
s URL, kde je "\/jobs"

Na tejto stránke bolo obrovské množstvo návštevnosti a uchovávali sme viac ako milión unikátnych URL adries len pre ňu. A chceli nájsť celkom jednoduchú šablónu URL, ktorá sa týka ich obchodného modelu.

Predbežné vyšetrovanie

Pozrime sa, čo sa deje v databáze. Nižšie je uvedený pôvodný pomalý SQL dotaz:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time =5 
    AND recording_data.num_of_pages > 0 ;

A tu sú časovania:

Plánovaný čas: 1,480 ms
Čas vykonávania: 1,431,924.650 ms

Dotaz prechádzal 150 tisíc riadkov. Plánovač dotazov ukázal pár zaujímavých detailov, ale žiadne očividné úzke miesta.

Poďme sa na tento dotaz pozrieť bližšie. Ako vidíte, vykonáva JOIN tri tabuľky:

  1. sessions: na zobrazenie sedenia informácií: prehliadač, používateľský agent, krajina a tak ďalej.
  2. recording_data: zaznamenané URL adresy, stránky, trvanie návštev
  3. urls: aby sme predišli duplicite mimoriadne veľkých URL adries, uchovávame ich v samostatnej tabuľke.

Tiež si všimnite, že všetky naše tabuľky už sú rozdelené podľa account_id. Takto je vylúčená situácia, keď kvôli jednému obzvlášť veľkému účtu majú problémy ostatní.

V hľadaní dôkazov

Pri bližšom skúmaní vidíme, že niečo v konkrétnom dopyte nie je v poriadku. Stojí za to pozrieť sa na tento riadok:

urls && array(
	select id from acc_{account_id}.urls 
	where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]

Prvá myšlienka bola, že možno kvôli ILIKE na všetkých týchto dlhých URL adresách (máme viac ako 1,4 milióna unikátnych URL adries, zozbieraných pre tento účet) môže byť výkon slabý.

Ale nie – problém nie je v tomto!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 riadkov)

Čas: 5,231.765 ms

Samotný dopyt vyhľadávania podľa vzoru trvá iba 5 sekúnd. Vyhľadávanie podľa vzoru na milióne unikátnych URL adresách zjavne nie je problém.

Ďalší podozrivý na zozname – niekoľko JOIN. Možno ich nadmerné používanie viedlo k spomaleniu? Zvyčajne JOIN‘y sú najzjavnejšími kandidátmi na problémy s výkonom, ale neveril som, že náš prípad je typový.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time = 5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 riadok)

Čas: 147.851 ms

A to takisto nebol náš prípad. JOIN‘y sa ukázali byť veľmi rýchle.

Zúžime okruh podozrivých

Bol som pripravený začať meniť dopyt na dosiahnutie akýchkoľvek možných zlepšení výkonu. S tímom sme vyvinuli 2 hlavné nápady:

  • Použiť EXISTS pre poddotaz URL: Chceli sme sa ešte raz presvedčiť, či nie sú problémy s poddotazom pre URL adresy. Jeden zo spôsobov, ako to dosiahnuť, je jednoducho použiť EXISTS. EXISTS môže výrazne zlepšiť výkon, keď sa dokončí okamžite, len čo nájde jediný riadok podľa podmienky.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time =5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Áno. Poddotaz, keď je zabalený do EXISTS, robí všetko super rýchlo. Ďalšia logická otázka je, prečo je dotaz s JOIN-ami a samotný poddotaz rýchle z individuálneho hľadiska, ale spolu sú hrozne pomalé?

  • Premiestnime poddotaz do CTE : ak je dotaz sám o sebe rýchly, môžeme jednoducho najprv vypočítať rýchly výsledok a potom ho predložiť hlavnému dotazu.

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time =5 
    AND recording_data.num_of_pages > 0;

Ale aj to bolo stále veľmi pomalé.

Nájdeme vinníka

Po celý čas sa pred očami objavoval jeden detail, od ktorého som sa neustále snažil odvrátiť. Ale keďže už nebolo nič iné, rozhodol som sa na neho pozrieť. Hovorím o && operátore. Kým EXISTS len zlepšil výkon, && bol jediným zostávajúcim spoločným faktorom vo všetkých verziách pomalého dotazu.

Pohľadom na dokumentácii, vidíme, že && sa používa, keď je potrebné nájsť spoločné prvky medzi dvoma políčkami.

V pôvodnom dotaze je to:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Čo znamená, že vykonávame vyhľadávanie podľa vzoru na našich URL, a potom nájdeme prienik so všetkými URL s spoločnými záznamami. Je to trochu zmätené, keďže "urls" tu odkazuje na stĺpec "urls" v tabuľke, nie na tabuľku, ktorá obsahuje všetky URL adresy. recording_data.

S rastúcim podozrením voči &&, snažil som sa nájsť potvrdenie v pláne dotazu vygenerovanom EXPLAIN ANALYZE (mal som už uložený plán, ale zvyčajne je pre mňa jednoduchšie experimentovať v SQL, než sa snažiť pochopiť neprehľadnosti plánovačov dotazov).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time = '5'::double precision) AND (num_of_pages > 0))
                           Odstránené riadky filtrom: 52710

Bolo tam niekoľko riadkov filtrov len z &&. Čo znamenalo, že táto operácia nebola len nákladná, ale aj vykonávala sa niekoľkokrát.

Skontroloval som to tým, že som izoloval podmienku

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Tento dotaz sa vykonával pomaly. Pretože JOIN-y sú rýchle a poddotazy sú rýchle, zostal iba && operátor.

Toto je kľúčová operácia. Vždy musíme vyhľadávať v hlavnej tabuľke URL, aby sme hľadali podľa vzoru, a vždy musíme nachádzať prieniky. Nemôžeme hľadať priamo v záznamoch URL, pretože to sú len ID, ktoré sa odkazujú na urls.

Na ceste k riešeniu

&& pomalé, pretože oba sety sú obrovské. Operácia bude relatívne rýchla, ak nahradím urls na { "http://google.com/", "http://wingify.com/" }.

Začal som hľadať spôsob, ako v Postgres vykonať prienik množín bez použitia &&, ale bez zvláštneho úspechu.

Nakoniec sme sa rozhodli jednoducho vyriešiť problém izolovane: daj mi všetky urls riadky, pre ktoré URL zodpovedá vzoru. Bez ďalších podmienok to bude — 

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

Namiesto JOIN syntax, jednoducho som použil poddotaz a rozvinul recording_data.urls pole, aby som mohol priamo aplikovať podmienku v WHERE.

Najdôležitejšie tu je, že && sa používa na kontrolu, či daný záznam obsahuje príslušný URL. Trochu sa zamyslite, v tejto operácii môžete vidieť presun cez elementy poľa (alebo riadky tabuľky) a zastavenie, keď sa podmienka (zhody) splní. Niečo vám to nepripomína? Aha, EXISTS.

Keďže na recording_data.urls sa dá odkazovať zvonku kontextu poddotazu, keď k tomu dôjde, môžeme sa vrátiť k nášmu starému priateľovi EXISTS a obaliť ním poddotaz.

Spojením všetkého dokopy dostaneme konečný optimalizovaný dotaz:

VYBERTE 
    count(*) 
Z 
    acc_{account_id}.urls ako recordings_urls, 
    acc_{account_id}.recording_data ako recording_data, 
    acc_{account_id}.sessions ako sessions 
KDE 
    recording_data.usp_id = sessions.usp_id 
    A (1 = 1) 
    A sessions.referrer_id = recordings_urls.id 
    A r_time > to_timestamp(1542585600) 
    A r_time = 5 
    A recording_data.num_of_pages > 0 
    A EXISTUJE (
        VYBERTE urls.url
        Z 
            acc_{account_id}.urls ako urls,
            (VYBERTE unnest(urls) AS rec_url_id Z acc_{account_id}.recording_data) 
            AKO unrolled_urls
        KDE
            urls.id = unrolled_urls.rec_url_id A
            urls.url ILIKE '%enterprise_customer.com/jobs%'
    );

A konečné vykonávacie čas Čas: 1898.717 ms Je čas oslavovať?!?

Nie tak rýchlo! Najskôr je potrebné overiť správnosť. Bol som veľmi podozrievavý voči EXISTS optimalizácii, pretože mení logiku na skoršie dokončenie. Musíme si byť istí, že sme do dopytu nepridali neviditeľnú chybu.

Jednoduchá kontrola spočívala vo vykonaní count(*) a na pomalých aj rýchlych dopytoch pre veľké množstvo rôznych súborov údajov. Potom som, pre malé podmnožstvo údajov, manuálne skontroloval správnosť všetkých výsledkov.

Všetky kontroly poskytli stabilne pozitívne výsledky. Všetko sme opravili!

Vyťažené Lekcie

Z tejto histórie sa dá načerpať mnoho lekcií:

  1. Plány dopytov neprezrádzajú celú históriu, ale môžu poskytnúť náznaky
  2. Hlavní podozriví nie sú vždy skutočnými vinníkmi
  3. Pomalé dopyty môžu byť rozdelené, aby sa izolovali úzke miesta
  4. Nie všetky optimalizácie sú prirodzene reduktívne
  5. Použitie EXIST, kde je to možné, môže viesť k výraznému nárastu výkonu

Záver

Prešli sme od času dopytu približne 24 minút na 2 sekundy — dosť výrazný nárast výkonu! Aj keď tento článok bol rozsiahly, všetky experimenty, ktoré sme robili, prebehli v jeden deň a odhadom trvali od 1,5 do 2 hodín pre optimalizácie a testovania.

SQL je úžasný jazyk, ak sa ho nebojíte, ale snažíte sa ho pochopiť a využiť. S dobrým porozumením toho, ako sa vykonávajú SQL dopyty, ako databáza generuje plány dopytov, ako fungujú indexy a jednoducho veľkosti údajov, s ktorými pracujete, môžete excelovať v optimalizácii dopytov. Rovnako dôležité je však neustále skúšať rôzne prístupy a pomaly analyzovať problém, pričom identifikujete úzke miesta.

Najlepšia časť dosahovania takýchto výsledkov je viditeľné zlepšenie rýchlosti — kedy sa správa, ktorá sa predtým ani nenačítala, teraz načítava takmer okamžite.

Osobitná vďaka mým kolegom v tíme Aditye Mishry, Aditye Gauru a Varunovi Malhotrovi za brainstorming a Dinkarovi Pandirovi za nájdenie dôležitej chyby v našej konečnej žiadosti, skôr než sme sa s ňou definitívne rozlúčili!

Zdroj: habr.com

Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery 🔥 Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery | ProHoster