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

V prosince loňského roku jsem obdržel zajímavou zprávu o chybě od týmu podpory VWO. Doba načítání jednoho z analytických reportů pro velkého korporátního klienta se zdála být nepřiměřeně dlouhá. A protože to spadá do mé odpovědnosti, okamžitě jsem se soustředil na řešení problému.

Pozadí

Abych objasnil, o co se jedná, povím jenom trochu o VWO. Je to platforma, pomocí které můžete spouštět různé cílené kampaně na svých webech: provádět A/B experimenty, sledovat návštěvníky a konverze, analyzovat prodejní trychtýře, zobrazovat tepelné mapy a přehrávat záznamy návštěv.

Ale nejdůležitější na platformě je tvorba reportů. Všechny výše uvedené funkce jsou vzájemně propojené. A pro korporátní klienty by obrovské množství informací bylo prostě neužitečné bez silné platformy, která by je reprezentovala pro analytiku.

Pomocí platformy můžete provést libovolný dotaz na velkém souboru dat. Zde je jednoduchý příklad:

Zobrazit všechny kliknutí na stránce "abc.com"
OD <data d1> DO <data d2>
pro uživatele, kteří
používali Chrome NEBO
(b byli v Evropě A používali iPhone)

Všimněte si logických operátorů. Jsou k dispozici pro klienty v rozhraní dotazu, aby mohli vytvářet libovolně složité dotazy pro získání vzorků.

Pomalý dotaz

Klient, o kterém je řeč, se pokusil udělat něco, co by intuitivně mělo fungovat rychle:

Zobraz všechny záznamy sezení
pro uživatele, kteří navštívili jakoukoli stránku
s URL, kde je "\/jobs"

Na tomto webu byl obrovský objem provozu a uchovávali jsme více než milion unikátních URL adres pouze pro něj. A chtěli najít celkem jednoduchou šablonu URL týkající se jejich obchodního modelu.

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

Podívejme se, co se děje v databázi. 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:

Očekávaný čas: 1.480 ms
Doba trvání: 1431924.650 ms

Dotaz procházel 150 tisíc řádků. Plánovač dotazů ukázal několik zajímavých detailů, ale žádná očividná úzká místa.

Pojďme se podívat na dotaz dál. Jak je vidět, dělá JOIN tří tabulek:

1. sessions: pro zobrazení relace: prohlížeč, uživatelská agentura, země atd.
2. recording_data: zaznamenané URL, stránky, délka návštěv
3. urls: abychom se vyhnuli duplikaci extrémně dlouhých URL, ukládáme je do samostatné tabulky.

Také si všimněte, že všechny naše tabulky jsou již rozděleny podle account_id. Tím je vyloučeno, že by kvůli jednomu obzvláště velkému účtu nastaly problémy ostatním.

Hledání stop

Při bližším pohledu vidíme, že něco v konkrétním dotazu není v pořádku. Stojí za to podívat se 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á kvůli ILIKE na všech těch dlouhých URL (máme více než 1,4 milionu unikátních URL adres shromážděných pro tento účet) může výkonnost klesat.

Ale ne — to není ono!

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

Čas: 5231.765 ms

Samotný dotaz na vyhledávání podle vzoru trvá pouze 5 sekund. Vyhledávání podle vzoru na milionu unikátních URL rozhodně není problém.

Další podezřelý na seznamu — několik JOIN. Možná jejich nadměrné používání vedlo k zpomalení? Obvykle JOIN‘y jsou nejzjevnějšími kandidáty na problémy s výkonem, ale nevěřil jsem, že náš pří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 =5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Čas: 147.851 ms

A to také nebyl náš případ. JOIN‘y se ukázaly být velmi rychlé.

Zúžení okruhu podezřelých

Byl jsem připraven začít měnit dotaz, abych dosáhl jakýchkoli možných zlepšení výkonu. My s týmem jsme vyvinuli 2 hlavní nápady:

• Použít EXISTS pro poddotaz URL: Chtěli jsme ještě jednou zkontrolovat, zda nejsou problémy s poddotazem pro URL. Jedním ze způsobů, jak toho dosáhnout, je jednoduše použít EXISTS. EXISTS může výrazně zlepšit výkon, jakmile se najde první řádek podle podmínky.

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

Ano. Poddotaz, když je zabalený do EXISTS, dělá vše extrémně rychlé. Další logickou otázkou je, proč je dotaz s JOIN-y a samotné poddotazy rychlé samostatně, ale společně výrazně zpomalují?

• Přesuneme poddotaz do CTE : pokud je dotaz rychlý sám o sobě, můžeme 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 =5 
    AND recording_data.num_of_pages > 0;

Ale i to bylo stále velmi pomalé.

Hledáme viníka

Po celou dobu mi před očima skočila jedna drobnost, od které jsem se neustále odkláněl. Ale protože už nebylo nic jiného, rozhodl jsem se na to podívat. Mluvím o && operátoru. Zatímco EXISTS to jen zlepšilo výkon, && byl jediným zbývajícím společným faktorem ve všech verzích pomalého dotazu.

