Egy SQL nyomozás története

Tavaly decemberben érdekes hiba-jelentést kaptam a VWO támogatói csapatától. Egy nagyvállalati ügyfél analitikai jelentésének betöltési ideje elképesztően hosszú volt. Mivel ez a terület a felelősségem volt, azonnal a probléma megoldására összpontosítottam.

Háttér

Hogy világos legyen, miről van szó, egy kicsit mesélek a VWO-ról. Ez egy platform, amely lehetővé teszi, hogy különböző célzott kampányokat indítsunk a weboldalainkon: A/B teszteket végezzünk, nyomon kövessük a látogatókat és a konverziókat, elemezzük az értékesítési tölcsért, hőtérképeket jelenítsünk meg, és látogatások felvételeit játszhassuk le.

De a platform legfontosabb funkciója a jelentések készítése. Az összes fent említett funkció összefonódik egymással. A nagyvállalati ügyfelek számára nagy mennyiségű információ haszontalan lenne egy erőteljes platform nélkül, amely ezt analitikai szempontból ábrázolja.

A platform használatával tetszőleges lekérdezést végezhetünk egy nagy adathalmazon. Íme egy egyszerű példa:

Mutass minden kattintást az "abc.com" oldalon
SZEPTEMBER 1-től
SZEPTEMBER 30-ig
azok számára, akik
Chrome-ot használtak VAGY
(Európában tartózkodtak ÉS iPhone-t használtak)

Figyeljen a logikai operátorokra. Ezek elérhetőek az ügyfelek számára a lekérdező felületen, hogy tetszőleges bonyolultságú lekérdezéseket végezzenek a minták megszerzésére.

Íme az optimizált változata ennek a lekérdezésnek.

Az említett ügyfél megpróbált valamit tenni, ami intuitív módon gyorsan működnie kellene:

Mutasd meg az összes ülést, ahol a felhasználók bármely oldalra látogattak, amelynek URL-jében szerepel a "jobs" kifejezés

Az oldalon hatalmas forgalom volt, és több mint egymillió egyedi URL-címet tároltunk csupán ennek érdekében. Ők pedig egy viszonylag egyszerű mintázatot akartak találni, amely a üzleti modelljükhöz kapcsolódik.

Előzetes vizsgálat

Nézzük meg, mi történik az adatbázisban. Alább látható az alapvetően lassú SQL lekérdezés:

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;

Íme az időzítések:

Tervezett idő: 1.480 ms
Futtatási idő: 1431924.650 ms

A lekérdezés 150 ezer sort érintett. A lekérdezés ütemezője néhány érdekes részletet mutatott, de nyilvánvaló szűk keresztmetszeteket nem találtunk.

Nézzük meg tovább a lekérdezést. Mint látható, ezt végzi. JOIN három táblázatot:

1. sessions: a munkamenet-információk megjelenítéséhez: böngésző, felhasználói ügynök, ország stb.
2. recording_data: rögzített URL-ek, oldalak, látogatási időtartamok.
3. urls: a rendkívül hosszú URL-ek megduplázódásának elkerülése érdekében külön táblázatban tároljuk őket.

Vegye észre azt is, hogy minden táblázatunk már el van választva account_id. Így elkerülhetjük, hogy egy különösen nagy fiókból adódó problémák a többieket is érintsék.

Nyomozás nyomok után

Közelebbről megvizsgálva látjuk, hogy valami nem stimmel a konkrét lekérdezéssel. Érdemes közelebbről megnézni ezt a sort:

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

Az első gondolatom az volt, hogy talán az ILIKE miatt van. ILIKE ezeken a hosszú URL-eken (több mint 1,4 millió egyedi URL-címet gyűjtöttünk össze ehhez a fiókhoz), a teljesítmény csökkenhet.

De nem — nem erről van szó!

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

Idő: 5231.765 ms

Maga a mintakeresés mindössze 5 másodpercet vesz igénybe. A mintakeresés egy millió egyedi URL-en nyilvánvalóan nem jelent problémát.

A következő gyanúsított a listán — több. JOINLehet, hogy a túlzott használatuk lassította le a rendszert? Általában JOINaz „‘ek” a legnyilvánvalóbb jelöltek a teljesítményproblémákra, de nem hittem, hogy a mi esetünk tipikus lenne.

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 sor)

