{"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\/nl\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"Het verhaal van een SQL-onderzoek","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

In december vorig jaar ontving ik een interessante foutmelding van het VWO-ondersteuningsteam. De laadtijd van een van de analytische rapporten voor een grote zakelijke klant leek ongewoon lang. Aangezien dit binnen mijn verantwoordelijkheid valt, concentreerde ik me onmiddellijk op het oplossen van het probleem.<\/p>\n

<\/p>\n

Achtergrond<\/h2>\n

<\/p>\n

Om duidelijk te maken waar het om gaat, zal ik kort iets vertellen over VWO. Dit is een platform waarmee je verschillende gerichte campagnes op je websites kunt uitvoeren: A\/B-experimenten uitvoeren, bezoekers en conversies volgen, verkoopfunnels analyseren, heatmaps weergeven en sessierecords afspelen.<\/p>\n

<\/p>\n

Maar het belangrijkste van het platform is rapportage. Al deze functies zijn met elkaar verbonden. Voor zakelijke klanten zou een enorme hoeveelheid informatie eenvoudigweg nutteloos zijn zonder een krachtig platform dat deze presenteert voor analyse.<\/p>\n

<\/p>\n

Met het platform kun je willekeurige verzoeken doen op een grote dataset. Hier is een eenvoudig voorbeeld:<\/p>\n

<\/p>\n

Toon alle klikken op de pagina \"abc.com\"\nVAN <datum d1> TOT <datum d2>\nvoor mensen die\nChrome gebruikten OF\n(in Europa waren EN iPhone gebruikten)<\/pre>\n
<\/p>\n
Let op de logische operators. Deze zijn beschikbaar voor klanten in de verzoekinterface, zodat ze zo complex mogelijke verzoeken kunnen doen om selecties te verkrijgen.<\/p>\n
<\/p>\n
Langzame aanvraag<\/h2>\n
<\/p>\n
De betrokken klant probeerde iets te doen dat intu\u00eftief snel zou moeten werken:<\/p>\n
<\/p>\n
Laat alle sessierecords zien\nvoor gebruikers die een pagina hebben bezocht\nmet een URL die \"\\\/jobs\" bevat<\/pre>\n
<\/p>\n
Deze site had een enorme hoeveelheid verkeer, en we bewaarden meer dan een miljoen unieke URL-adressen alleen al voor deze site. En ze wilden een vrij eenvoudig URL-sjabloon vinden dat betrekking had op hun bedrijfsmodel.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Voorlopig onderzoek<\/h2>\n
<\/p>\n
Laten we eens kijken naar wat er in de database gebeurt. Hieronder staat de oorspronkelijke langzame SQL-query:<\/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
Hier zijn de tijden:<\/p>\n
<\/p>\n
Geplande tijd: 1.480 ms\nUitvoertijd: 1431924.650 ms<\/pre>\n
<\/p>\n
De query doorzocht 150.000 rijen. De queryplanner toonde een paar interessante details, maar geen duidelijke knelpunten.<\/p>\n
<\/p>\n
Laten we de query verder bestuderen. Zoals te zien is, maakt hij JOIN<\/code> drie tabellen aan:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: om sessie-informatie weer te geven: browser, user agent, land, enzovoort.<\/li>\n
  2. recording_data<\/strong>: geregistreerde URL's, pagina's, duur van bezoeken<\/li>\n
  3. urls<\/strong>: om duplicatie van extreem lange URL's te voorkomen, bewaren we ze in een aparte tabel.<\/li>\n<\/ol>\n
    <\/p>\n
    Let ook op dat al onze tabellen al zijn gescheiden op account_id<\/code>. Dit voorkomt dat, door \u00e9\u00e9n bijzonder groot account, andere accounts problemen ondervinden.<\/p>\n
    <\/p>\n
    Op zoek naar aanwijzingen<\/h2>\n
    <\/p>\n
    Bij nader inzien zien we dat er iets niet klopt met deze specifieke query. We moeten deze regel nader bekijken:<\/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
    De eerste gedachte was dat het mogelijk aan ILIKE<\/code> ligt, aangezien al deze lange URL's (we hebben meer dan 1,4 miljoen unieke\u00a0<\/strong>URL's verzameld voor dit account) de prestaties zouden kunnen be\u00efnvloeden.<\/p>\n
    <\/p>\n
    Maar nee \u2014 dat is het niet!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 rijen)\n\nTijd: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    De zoekopdracht op basis van het patroon duurt slechts 5 seconden. Zoeken op een miljoen unieke URL's blijkt duidelijk geen probleem te zijn.<\/p>\n
    <\/p>\n
    De volgende verdachte op de lijst \u2014 meerdere JOIN<\/code>. Misschien heeft hun overmatig gebruik geleid tot vertraging? Gewoonlijk JOIN<\/code>'s zijn de meest voor de hand liggende kandidaten voor prestatieproblemen, maar ik geloofde niet dat ons geval typisch was.<\/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 rij)\n\nTijd: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    En ook dat was niet ons geval. JOIN<\/code>'s bleken verrassend snel te zijn.<\/p>\n
    <\/p>\n
    De kring van verdachten verkleinen<\/h2>\n
    <\/p>\n
    Ik was bereid om de query te wijzigen om eventuele prestatieverbeteringen te bereiken. Mijn team en ik ontwikkelden 2 belangrijke idee\u00ebn:<\/p>\n
    <\/p>\n