Viime joulukuussa sain mielenkiintoisen vikaraportin VWO:n tukitiimiltä. Yhden suuren yritysasiakkaan analytiikkaraportin latausaika vaikutti kohtuuttomalta. Ja koska tämä on minun vastuualueeni, keskityin heti ongelman ratkaisemiseen.
esihistoria
Selvittääkseni, mistä puhun, kerron sinulle hieman VWO:sta. Tämä on alusta, jolla voit käynnistää verkkosivustoillasi erilaisia kohdistettuja kampanjoita: suorittaa A/B-kokeita, seurata kävijöitä ja konversioita, analysoida myyntisuppiloa, näyttää lämpökarttoja ja toistaa vierailutallenteita.
Mutta tärkein asia alustassa on raportointi. Kaikki yllä mainitut toiminnot ovat yhteydessä toisiinsa. Ja yritysasiakkaille valtava määrä tietoa olisi yksinkertaisesti hyödytöntä ilman tehokasta alustaa, joka esittää sen analyyttisessä muodossa.
Alustan avulla voit tehdä satunnaisen kyselyn suurelle tietojoukolle. Tässä on yksinkertainen esimerkki:
Näytä kaikki napsautukset sivulla "abc.com" <päivämäärä d1> - <päivämäärä d2> ihmisille, jotka käyttivät Chromea TAI (sijaitsevat Euroopassa JA käyttivät iPhonea)
Kiinnitä huomiota Boolen operaattoreihin. Ne ovat asiakkaiden käytettävissä kyselyrajapinnassa, jotta he voivat tehdä mielivaltaisen monimutkaisia kyselyitä näytteiden saamiseksi.
Hidas pyyntö
Kyseinen asiakas yritti tehdä jotain, jonka intuitiivisesti pitäisi toimia nopeasti:
Näytä kaikki istuntotietueet käyttäjistä, jotka vierailivat millä tahansa sivulla, jonka URL-osoite sisältää "/jobs"
Tällä sivustolla oli paljon liikennettä, ja tallensimme yli miljoona yksilöllistä URL-osoitetta vain sitä varten. Ja he halusivat löytää melko yksinkertaisen URL-mallin, joka liittyi heidän liiketoimintamalliinsa.
Alustava tutkinta
Katsotaanpa, mitä tietokannassa tapahtuu. Alla on alkuperäinen hidas SQL-kysely:
SELECT
count(*)
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions
WHERE
recording_data.usp_id = sessions.usp_id
AND sessions.referrer_id = recordings_urls.id
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
AND r_time > to_timestamp(1542585600)
AND r_time < to_timestamp(1545177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;Ja tässä ovat ajankohdat:
Suunniteltu aika: 1.480 ms Suoritusaika: 1431924.650 ms
Kysely indeksoi 150 tuhatta riviä. Kyselysuunnittelija näytti pari mielenkiintoista yksityiskohtaa, mutta ei ilmeisiä pullonkauloja.
Tutkitaan pyyntöä tarkemmin. Kuten näet, hän tekee JOIN kolme pöytää:
- istuntoja: näyttää istuntotiedot: selain, käyttäjäagentti, maa ja niin edelleen.
- tallennus_tiedot: tallennetut URL-osoitteet, sivut, käyntien kesto
- URL: Välttääksemme äärimmäisen suurten URL-osoitteiden kopioimisen tallennamme ne erilliseen taulukkoon.
Huomaa myös, että kaikki taulukomme on jo osioitu account_id. Näin suljetaan pois tilanne, jossa yksi erityisen suuri tili aiheuttaa ongelmia muille.
Etsitään vihjeitä
Tarkemmin tarkasteltuna huomaamme, että tietyssä pyynnössä on jotain vialla. Tätä riviä kannattaa tarkastella lähemmin:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Ensimmäinen ajatus oli, että ehkä siksi ILIKE kaikissa näissä pitkissä URL-osoitteissa (meillä on yli 1,4 miljoonaa ainutlaatuinen tätä tiliä varten kerätyt URL-osoitteet) tehokkuus saattaa heikentyä.
Mutta ei, siitä ei ole kysymys!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msItse mallihakupyyntö kestää vain 5 sekuntia. Mallin etsiminen miljoonasta ainutlaatuisesta URL-osoitteesta ei selvästikään ole ongelma.
Listan seuraava epäilty on useita JOIN. Ehkä niiden liikakäyttö on aiheuttanut hidastumisen? Yleensä JOIN's ovat ilmeisimpiä ehdokkaita suorituskykyongelmiin, mutta en uskonut, että tapauksemme oli tyypillinen.
analytics_db=# SELECT
count(*)
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data_0 as recording_data,
acc_{account_id}.sessions_0 as sessions
WHERE
recording_data.usp_id = sessions.usp_id
AND sessions.referrer_id = recordings_urls.id
AND r_time > to_timestamp(1542585600)
AND r_time < to_timestamp(1545177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;
count
-------
8086
(1 row)
Time: 147.851 msJa tämä ei myöskään ollut meidän tapaus. JOINSe osoittautui melko nopeaksi.
Epäiltyjen piirin kaventaminen
Olin valmis aloittamaan kyselyn muuttamisen mahdollisten suorituskyvyn parannuksien saavuttamiseksi. Tiimini ja minä kehitimme 2 pääideaa:
- Käytä alikyselyn URL-osoitetta EXISTS: Halusimme tarkistaa uudelleen, onko URL-osoitteiden alikyselyssä ongelmia. Yksi tapa saavuttaa tämä on yksinkertaisesti käyttää
EXISTS.EXISTSparantaa suorituskykyä huomattavasti, koska se päättyy heti, kun se löytää ainoan ehtoa vastaavan merkkijonon.
SELECT
count(*)
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions
WHERE
recording_data.usp_id = sessions.usp_id
AND ( 1 = 1 )
AND sessions.referrer_id = recordings_urls.id
AND (exists(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%'))
AND r_time > to_timestamp(1547585600)
AND r_time < to_timestamp(1549177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;
count
32519
(1 row)
Time: 1636.637 msNo kyllä. Alakysely käärittynä EXISTS, tekee kaikesta supernopeaa. Seuraava looginen kysymys on, miksi pyyntö JOIN-ami ja itse alikysely ovat nopeita erikseen, mutta ovatko yhdessä hirveän hitaita?
- Alikyselyn siirtäminen CTE:hen : Jos kysely on itsessään nopea, voimme yksinkertaisesti laskea nopean tuloksen ensin ja toimittaa sen sitten pääkyselyyn
WITH matching_urls AS (
select id::text from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%'
)
SELECT
count(*) FROM acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions,
matching_urls
WHERE
recording_data.usp_id = sessions.usp_id
AND ( 1 = 1 )
AND sessions.referrer_id = recordings_urls.id
AND (urls && array(SELECT id from matching_urls)::text[])
AND r_time > to_timestamp(1542585600)
AND r_time < to_timestamp(1545107599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0;Mutta se oli silti hyvin hidasta.
Syyllisen löytäminen
Koko tämän ajan silmieni edessä välähti yksi pieni asia, jota harjasin jatkuvasti sivuun. Mutta koska muuta ei ollut jäljellä, päätin katsoa myös häntä. puhun ...-sta && operaattori. Hei hei EXISTS vain parantunut suorituskyky && oli ainoa jäljellä oleva yhteinen tekijä kaikissa hitaan kyselyn versioissa.
Katsoen , näemme sen && käytetään, kun sinun on löydettävä yhteisiä elementtejä kahden taulukon välillä.
Alkuperäisessä pyynnössä tämä on:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Tämä tarkoittaa, että teemme mallihaun URL-osoitteistamme ja löydämme sitten risteyksen kaikkien yleisten viestien URL-osoitteiden kanssa. Tämä on hieman hämmentävää, koska "urls" ei tarkoita taulukkoa, joka sisältää kaikki URL-osoitteet, vaan taulukon "urls"-saraketta recording_data.
Kasvavien epäilysten kanssa &&, yritin löytää heille vahvistuksen luodusta kyselysuunnitelmasta EXPLAIN ANALYZE (Minulla oli jo suunnitelma tallennettuna, mutta minulla on yleensä mukavampaa kokeilla SQL:ää kuin yrittää ymmärtää kyselysuunnittelijoiden läpinäkyvyyttä).
Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
Rows Removed by Filter: 52710Siellä oli useita suodattimia vain &&. Tämä tarkoitti, että tämä operaatio ei ollut vain kallis, vaan myös suoritettiin useita kertoja.
Testasin tätä eristämällä tilan
SELECT 1
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data_30 as recording_data_30,
acc_{account_id}.sessions_30 as sessions_30
WHERE
urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]Tämä kysely oli hidas. Koska JOIN-s ovat nopeita ja alikyselyt ovat nopeita, ainoa asia oli jäljellä && operaattori.
Tämä on vain avaintoiminto. Meidän on aina etsittävä koko alla olevasta URL-osoitteiden taulukosta etsiäksemme mallia, ja meidän on aina löydettävä risteyksiä. Emme voi etsiä suoraan URL-tietueiden perusteella, koska nämä ovat vain tunnisteita, jotka viittaavat urls.
Matkalla ratkaisuun
&& hidas, koska molemmat sarjat ovat valtavia. Toiminto on suhteellisen nopea, jos vaihdan urls päälle { "http://google.com/", "http://wingify.com/" }.
Aloin etsiä tapaa asettaa risteys Postgresissa ilman käyttöä &&, mutta ilman suurta menestystä.
Lopulta päätimme vain ratkaista ongelman eristyksissä: anna minulle kaikki urls rivit, joiden URL-osoite vastaa mallia. Ilman lisäehtoja se on -
SELECT urls.url
FROM
acc_{account_id}.urls as urls,
(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
urls.id = unrolled_urls.id AND
urls.url ILIKE '%jobs%'Sen sijasta JOIN syntaksi Käytin juuri alikyselyä ja laajensin recording_data.urls taulukko, jotta voit käyttää ehtoa suoraan WHERE.
Tärkeintä tässä on se && käytetään tarkistamaan, sisältääkö tietty merkintä vastaavan URL-osoitteen. Jos siristät hieman, voit nähdä, että tämä toiminto liikkuu taulukon elementtien (tai taulukon rivien) läpi ja pysähtyy, kun ehto (osuma) täyttyy. Ei muistuta mitään? Joo, EXISTS.
Siitä lähtien recording_data.urls voidaan viitata alikyselykontekstin ulkopuolelta, kun näin tapahtuu, voimme palata vanhaan ystäväämme EXISTS ja kääri alikysely siihen.
Kun kaikki yhdistetään, saadaan lopullinen optimoitu kysely:
SELECT
count(*)
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions
WHERE
recording_data.usp_id = sessions.usp_id
AND ( 1 = 1 )
AND sessions.referrer_id = recordings_urls.id
AND r_time > to_timestamp(1542585600)
AND r_time < to_timestamp(1545177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0
AND EXISTS(
SELECT urls.url
FROM
acc_{account_id}.urls as urls,
(SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data)
AS unrolled_urls
WHERE
urls.id = unrolled_urls.rec_url_id AND
urls.url ILIKE '%enterprise_customer.com/jobs%'
);
Ja viimeinen läpimenoaika Time: 1898.717 ms Aika juhlia?!?
Ei niin nopeasti! Ensin sinun on tarkistettava oikeellisuus. Olin erittäin epäileväinen EXISTS optimointia, koska se muuttaa logiikkaa päättymään aikaisemmin. Meidän on varmistettava, ettemme ole lisänneet pyyntöön ei-ilmeistä virhettä.
Yksinkertainen testi oli ajaa count(*) sekä hitaissa että nopeissa kyselyissä suurelle määrälle erilaisia tietojoukkoja. Sitten tarkistin manuaalisesti, että kaikki tulokset olivat oikeita pienille tiedoille.
Kaikki testit antoivat jatkuvasti positiivisia tuloksia. Me korjasimme kaiken!
Opittua
Tästä tarinasta voidaan oppia monia asioita:
- Kyselysuunnitelmat eivät kerro koko tarinaa, mutta ne voivat tarjota vihjeitä
- Pääepäillyt eivät aina ole todellisia syyllisiä
- Hitaat kyselyt voidaan jakaa pullonkaulojen eristämiseksi
- Kaikki optimoinnit eivät ole luonteeltaan pelkistäviä
- Käyttää
EXIST, mikäli mahdollista, voi johtaa dramaattiseen tuottavuuden kasvuun
johtopäätös
Muutimme ~24 minuutin kyselyajasta 2 sekuntiin - melko merkittävä suorituskyvyn lisäys! Vaikka tämä artikkeli tuli suureksi, kaikki tekemämme kokeet tapahtuivat yhdessä päivässä, ja niiden optimointiin ja testaukseen kului arvioiden mukaan 1,5–2 tuntia.
SQL on upea kieli, jos et pelkää sitä, vaan yrität oppia ja käyttää sitä. Kun sinulla on hyvä käsitys siitä, kuinka SQL-kyselyt suoritetaan, kuinka tietokanta luo kyselysuunnitelmia, kuinka indeksit toimivat ja yksinkertaisesti käsittelemäsi datan koko, voit onnistua optimoimaan kyselyt erittäin hyvin. Yhtä tärkeää on kuitenkin jatkaa erilaisten lähestymistapojen kokeilemista ja pikkuhiljaa ongelman purkamista pullonkaulojen löytämisessä.
Parasta tällaisten tulosten saavuttamisessa on havaittavissa oleva, näkyvä nopeuden parannus – jolloin raportti, joka ei aiemmin edes latautunut, latautuu nyt lähes välittömästi.
Erityiskiitokset toverini Aditya Mishran käskystä, Aditya Gauru и aivoriihiin ja Dinkar Pandir siitä, että löysimme tärkeän virheen viimeisessä pyynnössämme ennen kuin viimein sanoimme sille hyvästit!
Lähde: will.com