În decembrie anul trecut am primit un raport de eroare interesant de la echipa de asistență VWO. Timpul de încărcare pentru unul dintre rapoartele de analiză pentru un client corporativ mare părea prohibitiv. Și întrucât aceasta este domeniul meu de responsabilitate, m-am concentrat imediat pe rezolvarea problemei.
preistorie
Ca să fie clar despre ce vorbesc, o să vă povestesc puțin despre VWO. Aceasta este o platformă prin care puteți lansa diverse campanii direcționate pe site-urile dvs.: efectuați experimente A/B, urmăriți vizitatorii și conversiile, analizați pâlnia de vânzări, afișați hărți termice și redați înregistrări ale vizitelor.
Dar cel mai important lucru despre platformă este raportarea. Toate funcțiile de mai sus sunt interconectate. Iar pentru clienții corporativi, o cantitate imensă de informații ar fi pur și simplu inutilă fără o platformă puternică care să le prezinte sub formă de analiză.
Folosind platforma, puteți face o interogare aleatorie pe un set mare de date. Iată un exemplu simplu:
Afișați toate clicurile de pe pagina „abc.com” DE LA <data d1> LA <data d2> pentru persoanele care au folosit Chrome SAU (situat în Europa ȘI au folosit un iPhone)
Acordați atenție operatorilor booleeni. Acestea sunt disponibile clienților în interfața de interogare pentru a face interogări arbitrar complexe pentru a obține mostre.
Cerere lentă
Clientul în cauză încerca să facă ceva care intuitiv ar trebui să funcționeze rapid:
Afișați toate înregistrările sesiunilor pentru utilizatorii care au accesat orice pagină cu o adresă URL care conține „/jobs”
Acest site a avut o mulțime de trafic și stocam peste un milion de adrese URL unice doar pentru el. Și au vrut să găsească un șablon de adresă URL destul de simplu, care se referă la modelul lor de afaceri.
Investigatie preliminara
Să aruncăm o privire la ce se întâmplă în baza de date. Mai jos este interogarea SQL lentă inițială:
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 ;Și iată orarele:
Timp planificat: 1.480 ms Timp de execuție: 1431924.650 ms
Interogarea a accesat cu crawlere 150 de mii de rânduri. Planificatorul de interogări a arătat câteva detalii interesante, dar fără blocaje evidente.
Să studiem cererea în continuare. După cum puteți vedea, el are JOIN trei tabele:
- Sesiunile: pentru a afișa informații despre sesiune: browser, agent de utilizator, țară și așa mai departe.
- date_înregistrare: URL-uri înregistrate, pagini, durata vizitelor
- url-uri: Pentru a evita duplicarea adreselor URL extrem de mari, le stocăm într-un tabel separat.
De asemenea, rețineți că toate tabelele noastre sunt deja partiționate account_id. În acest fel, este exclusă o situație în care un cont deosebit de mare cauzează probleme altora.
Caut indicii
La o inspecție mai atentă, vedem că ceva este în neregulă cu o anumită cerere. Merită să aruncați o privire mai atentă la această linie:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Primul gând a fost că poate pentru că ILIKE pe toate aceste adrese URL lungi (avem peste 1,4 milioane unic Adresele URL colectate pentru acest cont) performanța poate avea de suferit.
Dar nu, nu asta este ideea!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msCererea de căutare a șablonului în sine durează doar 5 secunde. Căutarea unui model într-un milion de adrese URL unice nu este în mod clar o problemă.
Următorul suspect de pe listă este mai mulți JOIN. Poate că utilizarea lor excesivă a cauzat încetinirea? De obicei JOINSunt cei mai evidenti candidați pentru probleme de performanță, dar nu am crezut că cazul nostru este tipic.
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Și nici acesta nu a fost cazul nostru. JOINs-a dovedit a fi destul de rapid.
Îngustând cercul suspecților
Eram gata să încep să schimb interogarea pentru a obține eventuale îmbunătățiri ale performanței. Eu și echipa mea am dezvoltat 2 idei principale:
- Utilizați EXISTS pentru adresa URL a subinterogării: Am vrut să verificăm din nou dacă au existat probleme cu subinterogarea pentru adresele URL. O modalitate de a realiza acest lucru este pur și simplu utilizarea
EXISTS.EXISTSîmbunătățește considerabil performanța, deoarece se termină imediat de îndată ce găsește singurul șir care se potrivește cu condiția.
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 msEi bine, da. Subinterogare când este împachetat EXISTS, face totul super rapid. Următoarea întrebare logică este de ce cererea cu JOIN-ami și subinterogarea în sine sunt rapide individual, dar sunt teribil de lente împreună?
- Mutarea subinterogării în CTE : Dacă interogarea este rapidă singură, putem să calculăm pur și simplu rezultatul rapid mai întâi și apoi să-l furnizăm 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 < to_timestamp(1545107599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0;Dar a fost încă foarte lent.
Găsirea vinovatului
În tot acest timp, în fața ochilor mi-a fulgerat un lucru mic, pe care l-am dat deoparte constant. Dar, din moment ce nu mai era nimic altceva, am decis să mă uit și la ea. Vorbesc despre && operator. Pa EXISTS doar performanță îmbunătățită && a fost singurul factor comun rămas în toate versiunile interogării lente.
Uitandu-ma la , noi vedem asta && folosit atunci când trebuie să găsiți elemente comune între două matrice.
În cererea inițială aceasta este:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Ceea ce înseamnă că facem o căutare de model pe URL-urile noastre, apoi găsim intersecția cu toate adresele URL cu postări comune. Acest lucru este puțin confuz, deoarece „urls” aici nu se referă la tabelul care conține toate adresele URL, ci la coloana „urls” din tabel recording_data.
Cu suspiciuni tot mai mari cu privire la &&, am încercat să le găsesc confirmare în planul de interogare generat EXPLAIN ANALYZE (Am avut deja un plan salvat, dar de obicei sunt mai confortabil să experimentez în SQL decât să încerc să înțeleg opacitatea planificatorilor de interogări).
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: 52710Au existat mai multe linii de filtre numai de la &&. Ceea ce însemna că această operație nu a fost doar costisitoare, ci și efectuată de mai multe ori.
Am testat acest lucru izolând starea
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[]Această interogare a fost lentă. Deoarece JOIN-s sunt rapide și subinterogările sunt rapide, singurul lucru rămas a fost && operator.
Aceasta este doar o operațiune cheie. Întotdeauna trebuie să căutăm în întregul tabel de adrese URL de bază pentru a căuta un model și trebuie să găsim întotdeauna intersecții. Nu putem căuta direct după înregistrări URL, deoarece acestea sunt doar ID-uri la care se referă urls.
În drum spre o soluție
&& lent pentru că ambele seturi sunt uriașe. Operația va fi relativ rapidă dacă o înlocuiesc urls pe { "http://google.com/", "http://wingify.com/" }.
Am început să caut o modalitate de a face intersecția setată în Postgres fără a folosi &&, dar fără prea mult succes.
În cele din urmă, am decis să rezolvăm problema doar izolat: dă-mi totul urls linii pentru care URL-ul se potrivește cu modelul. Fără condiții suplimentare va fi -
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%'În loc de JOIN Sintaxă Tocmai am folosit o subinterogare și am extins recording_data.urls matrice, astfel încât să puteți aplica direct condiția în WHERE.
Cel mai important lucru aici este că && folosit pentru a verifica dacă o anumită intrare conține o adresă URL care se potrivește. Dacă strâmbați puțin, puteți vedea că această operație se mișcă prin elementele unui tablou (sau rânduri ale unui tabel) și se oprește atunci când este îndeplinită o condiție (potrivire). Nu-ți aduce aminte de nimic? Da, EXISTS.
De atunci recording_data.urls poate fi referit din afara contextului subinterogării, atunci când se întâmplă acest lucru, ne putem întoarce la vechiul nostru prieten EXISTS și împachetați subinterogarea cu ea.
Punâ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 < 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%'
);
Și timpul final de livrare Time: 1898.717 ms E timpul să sărbătorești?!?
Nu asa de repede! Mai întâi trebuie să verificați corectitudinea. Eram extrem de suspicios EXISTS optimizare deoarece schimbă logica pentru a fi finalizată mai devreme. Trebuie să ne asigurăm că nu am adăugat o eroare neevidentă la cerere.
Un test simplu trebuia să ruleze count(*) atât pentru interogări lente, cât și rapide pentru un număr mare de seturi de date diferite. Apoi, pentru un mic subset de date, am verificat manual că toate rezultatele au fost corecte.
Toate testele au dat rezultate pozitive constant. Am reparat totul!
Lecții învățate
Sunt multe lecții de învățat din această poveste:
- Planurile de interogare nu spun întreaga poveste, dar pot oferi indicii
- Principalii suspecți nu sunt întotdeauna adevărații vinovați
- Interogările lente pot fi defalcate pentru a izola blocajele
- Nu toate optimizările sunt de natură reductivă
- Folosi
EXIST, acolo unde este posibil, poate duce la creșteri dramatice ale productivității
Producție
Am trecut de la un timp de interogare de ~24 minute la 2 secunde - o creștere destul de semnificativă a performanței! Deși acest articol a ieșit mare, toate experimentele pe care le-am făcut s-au petrecut într-o singură zi și s-a estimat că au durat între 1,5 și 2 ore pentru optimizări și testare.
SQL este un limbaj minunat dacă nu vă este frică de el, dar încercați să îl învățați și să îl folosiți. Având o bună înțelegere a modului în care sunt executate interogările SQL, a modului în care baza de date generează planuri de interogări, a modului în care funcționează indexurile și, pur și simplu, a dimensiunii datelor cu care aveți de-a face, puteți avea mare succes la optimizarea interogărilor. Este la fel de important, totuși, să încercăm în continuare abordări diferite și să descompuneți încet problema, găsind blocajele.
Cea mai bună parte a obținerii unor astfel de rezultate este îmbunătățirea vizibilă și vizibilă a vitezei - unde un raport care anterior nici măcar nu se încărca acum se încarcă aproape instantaneu.
Mulțumiri speciale lui camarazii mei la comanda lui Aditya Mishra, Aditya Gauru и pentru brainstorming și Dinkar Pandir pentru că am găsit o eroare importantă în cererea noastră finală înainte să ne luăm rămas bun de la ea!
Sursa: www.habr.com