ProHoster > Blog > Amministrazione > A storia di una investigazione SQL

A storia di una investigazione SQL

Dicembre scorsu aghju ricevutu un rapportu di bug interessante da a squadra di supportu VWO. U tempu di carica per unu di i rapporti analitici per un grande cliente d'impresa pareva pruibitivu. E postu chì questu hè a mo zona di rispunsabilità, aghju subitu focu annantu à risolve u prublema.

Pristoria

Per esse chjaru di ciò chì parlu, vi dicu un pocu di VWO. Questa hè una piattaforma cù quale pudete lancià diverse campagni mirati nantu à i vostri siti web: cunduce esperimenti A / B, seguite i visitatori è cunversione, analizà u funnel di vendita, mostra carte di calore è ghjucate registrazioni di visita.

Ma u più impurtante nantu à a piattaforma hè di rapportu. Tutte e funzioni sopra sò interconnesse. È per i clienti corporativi, una quantità enorme di infurmazione seria simplicemente inutile senza una piattaforma putente chì a presenta in forma analitica.

Utilizendu a piattaforma, pudete fà una dumanda aleatoria nantu à un grande settore di dati. Eccu un esempiu simplice:

Mostra tutti i clicchi nantu à a pagina "abc.com" DA <data d1> A <data d2> per e persone chì anu utilizatu Chrome OR (situatu in Europa È utilizatu un iPhone)

Attenti à l'operatori booleani. Sò dispunibuli per i clienti in l'interfaccia di dumanda per fà dumande arbitrariamente cumplesse per ottene campioni.

Richiesta lenta

U cliente in quistione stava pruvatu à fà qualcosa chì intuitivamente deve travaglià rapidamente:

Mostra tutti i registri di sessione per l'utilizatori chì anu visitatu qualsiasi pagina cù un URL chì cuntene "/jobs"

Stu situ hà avutu una tonna di trafficu è avemu almacenatu più di un milione di URL unichi solu per questu. È vulianu truvà un mudellu URL abbastanza simplice chì hà in relazione cù u so mudellu di cummerciale.

Investigazione preliminaria

Fighjemu un ochju à ciò chì succede in a basa di dati. Quì sottu hè a dumanda SQL lenta originale:

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 ;

È quì sò i tempi:

Tempu pianificatu: 1.480 ms Tempu di esecuzione: 1431924.650 ms

A quistione hà rastreu 150 mila fila. U pianificatore di query hà dimustratu un paru di dettagli interessanti, ma senza colli di bottiglia evidenti.

Studiemu più a dumanda. Comu pudete vede, ellu face JOIN trè tavule:

  1. u dischettu: per vede l'infurmazioni di sessione: navigatore, agente d'utilizatore, paese, etc.
  2. dati_arregistramentu: URL registrati, pagine, durata di e visite
  3. urls: Per evità di duplicà URL estremamente grande, l'avemu guardatu in una tabella separata.

Innota ancu chì tutte e nostre tavule sò digià partizionate da account_id. In questu modu, una situazione induve un contu particularmente grande causa prublemi per l'altri hè esclusu.

In cerca di indizi

Dopu un'ispezione più stretta, vedemu chì qualcosa hè sbagliatu cù una dumanda particulare. Vale a pena guardà più attente à sta linea:

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

U primu pensamentu era chì forse perchè ILIKE nantu à tutti questi URL longu (avemu più di 1,4 milioni unicu URL raccolti per stu contu) u rendiment pò soffre.

Ma nò, ùn hè micca u puntu!

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

Time: 5231.765 ms

A dumanda di ricerca di mudellu stessu dura solu 5 seconde. A ricerca di un mudellu in un milione di URL unichi ùn hè chjaramente micca un prublema.

U prossimu suspettu nantu à a lista hè parechje JOIN. Forse u so overuse hà causatu a rallentazione? Di solitu JOINSò i candidati più evidenti per i prublemi di rendiment, ma ùn aghju micca cridutu chì u nostru casu era tipicu.

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

È questu ùn era ancu u nostru casu. JOINhè diventatu abbastanza veloce.

Restringe u circhiu di i suspettati

Eru prontu à cumincià à cambià a dumanda per ottene ogni pussibuli miglioramenti di rendiment. A mo squadra è aghju sviluppatu 2 idee principali:

  • Aduprate EXISTS per l'URL di a subquery: Vulemu verificà di novu s'ellu ci era qualchì problema cù a subquery per l'URL. Una manera di ottene questu hè solu di utilizà EXISTS. EXISTS pò migliurà assai u funziunamentu postu chì finisce immediatamente appena trova l'unica stringa chì currisponde à a cundizione.

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

Ebbè, sì. Subquery quandu hè imballatu in EXISTS, rende tuttu super veloce. A prossima quistione logica hè perchè a dumanda cù JOIN-ami è a subquery stessu sò veloci individualmente, ma sò terribilmente lenti inseme?

  • Trasferendu a subquery à u CTE : Se a dumanda hè rapida per sè stessu, pudemu simpricimenti calculà u risultatu rapidu prima è poi furnisce à a quistione 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;

Ma era sempre assai lentu.

Truvà u culpevule

Tuttu stu tempu, una piccula cosa lampò davanti à i mo ochji, chì aghju sempre sguassatu. Ma siccomu ùn ci era più nunda, decisu di fighjulà ancu ella. parlu && operatore. Avvedeci EXISTS appena migliuratu u rendiment && era l'unicu fattore cumuni restante in tutte e versioni di a query lenta.

