🥇Egy SQL-vizsgálat története

Tavaly decemberben kaptam egy érdekes hibajelentést a VWO támogatási csapatától. Az egyik nagyvállalati ügyfél elemzési jelentésének betöltési ideje túlzónak tűnt. És mivel ez az én felelősségi köröm, azonnal a probléma megoldására koncentráltam.

őstörténet

Hogy érthető legyen, miről beszélek, mesélek egy kicsit a VWO-ról. Ez egy olyan platform, amellyel különféle célzott kampányokat indíthat webhelyein: A/B kísérleteket végezhet, látogatókat és konverziókat követhet nyomon, elemezheti az értékesítési tölcsért, hőtérképeket jeleníthet meg és látogatási felvételeket játszhat le.

De a platform legfontosabb dolga a jelentéskészítés. A fenti funkciók mindegyike összekapcsolódik. A vállalati ügyfelek számára pedig hatalmas mennyiségű információ egyszerűen haszontalan lenne egy hatékony platform nélkül, amely elemzési formában jeleníti meg.

A platform használatával véletlenszerű lekérdezést végezhet nagy adathalmazra. Íme egy egyszerű példa:

Az összes kattintás megjelenítése az „abc.com” oldalon <d1.dátumtól <d2dátumig>-ig azok számára, akik Chrome-ot VAGY (Európában tartózkodtak ÉS iPhone-t használtak)

Ügyeljen a logikai operátorokra. Az ügyfelek számára elérhetőek a lekérdezési felületen, hogy tetszőlegesen összetett lekérdezéseket hajthassanak végre a minták lekéréséhez.

Lassú kérés

A szóban forgó ügyfél olyasmit próbált megtenni, aminek intuitív módon gyorsan működnie kell:

Az összes munkamenetrekord megjelenítése azon felhasználók számára, akik olyan oldalt kerestek fel, amelynek URL-je tartalmazza a „/jobs” kifejezést

Ennek a webhelynek hatalmas forgalma volt, és több mint egymillió egyedi URL-t tároltunk csak ezért. És egy meglehetősen egyszerű URL-sablont akartak találni, amely az ü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 eredeti 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 < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

És itt vannak az időpontok:

Tervezett idő: 1.480 ms Végrehajtási idő: 1431924.650 ms

A lekérdezés 150 ezer sort járt be. A lekérdezéstervező mutatott néhány érdekes részletet, de nyilvánvaló szűk keresztmetszetek nem voltak.

Tanulmányozzuk tovább a kérést. Amint látja, megteszi JOIN három táblázat:

ülések: a munkamenet információinak megjelenítéséhez: böngésző, felhasználói ügynök, ország stb.
rögzítési_adat: rögzített URL-ek, oldalak, látogatások időtartama
URL-ek: A rendkívül nagy URL-ek megkettőzésének elkerülése érdekében külön táblázatban tároljuk őket.

Vegye figyelembe azt is, hogy az összes táblánkat már particionálta account_id. Így kizárható az olyan helyzet, amikor egy különösen nagy számla másoknak okoz problémát.

Nyomokat keres

Közelebbről megvizsgálva azt látjuk, hogy valami nincs rendben egy adott kéréssel. Ezt a sort érdemes alaposabban megnézni:

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

Az első gondolat az volt, hogy talán azért ILIKE ezeken a hosszú URL-eken (több mint 1,4 millióan egyedülálló ehhez a fiókhoz gyűjtött URL-ek) teljesítménye csökkenhet.

De nem, nem ez a lényeg!

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

Time: 5231.765 ms

Maga a sablonkeresési kérelem mindössze 5 másodpercet vesz igénybe. Nyilvánvalóan nem jelent problémát a minta keresése millió egyedi URL-ben.

A következő gyanúsított a listán több JOIN. Talán a túlzott használatuk okozta a lassulást? Általában JOIN's a legnyilvánvalóbb jelöltek a teljesítményproblémákra, de nem hittem, hogy a mi esetünk tipikus.

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

És szintén nem ez volt a mi esetünk. JOINelég gyorsnak bizonyult.

A gyanúsítottak körének szűkítése

Készen álltam a lekérdezés megváltoztatására, hogy elérjem a teljesítmény esetleges javításait. Csapatommal 2 fő ötletet dolgoztunk ki:

Használja az EXISTS parancsot a segédlekérdezés URL-címéhez: Újra ellenőrizni akartuk, hogy nincs-e probléma az URL-címek allekérdezésével. Ennek egyik módja az egyszerű használat EXISTS. EXISTS képes jelentősen javítja a teljesítményt, mivel azonnal véget ér, amint megtalálja az egyetlen karakterláncot, amely megfelel a feltételnek.

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

Nos, igen. Alkérdés, ha be van csomagolva EXISTS, mindent szupergyorsít. A következő logikus kérdés az, hogy miért a kérelem JOIN-ami és maga az allekérdezés külön-külön gyors, de együtt rettenetesen lassúak?

Az allekérdezés áthelyezése a CTE-be : Ha a lekérdezés önmagában gyors, először egyszerűen kiszámíthatjuk a gyors eredményt, majd megadhatjuk 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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

De még így is nagyon lassú volt.

A tettes megtalálása

Egész idő alatt egy apróság villant a szemem előtt, amit folyamatosan félretettem. De mivel nem maradt más hátra, úgy döntöttem, hogy őt is megnézem. Arról beszélek && operátor. Viszlát EXISTS csak javult a teljesítmény && ez volt az egyetlen megmaradt közös tényező a lassú lekérdezés összes verziójában.

