🥇Ang kwento ng isang pagsisiyasat sa SQL

Noong nakaraang Disyembre nakatanggap ako ng isang kawili-wiling ulat ng bug mula sa VWO support team. Ang oras ng paglo-load para sa isa sa mga ulat ng analytics para sa isang malaking kliyente ng korporasyon ay tila mahirap. At dahil ito ang aking lugar ng responsibilidad, agad akong tumutok sa paglutas ng problema.

prehistory

Upang maging malinaw kung ano ang aking pinag-uusapan, sasabihin ko sa iyo ng kaunti ang tungkol sa VWO. Ito ay isang platform kung saan maaari kang maglunsad ng iba't ibang naka-target na mga kampanya sa iyong mga website: magsagawa ng mga eksperimento sa A/B, subaybayan ang mga bisita at conversion, pag-aralan ang funnel ng mga benta, magpakita ng mga mapa ng init at mag-play ng mga recording ng pagbisita.

Ngunit ang pinakamahalagang bagay tungkol sa platform ay ang pag-uulat. Ang lahat ng mga function sa itaas ay magkakaugnay. At para sa mga kliyente ng korporasyon, ang isang malaking halaga ng impormasyon ay magiging walang silbi nang walang isang malakas na platform na nagpapakita nito sa anyong analytics.

Gamit ang platform, maaari kang gumawa ng random na query sa isang malaking set ng data. Narito ang isang simpleng halimbawa:

Ipakita ang lahat ng mga pag-click sa pahinang "abc.com"
MULA  HANGGANG 
para sa mga taong
ginamit ang Chrome OR
(nasa Europe AT gumagamit ng iPhone)

Bigyang-pansin ang mga operator ng Boolean. Available ang mga ito sa mga kliyente sa interface ng query upang gumawa ng arbitraryong kumplikadong mga query upang makakuha ng mga sample.

Mabagal na kahilingan

Ang kliyenteng pinag-uusapan ay sinusubukang gumawa ng isang bagay na intuitively ay dapat gumana nang mabilis:

Ipakita ang lahat ng pag-record ng session
para sa mga user na bumisita sa anumang page
na may URL kung saan mayroong "/jobs"

Ang site na ito ay may isang tonelada ng trapiko at kami ay nag-iimbak ng higit sa isang milyong natatanging mga URL para lamang dito. At gusto nilang makahanap ng medyo simpleng template ng URL na nauugnay sa kanilang modelo ng negosyo.

Paunang pagsisiyasat

Tingnan natin kung ano ang nangyayari sa database. Nasa ibaba ang orihinal na mabagal na query sa SQL:

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 ;

At narito ang mga timing:

Nakaplanong oras: 1.480 ms
Oras ng pagpapatupad: 1431924.650 ms

Ang query ay gumapang ng 150 libong mga hilera. Nagpakita ang tagaplano ng query ng ilang kawili-wiling detalye, ngunit walang halatang mga bottleneck.

Pag-aralan pa natin ang kahilingan. Tulad ng nakikita mo, ginagawa niya JOIN tatlong talahanayan:

session: upang ipakita ang impormasyon ng session: browser, user agent, bansa, at iba pa.
recording_data: mga naitala na URL, pahina, tagal ng mga pagbisita
url: Upang maiwasan ang pagdoble ng napakalaking URL, iniimbak namin ang mga ito sa isang hiwalay na talahanayan.

Tandaan din na ang lahat ng aming mga talahanayan ay nahahati na ng account_id. Sa ganitong paraan, hindi kasama ang isang sitwasyon kung saan ang isang partikular na malaking account ay nagdudulot ng mga problema para sa iba.

Naghahanap ng mga pahiwatig