Idő: 147.851 ms

És ez sem a mi esetünk volt. JOINAz „‘ek” meglehetősen gyorsnak bizonyultak.

Szűkítjük a gyanúsítottak körét

Kész voltam módosítani a lekérdezést bármilyen lehetséges teljesítményjavulás érdekében. A csapatommal két fő ötletet dolgoztunk ki:

• Használjon EXISTS-t az URL allekérdezéshez: Szerettük volna még egyszer ellenőrizni, hogy van-e probléma az URL-ek allekérdezésével. Az egyik módja ennek elérésére az, hogy egyszerűen használja. VÉGE;. VÉGE; képes jelentősen javítja a teljesítményt, amint meg találja az egyetlen sort a megadott feltétel alapján.

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

Igen. A beágyazott lekérdezés, amikor körbefogva van VÉGE;, mindent szupergyorsan csinál. A következő logikus kérdés az, hogy miért a lekérdezések JOIN-ek és a beágyazott lekérdezés külön-külön gyors, de együtt borzalmasan lassúak?

• A beágyazott lekérdezést áthelyezzük CTE-be : ha a lekérdezés önállóan gyors, egyszerűen először kiszámíthatjuk a gyors eredményt, majd ezt szolgáltathatjuk a fő lekérdezésnek.

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;

De ez még mindig nagyon lassú volt.

Megkeressük a hibást

Ez idő alatt egy apróság folyamatosan a szemem előtt lebegett, amit mindig elutasítottam. De mivel már nem volt másra lehetőségem, úgy döntöttem, hogy rá is pillantok. Azt mondom, hogy a && operátor. Míg VÉGE; csak javította a teljesítményt, && az volt az egyetlen közös tényező a lassú lekérdezés minden változatában.

Nézve a a dokumentációval, látjuk, hogy && akkor használják, amikor közös elemeket kell találni két tömb között.

Az eredeti lekérdezésben ez:

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

Ami azt jelenti, hogy sablon alapján keresünk az URL-jeink között, majd megtaláljuk a közös elemeket az összes URL között, amely közös rekordokkal rendelkezik. Ez kicsit zavaros, mivel az „urls” itt nem egy táblára utal, ami minden URL-t tartalmaz, hanem az „urls” oszlopra az recording_data.

Ahogy a gyanúim nőttek a &&, megpróbáltam megerősítést találni a lekérdezés értekezletében, amely létrejött EXPLAIN ANALYZE (már volt egy mentett tervem, de általában kényelmesebb SQL-ben kísérletezni, mint megérteni a lekérdezés-tervezők áttetszőségét).

Szűrő: ((urls && ($0)::text[]) ÉS (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) ÉS (r_time = '5'::double precision) ÉS (num_of_pages > 0))
                           Szűrő által eltávolított sorok: 52710

Volt néhány szűrő sor csak &&. Ez azt jelentette, hogy ez a művelet nemcsak költséges volt, hanem többször is végrehajtódott.

Ezt ellenőriztem, elkülönítve a feltételt

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[]

Ez a lekérdezés lassan futott. Mivel JOIN-ok gyorsak és az alsó lekérdezések gyorsak, csak a && operátor maradt.

Ez a kulcsfontosságú művelet. Mindig keresnünk kell a fő URL-táblázaton, hogy mintára keressünk, és mindig meg kell találnunk a metszeteket. Közvetlenül nem kereshetünk az url feljegyzések között, mert azok csak ID-k, amelyek urls.

A megoldás felé vezető úton

&& lassú, mivel mindkét halmaz hatalmas. A művelet viszonylag gyors lesz, ha kicserélem urls Gyorsan emlékezzünk vissza, hogyan működik a moduláris aritmetika. Az óra már egy ismert koncepció. Az órák használata { "http://google.com/", "http://wingify.com/" }.

Elkezdtem keresni egy módot, hogy Postgres-ben halmazok metszetét képezzem anélkül, hogy &&, de nem túl nagy sikerrel.

Végül úgy döntöttünk, hogy egyszerűen izoláltan oldjuk meg a problémát: adj meg nekem minden urls sort, amelyre az url megfelel a mintának. További feltételek nélkül ez lesz — 

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 ÉS
	urls.url ILIKE '%jobs%'

