Lo scorso dicembre ho ricevuto un interessante rapporto su un errore dal team di supporto VWO. Il tempo di caricamento di uno dei rapporti analitici per un grande cliente aziendale sembrava eccessivamente lungo. E dato che questo rientra nelle mie responsabilità, mi sono subito concentrato sulla risoluzione del problema.
Antefatti
Per chiarire di cosa stiamo parlando, racconterò brevemente di VWO. È una piattaforma che consente di lanciare diverse campagne targetizzate sui propri siti: condurre esperimenti A/B, monitorare i visitatori e le conversioni, analizzare il funnel di vendita, mostrare mappe di calore e riprodurre registrazioni delle visite.
Ma la cosa più importante della piattaforma è la creazione di report. Tutte le funzioni sopra menzionate sono collegate tra loro. E per i clienti aziendali, un enorme insieme di informazioni sarebbe semplicemente inutile senza una potente piattaforma che le rappresenti in forma analitica.
Utilizzando la piattaforma, è possibile effettuare richieste arbitrarie su un grande insieme di dati. Ecco un semplice esempio:
Mostra tutti i clic sulla pagina "abc.com" DAL <data d1> AL <data d2> per le persone che hanno utilizzato Chrome O (si trovavano in Europa E hanno utilizzato iPhone)
Fai attenzione agli operatori booleani. Sono disponibili per i clienti nell'interfaccia di query, per creare richieste complesse per ottenere campioni.
Richiesta lenta
Il cliente in questione cercava di fare qualcosa che intuitivamente doveva funzionare rapidamente:
Mostra tutte le registrazioni delle sessioni per gli utenti che hanno visitato qualsiasi pagina con un URL che contiene "/jobs"
Questo sito aveva un'enorme quantità di traffico e conservavamo oltre un milione di URL unici solo per esso. E volevano trovare un formato di URL piuttosto semplice legato al loro modello di business.
Indagine preliminare
Diamo un'occhiata a cosa succede nel database. Di seguito è riportata l'originale query SQL lenta:
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 ;Ecco i tempi:
Tempo pianificato: 1,480 ms Tempo di esecuzione: 1.431.924,650 ms
La query ha esaminato 150 mila righe. Il pianificatore di query ha mostrato un paio di dettagli interessanti, ma nessun collo di bottiglia evidente.
Esploriamo ulteriormente la query. Come si vede, essa effettua JOIN di tre tabelle:
- sessions: per visualizzare le informazioni sulle sessioni: browser, user agent, paese e così via.
- recording_data: URL registrati, pagine, durata delle visite
- urls: per evitare la duplicazione di URL estremamente lunghi, li memorizziamo in una tabella separata.
Inoltre, si noti che tutte le nostre tabelle sono già divise per account_id. In questo modo si esclude la situazione in cui un singolo account particolarmente grande causa problemi agli altri.
In cerca di indizi
All'esame più attento, vediamo che qualcosa in una specifica query non va. È opportuno prestare attenzione a questa riga:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]La prima idea era che forse, a causa di ILIKE su tutti questi URL lunghi (abbiamo più di 1,4 milioni di URL unici, raccolti per questo account) la performance potrebbe deteriorarsi. Ma no — non è questo il problema!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 righe)Tempo: 5.231,765 ms
La query di ricerca per modello impiega solo 5 secondi. La ricerca per modello su un milione di URL unici non è chiaramente un problema.Il prossimo sospettato in lista è — alcuni
. Forse il loro uso eccessivo ha portato a un rallentamento? Di solito JOIN‘s sono i candidati più ovvi per problemi di performance, ma non credevo che il nostro caso fosse tipico. 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 row)Tempo: 147,851 ms
E anche questo non era il nostro caso.‘s si sono rivelati piuttosto veloci. JOINRaffiniamo il cerchio dei sospetti
Ero pronto a modificare la query per ottenere eventuali miglioramenti di performance. Io e il mio team abbiamo sviluppato 2 idee principali:
Utilizzare EXISTS per la sottoquery degli URL
- : Volevamo verificare ancora una volta se ci fossero problemi con la sottoquery per gli URL. Uno dei modi per ottenerlo è semplicemente usareEXISTS
ESISTE.ESISTEmigliorare notevolmente le prestazioni poiché termina immediatamente non appena trova una sola 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ì. Una sottoquery, quando è incapsulata in ESISTE, rende tutto super veloce. La prossima domanda logica è perché la query con JOIN- e la sottoquery siano veloci separatamente, ma rallentino terribilmente insieme?
- Spostiamo la sottoquery in CTE : se la query è veloce da sola, possiamo semplicemente calcolare prima il risultato veloce e poi fornirlo alla query 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.
Identifichiamo il colpevole
Per tutto questo tempo, una piccola cosa mi è balzata agli occhi, dalla quale ho continuamente tentato di distolgermi. Ma poiché non c'era altro da fare, ho deciso di dare un'occhiata. Parlo di && operatore. Finora ESISTE ha semplicemente migliorato le prestazioni, && era l'unico fattore comune rimanente in tutte le versioni della query lenta.
Guardando , vediamo che && viene utilizzato quando è necessario trovare elementi comuni tra due array.
Nella query originale questo è:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Il che significa che stiamo cercando tramite un pattern nei nostri url, quindi troviamo l'intersezione con tutti gli url che hanno record comuni. È un po' confuso, poiché "urls" qui non si riferisce a una tabella contenente tutti gli URL, ma alla colonna "urls" nella tabella recording_data.
Con l'aumentare dei sospetti riguardo &&, ho provato a trovarne conferma in termini di query generata da EXPLAIN ANALYZE (avevo già un piano salvato, ma di solito mi è più comodo 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 diverse righe di filtri solo da &&. Ciò significava che quest'operazione non solo era costosa, ma veniva anche eseguita più volte.
L'ho controllato isolando la condizione
SELECT 1
DA
acc_{account_id}.urls come recordings_urls,
acc_{account_id}.recording_data_30 come recording_data_30,
acc_{account_id}.sessions_30 come sessions_30
DOVE
urls && array(select id da acc_{account_id}.urls dove url ILIKE '%enterprise_customer.com/jobs%')::text[]Questa query veniva eseguita lentamente. Poiché JOIN- le subquery sono veloci e le subquery sono veloci, rimaneva solo && l'operatore.
Questa è l'operazione chiave. Dobbiamo sempre cercare in tutta la tabella principale degli URL per cercare nel modello, e dobbiamo sempre trovare intersezioni. Non possiamo cercare direttamente nei record degli URL, poiché sono semplicemente ID che fanno riferimento a urls.
Sulla strada per la soluzione
&& è lento, poiché entrambi i set sono enormi. L'operazione sarà relativamente veloce se sostituisco urls in { "http://google.com/", "http://wingify.com/" }.
Ho iniziato a cercare un modo per fare in Postgres l'intersezione degli insiemi senza utilizzare &&, ma senza particolare 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 condizioni aggiuntive sarà —
SELECT urls.url
DA
acc_{account_id}.urls come urls,
(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
DOVE
urls.id = unrolled_urls.id E
urls.url ILIKE '%jobs%'Invece di JOIN sintassi ho semplicemente usato una subquery e ho srotolato recording_data.urls l'array, in modo da poter applicare direttamente la condizione in DOVE.
La cosa più importante qui è che && viene utilizzato per verificare se il record dà corrispondenza con l'URL specificato. Strizzando un po' gli occhi, si può vedere in questa operazione il movimento tra gli elementi dell'array (o righe della tabella) e l'interruzione al soddisfacimento della condizione (corrispondenza). Ti ricorda qualcosa? Ah, ESISTE.
Poiché su recording_data.urls può essere fatto riferimento dall'esterno del contesto della subquery, quando accade, possiamo tornare al nostro vecchio amico ESISTE e racchiuderlo in una subquery.
Unendo tutto insieme, otteniamo la query finale ottimizzata:
SELEZIONA
count(*)
DA
acc_{account_id}.urls come recordings_urls,
acc_{account_id}.recording_data come recording_data,
acc_{account_id}.sessions come sessions
DOVE
recording_data.usp_id = sessions.usp_id
E ( 1 = 1 )
E sessions.referrer_id = recordings_urls.id
E r_time > to_timestamp(1542585600)
E r_time =5
E recording_data.num_of_pages > 0
E ESISTE(
SELEZIONA urls.url
DA
acc_{account_id}.urls come urls,
(SELEZIONA unnest(urls) COME rec_url_id DA acc_{account_id}.recording_data)
COME unrolled_urls
DOVE
urls.id = unrolled_urls.rec_url_id E
urls.url ILIKE '%enterprise_customer.com/jobs%'
);
E il tempo totale di esecuzione Tempo: 1898.717 ms È ora di festeggiare?!?
Non così in fretta! Prima devi controllare la correttezza. Ero estremamente sospettoso riguardo a ESISTE ottimizzazione, poiché modifica la logica per una conclusione più rapida. Dobbiamo essere certi di non aver introdotto un errore non evidente nella query.
Una semplice verifica consisteva nell'eseguire count(*) sia su query lente che veloci per un gran numero di diversi set di dati. Poi, per un piccolo sottoinsieme di dati, ho verificato manualmente la correttezza di tutti i risultati.
Tutte le verifiche hanno dato risultati costantemente positivi. Abbiamo sistemato tutto!
Lezioni apprese
Da questa storia si possono estrarre molte lezioni:
- I piani di query non raccontano tutta la storia, ma possono fornire spunti
- I principali sospettati non sono sempre i veri colpevoli
- Le query lente possono essere separati per isolare i colli di bottiglia
- Non tutte le ottimizzazioni sono per loro natura riduttive
- Utilizzo
ESISTE, dove possibile, può portare a un aumento netto delle prestazioni
Conclusione
Siamo passati da un tempo di query di ~24 minuti a 2 secondi — un aumento di prestazioni piuttosto significativo! Anche se questo articolo è stato lungo, tutti gli esperimenti che abbiamo effettuato si sono svolti in un giorno e, a nostra stima, hanno preso da 1,5 a 2 ore per ottimizzazioni e test.
SQL è un linguaggio meraviglioso, se non ne hai paura, ma cerchi di conoscerlo e utilizzarlo. Avendo una buona comprensione di come vengono eseguite le query SQL, di come i DB generano i piani di query, di come funzionano gli indici e semplicemente delle dimensioni dei dati con cui hai a che fare, puoi davvero eccellere nell'ottimizzazione delle query. Tuttavia, è altrettanto importante continuare a provare approcci diversi e a scomporre lentamente il problema, identificando i colli di bottiglia.
La parte migliore nel raggiungere risultati simili è il miglioramento visibile e tangibile della velocità — quando un rapporto che prima non si caricava nemmeno, ora si carica quasi istantaneamente.
Un grazie speciale ai miei colleghi del team di Aditya Mishra, Aditya Gaur e per il brainstorming e Dinkar Pandir per aver trovato un errore importante nella nostra richiesta finale, prima che ci separassimo definitivamente da essa!
Fonte: habr.com