Sa mas malapit na pagsisiyasat, nakita namin na may mali sa isang partikular na kahilingan. Ito ay nagkakahalaga ng mas malapitang pagtingin sa linyang ito:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Ang unang naisip ay marahil dahil ILIKE sa lahat ng mahabang URL na ito (mayroon kaming mahigit 1,4 milyon natatangi Maaaring maghirap ang pagganap ng mga URL na nakolekta para sa account na ito.

Ngunit hindi, hindi iyon ang punto!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

Ang kahilingan sa paghahanap ng template mismo ay tumatagal lamang ng 5 segundo. Ang paghahanap ng pattern sa isang milyong natatanging URL ay malinaw na hindi isang problema.

Ang susunod na suspek sa listahan ay ilan JOIN. Marahil ang kanilang labis na paggamit ay naging sanhi ng paghina? Karaniwan JOINAng mga ay ang pinaka-halatang kandidato para sa mga problema sa pagganap, ngunit hindi ako naniniwala na ang aming kaso ay karaniwan.

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 ms

At hindi rin ito ang kaso namin. JOIN's ay naging medyo mabilis.

Pagpapaliit sa bilog ng mga suspek

Handa na akong simulan ang pagbabago ng query para makamit ang anumang posibleng pagpapahusay sa performance. Ang aking koponan at ako ay bumuo ng 2 pangunahing ideya:

Gamitin ang EXISTS para sa subquery URL: Nais naming suriin muli kung mayroong anumang mga problema sa subquery para sa mga URL. Ang isang paraan upang makamit ito ay ang paggamit lamang EXISTS. EXISTS maaari lubos na nagpapabuti sa pagganap dahil agad itong nagtatapos sa sandaling mahanap nito ang tanging string na tumutugma sa kundisyon.

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 ms

Oo. Subquery kapag nakabalot EXISTS, ginagawang napakabilis ng lahat. Ang susunod na lohikal na tanong ay kung bakit ang kahilingan sa JOIN-ami at ang subquery mismo ay mabilis nang paisa-isa, ngunit napakabagal nang magkasama?

Ang paglipat ng subquery sa CTE : Kung ang query ay mabilis sa sarili nitong, maaari nating kalkulahin muna ang mabilis na resulta at pagkatapos ay ibigay ito sa pangunahing query

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;

Ngunit napakabagal pa rin nito.

Paghanap ng salarin

Sa lahat ng oras na ito, isang maliit na bagay ang kumislap sa harap ng aking mga mata, na palagi kong itinatabi. Pero dahil wala ng ibang natira, I decided to look at her too. kausap ko && operator. Bye EXISTS buti na lang performance && ay ang tanging natitirang karaniwang kadahilanan sa lahat ng mga bersyon ng mabagal na query.

Nakatingin dokumentasyon, nakikita natin yan && ginagamit kapag kailangan mong maghanap ng mga karaniwang elemento sa pagitan ng dalawang array.

Sa orihinal na kahilingan ito ay:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Ibig sabihin, gumagawa kami ng pattern na paghahanap sa aming mga URL, pagkatapos ay hanapin ang intersection sa lahat ng URL na may mga karaniwang post. Ito ay medyo nakakalito dahil ang "mga url" dito ay hindi tumutukoy sa talahanayan na naglalaman ng lahat ng mga URL, ngunit sa column na "mga url" sa talahanayan recording_data.

Sa lumalaking hinala hinggil sa &&, sinubukan kong humanap ng kumpirmasyon para sa kanila sa nabuong query plan EXPLAIN ANALYZE (Mayroon na akong na-save na plano, ngunit kadalasan ay mas komportable akong mag-eksperimento sa SQL kaysa sa pagsisikap na maunawaan ang opacity ng mga tagaplano ng query).

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: 52710

Mayroong ilang mga linya ng mga filter mula lamang sa &&. Na nangangahulugan na ang operasyong ito ay hindi lamang mahal, ngunit gumanap din ng ilang beses.

Sinubukan ko ito sa pamamagitan ng paghihiwalay ng kundisyon

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[]

Mabagal ang query na ito. Dahil ang JOIN-s ay mabilis at subquery ay mabilis, ang tanging natitira ay && operator.

Ito ay isang pangunahing operasyon lamang. Palagi naming kailangan na hanapin ang buong pinagbabatayan na talahanayan ng mga URL upang maghanap ng pattern, at palagi naming kailangang maghanap ng mga intersection. Hindi kami direktang makakapaghanap sa pamamagitan ng mga talaan ng URL, dahil ang mga ito ay mga ID lamang na tinutukoy urls.

Sa daan patungo sa isang solusyon

&& mabagal dahil malaki ang dalawang set. Ang operasyon ay medyo mabilis kung papalitan ko urls sa { "http://google.com/", "http://wingify.com/" }.

Nagsimula akong maghanap ng isang paraan upang gawin ang set intersection sa Postgres nang hindi gumagamit &&, ngunit walang gaanong tagumpay.

Sa huli, nagpasya kaming lutasin lamang ang problema sa paghihiwalay: ibigay sa akin ang lahat urls mga linya kung saan tumutugma ang URL sa pattern. Kung walang karagdagang kundisyon ito ay magiging -

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%'

Sa halip ng JOIN syntax Gumamit lang ako ng subquery at pinalawak recording_data.urls array upang direkta mong mailapat ang kundisyon sa WHERE.

Ang pinakamahalagang bagay dito ay iyon && ginagamit upang suriin kung ang isang naibigay na entry ay naglalaman ng katugmang URL. Kung duling ka ng kaunti, makikita mong gumagalaw ang operasyong ito sa mga elemento ng array (o mga row ng table) at hihinto kapag natugunan ang isang kundisyon (tugma). Hindi nagpapaalala sa iyo ng kahit ano? oo, EXISTS.

Simula noon recording_data.urls maaaring i-reference mula sa labas ng konteksto ng subquery, kapag nangyari ito maaari tayong bumalik sa dati nating kaibigan EXISTS at balutin ang subquery dito.

Pagsasama-sama ng lahat, makuha namin ang panghuling na-optimize na query:

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%'
    );

