{"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\/hu\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"Egy SQL nyomoz\u00e1s t\u00f6rt\u00e9nete","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Tavaly decemberben \u00e9rdekes hiba-jelent\u00e9st kaptam a VWO t\u00e1mogat\u00f3i csapat\u00e1t\u00f3l. Egy nagyv\u00e1llalati \u00fcgyf\u00e9l analitikai jelent\u00e9s\u00e9nek bet\u00f6lt\u00e9si ideje elk\u00e9peszt\u0151en hossz\u00fa volt. Mivel ez a ter\u00fclet a felel\u0151ss\u00e9gem volt, azonnal a probl\u00e9ma megold\u00e1s\u00e1ra \u00f6sszpontos\u00edtottam.<\/p>\n

<\/p>\n

H\u00e1tt\u00e9r<\/h2>\n

<\/p>\n

Hogy vil\u00e1gos legyen, mir\u0151l van sz\u00f3, egy kicsit mes\u00e9lek a VWO-r\u00f3l. Ez egy platform, amely lehet\u0151v\u00e9 teszi, hogy k\u00fcl\u00f6nb\u00f6z\u0151 c\u00e9lzott kamp\u00e1nyokat ind\u00edtsunk a weboldalainkon: A\/B teszteket v\u00e9gezz\u00fcnk, nyomon k\u00f6vess\u00fck a l\u00e1togat\u00f3kat \u00e9s a konverzi\u00f3kat, elemezz\u00fck az \u00e9rt\u00e9kes\u00edt\u00e9si t\u00f6lcs\u00e9rt, h\u0151t\u00e9rk\u00e9peket jelen\u00edts\u00fcnk meg, \u00e9s l\u00e1togat\u00e1sok felv\u00e9teleit j\u00e1tszhassuk le.<\/p>\n

<\/p>\n

De a platform legfontosabb funkci\u00f3ja a jelent\u00e9sek k\u00e9sz\u00edt\u00e9se. Az \u00f6sszes fent eml\u00edtett funkci\u00f3 \u00f6sszefon\u00f3dik egym\u00e1ssal. A nagyv\u00e1llalati \u00fcgyfelek sz\u00e1m\u00e1ra nagy mennyis\u00e9g\u0171 inform\u00e1ci\u00f3 haszontalan lenne egy er\u0151teljes platform n\u00e9lk\u00fcl, amely ezt analitikai szempontb\u00f3l \u00e1br\u00e1zolja.<\/p>\n

<\/p>\n

A platform haszn\u00e1lat\u00e1val tetsz\u0151leges lek\u00e9rdez\u00e9st v\u00e9gezhet\u00fcnk egy nagy adathalmazon. \u00cdme egy egyszer\u0171 p\u00e9lda:<\/p>\n

<\/p>\n

Mutass minden kattint\u00e1st az \"abc.com\" oldalon\nSZEPTEMBER 1-t\u0151l\nSZEPTEMBER 30-ig\nazok sz\u00e1m\u00e1ra, akik\nChrome-ot haszn\u00e1ltak VAGY\n(Eur\u00f3p\u00e1ban tart\u00f3zkodtak \u00c9S iPhone-t haszn\u00e1ltak)<\/pre>\n
<\/p>\n
Figyeljen a logikai oper\u00e1torokra. Ezek el\u00e9rhet\u0151ek az \u00fcgyfelek sz\u00e1m\u00e1ra a lek\u00e9rdez\u0151 fel\u00fcleten, hogy tetsz\u0151leges bonyolults\u00e1g\u00fa lek\u00e9rdez\u00e9seket v\u00e9gezzenek a mint\u00e1k megszerz\u00e9s\u00e9re.<\/p>\n
<\/p>\n
\u00cdme az optimiz\u00e1lt v\u00e1ltozata ennek a lek\u00e9rdez\u00e9snek.<\/h2>\n
<\/p>\n
Az eml\u00edtett \u00fcgyf\u00e9l megpr\u00f3b\u00e1lt valamit tenni, ami intuit\u00edv m\u00f3don gyorsan m\u0171k\u00f6dnie kellene:<\/p>\n
<\/p>\n
Mutasd meg az \u00f6sszes \u00fcl\u00e9st, ahol a felhaszn\u00e1l\u00f3k b\u00e1rmely oldalra l\u00e1togattak, amelynek URL-j\u00e9ben szerepel a \"jobs\" kifejez\u00e9s<\/pre>\n
<\/p>\n
Az oldalon hatalmas forgalom volt, \u00e9s t\u00f6bb mint egymilli\u00f3 egyedi URL-c\u00edmet t\u00e1roltunk csup\u00e1n ennek \u00e9rdek\u00e9ben. \u0150k pedig egy viszonylag egyszer\u0171 mint\u00e1zatot akartak tal\u00e1lni, amely a \u00fczleti modellj\u00fckh\u00f6z kapcsol\u00f3dik.<\/p>\n
\n<\/a><\/noindex><\/p>\n
El\u0151zetes vizsg\u00e1lat<\/h2>\n
<\/p>\n
N\u00e9zz\u00fck meg, mi t\u00f6rt\u00e9nik az adatb\u00e1zisban. Al\u00e1bb l\u00e1that\u00f3 az alapvet\u0151en lass\u00fa SQL lek\u00e9rdez\u00e9s:<\/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 = 5 \n    AND recording_data.num_of_pages > 0;<\/code><\/pre>\n
<\/p>\n
\u00cdme az id\u0151z\u00edt\u00e9sek:<\/p>\n
<\/p>\n
Tervezett id\u0151: 1.480 ms\nFuttat\u00e1si id\u0151: 1431924.650 ms<\/pre>\n
<\/p>\n
A lek\u00e9rdez\u00e9s 150 ezer sort \u00e9rintett. A lek\u00e9rdez\u00e9s \u00fctemez\u0151je n\u00e9h\u00e1ny \u00e9rdekes r\u00e9szletet mutatott, de nyilv\u00e1nval\u00f3 sz\u0171k keresztmetszeteket nem tal\u00e1ltunk.<\/p>\n
<\/p>\n
N\u00e9zz\u00fck meg tov\u00e1bb a lek\u00e9rdez\u00e9st. Mint l\u00e1that\u00f3, ezt v\u00e9gzi. JOIN<\/code> h\u00e1rom t\u00e1bl\u00e1zatot:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: a munkamenet-inform\u00e1ci\u00f3k megjelen\u00edt\u00e9s\u00e9hez: b\u00f6ng\u00e9sz\u0151, felhaszn\u00e1l\u00f3i \u00fcgyn\u00f6k, orsz\u00e1g stb.<\/li>\n
  2. recording_data<\/strong>: r\u00f6gz\u00edtett URL-ek, oldalak, l\u00e1togat\u00e1si id\u0151tartamok.<\/li>\n
  3. urls<\/strong>: a rendk\u00edv\u00fcl hossz\u00fa URL-ek megdupl\u00e1z\u00f3d\u00e1s\u00e1nak elker\u00fcl\u00e9se \u00e9rdek\u00e9ben k\u00fcl\u00f6n t\u00e1bl\u00e1zatban t\u00e1roljuk \u0151ket.<\/li>\n<\/ol>\n
    <\/p>\n
    Vegye \u00e9szre azt is, hogy minden t\u00e1bl\u00e1zatunk m\u00e1r el van v\u00e1lasztva account_id<\/code>. \u00cdgy elker\u00fclhetj\u00fck, hogy egy k\u00fcl\u00f6n\u00f6sen nagy fi\u00f3kb\u00f3l ad\u00f3d\u00f3 probl\u00e9m\u00e1k a t\u00f6bbieket is \u00e9rints\u00e9k.<\/p>\n
    <\/p>\n
    Nyomoz\u00e1s nyomok ut\u00e1n<\/h2>\n
    <\/p>\n
    K\u00f6zelebbr\u0151l megvizsg\u00e1lva l\u00e1tjuk, hogy valami nem stimmel a konkr\u00e9t lek\u00e9rdez\u00e9ssel. \u00c9rdemes k\u00f6zelebbr\u0151l megn\u00e9zni ezt a sort:<\/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
    Az els\u0151 gondolatom az volt, hogy tal\u00e1n az ILIKE miatt van. ILIKE<\/code> ezeken a hossz\u00fa URL-eken (t\u00f6bb mint 1,4 milli\u00f3 egyedi\u00a0<\/strong>URL-c\u00edmet gy\u0171jt\u00f6tt\u00fcnk \u00f6ssze ehhez a fi\u00f3khoz), a teljes\u00edtm\u00e9ny cs\u00f6kkenhet.<\/p>\n
    <\/p>\n
    De nem \u2014 nem err\u0151l van sz\u00f3!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 sor)\n\nId\u0151: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    Maga a mintakeres\u00e9s mind\u00f6ssze 5 m\u00e1sodpercet vesz ig\u00e9nybe. A mintakeres\u00e9s egy milli\u00f3 egyedi URL-en nyilv\u00e1nval\u00f3an nem jelent probl\u00e9m\u00e1t.<\/p>\n
    <\/p>\n
    A k\u00f6vetkez\u0151 gyan\u00fas\u00edtott a list\u00e1n \u2014 t\u00f6bb. JOIN<\/code>Lehet, hogy a t\u00falzott haszn\u00e1latuk lass\u00edtotta le a rendszert? \u00c1ltal\u00e1ban JOIN<\/code>\u2018A mem\u00f3ria \u2014 a legszembet\u0171n\u0151bb jel\u00f6lt a teljes\u00edtm\u00e9nyprobl\u00e9m\u00e1kra, de nem hittem, hogy a mi eset\u00fcnk tipikus.<\/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 sor)\n\nId\u0151: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    \u00c9s ez sem a mi eset\u00fcnk volt. JOIN<\/code>\u2018A mem\u00f3ria meglehet\u0151sen gyorsnak bizonyult.<\/p>\n
    <\/p>\n
    Sz\u0171k\u00edtj\u00fck a gyan\u00fas\u00edtottak k\u00f6r\u00e9t<\/h2>\n
    <\/p>\n
    K\u00e9sz voltam m\u00f3dos\u00edtani a lek\u00e9rdez\u00e9st b\u00e1rmilyen lehets\u00e9ges teljes\u00edtm\u00e9nyjavul\u00e1s \u00e9rdek\u00e9ben. A csapatommal k\u00e9t f\u0151 \u00f6tletet dolgoztunk ki:<\/p>\n
    <\/p>\n