Fighjendu documentazione, vedemu chì && utilizatu quandu avete bisognu di truvà elementi cumuni trà dui arrays.

In a dumanda originale questu hè:

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

Chì significa chì facemu una ricerca di mudelli nantu à i nostri URL, dopu truvà l'intersezzione cù tutti l'URL cù i posti cumuni. Questu hè un pocu cunfusu perchè "urls" quì ùn si riferisce micca à a tavula chì cuntene tutti l'URL, ma à a colonna "urls" in a tabella. recording_data.

Cù suspetti crescente in quantu &&, Aghju pruvatu à truvà cunferma per elli in u pianu di dumanda generatu EXPLAIN ANALYZE (Aghju digià avutu un pianu salvatu, ma sò generalmente più còmode di sperimentà in SQL cà di pruvà à capisce l'opacità di i pianificatori di query).

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

Ci era parechje linee di filtri solu da &&. Chì significava chì sta operazione ùn era micca solu caru, ma ancu realizatu parechje volte.

Aghju pruvatu questu isolando a cundizione

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

Sta dumanda era lenta. Perchè u JOIN-s sò veloci è i subqueries sò veloci, l'unicu chì restava era && operatore.

Questu hè solu una operazione chjave. Avemu sempre bisognu di circà l'intera tabella sottostante di URL per circà un mudellu, è avemu sempre bisognu di truvà intersezioni. Ùn pudemu micca circà direttamente per i registri URL, perchè questi sò solu ID chì si riferiscenu urls.

In a strada di una suluzione

&& lentu perchè i dui setti sò enormi. L'operazione serà relativamente rapida se rimpiazzà urls nantu { "http://google.com/", "http://wingify.com/" }.

Aghju cuminciatu à circà un modu per fà l'intersezzione in Postgres senza aduprà &&, ma senza assai successu.

À a fine, avemu decisu di risolve solu u prublema in isolamentu: dammi tuttu urls linee per quale l'URL currisponde à u mudellu. Senza cundizioni supplementari serà - 

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

Invece JOIN sintassi Aghju utilizatu solu una subquery è espansione recording_data.urls array in modu chì pudete applicà direttamente a cundizione in WHERE.

U più impurtante quì hè questu && utilizatu per verificà s'ellu una data entrata cuntene un URL currispondente. Sè squint un pocu, pudete vede chì sta operazione si move à traversu l'elementi di un array (o fila di una tavula) è si ferma quandu una cundizione (match) hè cumpleta. Ùn vi ricorda nunda ? Iè, EXISTS.

Dapoi recording_data.urls pò esse riferitu da fora di u cuntestu subquery, quandu questu succede, pudemu falà nantu à u nostru vechju amicu EXISTS è imbulighjate a subquery cun ella.

Mettendu tuttu inseme, avemu a dumanda finale ottimizzata:

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

È u tempu di piombu finali Time: 1898.717 ms U tempu di festighjà?!?

Micca cusì veloce! Prima vi tocca à verificà a currettu. Eru assai suspettu EXISTS ottimisazione cum'è cambia a logica per compie prima. Avemu da esse sicuru chì ùn avemu micca aghjustatu un errore micca evidenti à a dumanda.

Una prova simplice era di curriri count(*) nantu à e dumande lente è veloci per un gran numaru di setti di dati diffirenti. Allora, per un picculu subset di dati, aghju verificatu manualmente chì tutti i risultati eranu curretti.

Tutti i testi anu datu risultati sempre pusitivi. Avemu riparatu tuttu!

Lezioni amparate

Ci hè parechje lezioni per esse amparatu da sta storia:

  1. I piani di dumanda ùn contanu micca tutta a storia, ma ponu furnisce indizi
  2. I suspettati principali ùn sò micca sempre i veri culpiti
  3. E dumande lente ponu esse distrutte per isolà i colli di bottiglia
  4. Micca tutti l'ottimisazioni sò riduttivi in ​​natura
  5. Usu EXIST, induve pussibule, pò purtà à aumentu drammaticu di a produtividade

cunchiusioni

Passemu da un tempu di dumanda di ~ 24 minuti à 2 seconde - un aumentu di rendiment abbastanza significativu! Ancu s'ellu hè statu grande, tutti l'esperimenti chì avemu fattu sò accaduti in un ghjornu, è hè statu stimatu chì anu pigliatu trà 1,5 è 2 ore per ottimisazioni è teste.

SQL hè una lingua maravigliosa si ùn avete micca paura di questu, ma pruvate d'amparà è aduprà. Per avè una bona cunniscenza di cume e dumande SQL sò eseguite, cumu a basa di dati genera piani di dumanda, cumu funziona l'indici, è solu a dimensione di e dati chì avete trattatu, pudete esse assai successu à ottimisà e dumande. Hè ugualmente impurtante, però, per cuntinuà à pruvà sferenti approcci è lentamente scumpressate u prublema, truvendu i buttiglii.

A più bona parte di ottene risultati cum'è questi hè a migliione di velocità notevule è visibile - induve un rapportu chì prima ùn si caricava mancu avà carica quasi istantaneamente.

Grazie speciale à i me camaradi à u cumandimu di Aditya Mishra, Aditya Gauru и Varun Malhotra per brainstorming è Dinkar Pandir per avè trovu un errore impurtante in a nostra dumanda finale prima chì avemu finalmente dettu addiu!

Source: www.habr.com

Add a comment