{"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\/sl\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"Zgodba enega SQL pregleda","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Decembra lani sem prejel zanimivo poro\u010dilo o napaki od ekipe za podporo VWO. \u010cas nalaganja enega od analiti\u010dnih poro\u010dil za velikega korporativnega klienta se je zdel izjemno dolg. Glede na to, da je to podro\u010dje moje odgovornosti, sem se takoj osredoto\u010dil na re\u0161itev te\u017eave.<\/p>\n

<\/p>\n

Pozadina<\/h2>\n

<\/p>\n

Da bo jasno, o \u010dem govorim, bom na kratko predstavil VWO. To je platforma, s katero lahko izvajate razli\u010dne ciljne kampanje na svojih spletnih straneh: izvajate A\/B eksperimente, spremljate obiskovalce in konverzije, analizirate prodajne lijake, prikazujete toplotne zemljevide in predvajate posnetke obiskov.<\/p>\n

<\/p>\n

Toda najpomembnej\u0161a funkcija platforme je priprava poro\u010dil. Vse zgoraj omenjene funkcije so med seboj povezane. Za korporativne stranke bi bil velik obseg informacij preprosto brez smisla brez mo\u010dne platforme, ki jih predstavlja v analiti\u010dni obliki.<\/p>\n

<\/p>\n

Z uporabo platforme lahko postavite poljuben zahtevek na velikem naboru podatkov. Tukaj je preprost primer:<\/p>\n

<\/p>\n

Prika\u017ei vse klike na strani \"abc.com\"\nOD <datuma d1> DO <datuma d2>\nza ljudi, ki\nso uporabili Chrome ALI\n(sobili v Evropi IN uporabljali iPhone)<\/pre>\n
<\/p>\n
Upo\u0161tevajte logi\u010dne operatorje. Ti so na voljo klientom v vmesniku za poizvedbe, da bi omogo\u010dili \u010dim bolj kompleksne poizvedbe za pridobivanje vzorcev.<\/p>\n
<\/p>\n
Po\u010dasen poizvedba<\/h2>\n
<\/p>\n
Klient, o katerem govorimo, je posku\u0161al narediti nekaj, kar intuitivno deluje hitro:<\/p>\n
<\/p>\n
Prika\u017ei vse zapise sej\nza uporabnike, ki so obiskali katerokoli stran\ns URL-jem, ki vsebuje \"\\\/jobs\"<\/pre>\n
<\/p>\n
Na tej spletni strani je bilo ogromno prometa, shranili smo ve\u010d kot milijon edinstvenih URL-jev samo za to. In \u017eeleli so najti precej preprost vzorec URL-ja, ki se nana\u0161a na njihov poslovni model.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Predhodno preiskovanje<\/h2>\n
<\/p>\n
Poglejmo, kaj se dogaja v bazi podatkov. Spodaj je izvirni po\u010dasen SQL poizvedba:<\/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
Tukaj so \u010dasovne oznake:<\/p>\n
<\/p>\n
Na\u010drtovani \u010das: 1.480 ms\n\u010cas izvedbe: 1431924.650 ms<\/pre>\n
<\/p>\n
Poizvedba je obdelala 150 tiso\u010d vrstic. Na\u010drtovalec poizvedb je pokazal nekaj zanimivih podrobnosti, a nobenih o\u010ditnih ozkih grl.<\/p>\n
<\/p>\n
Poglejmo \u0161e naprej to poizvedbo. Kot je razvidno, dela JOIN<\/code> treh tabel:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: za prikaz informacij o sejah: brskalnik, uporabni\u0161ka agenta, dr\u017eava itd.<\/li>\n
  2. recording_data<\/strong>: zabele\u017eeni URL-ji, strani, trajanje obiskov<\/li>\n
  3. urls<\/strong>: da bi se izognili podvajanju izjemno dolgih URL-jev, jih shranjujemo v lo\u010deno tabelo.<\/li>\n<\/ol>\n
    <\/p>\n
    Prav tako bodite pozorni, da so vse na\u0161e tabele \u017ee razdeljene po account_id<\/code>. S tem je izklju\u010dena situacija, ko zaradi enega posebej velikega ra\u010duna te\u017eave nastanejo pri drugih.<\/p>\n
    <\/p>\n
    V iskanju dokazov<\/h2>\n
    <\/p>\n
    Ob natan\u010dnej\u0161em pregledu vidimo, da nekaj pri tej konkretni poizvedbi ni v redu. Osvetljiti je vredno to vrstico:<\/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
    Prva misel je bila, da bi morda zaradi ILIKE<\/code> na vseh teh dolgih URL-jih (imamo ve\u010d kot 1,4 milijona edinstvenih\u00a0<\/strong>URL-jev, zbranih za ta ra\u010dun) lahko pri\u0161lo do zmanj\u0161anja uspe\u0161nosti.<\/p>\n
    <\/p>\n
    Ampak ne \u2014 to ni bilo to!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 vrstic)\n\n\u010cas: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    Sama poizvedba za iskanje po vzorcu traja le pet sekund. Iskanje po vzorcu pri milijonu unikatnih URL-jev o\u010ditno ni te\u017eava.<\/p>\n
    <\/p>\n
    Naslednji osumljenec na seznamu \u2014 nekaj JOIN<\/code>. Morda je njihova pretirana uporaba povzro\u010dila upo\u010dasnitev? Obi\u010dajno JOIN<\/code>\u2018y\u2019 so obvious candidates for performance issues, but I didn't believe our case was typical.<\/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 vrstica)\n\n\u010cas: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    In to prav tako ni bil na\u0161 primer. JOIN<\/code>\u2018y\u2019 turned out to be quite fast.<\/p>\n
    <\/p>\n
    Omejitev kroga osumljencev<\/h2>\n
    <\/p>\n
    Bil sem pripravljen za\u010deti spreminjati poizvedbo, da bi dosegel kakr\u0161ne koli mo\u017ene izbolj\u0161ave uspe\u0161nosti. Skupaj s timom smo razvili 2 glavna na\u010dina:<\/p>\n
    <\/p>\n