ProHoster > Blog > Administrácia > Príbeh jedného vyšetrovania SQL

Príbeh jedného vyšetrovania SQL

Minulý rok v decembri som dostal zaujímavú správu o chybe od tímu podpory VWO. Čas načítania jedného z analytických prehľadov pre veľkého podnikového klienta sa zdal príliš vysoký. A keďže je to moja oblasť zodpovednosti, okamžite som sa zameral na vyriešenie problému.

pravek

Aby bolo jasné, o čom hovorím, poviem vám niečo o VWO. Ide o platformu, pomocou ktorej môžete na svojich webových stránkach spúšťať rôzne cielené kampane: vykonávať A/B experimenty, sledovať návštevníkov a konverzie, analyzovať predajný lievik, zobrazovať teplotné mapy a prehrávať záznamy návštev.

Najdôležitejšia vec na platforme je však podávanie správ. Všetky vyššie uvedené funkcie sú vzájomne prepojené. A pre firemných klientov by obrovské množstvo informácií bolo jednoducho zbytočné bez výkonnej platformy, ktorá ich prezentuje v analytickej forme.

Pomocou platformy môžete vytvoriť náhodný dotaz na veľký súbor údajov. Tu je jednoduchý príklad:

Zobraziť všetky kliknutia na stránke „abc.com“ OD <dátum d1> DO <dátum d2> pre ľudí, ktorí používali Chrome ALEBO (nachádzajú sa v Európe A používali iPhone)

Venujte pozornosť booleovským operátorom. Klientom sú k dispozícii v rozhraní dotazov na vytváranie ľubovoľne zložitých dotazov na získanie vzoriek.

Pomalá požiadavka

Dotyčný klient sa snažil urobiť niečo, čo by intuitívne malo fungovať rýchlo:

Zobraziť všetky záznamy relácií pre používateľov, ktorí navštívili akúkoľvek stránku s adresou URL obsahujúcou „/jobs“

Táto stránka mala veľkú návštevnosť a len pre ňu sme ukladali viac ako milión jedinečných adries URL. A chceli nájsť pomerne jednoduchú šablónu adresy URL, ktorá by súvisela s ich obchodným modelom.

Predbežné vyšetrovanie

Poďme sa pozrieť na to, č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 < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

A tu sú časy:

Plánovaný čas: 1.480 ms Čas vykonania: 1431924.650 ms

Dopyt prehľadal 150 tisíc riadkov. Plánovač dopytov ukázal niekoľko zaujímavých detailov, ale žiadne zjavné prekážky.

Poďme študovať žiadosť ďalej. Ako vidíte, robí JOIN tri stoly:

  1. zasadnutí: zobrazenie informácií o relácii: prehliadač, používateľský agent, krajina atď.
  2. záznam_údajov: zaznamenané adresy URL, stránky, trvanie návštev
  3. urls: Aby sme sa vyhli duplikácii extrémne veľkých adries URL, ukladáme ich do samostatnej tabuľky.

Všimnite si tiež, že všetky naše tabuľky sú už rozdelené podľa account_id. Týmto spôsobom je vylúčená situácia, keď jeden obzvlášť veľký účet spôsobuje problémy iným.

Hľadá sa stopy

Pri bližšom skúmaní vidíme, že s konkrétnou požiadavkou niečo nie je v poriadku. Stojí za to pozrieť sa bližšie 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 preto ILIKE na všetkých týchto dlhých adresách URL (máme viac ako 1,4 milióna jedinečný adresy URL zhromaždené pre tento účet) môže dôjsť k zhoršeniu výkonnosti.

Ale nie, o to nejde!

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

Time: 5231.765 ms

Samotná požiadavka na vyhľadávanie šablóny trvá len 5 sekúnd. Hľadanie vzoru v milióne jedinečných adries URL zjavne nie je problém.

Ďalší podozrivý na zozname je niekoľko JOIN. Možno ich nadmerné používanie spôsobilo spomalenie? Zvyčajne JOIN's sú najzrejmejšími kandidátmi na problémy s výkonom, ale neveril som, že náš prípad je typický.

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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

A to tiež nebol náš prípad. JOINUkázalo sa, že je to celkom rýchle.

Zúženie okruhu podozrivých

Bol som pripravený začať meniť dotaz, aby som dosiahol akékoľvek možné zlepšenie výkonu. Môj tím a ja sme vyvinuli 2 hlavné myšlienky:

  • Pre adresu URL poddotazu použite EXISTS: Chceli sme znova skontrolovať, či sa vyskytli nejaké problémy s poddotazom pre adresy URL. Jedným zo spôsobov, ako to dosiahnuť, je jednoducho použiť EXISTS. EXISTS plechovka výrazne zlepšuje výkon, pretože sa skončí okamžite, akonáhle nájde jediný reťazec, ktorý zodpovedá podmienke.

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 < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

No áno. Poddotaz po zabalení EXISTS, robí všetko super rýchlo. Ďalšou logickou otázkou je, prečo žiadosť s JOIN-ami a samotný poddotaz sú rýchle jednotlivo, ale spolu strašne pomalé?

  • Presun poddotazu do CTE : Ak je dopyt sám o sebe rýchly, môžeme jednoducho najskôr vypočítať rýchly výsledok a potom ho poskytnúť hlavnému dopytu

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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Ale aj tak to bolo veľmi pomalé.

Hľadanie vinníka

Celý ten čas sa mi pred očami mihla jedna maličkosť, ktorú som neustále odhŕňal. Ale keďže nič iné nezostávalo, rozhodol som sa pozrieť aj na ňu. hovorím o && operátor. Zbohom EXISTS len vylepšený výkon && bol jediným zostávajúcim spoločným faktorom vo všetkých verziách pomalého dotazu.