Díváme se na dokumentaci, vidíme, že && se používá, když potřebujeme najít společné prvky mezi dvěma poli.

V původním dotazu je to:

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

Což znamená, že provádíme hledání podle vzoru našich URL, poté nalezneme průnik se všemi URL, které mají společné záznamy. To je trochu matoucí, protože „urls“ zde neodkazuje na tabulku obsahující všechny URL adresy, ale na sloupec „urls“ v tabulce recording_data.

S rostoucím podezřením ohledně &&, pokusil jsem se najít jejich potvrzení v plánu dotazu, který byl vygenerován. EXPLAIN ANALYZE (měl jsem již uložený plán, ale obvykle je pro mě pohodlnější experimentovat v SQL, než se snažit pochopit neprůhlednosti plánovačů dotazů).

Filtr: ((urls && ($0)::text[]) A (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) A (r_time = '5'::double precision) A (num_of_pages > 0))
                           Řádky odstraněné filtrem: 52710

Bylo tam několik řádků filtrů pouze z &&. Což znamenalo, že tato operace byla nejen nákladná, ale také se prováděla několikrát.

Ověřil jsem to tím, že jsem izoloval podmínku

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 prováděn pomalu. Protože JOIN-y jsou rychlé a poddotazy jsou rychlé, zůstal pouze && operátor.

Toto je však klíčová operace. Vždy musíme hledat v celé hlavní tabulce URL adres, abychom mohli hledat podle vzoru, a vždy musíme najít průniky. Nemůžeme hledat podle záznamů URL přímo, protože jsou to pouze ID odkazující na urls.

Na cestě k řešení

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

Začal jsem hledat způsob, jak v Postgres provést průnik množin bez použití &&, ale bez zvláštního úspěchu.

Nakonec jsme se rozhodli prostě vyřešit problém izolovaně: dej mi všechny urls řádky, pro které 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%'

Namísto JOIN sintaxi jsem jednoduše použil poddotaz a rozvinul recording_data.urls pole, abych mohl přímo použít podmínku v není spolehlivý:.

Nejdůležitější je, že && se používá k ověření, zda daný záznam obsahuje odpovídající URL adresu. Při pohledu zblízka můžeme v této operaci vidět procházení prvky pole (nebo řádky tabulky) a zastavení, když se podmínka splní (shoda). Nic vám to nepřipomíná? Ano, EXISTS.

Protože na recording_data.urls se může odkazovat zvenčí kontextu poddotazu, když k tomu dojde, můžeme se vrátit k našemu starému příteli EXISTS a obalit ho poddotazem.

Když to všechno spojíme dohromady, dostáváme 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 =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é časování Čas: 1898.717 ms Je čas na oslavu?!?

Není to tak rychlé! Nejprve je potřeba zkontrolovat správnost. Byl jsem velmi podezřívavý k EXISTS optimalizaci, protože mění logiku na dřívější dokončení. Musíme si být jisti, že jsme do dotazu nepřidali nepatrnou chybu.

Jednoduchá kontrola spočívala v provedení count(*) na pomalých i rychlých dotazech pro velké množství různých datových sad. Poté jsem ručně zkontroloval správnost všech výsledků pro malé podmnožiny dat.

Všechny kontroly měly stabilně pozitivní výsledky. Všechno jsme napravili!

Získané Lekce

Z tohoto příběhu lze vyvodit mnoho lekcí:

1. Plány dotazů nevyprávějí celou historii, ale mohou poskytnout vodítka
2. Hlavní podezřelí nejsou vždy skutečnými viníky
3. Pomalé dotazy lze rozdělit, aby se izolovaly úzká místa
4. Ne všechny optimalizace jsou z povahy redukční
5. Použití EXIST, kde je to možné, může vést k drastickému nárůstu výkonu

Výstup

Přešli jsme od času dotazu z přibližně 24 minut na 2 sekundy — to je značný nárůst výkonu! Ačkoli tento článek je poměrně dlouhý, všechny experimenty, které jsme provedli, proběhly během jednoho dne a odhadujeme, že optimalizace a testování trvalo 1,5 až 2 hodiny.

SQL je úžasný jazyk, pokud se ho nebojíte, a pokusíte se ho poznat a využít. S dobrým pochopením toho, jak se SQL dotazy vyhodnocují, jak databáze generuje plány dotazů, jak fungují indexy a velikosti dat, se kterými máte co do činění, můžete velmi dobře uspět v optimalizaci dotazů. Není méně důležité pokračovat ve zkoušení různých přístupů a postupně rozebírat problém, abyste našli úzká místa.

Nejlepší část dosažení takových výsledků je výrazné zlepšení rychlosti – když se zpráva, která se dříve ani nenačítala, nyní načítá téměř okamžitě.

Zvláštní poděkování mým kolegům v týmu Aditya Mishra, Aditya Gaur a Varun Malhotra za brainstorming a Dinkar Pandir za to, že našel důležitou chybu v našem finálním požadavku, než jsme se s ním definitivně rozloučili!

Zdroj: habr.com