Ránéz dokumentáció, ezt látjuk && akkor használatos, ha közös elemeket kell találnia két tömb között.

Az eredeti kérésben ez áll:

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

Ez azt jelenti, hogy mintakeresést végzünk az URL-címeinken, majd megtaláljuk a metszéspontot az összes gyakori bejegyzést tartalmazó URL-lel. Ez egy kicsit zavaró, mert az "urls" itt nem az összes URL-t tartalmazó táblázatra vonatkozik, hanem a táblázat "urls" oszlopára. recording_data.

Növekvő gyanúval kapcsolatban &&, próbáltam megerősítést találni számukra a generált lekérdezési tervben EXPLAIN ANALYZE (Már volt elmentve egy tervem, de általában kényelmesebb az SQL-ben való kísérletezés, mint a lekérdezéstervezők átlátszatlanságának megértése).

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

Csak a szűrőkből volt több sor &&. Ami azt jelentette, hogy ez a művelet nem csak drága volt, hanem többször is elvégezték.

Ezt az állapot elkülönítésével teszteltem

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 lassú volt. Mert a JOIN-s gyorsak és az allekérdezések gyorsak, csak az maradt && operátor.

Ez csak egy kulcsművelet. Mindig a teljes mögöttes URL-táblázatban kell keresnünk egy minta kereséséhez, és mindig meg kell találnunk a kereszteződéseket. Az URL rekordok alapján nem tudunk közvetlenül keresni, mert ezek csak azokra utaló azonosítók urls.

Útban a megoldás felé

&& lassú, mert mindkét készlet hatalmas. A művelet viszonylag gyors lesz, ha kicserélem urls on { "http://google.com/", "http://wingify.com/" }.

Elkezdtem keresni egy módot arra, hogy használat nélkül állítsam be a kereszteződést Postgresben &&, de nem sok sikerrel.

Végül úgy döntöttünk, hogy csak elszigetelten oldjuk meg a problémát: adj meg mindent urls sorok, amelyek URL-címe megegyezik a mintával. 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 AND
	urls.url  ILIKE  '%jobs%'

Helyett JOIN szintaxis Csak egy segédlekérdezést használtam, és kiterjesztettem recording_data.urls tömböt, így közvetlenül alkalmazhatja a feltételt WHERE.

A legfontosabb itt az && annak ellenőrzésére szolgál, hogy egy adott bejegyzés tartalmaz-e egyező URL-t. Ha egy kicsit hunyorog, láthatja, hogy ez a művelet egy tömb elemein (vagy egy táblázat sorain) keresztül halad, és leáll, ha egy feltétel (egyezés) teljesül. Nem emlékeztet semmire? Igen, EXISTS.

Azóta recording_data.urls Az allekérdezés kontextusán kívülről is hivatkozhatunk, amikor ez megtörténik, visszaeshetünk régi barátunkra EXISTS és csomagolja be vele az allekérdezést.

Mindent összerakva megkapjuk a végső optimalizált lekérdezést:

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%'
    );

És a végső átfutási idő Time: 1898.717 ms Ideje ünnepelni?!?

Nem olyan gyorsan! Először ellenőriznie kell a helyességet. Nagyon gyanakodtam EXISTS optimalizálás, mivel megváltoztatja a logikát a korábbi befejezéshez. Meg kell győződnünk arról, hogy nem adtunk hozzá nem nyilvánvaló hibát a kéréshez.

Egy egyszerű teszt volt a futtatás count(*) lassú és gyors lekérdezések esetén is nagyszámú különböző adatkészlethez. Ezután az adatok egy kis részhalmazánál manuálisan ellenőriztem, hogy minden eredmény helyes-e.

Minden teszt következetesen pozitív eredményt adott. Mindent javítottunk!

Tanulságok

Sok tanulságot lehet levonni ebből a történetből:

A lekérdezési tervek nem mondják el a teljes történetet, de támpontokat adhatnak
A fő gyanúsítottak nem mindig a valódi tettesek
A lassú lekérdezések lebonthatók a szűk keresztmetszetek elkülönítése érdekében
Nem minden optimalizálás reduktív jellegű
Használat EXIST, ahol lehetséges, a termelékenység drámai növekedéséhez vezethet

Teljesítmény

A ~24 perces lekérdezési időről 2 másodpercre váltunk – ez elég jelentős teljesítménynövekedés! Bár ez a cikk nagy sikert aratott, minden kísérletünk egy nap alatt történt, és a becslések szerint 1,5-2 órát vett igénybe az optimalizálás és a tesztelés.

Az SQL egy csodálatos nyelv, ha nem félsz tőle, hanem megpróbálod megtanulni és használni. Ha jól ismeri az SQL-lekérdezések végrehajtását, az adatbázis lekérdezési terveket, az indexek működését és egyszerűen a kezelt adatok méretét, nagyon sikeres lehet a lekérdezések optimalizálása terén. Ugyanilyen fontos azonban, hogy továbbra is próbáljunk különféle megközelítéseket, és lassan bontsuk le a problémát, megtaláljuk a szűk keresztmetszeteket.

Az ilyen eredmények elérésének legjobb része az észrevehető, látható sebességnövekedés – ahol egy olyan jelentés, amely korábban be sem töltődött, most szinte azonnal betöltődik.

Külön köszönet bajtársaim Aditya Mishra parancsára, Aditya Gauru и Varun Malhotra ötletbörze és Dinkar Pandir amiért fontos hibát találtunk végső kérésünkben, mielőtt végleg elbúcsúztunk volna tőle!

Forrás: will.com

Egy SQL-vizsgálat története