{"id":35259,"date":"2019-10-31T22:03:17","date_gmt":"2019-10-31T19:03:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-odnogo-sql-rassledovaniya\/"},"modified":"2019-10-31T22:03:17","modified_gmt":"2019-10-31T19:03:17","slug":"istoriya-odnogo-sql-rassledovaniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"La storia di un'inchiesta SQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

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\u00e0, mi sono subito concentrato sulla risoluzione del problema.<\/p>\n

<\/p>\n

Antefatti<\/h2>\n

<\/p>\n

Per chiarire di cosa stiamo parlando, racconter\u00f2 brevemente di VWO. \u00c8 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.<\/p>\n

<\/p>\n

Ma la cosa pi\u00f9 importante della piattaforma \u00e8 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.<\/p>\n

<\/p>\n

Utilizzando la piattaforma, \u00e8 possibile effettuare richieste arbitrarie su un grande insieme di dati. Ecco un semplice esempio:<\/p>\n

<\/p>\n

Mostra tutti i clic sulla pagina \"abc.com\"\nDAL <data d1> AL <data d2>\nper le persone che\nhanno utilizzato Chrome O\n(si trovavano in Europa E hanno utilizzato iPhone)<\/pre>\n
<\/p>\n
Fai attenzione agli operatori booleani. Sono disponibili per i clienti nell'interfaccia di query, per creare richieste complesse per ottenere campioni.<\/p>\n
<\/p>\n
Richiesta lenta<\/h2>\n
<\/p>\n
Il cliente in questione cercava di fare qualcosa che intuitivamente doveva funzionare rapidamente:<\/p>\n
<\/p>\n
Mostra tutte le registrazioni delle sessioni\nper gli utenti che hanno visitato qualsiasi pagina\ncon un URL che contiene \"\/jobs\"<\/pre>\n
<\/p>\n
Questo sito aveva un'enorme quantit\u00e0 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.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Indagine preliminare<\/h2>\n
<\/p>\n
Diamo un'occhiata a cosa succede nel database. Di seguito \u00e8 riportata l'originale query SQL lenta:<\/p>\n
<\/p>\n
SELECT \n    count(*) \nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions \nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND sessions.referrer_id = recordings_urls.id \n    AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com\/jobs%')::text[] ) \n    AND r_time > to_timestamp(1542585600) \n    AND r_time < to_timestamp(1545177599) \n    AND recording_data.duration >= 5 \n    AND recording_data.num_of_pages > 0 ;<\/code><\/pre>\n
<\/p>\n
Ecco i tempi:<\/p>\n
<\/p>\n
Tempo pianificato: 1,480 ms\nTempo di esecuzione: 1.431.924,650 ms<\/pre>\n
<\/p>\n
La query ha esaminato 150 mila righe. Il pianificatore di query ha mostrato un paio di dettagli interessanti, ma nessun collo di bottiglia evidente.<\/p>\n
<\/p>\n
Esploriamo ulteriormente la query. Come si vede, essa effettua JOIN<\/code> di tre tabelle:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: per visualizzare le informazioni sulle sessioni: browser, user agent, paese e cos\u00ec via.<\/li>\n
  2. recording_data<\/strong>: URL registrati, pagine, durata delle visite<\/li>\n
  3. urls<\/strong>: per evitare la duplicazione di URL estremamente lunghi, li memorizziamo in una tabella separata.<\/li>\n<\/ol>\n
    <\/p>\n
    Inoltre, si noti che tutte le nostre tabelle sono gi\u00e0 divise per account_id<\/code>. In questo modo si esclude la situazione in cui un singolo account particolarmente grande causa problemi agli altri.<\/p>\n
    <\/p>\n
    In cerca di indizi<\/h2>\n
    <\/p>\n
    All'esame pi\u00f9 attento, vediamo che qualcosa in una specifica query non va. \u00c8 opportuno prestare attenzione a questa riga:<\/p>\n
    <\/p>\n
    urls && array(\n\tselect id from acc_{account_id}.urls \n\twhere url  ILIKE  '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n
    <\/p>\n
    La prima idea era che forse, a causa di ILIKE<\/code> su tutti questi URL lunghi (abbiamo pi\u00f9 di 1,4 milioni di URL unici, raccolti per questo account) la performance potrebbe deteriorarsi.\u00a0<\/strong>Ma no \u2014 non \u00e8 questo il problema!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 righe)\n\nTempo: 5.231,765 ms<\/p>\n
    <\/p>\n
    La query di ricerca per modello impiega solo 5 secondi. La ricerca per modello su un milione di URL unici non \u00e8 chiaramente un problema.<\/code><\/pre>\n
    <\/p>\n
    Il prossimo sospettato in lista \u00e8 \u2014 alcuni<\/p>\n
    <\/p>\n
    . Forse il loro uso eccessivo ha portato a un rallentamento? Di solito JOIN<\/code>\u2018s sono i candidati pi\u00f9 ovvi per problemi di performance, ma non credevo che il nostro caso fosse tipico. JOIN<\/code>I ‘ jsou i candidati evidenti per problemi di prestazioni, ma non credevo che il nostro caso fosse tipico.<\/p>\n
    <\/p>\n
    E anche questo non era il nostro caso.<\/code><\/pre>\n
    <\/p>\n
    \u2018s si sono rivelati piuttosto veloci. JOIN<\/code>I ‘ si sono rivelati piuttosto veloci.<\/p>\n
    <\/p>\n
    Ero pronto a modificare la query per ottenere eventuali miglioramenti di performance. Io e il mio team abbiamo sviluppato 2 idee principali:<\/h2>\n
    <\/p>\n
    Utilizzare EXISTS per la sottoquery degli URL<\/p>\n
    <\/p>\n