A JOIN szintaxisa, egyszerűen alsó lekérdezést használtam és kibővítettem recording_data.urls tömböt, hogy közvetlenül alkalmazhassam a feltételt AHOL.

A legfontosabb itt az, hogy && ellenőrzi, hogy a megadott rekord tartalmazza-e a megfelelő URL-t. Kicsit hunyorogva látható ebben a műveletben az elemekre (vagy táblázatsorokra) való lépkedés és a feltétel (megemelkedés) teljesítésekor való megállás. Valami ismerős? Igen, VÉGE;.

Mivel a recording_data.urls külső hivatkozásként hivatkozhat az alsó lekérdezés kontextusából, amikor ez megtörténik, visszatérhetünk régi barátunkhoz VÉGE; és burkolhatjuk alsó lekérdezésbe.

Összegezve, megkapjuk a végső optimalizált lekérdezést:

VÁLASSZA KI 
    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%'
    );

És a végső végrehajtási idő Idő: 1898.717 ms Itt az ideje az ünneplésnek?!?

Ne siess! Először ellenőrizni kell a helyességet. Nagyon gyanakvó voltam a optimalizációval kapcsolatban, mivel az korai befejezést okozhat. Meg kell győződnünk arról, hogy nem adtunk hozzá valamilyen rejtett hibát a lekérdezéshez. VÉGE; Az egyszerű ellenőrzés a következő végrehajtására vonatkozott

count(*) és mind a lassú, mind a gyors lekérdezések esetén különböző adatminták esetén. Ezután egy kis adatmintában kézzel ellenőriztem az összes eredmény helyességét. Minden ellenőrzés következetesen pozitív eredményeket hozott. Minden helyreállt!

Tanulságok

Számos tanulságot vonhatunk le ebből a történetből:

A lekérdezési tervek nem mondják el az egész történetet, de utalásokat adhatnak

1. A fő gyanúsítottak nem mindig a valódi elkövetők
2. A lassú lekérdezéseket fel lehet bontani, hogy izoláljunk szűk keresztmetszeteket
3. Nem minden optimalizáció természeténél fogva reduktív
4. AZ EXIST
5. Használat , ahol lehetséges, jelentős teljesítménynövekedéshez vezethetÁtmentünk a lekérdezési időnek ~24 percről 2 másodpercre — ez egy jelentős teljesítménynövekedés! Bár ez a cikk hosszúra sikeredett, minden kísérletet, amit végeztünk, egy napon belül valósítottunk meg, és becslések szerint 1,5-2 órát vett igénybe az optimalizálások és tesztelések.

Következtetés

A SQL csodálatos nyelv, ha nem félünk tőle, hanem próbálunk megismerni és használni. Ha jó megértéssel rendelkezünk arról, hogyan hajtják végre a SQL-lekérdezéseket, hogyan generálnak a DB lekérdezési terveket, hogyan működnek a indexek és csak a méretről van szó, amelyet kezelünk, akkor nagyon sikeresek lehetünk a lekérdezések optimalizálásában. Nem kevésbé fontos azonban továbbra is próbálkozni különféle megközelítésekkel és lassan bontani a problémát, megtalálva a szűk keresztmetszeteket.

Az SQL egy csodálatos nyelv, ha nem félünk tőle, hanem megpróbáljuk megérteni és használni. Ha jó áttekintésünk van arról, hogyan hajtják végre az SQL lekérdezéseket, hogyan generálja az adatbázis a lekérdezési terveket, hogyan működnek az indexek, és egyszerűen a kezelt adatok méretéről, akkor jelentős előrelépést érhetünk el a lekérdezések optimalizálásában. Ugyanakkor nem kevésbé fontos, hogy folyamatosan próbálgassuk a különböző megközelítéseket és fokozatosan bontsuk le a problémát, miközben megtaláljuk a szűk keresztmetszeteket.

A legjobb része az ilyen eredmények elérésének a sebesség jelentős, szemmel látható javulása - amikor az a jelentés, ami korábban még nem is töltődött be, most szinte azonnal betöltődik.

Külön köszönet barátaimnak Aditya Mishrának, Aditya Gaurnak és Varun Malhotrának a brainstormingért és Dinkar Pandirinak mert felfedezte a kritikus hibát a végső kérésünkben, mielőtt végleg búcsút mondtunk volna neki!

Forrás: habr.com