Lo scorso dicembre ho ricevuto un'interessante segnalazione di bug dal team di supporto VWO. Il tempo di caricamento di uno dei report di analisi per un grande cliente aziendale sembrava proibitivo. E poiché questa è la mia area di responsabilità, mi sono subito concentrato sulla risoluzione del problema.
Sfondo
Per rendere chiaro di cosa sto parlando, ti parlerò un po' del VWO. Si tratta di una piattaforma con la quale puoi lanciare diverse campagne mirate sui tuoi siti web: condurre esperimenti A/B, monitorare visitatori e conversioni, analizzare il funnel di vendita, visualizzare mappe di calore e riprodurre registrazioni delle visite.
Ma la cosa più importante della piattaforma è il reporting. Tutte le funzioni di cui sopra sono interconnesse. E per i clienti aziendali, un’enorme quantità di informazioni sarebbe semplicemente inutile senza una potente piattaforma che le presenti in forma analitica.
Utilizzando la piattaforma, puoi effettuare una query casuale su un set di dati di grandi dimensioni. Ecco un semplice esempio:
Mostra tutti i clic sulla pagina "abc.com" DA <data d1> A <data d2> per le persone che hanno utilizzato Chrome OPPURE (si trovano in Europa E hanno utilizzato un iPhone)
Prestare attenzione agli operatori booleani. Sono disponibili per i client nell'interfaccia di query per eseguire query arbitrariamente complesse per ottenere campioni.
Richiesta lenta
Il cliente in questione stava cercando di fare qualcosa che intuitivamente dovrebbe funzionare rapidamente:
Mostra tutti i record di sessione per gli utenti che hanno visitato qualsiasi pagina con un URL contenente "/jobs"
Questo sito aveva un sacco di traffico e memorizzavamo oltre un milione di URL univoci solo per questo. E volevano trovare un modello di URL abbastanza semplice correlato al loro modello di business.
Investigazioni preliminari
Diamo un'occhiata a cosa sta succedendo nel database. Di seguito è riportata la query 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 ;Ed ecco le tempistiche:
Tempo pianificato: 1.480 ms Tempo di esecuzione: 1431924.650 ms
La query ha scansionato 150mila righe. Il pianificatore di query ha mostrato un paio di dettagli interessanti, ma nessun collo di bottiglia evidente.
Studiamo ulteriormente la richiesta. Come puoi vedere, lo fa JOIN tre tabelle:
- sessioni: per visualizzare le informazioni sulla sessione: browser, agente utente, paese e così via.
- registrazione_dati: URL registrati, pagine, durata delle visite
- urls: per evitare di duplicare URL estremamente grandi, li memorizziamo in una tabella separata.
Tieni inoltre presente che tutte le nostre tabelle sono già partizionate da account_id. In questo modo si esclude la situazione in cui un conto particolarmente grande causa problemi ad altri.
Alla ricerca di indizi
Ad un esame più attento, vediamo che qualcosa non va in una particolare richiesta. Vale la pena dare un'occhiata più da vicino a questa riga:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Il primo pensiero è stato: forse perché ILIKE su tutti questi URL lunghi (ne abbiamo oltre 1,4 milioni unico URL raccolti per questo account) le prestazioni potrebbero risentirne.
Ma no, non è questo il punto!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msLa richiesta di ricerca del modello richiede solo 5 secondi. La ricerca di un modello in un milione di URL univoci chiaramente non è un problema.
Il prossimo sospettato sulla lista è diversi JOIN. Forse il loro uso eccessivo ha causato il rallentamento? Generalmente JOINsono i candidati più ovvi per problemi di prestazioni, ma non credevo 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 < to_timestamp(1545177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;
count
-------
8086
(1 row)
Time: 147.851 msE anche questo non è stato il nostro caso. JOINsi è rivelato abbastanza veloce.
Restringere la cerchia dei sospettati
Ero pronto per iniziare a modificare la query per ottenere eventuali miglioramenti delle prestazioni. Io e il mio team abbiamo sviluppato 2 idee principali:
- Utilizza EXISTS per l'URL della sottoquery: Volevamo verificare nuovamente se ci fossero problemi con la sottoquery per gli URL. Un modo per raggiungere questo obiettivo è semplicemente utilizzare
EXISTS.EXISTSmigliora notevolmente le prestazioni poiché termina immediatamente non appena trova l'unica stringa che corrisponde alla 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 < to_timestamp(1549177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;
count
32519
(1 row)
Time: 1636.637 msBeh si. Sottoquery quando inserita EXISTS, rende tutto super veloce. La prossima domanda logica è perché la richiesta con JOIN-ami e la sottoquery stessa sono veloci individualmente, ma sono terribilmente lenti insieme?
- Spostamento della sottoquery nel CTE : Se la query è di per sé veloce, 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 < to_timestamp(1545107599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0;Ma era ancora molto lento.
Trovare il colpevole
Per tutto questo tempo, una piccola cosa mi è balenata davanti agli occhi, che ho costantemente messo da parte. Ma visto che non c'era più niente, ho deciso di guardare anche lei. sto parlando di && operatore. Ciao EXISTS solo prestazioni migliorate && era l'unico fattore comune rimasto tra tutte le versioni della query lenta.
Guardando , Lo vediamo && utilizzato quando è necessario trovare elementi comuni tra due array.
Nella richiesta originale questo è:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Ciò significa che eseguiamo una ricerca di pattern sui nostri URL, quindi troviamo l'intersezione con tutti gli URL con post comuni. Questo crea un po' di confusione perché "urls" qui non si riferisce alla tabella contenente tutti gli URL, ma alla colonna "urls" nella tabella recording_data.
Con crescenti sospetti al riguardo &&, ho provato a trovarne conferma nel piano di query generato EXPLAIN ANALYZE (Avevo già un piano salvato, ma di solito mi sento più a mio agio a sperimentare in SQL piuttosto che cercare di comprendere l'opacità dei 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: 52710C'erano diverse linee di filtri solo da &&. Ciò significava che questa operazione non solo era costosa, ma veniva anche eseguita più volte.
L'ho testato 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 è stata lenta. Perché il JOIN-s sono veloci e le sottoquery sono veloci, l'unica cosa rimasta era && operatore.
Questa è solo un'operazione chiave. Dobbiamo sempre cercare nell'intera tabella sottostante di URL per cercare un modello e dobbiamo sempre trovare intersezioni. Non possiamo cercare direttamente per record URL, perché si tratta solo di ID a cui si fa riferimento urls.
Sulla strada per una soluzione
&& lento perché entrambi i set sono enormi. L'operazione sarà relativamente rapida se sostituisco urls su { "http://google.com/", "http://wingify.com/" }.
Ho iniziato a cercare un modo per impostare l'intersezione in Postgres senza utilizzare &&, ma senza molto successo.
Alla fine abbiamo deciso di risolvere il problema isolatamente: dammi tutto urls righe per le quali 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 appena utilizzato una sottoquery e l'ho espansa recording_data.urls array in modo da poter applicare direttamente la condizione in WHERE.
La cosa più importante qui è questa && utilizzato per verificare se una determinata voce contiene un URL corrispondente. Se strizzi un po' gli occhi, puoi vedere che questa operazione si sposta attraverso gli elementi di un array (o le righe di una tabella) e si ferma quando viene soddisfatta una condizione (corrispondenza). Non ti ricorda niente? Sì, EXISTS.
Da allora recording_data.urls può essere referenziato dall'esterno del contesto della sottoquery, quando ciò accade possiamo ricorrere al nostro vecchio amico EXISTS e avvolgi la sottoquery con esso.
Mettendo tutto insieme, otteniamo la query ottimizzata finale:
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%'
);
E il tempo di consegna finale Time: 1898.717 ms Tempo di festeggiare?!?
Non così in fretta! Per prima cosa devi verificare la correttezza. Ero estremamente sospettoso EXISTS ottimizzazione poiché cambia la logica per completarla prima. Dobbiamo essere sicuri di non aver aggiunto un errore non evidente alla richiesta.
Bisognava eseguire un semplice test count(*) su query sia lente che veloci per un gran numero di set di dati diversi. Quindi, per un piccolo sottoinsieme di dati, ho verificato manualmente che tutti i risultati fossero corretti.
Tutti i test hanno dato risultati costantemente positivi. Abbiamo sistemato tutto!
Lezioni imparate
Ci sono molte lezioni da imparare da questa storia:
- I piani di query non raccontano tutta la storia, ma possono fornire indizi
- Non sempre i principali sospettati sono i veri colpevoli
- Le query lente possono essere suddivise per isolare i colli di bottiglia
- Non tutte le ottimizzazioni sono di natura riduttiva
- l'uso di
EXIST, ove possibile, può portare a notevoli aumenti della produttività
conclusione
Siamo passati da un tempo di query di circa 24 minuti a 2 secondi: un aumento delle prestazioni piuttosto significativo! Anche se questo articolo è uscito alla grande, tutti gli esperimenti che abbiamo fatto si sono svolti in un giorno e si stima che abbiano impiegato da 1,5 a 2 ore per le ottimizzazioni e i test.
SQL è un linguaggio meraviglioso se non ne hai paura, ma cerca di impararlo e usarlo. Avendo una buona conoscenza di come vengono eseguite le query SQL, di come il database genera piani di query, di come funzionano gli indici e semplicemente della dimensione dei dati con cui hai a che fare, puoi avere molto successo nell'ottimizzazione delle query. È altrettanto importante, però, continuare a provare approcci diversi e pian piano abbattere il problema, individuando i colli di bottiglia.
L'aspetto migliore nel raggiungimento di risultati come questi è il notevole e visibile miglioramento della velocità, grazie al quale un report che prima non si caricava nemmeno, ora si carica quasi istantaneamente.
Ringraziamenti speciali i miei compagni al comando di Aditya Mishra, Aditya Gauru и per il brainstorming e Dinkar Pandir per aver trovato un errore importante nella nostra richiesta finale prima di salutarla definitivamente!
Fonte: habr.com