ProHoster > Blog > Uprava > Zgodba o eni preiskavi SQL

Zgodba o eni preiskavi SQL

Lani decembra sem prejel zanimivo poročilo o napaki od ekipe za podporo VWO. Čas nalaganja za eno od analitičnih poročil za veliko korporativno stranko se je zdel previsok. In ker je to moje področje odgovornosti, sem se takoj posvetil reševanju problema.

prazgodovina

Da bo jasno, o čem govorim, vam bom povedal nekaj o VWO. To je platforma, s katero lahko na svojih spletnih straneh lansirate različne ciljane kampanje: izvajate A/B eksperimente, sledite obiskovalcem in konverzijam, analizirate prodajni tok, prikazujete toplotne karte in predvajate posnetke obiskov.

Toda najpomembnejša stvar pri platformi je poročanje. Vse zgoraj navedene funkcije so med seboj povezane. In za korporativne stranke bi bila ogromna količina informacij preprosto neuporabna brez zmogljive platforme, ki bi jih predstavila v analitični obliki.

Z uporabo platforme lahko naredite naključno poizvedbo za velik nabor podatkov. Tukaj je preprost primer:

Prikaži vse klike na strani "abc.com" OD <datum d1> DO <datum d2> za ljudi, ki so uporabljali Chrome ALI (nahajajo se v Evropi IN so uporabljali iPhone)

Bodite pozorni na logične operatorje. Odjemalcem so na voljo v poizvedovalnem vmesniku za izdelavo poljubno zapletenih poizvedb za pridobitev vzorcev.

Počasna zahteva

Zadevna stranka je poskušala narediti nekaj, kar bi intuitivno moralo delovati hitro:

Pokaži vse zapise seje za uporabnike, ki so obiskali katero koli stran z URL-jem, ki vsebuje "/jobs"

To spletno mesto je imelo ogromno prometa in samo zanj smo shranili več kot milijon edinstvenih URL-jev. Želeli so najti dokaj preprosto predlogo URL-ja, ki je povezana z njihovim poslovnim modelom.

Predhodna preiskava

Poglejmo, kaj se dogaja v bazi podatkov. Spodaj je originalna počasna poizvedba SQL:

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 ;

In tukaj so časi:

Načrtovani čas: 1.480 ms Čas izvedbe: 1431924.650 ms

Poizvedba je prešla 150 tisoč vrstic. Načrtovalnik poizvedb je pokazal nekaj zanimivih podrobnosti, vendar brez očitnih ozkih grl.

Preučimo zahtevo naprej. Kot vidite, ga ima JOIN tri mize:

  1. sej: za prikaz informacij o seji: brskalnik, uporabniški agent, država itd.
  2. snemalni_podatki: zabeleženi URL-ji, strani, trajanje obiskov
  3. URL-ji: Da bi se izognili podvajanju izjemno velikih URL-jev, jih hranimo v ločeni tabeli.

Upoštevajte tudi, da so vse naše tabele že razdeljene po account_id. Na ta način je izključena situacija, ko en posebej velik račun povzroča težave drugim.

Iščem namige

Ob podrobnejšem pregledu vidimo, da je z določeno zahtevo nekaj narobe. Vredno si je podrobneje ogledati to vrstico:

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

Prva misel je bila, da morda zato ILIKE na vseh teh dolgih URL-jih (imamo več kot 1,4 milijona edinstven URL-ji, zbrani za ta račun) lahko poslabšajo delovanje.

Ampak ne, to ni bistvo!

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

Time: 5231.765 ms

Sama zahteva za iskanje predloge traja le 5 sekund. Iskanje vzorca v milijonih edinstvenih URL-jev očitno ni problem.

Naslednjih osumljencev na seznamu je več JOIN. Morda je njihova prekomerna uporaba povzročila upočasnitev? Običajno JOINso najbolj očitni kandidati za težave z zmogljivostjo, vendar nisem verjel, da je naš primer tipičen.

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

In to tudi ni bil naš primer. JOINse je izkazalo za precej hitro.

Zoženje kroga osumljencev

Bil sem pripravljen začeti spreminjati poizvedbo, da bi dosegel morebitne izboljšave zmogljivosti. Z mojo ekipo smo razvili 2 glavni zamisli:

  • Za URL podpoizvedbe uporabite EXISTS: Znova smo želeli preveriti, ali so bile težave s podpoizvedbo za URL-je. Eden od načinov, kako to doseči, je preprosta uporaba EXISTS. EXISTS lahko močno izboljša delovanje, saj se konča takoj, ko najde edini niz, ki ustreza pogoju.

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

No ja. Podpoizvedba, ko je ovita EXISTS, naredi vse zelo hitro. Naslednje logično vprašanje je, zakaj zahteva z JOIN-ami in sama podpoizvedba sta posamično hitri, skupaj pa strašno počasni?

  • Premikanje podpoizvedbe v CTE : Če je poizvedba hitra sama po sebi, lahko preprosto najprej izračunamo hiter rezultat in ga nato posredujemo glavni poizvedbi

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;

Ampak še vedno je šlo zelo počasi.

Iskanje krivca

Ves ta čas mi je pred očmi švigala ena malenkost, ki sem jo ves čas odmikal. Ker pa ni ostalo nič drugega, sem se odločila, da jo pogledam tudi jaz. govorim o && operater. adijo EXISTS samo izboljšana zmogljivost && je bil edini preostali skupni dejavnik v vseh različicah počasne poizvedbe.

