{"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\/no\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"Historien om en SQL-etterforskning","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

I fjor i desember mottok jeg en interessant feilrapport fra VWO-st\u00f8tteteamet. Lastetiden for en av analyserapportene for en stor bedriftskunde virket uforholdsmessig lang. Siden dette er mitt ansvar, begynte jeg straks \u00e5 fokusere p\u00e5 \u00e5 l\u00f8se problemet.<\/p>\n

<\/p>\n

Bakgrunn<\/h2>\n

<\/p>\n

For \u00e5 gi litt kontekst, vil jeg forklare litt om VWO. Dette er en plattform som lar deg kj\u00f8re ulike m\u00e5lrettede kampanjer p\u00e5 nettsidene dine: gjennomf\u00f8re A\/B-tester, spore bes\u00f8kende og konverteringer, analysere salgstrakter, vise varmekart og spille av bes\u00f8kopptak.<\/p>\n

<\/p>\n

Men det viktigste med plattformen er rapporteringen. Alle funksjonene nevnt ovenfor er sammenkoblet. For bedriftskunder ville en enorm mengde informasjon v\u00e6rt helt ubrukelig uten en kraftig plattform som presenterer den for analyse.<\/p>\n

<\/p>\n

Ved \u00e5 bruke plattformen kan du gj\u00f8re foresp\u00f8rsel p\u00e5 et stort datasett. Her er et enkelt eksempel:<\/p>\n

<\/p>\n

Vis alle klikk p\u00e5 siden \"abc.com\"\nFRA <dato d1> TIL <dato d2>\nfor personer som\nbrukte Chrome ELLER\n(var i Europa OG brukte iPhone)<\/pre>\n
<\/p>\n
Legg merke til de boolske operatorene. De er tilgjengelige for kunder i foresp\u00f8rselgrensesnittet, slik at du kan lage s\u00e5 kompliserte foresp\u00f8rsel som n\u00f8dvendig for \u00e5 f\u00e5 utvalg.<\/p>\n
<\/p>\n
Langsom foresp\u00f8rsel<\/h2>\n
<\/p>\n
Kunden det er snakk om, pr\u00f8vde \u00e5 gj\u00f8re noe som intuitivt burde fungere raskt:<\/p>\n
<\/p>\n
Vis alle sesjonsopptak\nfor brukere som bes\u00f8kte hvilken som helst side\nmed en URL som inneholder \"\/jobs\"<\/pre>\n
<\/p>\n
Denne nettsiden hadde enorm mengde trafikk, og vi lagret over en million unike URL-adresser kun for den. Og de \u00f8nsket \u00e5 finne et ganske enkelt URL-m\u00f8nster relatert til forretningsmodellen deres.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Foresp\u00f8rsel om etterforskning<\/h2>\n
<\/p>\n
La oss se hva som skjer i databasen. Nedenfor er den originale langsomme SQL-foresp\u00f8rselen:<\/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
Og her er tidene:<\/p>\n
<\/p>\n
Forventet tid: 1.480 ms\nKj\u00f8ringstid: 1431924.650 ms<\/pre>\n
<\/p>\n
Foresp\u00f8rselen krysset 150 000 rader. Sp\u00f8rringsplanleggeren viste noen interessante detaljer, men ingen \u00e5penbare flaskehalser.<\/p>\n
<\/p>\n
La oss g\u00e5 videre med foresp\u00f8rselen. Som vi ser, gj\u00f8r den JOIN<\/code> tre tabeller:<\/p>\n
<\/p>\n
    \n
  1. \u00f8kter<\/strong>: for \u00e5 vise sesjonsinformasjon: nettleser, brukeragent, land og s\u00e5 videre.<\/li>\n
  2. recording_data<\/strong>: registrerte URL-er, sider, varighet av bes\u00f8kene<\/li>\n
  3. urls<\/strong>: for \u00e5 unng\u00e5 duplisering av ekstremt store URL-er, lagrer vi dem i en egen tabell.<\/li>\n<\/ol>\n
    <\/p>\n
    V\u00e6r ogs\u00e5 oppmerksom p\u00e5 at alle tabellene v\u00e5re allerede er delt opp etter account_id<\/code>. Dermed er situasjonen utelukket hvor problemer med andre oppst\u00e5r p\u00e5 grunn av \u00e9n spesielt stor konto.<\/p>\n
    <\/p>\n
    I s\u00f8ken etter spor<\/h2>\n
    <\/p>\n
    Ved n\u00e6rmere ettersyn ser vi at noe er galt med den spesifikke foresp\u00f8rselen. Det er verdt \u00e5 se n\u00e6rmere p\u00e5 denne raden:<\/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
    Den f\u00f8rste tanken var at p\u00e5 grunn av ILIKE<\/code> p\u00e5 alle disse lange URL-ene (vi har mer enn 1,4 millioner unike\u00a0<\/strong>URL-er samlet for denne kontoen) kan ytelsen v\u00e6re d\u00e5rlig.<\/p>\n
    <\/p>\n
    Men nei \u2014 det er ikke saken!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 rader)\n\nTid: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    Selve s\u00f8keforesp\u00f8rselen etter m\u00f8nstre tar bare 5 sekunder. S\u00f8king etter m\u00f8nstre p\u00e5 en million unike URL-er er tydeligvis ikke et problem.<\/p>\n
    <\/p>\n
    Den neste mistenkte p\u00e5 listen \u2014 flere JOIN<\/code>. Kan det ha v\u00e6rt deres overdrevne bruk som f\u00f8rte til forsinkelse? Vanligvis JOIN<\/code>\u2018er er de mest \u00e5penbare kandidatene for ytelsesproblemer, men jeg trodde ikke at v\u00e5rt tilfelle var typisk.<\/p>\n
    <\/p>\n
    analytics_db=# SELECT\n    count(*)\nFROM\n    acc_{account_id}.urls as recordings_urls,\n    acc_{account_id}.recording_data_0 as recording_data,\n    acc_{account_id}.sessions_0 as sessions\nWHERE\n    recording_data.usp_id = sessions.usp_id\n    AND sessions.referrer_id = recordings_urls.id\n    AND r_time > to_timestamp(1542585600)\n    AND r_time =5\n    AND recording_data.num_of_pages > 0 ;\n count\n-------\n  8086\n(1 rad)\n\nTid: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    Og det var heller ikke v\u00e5rt tilfelle. JOIN<\/code>\u2018er viste seg \u00e5 v\u00e6re ganske raske.<\/p>\n
    <\/p>\n
    Vi snevrer inn listen over mistenkte<\/h2>\n
    <\/p>\n
    Jeg var klar til \u00e5 begynne \u00e5 endre foresp\u00f8rselen for \u00e5 oppn\u00e5 enhver mulig forbedring i ytelse. Vi i teamet utviklet to hovedideer:<\/p>\n
    <\/p>\n