Pozerajúc sa na dokumentácia, to vidíme && používa sa, keď potrebujete nájsť spoločné prvky medzi dvoma poliami.

V pôvodnej žiadosti je toto:

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

To znamená, že na našich adresách URL vykonáme vyhľadávanie vzorov a potom nájdeme priesečník so všetkými adresami URL s bežnými príspevkami. Je to trochu mätúce, pretože „url“ tu neodkazuje na tabuľku obsahujúcu všetky adresy URL, ale na stĺpec „urls“ v tabuľke recording_data.

S rastúcimi podozreniami ohľadom &&, pokúsil som sa pre nich nájsť potvrdenie vo vygenerovanom pláne dopytov EXPLAIN ANALYZE (Plán som už mal uložený, ale zvyčajne mi vyhovuje viac experimentovať v SQL, ako sa snažiť pochopiť neprehľadnosť 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 < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Len z niekoľkých riadkov filtrov &&. Čo znamenalo, že táto operácia bola nielen drahá, ale aj niekoľkokrát vykonaná.

Testoval som to izoláciou stavu

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 dopyt bol pomalý. Pretože JOIN-s sú rýchle a poddotazy sú rýchle, jediné čo zostalo bolo && operátor.

Toto je len kľúčová operácia. Vždy musíme prehľadať celú podkladovú tabuľku adries URL, aby sme našli vzor, ​​a vždy musíme nájsť priesečníky. Nemôžeme priamo vyhľadávať podľa záznamov adries URL, pretože ide len o identifikátory, na ktoré sa odkazuje urls.

Na ceste k riešeniu

&& pomalé, pretože obe sady sú obrovské. Ak vymením, operácia bude relatívne rýchla urls na { "http://google.com/", "http://wingify.com/" }.

Začal som hľadať spôsob, ako urobiť križovatku v Postgrese bez použitia &&, ale bez väčšieho úspechu.

Nakoniec sme sa rozhodli vyriešiť problém izolovane: dajte mi všetko urls riadky, pre ktoré sa adresa URL zhoduje so vzorom. 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 toho JOIN syntax Použil som poddotaz a rozbalil som ho recording_data.urls pole, aby ste mohli priamo použiť podmienku WHERE.

Najdôležitejšia vec je tu && slúži na kontrolu, či daný záznam obsahuje zhodnú URL. Ak trochu prižmúrite oči, môžete vidieť, ako sa táto operácia pohybuje cez prvky poľa (alebo riadky tabuľky) a zastaví sa, keď je splnená podmienka (zhoda). Nič vám to nepripomína? áno, EXISTS.

Odvtedy recording_data.urls môže byť odkazované mimo kontextu poddotazu, keď sa to stane, môžeme sa obrátiť na nášho starého priateľa EXISTS a zabaliť ním poddotaz.

Keď dáme všetko dokopy, dostaneme konečný optimalizovaný 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  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

A konečný čas vedenia Time: 1898.717 ms Čas oslavovať?!?

Nie tak rýchlo! Najprv musíte skontrolovať správnosť. Bolo mi to mimoriadne podozrievavé EXISTS optimalizácia, pretože mení logiku tak, aby bola ukončená skôr. Musíme si byť istí, že sme do žiadosti nepridali nezjavnú chybu.

Jednoduchý test bol spustiť count(*) na pomalé aj rýchle dopyty pre veľké množstvo rôznych množín údajov. Potom som pre malú podmnožinu údajov manuálne overil, že všetky výsledky sú správne.

Všetky testy mali trvalo pozitívne výsledky. Všetko sme opravili!

Ponaučenie

Z tohto príbehu sa dá veľa poučiť:

  1. Plány dopytov nehovoria celý príbeh, ale môžu poskytnúť vodítka
  2. Hlavnými podozrivými nie sú vždy skutoční vinníci
  3. Pomalé dopyty je možné rozdeliť na izoláciu úzkych miest
  4. Nie všetky optimalizácie majú reduktívny charakter
  5. Použitie EXIST, kde je to možné, môže viesť k dramatickému zvýšeniu produktivity

Výkon

Prešli sme z času dopytu ~ 24 minút na 2 sekundy - dosť významný nárast výkonu! Hoci tento článok vyšiel ako veľký, všetky experimenty, ktoré sme urobili, sa uskutočnili za jeden deň a odhaduje sa, že optimalizácia a testovanie im trvalo 1,5 až 2 hodiny.

SQL je úžasný jazyk, ak sa ho nebojíte, ale skúste sa ho naučiť a používať. Ak dobre rozumiete tomu, ako sa vykonávajú dotazy SQL, ako databáza generuje plány dotazov, ako fungujú indexy a jednoducho veľkosť údajov, s ktorými pracujete, môžete byť veľmi úspešní pri optimalizácii dotazov. Rovnako dôležité je však aj naďalej skúšať rôzne prístupy a problém pomaly rozoberať a hľadať prekážky.

Najlepšie na dosahovaní výsledkov, ako je tento, je viditeľné, viditeľné zlepšenie rýchlosti – kde sa správa, ktorá sa predtým ani nenačítala, teraz načíta takmer okamžite.

Špeciálna vďaka súdruhovia moji na príkaz Aditya Mishra, Aditya Gauru и Varun Malhotra pre brainstorming a Dinkar Pandir za nájdenie dôležitej chyby v našej poslednej žiadosti predtým, ako sme sa s ňou definitívne rozlúčili!

Zdroj: hab.com

Pridať komentár