Gledati dokumentacijo, to vidimo && se uporablja, ko morate najti skupne elemente med dvema poljema.

V prvotni zahtevi je to:

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

Kar pomeni, da izvedemo iskanje po vzorcu naših URL-jev, nato najdemo presečišče z vsemi URL-ji s skupnimi objavami. To je nekoliko zmedeno, ker se "urls" tukaj ne nanaša na tabelo, ki vsebuje vse URL-je, ampak na stolpec "urls" v tabeli recording_data.

Z naraščajočimi sumi glede &&, sem poskušal najti potrditev zanje v ustvarjenem načrtu poizvedbe EXPLAIN ANALYZE (Načrt sem že imel shranjen, vendar mi običajno bolj ustreza eksperimentiranje s SQL kot poskušanje razumeti nepreglednost načrtovalcev poizvedb).

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

Bilo je več vrstic filtrov samo iz &&. Kar je pomenilo, da je bila ta operacija ne le draga, ampak tudi večkratna.

To sem preizkusil z izolacijo stanja

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

Ta poizvedba je bila počasna. Zaradi JOIN-s so hitri in podpoizvedbe so hitre, ostalo je samo še && operater.

To je le ključna operacija. Vedno moramo preiskati celotno osnovno tabelo URL-jev, da poiščemo vzorec, in vedno moramo najti presečišča. Ne moremo iskati neposredno po zapisih URL, ker so to samo ID-ji, ki se nanašajo na urls.

Na poti do rešitve

&& počasi, ker sta oba sklopa ogromna. Če zamenjam, bo operacija razmeroma hitra urls o { "http://google.com/", "http://wingify.com/" }.

Začel sem iskati način, kako narediti križišče nizov v Postgresu brez uporabe &&, vendar brez večjega uspeha.

Na koncu sva se odločila, da problem rešiva ​​samo v izolaciji: daj mi vse urls vrstice, za katere se URL ujema z vzorcem. Brez dodatnih pogojev bo - 

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

Namesto JOIN sintaksa Pravkar sem uporabil podpoizvedbo in jo razširil recording_data.urls matriko, tako da lahko neposredno uporabite pogoj v WHERE.

Najpomembnejše pri tem je to && uporablja se za preverjanje, ali dani vnos vsebuje ujemajoči se URL. Če malo pomežiknete, lahko vidite, da se ta operacija premika skozi elemente matrike (ali vrstice tabele) in se ustavi, ko je izpolnjen pogoj (ujemanje). Vas ne spominja na nič? ja, EXISTS.

Od naprej recording_data.urls se lahko sklicuje zunaj konteksta podpoizvedbe, ko se to zgodi, se lahko obrnemo na svojega starega prijatelja EXISTS in z njim ovijte podpoizvedbo.

Če vse skupaj sestavimo, dobimo končno optimizirano poizvedbo:

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

In končni čas izvedbe Time: 1898.717 ms Čas za praznovanje?!?

Ne tako hitro! Najprej morate preveriti pravilnost. Bil sem zelo sumljiv glede EXISTS optimizacijo, saj spremeni logiko za dokončanje prej. Prepričani moramo biti, da v zahtevo nismo dodali neočitne napake.

Preprost test je bil teči count(*) pri počasnih in hitrih poizvedbah za veliko število različnih nizov podatkov. Nato sem za majhno podmnožico podatkov ročno preveril, ali so vsi rezultati pravilni.

Vsi testi so dali dosledno pozitivne rezultate. Vse smo popravili!

Naučena lekcija

Iz te zgodbe se lahko naučimo veliko lekcij:

  1. Načrti poizvedbe ne povedo celotne zgodbe, lahko pa ponudijo namige
  2. Glavni osumljenci niso vedno pravi krivci
  3. Počasne poizvedbe je mogoče razčleniti, da se izolirajo ozka grla
  4. Niso vse optimizacije reduktivne narave
  5. Uporaba EXIST, kjer je to mogoče, lahko povzroči dramatično povečanje produktivnosti

Izhod

S časa poizvedbe, ki je znašal približno 24 minut, smo šli na 2 sekundi – precejšnje povečanje zmogljivosti! Čeprav je bil ta članek velik, so se vsi poskusi, ki smo jih izvedli, zgodili v enem dnevu in ocenjeno je, da so za optimizacije in testiranje potrebovali od 1,5 do 2 uri.

SQL je čudovit jezik, če se ga ne bojite, ampak se ga poskušate naučiti in uporabljati. Če dobro razumete, kako se izvajajo poizvedbe SQL, kako zbirka podatkov ustvarja načrte poizvedb, kako delujejo indeksi in preprosto velikost podatkov, s katerimi imate opravka, ste lahko zelo uspešni pri optimizaciji poizvedb. Enako pomembno pa je še naprej preizkušati različne pristope in počasi razčleniti problem ter poiskati ozka grla.

Najboljši del pri doseganju teh rezultatov je opazno in vidno izboljšanje hitrosti – kjer se poročilo, ki se prej sploh ni naložilo, zdaj naloži skoraj v trenutku.

Posebna zahvala moji tovariši na ukaz Aditya Mishra, Aditya Gauru и Varun Malhotra za možgansko nevihto in Dinkar Pandir ker ste našli pomembno napako v naši zadnji zahtevi, preden smo se dokončno poslovili od nje!

Vir: www.habr.com

Dodaj komentar