🥇Příběh jednoho SQL vyšetřování

Loni v prosinci jsem od týmu podpory VWO obdržel zajímavou zprávu o chybě. Doba načítání jednoho z analytických sestav pro velkého firemního klienta se zdála příliš vysoká. A protože je to moje oblast odpovědnosti, okamžitě jsem se zaměřil na řešení problému.

pravěk

Aby bylo jasné, o čem mluvím, řeknu vám něco málo o VWO. Jedná se o platformu, pomocí které můžete na svých webových stránkách spouštět různé cílené kampaně: provádět A/B experimenty, sledovat návštěvníky a konverze, analyzovat prodejní cestu, zobrazovat teplotní mapy a přehrávat záznamy návštěv.

Nejdůležitější věcí na platformě je ale reporting. Všechny výše uvedené funkce jsou vzájemně propojeny. A pro firemní klienty by obrovské množství informací bylo jednoduše k ničemu bez výkonné platformy, která je prezentuje v analytické podobě.

Pomocí platformy můžete vytvořit náhodný dotaz na velký soubor dat. Zde je jednoduchý příklad:

Zobrazit všechna kliknutí na stránce „abc.com“ OD <datum d1> DO <datum d2> pro uživatele, kteří používali Chrome NEBO (nacházející se v Evropě A používali iPhone)

Věnujte pozornost booleovským operátorům. Jsou k dispozici klientům v rozhraní dotazů, aby mohli provádět libovolně složité dotazy k získání vzorků.

Pomalý požadavek

Dotyčný klient se snažil udělat něco, co by intuitivně mělo fungovat rychle:

Zobrazit všechny záznamy relací pro uživatele, kteří navštívili jakoukoli stránku s adresou URL obsahující „/jobs“

Tento web měl velkou návštěvnost a jen pro něj jsme ukládali přes milion unikátních adres URL. A chtěli najít poměrně jednoduchou šablonu URL, která by se vztahovala k jejich obchodnímu modelu.

Předběžné vyšetřování

Pojďme se podívat, co se v databázi děje. Níže je 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 tady jsou časy:

Plánovaný čas: 1.480 ms Čas provedení: 1431924.650 ms

Dotaz prolezl 150 tisíc řádků. Plánovač dotazů ukázal několik zajímavých detailů, ale žádné zjevné překážky.

Prostudujme žádost dále. Jak vidíte, dělá JOIN tři tabulky:

zasedání: zobrazení informací o relaci: prohlížeč, uživatelský agent, země atd.
záznam_data: zaznamenané adresy URL, stránky, trvání návštěv
urls: Abychom se vyhnuli duplikaci extrémně velkých adres URL, ukládáme je do samostatné tabulky.

Všimněte si také, že všechny naše tabulky jsou již rozděleny podle account_id. Tímto způsobem je vyloučena situace, kdy jeden zvlášť velký účet způsobuje problémy ostatním.

Hledání vodítek

Při bližším zkoumání vidíme, že s konkrétní žádostí není něco v pořádku. Stojí za to se blíže podívat na tento řádek:

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

První myšlenka byla, že možná proto ILIKE na všech těchto dlouhých URL (máme více než 1,4 milionu jedinečný adresy URL shromážděné pro tento účet) může dojít ke zhoršení výkonu.

Ale ne, o to nejde!

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

Time: 5231.765 ms

Samotný požadavek na vyhledání šablony trvá pouhých 5 sekund. Hledání vzoru v milionu unikátních adres URL zjevně není problém.

Další podezřelý na seznamu je několik JOIN. Možná jejich nadměrné používání způsobilo zpomalení? Obvykle JOIN's jsou nejzjevnějšími kandidáty na problémy s výkonem, ale nevěřil jsem, že náš případ byl 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 také nebyl náš případ. JOINUkázalo se, že je to docela rychlé.

Zúžení okruhu podezřelých

Byl jsem připraven začít měnit dotaz, abych dosáhl jakéhokoli možného zlepšení výkonu. Můj tým a já jsme vyvinuli 2 hlavní myšlenky:

Pro adresu URL poddotazu použijte EXISTS: Chtěli jsme znovu zkontrolovat, zda nedošlo k problémům s dílčím dotazem na adresy URL. Jedním ze způsobů, jak toho dosáhnout, je jednoduše používat EXISTS. EXISTS moci výrazně zlepší výkon, protože skončí okamžitě, jakmile najde jediný řetězec, který odpovídá podmínce.

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

Dobře, ano. Poddotaz po zabalení EXISTS, vše je super rychlé. Další logickou otázkou je, proč žádost s JOIN-ami a samotný poddotaz jsou rychlé jednotlivě, ale jsou strašně pomalé dohromady?

Přesunutí poddotazu do CTE : Pokud je dotaz sám o sobě rychlý, můžeme jednoduše nejprve vypočítat rychlý výsledek a poté jej poskytnout 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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Ale i tak to bylo velmi pomalé.

Hledání viníka

Celou tu dobu se mi před očima mihla jedna maličkost, kterou jsem neustále odhrnoval. Protože ale nic jiného nezbývalo, rozhodl jsem se podívat i na ni. mluvím o && operátor. sbohem EXISTS jen vylepšený výkon && byl jediným zbývajícím společným faktorem ve všech verzích pomalého dotazu.