At ang huling lead time Time: 1898.717 ms Oras na para magdiwang?!?

Teka muna! Una kailangan mong suriin ang kawastuhan. Ako ay lubhang naghihinala tungkol sa EXISTS pag-optimize habang binabago nito ang lohika upang makumpleto nang mas maaga. Kailangan naming makatiyak na hindi kami nagdagdag ng hindi halatang error sa kahilingan.

Isang simpleng pagsubok ang tumakbo count(*) sa parehong mabagal at mabilis na mga query para sa isang malaking bilang ng iba't ibang set ng data. Pagkatapos, para sa isang maliit na subset ng data, manu-mano kong na-verify na tama ang lahat ng resulta.

Ang lahat ng mga pagsubok ay nagbigay ng patuloy na positibong resulta. Inayos namin ang lahat!

Mga aral na natutunan

Maraming aral ang mapupulot sa kwentong ito:

Hindi sinasabi ng mga plano sa query ang buong kuwento, ngunit maaari silang magbigay ng mga pahiwatig
Ang mga pangunahing suspek ay hindi palaging ang tunay na salarin
Maaaring hatiin ang mga mabagal na query upang ihiwalay ang mga bottleneck
Hindi lahat ng pag-optimize ay likas na nakakabawas
Gamitin EXIST, kung saan posible, ay maaaring humantong sa kapansin-pansing pagtaas sa produktibidad

Pagbubuhos

Nagpunta kami mula sa oras ng query na ~24 minuto hanggang 2 segundo - isang makabuluhang pagtaas ng pagganap! Bagama't ang artikulong ito ay lumabas nang malaki, lahat ng mga eksperimento na ginawa namin ay nangyari sa isang araw, at tinatayang tumagal ang mga ito sa pagitan ng 1,5 at 2 oras para sa mga pag-optimize at pagsubok.

Ang SQL ay isang kahanga-hangang wika kung hindi ka matatakot dito, ngunit subukang matutunan at gamitin ito. Sa pamamagitan ng pagkakaroon ng isang mahusay na pag-unawa sa kung paano isinasagawa ang mga query sa SQL, kung paano bumubuo ang database ng mga plano sa query, kung paano gumagana ang mga index, at simpleng laki ng data na iyong kinakaharap, maaari kang maging matagumpay sa pag-optimize ng mga query. Parehong mahalaga, gayunpaman, na patuloy na subukan ang iba't ibang mga diskarte at dahan-dahang sirain ang problema, sa paghahanap ng mga bottleneck.

Ang pinakamagandang bahagi tungkol sa pagkamit ng mga resultang tulad nito ay ang kapansin-pansin, nakikitang pagpapabuti ng bilis - kung saan ang isang ulat na dati ay hindi man lang naglo-load ngayon ay naglo-load nang halos agad-agad.

Espesyal na salamat sa mga kasama ko sa utos ni Aditya Mishra, Aditya Gauru и Varun Malhotra para sa brainstorming at Dinkar Pandir para sa paghahanap ng isang mahalagang error sa aming huling kahilingan bago kami tuluyang nagpaalam dito!

Pinagmulan: www.habr.com

Ang kwento ng isang pagsisiyasat sa SQL