Lo scorso dicembre ho ricevuto un interessante rapporto di errore dal team di supporto VWO. Il tempo di caricamento di uno dei rapporti analitici per un grande cliente aziendale sembrava eccessivo. Poiché questa è una mia responsabilità, mi sono subito concentrato sulla risoluzione del problema.
Contesto
Per chiarire di cosa stiamo parlando, voglio dire brevemente qualcosa su VWO. È una piattaforma che consente di avviare diverse campagne mirate sui propri siti: condurre esperimenti A/B, monitorare i visitatori e le conversioni, analizzare il funnel di vendita, visualizzare le heatmap e riprodurre le registrazioni delle visite.
Ma la cosa più importante della piattaforma è la creazione di rapporti. Tutte le funzioni sopra elencate sono interconnesse. E per i clienti aziendali, un'enorme quantità di informazioni sarebbe semplicemente inutile senza una piattaforma potente in grado di presentarle in modo analitico.
Utilizzando la piattaforma, è possibile effettuare una richiesta arbitraria su un grande set di dati. Ecco un semplice esempio:
Mostra tutti i click sulla pagina "abc.com" DAL <data d1> A <data d2> per le persone che hanno usato Chrome O (sono stati in Europa E hanno usato iPhone)
Fai attenzione agli operatori booleani. Sono disponibili per i clienti nell'interfaccia di query, per creare query di qualsiasi complessità per ottenere i campioni.
Richiesta lenta
Il cliente in questione ha tentato di fare qualcosa che intuitivamente dovrebbe funzionare rapidamente:
Mostra tutti i registri delle sessioni per gli utenti che hanno visitato qualsiasi pagina con un URL che contiene "/jobs"
Su questo sito c'era un'enorme quantità di traffico, e conservavamo oltre un milione di URL unici solo per esso. E volevano trovare un modello di URL abbastanza semplice, relativo al loro modello di business.
Indagine preliminare
Diamo un'occhiata a cosa sta succedendo nel database. Di seguito è riportata la query SQL lenta originale:
SELEZIONA
count(*)
DA
acc_{account_id}.urls AS recordings_urls,
acc_{account_id}.recording_data AS recording_data,
acc_{account_id}.sessions AS sessions
DOVE
recording_data.usp_id = sessions.usp_id
E sessions.referrer_id = recordings_urls.id
E ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
E r_time > to_timestamp(1542585600)
E r_time = 5
E recording_data.num_of_pages > 0 ;Ecco i tempi:
Tempo previsto: 1.480 ms Tempo di esecuzione: 1431924.650 ms
La query ha esaminato 150 mila righe. Il pianificatore ha mostrato un paio di dettagli interessanti, ma nessun collo di bottiglia evidente.
Esploriamo ulteriormente la query. Come si vede, essa fa riferimento JOIN a tre tabelle:
- sessioni: per mostrare informazioni sulla sessione: browser, user agent, paese e così via.
- recording_data: url registrati, pagine, durata delle visite
- urls: per evitare la duplicazione di url estremamente grandi, li conserviamo in una tabella separata.
Si noti inoltre che tutte le nostre tabelle sono già suddivise per account_id. In questo modo, si esclude la possibilità che a causa di un account particolarmente grande sorgano problemi per gli altri.
In cerca di indizi
Dopo un'attenta analisi, notiamo che c'è qualcosa di strano nella richiesta specifica. Vale la pena esaminare questa riga:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]La prima idea è stata che forse, a causa di ILIKE su tutti questi URL lunghi (abbiamo oltre 1,4 milioni di unici URL raccolti per questo account) le prestazioni potrebbero risentirne.
Ma no — non è questo il problema!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 righe)
Tempo: 5231.765 msLa ricerca con il modello richiede solo 5 secondi. La ricerca del modello su un milione di URL unici non sembra essere un problema.
Il prossimo sospetto in lista sono alcuni JOIN. Forse il loro uso eccessivo ha portato a rallentamenti? Di solito JOINi caratteri speciali sono i candidati più ovvi per problemi di prestazioni, ma non pensavo che il nostro caso fosse tipico.
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 =5
AND recording_data.num_of_pages > 0 ;
count
-------
8086
(1 riga)
Tempo: 147.851 msE questo non era nemmeno il nostro caso. JOINQuesti si sono dimostrati piuttosto rapidi.
Ristabiliamo il gruppo dei sospettati
Ero pronto a modificare la query per ottenere qualsiasi possibile miglioramento delle prestazioni. Insieme al team, abbiamo sviluppato 2 idee principali:
- Utilizzare EXISTS per la sottoquery URL: Volevamo controllare nuovamente se ci fossero problemi con la sottoquery per gli URL. Un modo per farlo è semplicemente usare
EXISTS.EXISTSper migliorare notevolmente le prestazioni poiché termina immediatamente, non appena trova la prima riga che soddisfa la condizione.
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 msSì. La sottoquery, quando è avvolta in EXISTS, rende tutto super veloce. La prossima domanda logica è perché la query con JOIN-la e la sottoquery siano veloci separatamente, ma rallentino terribilmente insieme?
- Spostiamo la sottoquery in CTE : se la richiesta è veloce di per sé, possiamo semplicemente calcolare prima il risultato rapido e poi fornirlo alla richiesta 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;Ma anche questo era ancora molto lento.
Troviamo il colpevole
Per tutto il tempo davanti ai miei occhi splendeva un piccolo dettaglio, da cui mi tenevo sempre a distanza. Ma poiché non c'era più nulla da perdere, ho deciso di dare un'occhiata anche a quello. Sto parlando di && l'operatore. Finora EXISTS ha semplicemente migliorato le prestazioni, && era l'unico fattore comune rimasto in tutte le versioni della richiesta lenta.
Guardando a , vediamo che && si usa quando è necessario trovare elementi comuni tra due array.
Nella richiesta originale è:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Cosa significa che facciamo una ricerca per modello sui nostri URL e poi troviamo l'intersezione con tutti gli URL che hanno voci comuni. È un po' confuso, poiché "urls" qui non si riferisce a una tabella contenente tutti gli URL, ma a una colonna "urls" nella tabella. recording_data.
Con l'aumento dei sospetti riguardo &&, ho cercato di trovare loro conferma nel piano di query generato EXPLAIN ANALYZE (avevo già un piano salvato, ma di solito preferisco sperimentare in SQL piuttosto che cercare di capire le opacità dei pianificatori di query).
Filtro: ((urls && ($0)::text[]) E (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) E (r_time = '5'::double precision) E (num_of_pages > 0))
Righe rimosse dal filtro: 52710C'erano alcune righe di filtri solo da &&. Il che significava che questa operazione non solo era costosa, ma veniva eseguita più volte.
L'ho verificato isolando la condizione
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[]Questa query veniva eseguita lentamente. Poiché JOIN-i sono veloci e i sottoquery sono veloci, restava solo && operatore.
Ma questa è l'operazione chiave. Dobbiamo sempre cercare in tutta la tabella principale degli URL per trovare secondo il modello e dobbiamo sempre trovare le intersezioni. Non possiamo cercare direttamente nei record degli URL, perché sono solo ID che fanno riferimento a urls.
Sulla strada verso la soluzione
&& lento, perché entrambi i set sono enormi. L'operazione sarà relativamente veloce se sostituisco urls con { "http://google.com/", "http://wingify.com/" }.
Ho iniziato a cercare un modo per ottenere l'intersezione di insiemi in Postgres senza usare &&, ma senza molto successo.
Alla fine, abbiamo deciso di risolvere il problema in modo isolato: dammi tutte le urls righe per cui l'URL corrisponde al modello. Senza ulteriori condizioni sarà —
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 di JOIN sintassi ho semplicemente usato una sottoquery e ho espanso recording_data.urls array, così potevo applicare direttamente la condizione in DOVE.
La cosa più importante qui è che && viene utilizzato per controllare se questa voce contiene l'URL corrispondente. Se si guarda attentamente, si può notare che in questa operazione avviene un attraversamento degli elementi dell'array (o delle righe della tabella) e ci si ferma al soddisfacimento della condizione (di corrispondenza). Ti ricorda qualcosa? Ah, EXISTS.
Poiché su recording_data.urls si può fare riferimento al di fuori del contesto della sottoquery, quando ciò accade, possiamo tornare al nostro vecchio amico EXISTS e avvolgerlo attorno alla sottoquery.
Unendo tutto insieme, otteniamo la query 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 = 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%'
);
E il tempo di esecuzione finale Time: 1898.717 ms È ora di festeggiare?!?
Non così in fretta! Prima bisogna verificare la correttezza. Ero estremamente sospettoso riguardo EXISTS l'ottimizzazione, poiché cambia la logica per una conclusione più precoce. Dobbiamo assicurarci di non aver introdotto errori non evidenti nella query.
Una semplice verifica consisteva nell'eseguire count(*) sia su query lente che veloci per un gran numero di diversi set di dati. Successivamente, per un piccolo sottoinsieme di dati, ho controllato manualmente la correttezza di tutti i risultati.
Tutti i controlli hanno dato risultati costantemente positivi. Abbiamo risolto tutto!
Lezioni Apprese
Da questa storia si possono trarre molte lezioni:
- I piani delle query non raccontano tutta la storia, ma possono offrire indizi
- I principali sospetti non sono sempre i veri colpevoli
- Le query lente possono essere suddivise per isolare i colli di bottiglia
- Non tutte le ottimizzazioni sono di natura riduttiva
- L'utilizzo di
EXIST, dove possibile, può portare a un aumento notevole delle prestazioni
Risultato
Siamo passati da un tempo di query di circa 24 minuti a 2 secondi — un notevole incremento di prestazioni! Anche se questo articolo è lungo, tutti gli esperimenti che abbiamo condotto si sono svolti in un giorno e, stimando, hanno richiesto da 1,5 a 2 ore per ottimizzazioni e test.
SQL è un linguaggio straordinario, se non lo si teme, ma si cerca di comprenderlo e utilizzarlo. Con una buona comprensione di come vengono eseguite le query SQL, di come il database genera i piani di query, di come funzionano gli indici e delle dimensioni dei dati con cui si ha a che fare, è possibile eccellere nell'ottimizzazione delle query. È altrettanto importante, però, continuare a provare diversi approcci e risolvere lentamente i problemi individuando i colli di bottiglia.
La parte migliore nel raggiungere risultati simili è il notevole miglioramento visibile della velocità di esecuzione: quando un report che prima non si caricava nemmeno ora si apre quasi istantaneamente.
Un ringraziamento speciale ai miei colleghi del team Aditya Mishra, Aditya Gaur e per il brainstorming e Dinkar Pandir per aver trovato un errore importante nella nostra query finale, prima che ci dicessimo addio definitivamente!
Fonte: habr.com
