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:
- sessions: na zobrazenie sedenia informácií: prehliadač, používateľský agent, krajina a tak ďalej.
- recording_data: zaznamenané URL adresy, stránky, trvanie návštev
- 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 msSamotný 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 msA 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.EXISTSvý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 , 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: 52710Bolo 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í:
- Plány dopytov neprezrádzajú celú históriu, ale môžu poskytnúť náznaky
- Hlavní podozriví nie sú vždy skutočnými vinníkmi
- Pomalé dopyty môžu byť rozdelené, aby sa izolovali úzke miesta
- Nie všetky optimalizácie sú prirodzene reduktívne
- 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 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
