În decembrie anul trecut, am primit un raport interesant despre o eroare din partea echipei de suport VWO. Timpul de încărcare al unuia dintre rapoartele analitice pentru un client corporativ mare părea extrem de mare. Fiindcă aceasta este domeniul meu de responsabilitate, m-am concentrat imediat pe rezolvarea problemei.
Povestea
Pentru a fi clar despre ce este vorba, voi povesti puțin despre VWO. Este o platformă prin care putem lansa diferite campanii țintite pe site-urile noastre: realizarea de experimente A/B, urmărirea vizitatorilor și conversiilor, efectuarea de analize ale canalelor de vânzări, prezentarea hărților de căldură și redarea înregistrărilor vizitelor.
Dar cel mai important aspect al platformei este elaborarea rapoartelor. Toate funcțiile enumerate mai sus sunt interconectate. Iar pentru clienții corporativi, un volum mare de informații ar fi fost pur și simplu inutil fără o platformă puternică care să le prezinte într-un format analitic.
Folosind platforma, poți efectua o cerere aleatorie pe un set mare de date. Iată un exemplu simplu:
Arată toate clicurile de pe pagina "abc.com" DE LA <data d1> PÂNĂ LA <data d2> pentru persoanele care a folosit Chrome SAU (au fost în Europa ȘI au folosit iPhone)
Observați operatorii booleani. Aceștia sunt disponibili pentru clienți în interfața cererii, pentru a face cereri oricât de complexe pentru a obține eșantioane.
Cerere lentă
Clientul despre care vorbim a încercat să facă ceva ce ar trebui să funcționeze rapid din instinct:
Afișează toate înregistrările sesiunilor pentru utilizatorii care au vizitat orice pagină cu URL-ul care conține "\/jobs"
Acest site avea un număr uriaș de trafic, iar noi stocam peste un milion de URL-uri unice doar pentru el. Și voiau să găsească un model destul de simplu de URL, relevant pentru modelul lor de afaceri.
Investigație preliminară
Să vedem ce se întâmplă în baza de date. Iată cererea SQL lentă de bază:
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 ;Iată timpii:
Timpul estimat: 1.480 ms Timpul de execuție: 1431924.650 ms
Interogarea a parcurs 150 de mii de rânduri. Planificatorul de interogări a arătat câteva detalii interesante, dar fără niciun punct evident de blocaj.
Să analizăm interogarea mai departe. După cum se vede, aceasta face JOIN trei tabele:
- sessions: pentru a afișa informațiile despre sesiune: browser, agent utilizator, țară și așa mai departe.
- recording_data: URL-uri înregistrate, pagini, durata vizitelor
- urls: pentru a evita duplicarea unor URL-uri extrem de mari, le stocăm într-un tabel separat.
De asemenea, observați că toate tabelele noastre sunt deja împărțite pe account_id. Astfel, situația în care un singur cont foarte mare afectează pe celelalte este exclusă.
În căutarea indiciilor
La o examinare mai atentă, vedem că ceva în interogarea specifică nu funcționează corect. Ar trebui să ne uităm la această linie:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Prima idee a fost că poate din cauza ILIKE la toate aceste URL-uri lungi (avem peste 1,4 milioane de adreselor URL unice, colectate pentru acest cont) performanța ar putea fi afectată. Dar, nu — asta nu este problema!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 rânduri)Timp: 5231.765 ms
Interogarea de căutare după șablon durează doar 5 secunde. Căutarea după șablon pe un milion de URL-uri unice nu este evident o problemă.Următorul suspect de pe listă — câteva
. Poate utilizarea excesivă a acestora a dus la încetinire? De obicei JOIN‘ele sunt cele mai evidente candidați pentru probleme de performanță, dar nu am crezut că cazul nostru este tipic. JOINanalytics_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 rând)Timp: 147.851 ms
Și acesta nu a fost cazul nostru.‘ele s-au dovedit a fi destul de rapide. JOINRestrângem cercul suspecților
Eram gata să încep să modific interogarea pentru a obține orice îmbunătățiri posibile de performanță. Cu echipa am dezvoltat 2 idei principale:
Utilizați EXISTS pentru subinterogarea URL-urilor
- : Vream să verificăm din nou dacă există probleme cu subinterogarea pentru URL-uri. Unul dintre modurile de a face acest lucru este de a folosi pur și simpluEXISTS
[START WITH …] CONNECT BY.[START WITH …] CONNECT BYîmbunătățește semnificativ performanța, deoarece se încheie imediat după ce găsește o singură linie în funcție de condiție.
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 msDa. Subinterogarea, când este învelită în [START WITH …] CONNECT BY, face totul super rapid. Următoarea întrebare logică este, de ce interogarea cu JOIN-urile și subinterogarea sunt rapide separat, dar încetinesc teribil împreună?
- Mutăm subinterogarea în CTE : dacă interogarea este rapidă de la sine, putem doar calcula mai întâi rezultatul rapid și apoi să-l oferim interogării principale.
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;Dar și asta era încă foarte lent.
Găsim vinovatul
Toată această vreme, un detaliu mi-a tot trecut prin fața ochilor, de care mă îndepărtam constant. Dar, deoarece nu mai rămăsese nimic altceva, am decis să mă uit și la el. Vorbesc despre && operator. Până acum [START WITH …] CONNECT BY a îmbunătățit performanța, && a fost singurul factor comun rămas în toate versiunile interogării lente.
Privind la , vedem că && este folosit atunci când trebuie să găsim elementele comune dintre două matrice.
În interogarea originală, acesta este:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Ce înseamnă că facem o căutare pe bază de model pe adresele noastre, apoi găsim intersecția cu toate adresele cu înregistrări comune. Este puțin confuz, deoarece "urls" aici nu se referă la tabela care conține toate URL-urile, ci la coloana "urls" din tabela recording_data.
Cu îndoieli crescând cu privire la &&, am încercat să le confirm în planul de interogare generat de EXPLAIN ANALYZE (am avut deja un plan salvat, dar de obicei îmi este mai convenabil să experimentez în SQL decât să încerc să înțeleg opacitățile planificatorilor de interogări).
Filtru: ((urls && ($0)::text[]) ȘI (r_time > '2018-12-17 12:17:23+00'::timestamp cu fus orar) ȘI (r_time = '5'::double precision) ȘI (num_of_pages > 0))
Rânduri eliminate de filtru: 52710A fost câteva linii de filtre doar din &&. Ceea ce a însemnat că această operație nu doar că a fost costisitoare, dar a fost efectuată de mai multe ori.
Am verificat asta, izolând condiția
SELECT 1
FROM
acc_{account_id}.urls ca recordings_urls,
acc_{account_id}.recording_data_30 ca recording_data_30,
acc_{account_id}.sessions_30 ca sessions_30
WHERE
urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]Această interogare s-a executat lent. Deoarece JOIN-representările sunt rapide și subinterogările sunt rapide, rămâne doar && operator.
Acesta este doar cheia operației. Trebuie întotdeauna să căutăm în toată tabela principală a URL-urilor pentru a căuta după model și trebuie întotdeauna să găsim intersecțiile. Nu putem căuta în înregistrările URL-urilor direct, deoarece sunt doar id-uri care se referă la urls.
Pe drumul către soluție,
&& încet, deoarece ambele seturi sunt enorme. Operația va fi relativ rapidă dacă înlocuiesc urls pe { "http://google.com/", "http://wingify.com/" }.
Am început să căutăm o modalitate de a face în Postgres intersecția mulțimilor fără a folosi &&, dar fără prea mult succes.
În cele din urmă, am decis pur și simplu să rezolvăm problema izolat: dă-mi toate urls linie pentru care URL-ul corespunde modelului. Fără condiții suplimentare, aceasta ar fi —
SELECT urls.url
FROM
acc_{account_id}.urls ca urls,
(SELECT unnest(recording_data.urls) AS id) CA unrolled_urls
WHERE
urls.id = unrolled_urls.id ȘI
urls.url ILIKE '%jobs%'În loc de JOIN sintaxă, am folosit pur și simplu o subinterogare și am desfăcut recording_data.urls array-ul, pentru a putea aplica direct condiția în WHERE.
Cel mai important aici este că && este folosit pentru a verifica dacă o înregistrare conține URL-ul corespunzător. Ușor înclinat, se poate observa în această operație mutarea prin elementele array-ului (sau rândurile tabelului) și oprirea la îndeplinirea condiției (corespondenței). Nu-ți amintește de nimic? Aha, [START WITH …] CONNECT BY.
Deoarece pe recording_data.urls poate fi referit din exterior contextului subinterogării, când se întâmplă acest lucru, putem reveni la vechiul nostru prieten [START WITH …] CONNECT BY și să-l înfășurăm pe el într-o subinterogare.
Combinând totul împreună, obținem interogarea finală optimizată:
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%'
);
Și timpul total de execuție Timp: 1898.717 ms E timpul să sărbătorim?!?
Nu atât de repede! Mai întâi trebuie să verificăm corectitudinea. Am fost extrem de suspicios cu privire la [START WITH …] CONNECT BY optimizare, deoarece aceasta schimbă logică pentru o finalizare mai rapidă. Trebuie să ne asigurăm că nu am introdus o eroare neclară în interogare.
O verificare simplă a fost executarea count(*) atât pentru interogările lente, cât și pentru cele rapide, pe un număr mare de seturi de date diferite. Apoi, pentru un subset mic de date am verificat manual corectitudinea tuturor rezultatelor.
Toate verificările au dat rezultate constant pozitive. Am reparat totul!
Învățăminte Extrase
Din această poveste se pot extrage multe învățăminte:
- Planurile de interogare nu spun întreaga poveste, dar pot oferi indicii
- Principalele suspecte nu sunt întotdeauna adevărații vinovați
- Interogările lente pot fi divizate pentru a izola blocajele
- Nu toate optimizările sunt în mod natural reducătoare
- Utilizare
EXIST, acolo unde este posibil, poate duce la o creștere semnificativă a performanței
Ieșire
Am dus timpul de interogare de la ~24 de minute la 2 secunde — o creștere semnificativă a performanței! Deși acest articol a ieșit destul de lung, toate experimentele pe care le-am făcut s-au desfășurat într-o singură zi și, estimativ, au durat între 1,5 și 2 ore pentru optimizări și testare.
SQL este un limbaj minunat, dacă nu te temi de el, ci încerci să-l înțelegi și să-l folosești. Având o bună înțelegere a modului în care se execută interogările SQL, cum generează baza de date planurile de interogare, cum funcționează indecșii și pur și simplu dimensiunea datelor cu care ai de-a face, vei putea să excelezi în optimizarea interogărilor. Nu mai puțin important, însă, este să continui să încerci diverse abordări și să rezolvi gradual problema, găsind blocajele.
Cea mai bună parte a obținerii unor astfel de rezultate este îmbunătățirea vizibilă și semnificativă a vitezei — atunci când un raport care anterior nu se încărca deloc acum se încarcă aproape instantaneu.
Mulțumiri deosebite colegilor mei din echipa lui Aditi Mishra, Aditi Gaur și pentru brainstorming și Dinkar Pandir pentru că a găsit o eroare importantă în cererea noastră finală, înainte de a ne despărți de ea!
Sursa: habr.com