Při pohledu na dokumentace, to vidíme && používá se, když potřebujete najít společné prvky mezi dvěma poli.

V původní žádosti 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 vyhledáme vzory a poté najdeme průsečík se všemi adresami URL s běžnými příspěvky. To je trochu matoucí, protože „url“ zde neodkazuje na tabulku obsahující všechny adresy URL, ale na sloupec „urls“ v tabulce recording_data.

S rostoucím podezřením ohledně &&, pokusil jsem se pro ně najít potvrzení ve vygenerovaném plánu dotazů EXPLAIN ANALYZE (Plán už jsem měl uložený, ale obvykle mi vyhovuje více experimentovat v SQL, než se snažit pochopit neprůhlednost plánovačů dotazů).

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

Pouze z několika řádků filtrů &&. Což znamenalo, že tato operace byla nejen drahá, ale také několikrát provedená.

Testoval jsem to izolováním 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 dotaz byl pomalý. Protože JOIN-s jsou rychlé a poddotazy rychlé, jediné, co zbylo, bylo && operátor.

Toto je pouze klíčová operace. Vždy potřebujeme prohledat celou základní tabulku adres URL, abychom našli vzor, a vždy musíme najít průsečíky. Nemůžeme vyhledávat přímo podle záznamů URL, protože to jsou pouze ID, na která se odkazují urls.

Na cestě k řešení

&& pomalé, protože obě sady jsou obrovské. Operace bude relativně rychlá, pokud ji vyměním urls na { "http://google.com/", "http://wingify.com/" }.

Začal jsem hledat způsob, jak nastavit křižovatku v Postgres bez použití &&, ale bez většího úspěchu.

Nakonec jsme se rozhodli vyřešit problém jen izolovaně: dej mi všechno urls řádky, pro které adresa URL odpovídá vzoru. Bez dalších podmínek 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%'

Místo toho JOIN syntaxe Právě jsem použil poddotaz a rozšířil recording_data.urls pole, abyste mohli přímo použít podmínku WHERE.

Nejdůležitější je zde to && slouží ke kontrole, zda daný záznam obsahuje odpovídající URL. Pokud trochu přimhouříte oči, můžete vidět, jak se tato operace pohybuje mezi prvky pole (nebo řádky tabulky) a zastaví se, když je splněna podmínka (shoda). Nepřipomíná vám to nic? To jo, EXISTS.

Od té doby recording_data.urls může být odkazováno mimo kontext poddotazu, když k tomu dojde, můžeme se obrátit na našeho starého přítele EXISTS a zabalit jím poddotaz.

Když vše dáme dohromady, 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ý dodací čas Time: 1898.717 ms Čas na oslavu?!?

Ne tak rychle! Nejprve je třeba zkontrolovat správnost. Bylo mi to krajně podezřelé EXISTS optimalizace, protože mění logiku tak, aby byla dokončena dříve. Musíme si být jisti, že jsme do požadavku nepřidali nezřejmou chybu.

Jednoduchý test byl spustit count(*) na pomalé i rychlé dotazy pro velké množství různých datových sad. Poté jsem pro malou podmnožinu dat ručně ověřil, že všechny výsledky jsou správné.

Všechny testy vykazovaly trvale pozitivní výsledky. Všechno jsme opravili!

Ponaučení

Z tohoto příběhu plyne mnoho ponaučení:

Plány dotazů nevyprávějí celý příběh, ale mohou poskytnout vodítka
Hlavní podezřelí nejsou vždy skutečnými viníky
Pomalé dotazy lze rozdělit a izolovat úzká místa
Ne všechny optimalizace jsou reduktivní povahy
Použití EXIST, kde je to možné, může vést k dramatickému zvýšení produktivity

Výkon

Z doby dotazu ~ 24 minut jsme přešli na 2 sekundy - poměrně významný nárůst výkonu! Přestože tento článek vyšel jako velký, všechny experimenty, které jsme provedli, se odehrály během jednoho dne a odhaduje se, že optimalizace a testování zabraly 1,5 až 2 hodiny.

SQL je skvělý jazyk, pokud se ho nebojíte, ale zkuste se ho naučit a používat. Pokud dobře rozumíte tomu, jak se provádějí dotazy SQL, jak databáze generuje plány dotazů, jak fungují indexy a jednoduše velikost dat, se kterými pracujete, můžete být velmi úspěšní při optimalizaci dotazů. Stejně důležité je však i nadále zkoušet různé přístupy a pomalu problém odbourávat a nacházet úzká hrdla.

Nejlepší na dosahování takových výsledků je znatelné, viditelné zlepšení rychlosti – kde se zpráva, která se dříve ani nenačetla, nyní načte téměř okamžitě.

Zvláštní poděkování moji soudruzi na příkaz Aditya Mishra, Aditya Gauru и Varun Malhotra pro brainstorming a Dinkar Pandir za nalezení důležité chyby v naší poslední žádosti, než jsme se s ní definitivně rozloučili!

Zdroj: www.habr.com

Příběh jednoho SQL vyšetřování