Vienas SQL izmeklēšanas stāsts

Pagājušajā gada decembrī saņēmu interesantu ziņojumu par kļūdu no VWO atbalsta komandas. Lielā korporatīvā klienta analītiskā ziņojuma ielādes laiks šķita neticami garš. Tā kā šī ir mana atbildība, es tūlīt pievērsos problēmas risināšanai.

Iepriekšējā pieredze

Lai saprastu, par ko ir runa, pāris vārdos pastāstīšu par VWO. Tā ir platforma, ar kuras palīdzību var palaiž dažādas mērķtiecīgas kampaņas savās vietnēs: veikt A/B eksperimentus, izsekot apmeklētājus un konversijas, veikt pārdošanas piltuves analīzi, attēlot siltuma kartes un atskaņot apmeklējumu ierakstus.

Bet vissvarīgākais platformā ir ziņojumu izveidošana. Visas iepriekšminētās funkcijas ir savstarpēji saistītas. Un korporatīviem klientiem milzīgs informācijas apjoms būtu vienkārši bezjēdzīgs bez spēcīgas platformas, kas to pārstāv analīzei.

Izmantojot platformu, var veikt brīvas pieprasījumus lielā datu kopā. Šeit ir vienkāršs piemērs:

Parādīt visus klikšķus uz lapas "abc.com"
NO <datums d1> LĪDZ <datums d2>
cilvēkiem, kuri
izmantoja Chrome VAI
(bija Eiropā UN izmantoja iPhone)

Pievērsiet uzmanību loģiskajiem operatoriem. Tie ir pieejami klientiem pieprasījuma interfeisā, lai veiktu jebkura veida sarežģītus pieprasījumus par paraugu saņemšanu.

Lēns vaicājums

Klients, par kuru runājam, centās izdarīt kaut ko, kas intuitīvi būtu jāstrādā ātri:

Parādiet visus sesiju ierakstus
līdz lietotājiem, kas apmeklējuši jebkuru lapu
ar URL, kurā ir "\/jobs"

Šajā vietnē bija milzīgs datplūsmas apjoms, un mēs glabājām vairāk nekā miljonu unikālu URL adreses tikai tai. Un viņi vēlējās atrast diezgan vienkāršu URL paraugu, kas attiektos uz viņu biznesa modeli.

Sākotnējais izmeklējums

Apskatīsim, kas notiek datu bāzē. Zemāk ir norādīts lēnais SQL pieprasījums:

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 ;

Lūk, laiki:

Plānotais laiks: 1.480 ms
Izpildes laiks: 1431924.650 ms

Pieprasījums apiet 150 tūkstošus rindu. Vaicājumu plānotājs parādīja dažas interesantas detaļas, taču acīmredzamu vāju vietu nebija.

Paskatīsimies uz šo pieprasījumu vairāk. Kā redzams, tas veic JOIN trīs tabulas:

1. sessions: lai attēlotu sesijas informāciju: pārlūks, lietotāja aģents, valsts un tā tālāk.
2. recording_data: ierakstītās URL, lapas, apmeklējumu ilgums
3. urls: lai izvairītos no ārkārtīgi garu URL dublēšanas, mēs tos uzglabājam atsevišķā tabulā.

Tāpat ņemiet vērā, ka visas mūsu tabulas jau ir sadalītas pēc account_id. Tādējādi tiek izslēgta situācija, kad viena īpaši liela konta dēļ citas sastop grūtības.

Pierādījumu meklējumos

Pēc tuvākas apskates mēs redzam, ka kaut kas konkrētajā pieprasījumā nav kārtībā. Jāpievērš uzmanība šai rindai:

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

Pirmais domas iznākums bija, ka iespējams, dēļ ILIKE visos šajos garajos URL (mums ir vairāk nekā 1,4 miljoni unikālo URL adreses, kas apkopotas šim kontam) veiktspēja varētu samazināties.

Bet nē — tā nav lieta!

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

Laiks: 5231.765 ms

Pats meklēšanas pieprasījums pēc modeļa aizņem tikai 5 sekundes. Meklēšana pēc modeļa miljonā unikālo URL noteikti nav problēma.

Nākamais aizdomīgs sarakstā — vairāki JOIN. Iespējams, to pārmērīga izmantošana izraisīja palēnināšanos? Parasti JOIN‘as ir acīmredzami iespējamie kandidāti veiktspējas problēmām, bet es neticēju, ka mūsu gadījums ir tipisks.

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 =5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 rinda)

Laiks: 147.851 ms

Un tas arī nebija mūsu gadījums. JOIN‘as izrādījās diezgan ātras.

Sašaurinām aizdomīgo loku

Es biju gatavs sākt mainīt pieprasījumu, lai panāktu jebkādus iespējamos veiktspējas uzlabojumus. Mēs ar komandu izstrādājām 2 galvenās idejas:

• Izmantot EXISTS URL apakšpieprasījumam: Mēs vēlējāmies vēlreiz pārbaudīt, vai nav problēmu ar apakšpieprasījumu URL. Viens no veidiem, kā to panākt — vienkārši izmantot EXISTS. EXISTS var krietni uzlabot veiktspēju, jo tā beidzas uzreiz, tiklīdz atrod vienīgo rindu pēc nosacījuma.

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 =5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Nu jā. Apakšuzdots, kad tas ir ietverts, EXISTS, padara visu super ātru. Nākamais loģiskais jautājums ir, kāpēc vaicājums ar JOIN-ām un pats apakšuzdots ir ātri atsevišķi, bet kopā tie ir šausmīgi lēni?

• Pārvietojam apakšuzdotu uz CTE : ja vaicājums ir ātrs pats par sevi, mēs varam vispirms aprēķināt ātru rezultātu un pēc tam piegādāt to galvenajam vaicājumam.

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 =5 
    AND recording_data.num_of_pages > 0;

Bet tas joprojām bija ļoti lēni.

Atrodam vainīgo

Visu šo laiku acīs acu priekšā mirgoja viena nianse, no kuras es pastāvīgi novērstos. Bet, tā kā nekas cits vairs nebija palicis, es nolēmu paskatīties uz to. Es runāju par && operatoru. Līdz šim EXISTS vienkārši uzlabojusi veiktspēju, && bija vienīgais palikušais kopīgais faktors visās lēnā vaicājuma versijās.

Skatoties uz dokumenti, mēs redzam, ka && tiek izmantots, kad nepieciešams atrast kopīgus elementus starp diviem masīviem.

Oriģinālajā vaicājumā tas ir:

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

Kas nozīmē, ka mēs veicam meklēšanu pēc parauga mūsu URL, pēc tam atrodam krustojumu ar visiem URL ar kopīgajiem ierakstiem. Tas ir nedaudz mulsinoši, jo 'urls' šeit nenorāda uz tabulu, kurā ir visi URL, bet uz kolonnu 'urls' tabulā recording_data.

Pieaugot aizdomām par &&, es mēģināju atrast tiem apstiprinājumu vaicājuma plānā, ko izveidoju. EXPLAIN ANALYZE (man jau bija saglabāts plāns, bet man parasti ir ērtāk eksperimentēt SQL, nekā mēģināt saprast vaļējās vaicājumu plānotājus).

Filtra: ((urls && ($0)::text[]) UN (r_time > '2018-12-17 12:17:23+00'::timestamp ar laika joslu) UN (r_time = '5'::double precision) UN (num_of_pages > 0))
                           Filtra dēļ noņemtās rindas: 52710

Tur bija vairākas rindu filtrus tikai no &&. Kas nozīmēja, ka šī darbība nebija tikai dārga, bet arī tika izpildīta vairākas reizes.

Es to pārbaudīju, izolējot nosacījumu

SELECT 1
FROM 
    acc_{account_id}.urls kā recordings_urls,
    acc_{account_id}.recording_data_30 kā recording_data_30,
    acc_{account_id}.sessions_30 kā sessions_30 
WHERE 
	urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]

Šis vaicājums tika izpildīts lēni. Tā kā JOIN-i ir ātri un apakšvaicājumi ir ātri, palika tikai && operators.

Tieši tas ir galvenais operators. Mums vienmēr ir jāmeklē visā galvenajā URL tabulā, lai meklētu pēc parauga, un mums vienmēr ir jāpārbauda krustojumi. Mēs nevaram meklēt tieši pēc URL ierakstiem, jo tie ir tikai identifikatori, kas atsaucas uz urls.

Ceļā uz risinājumu

&& lēns, jo abi kopas ir milzīgas. Darbība būs salīdzinoši ātra, ja es aizstāšu urls uz { "http://google.com/", "http://wingify.com/" }.

Es sāku meklēt veidu, kā Postgres veikt vairākumu krustojumus bez &&, bet bez īpašiem panākumiem.

Galu galā mēs nolēmām vienkārši atrisināt problēmu izolēti: dod man visas urls rindas, kurām URL atbilst paraugam. Bez papildu nosacījumiem tas būs — 

SELECT urls.url
FROM 
	acc_{account_id}.urls kā urls,
	(SELECT unnest(recording_data.urls) AS id) KĀ atvērti_urls
WHERE
	urls.id = atvērti_urls.id UN
	urls.url ILIKE '%jobs%'

Tā vietā JOIN sintakse es vienkārši izmantoju apakšvaicājumu un izpletu recording_data.urls masīvu, lai varētu tieši piemērot nosacījumu WHERE.

Vispārējais svarīgākais šeit ir tas, ka && tiek izmantots, lai pārbaudītu, vai konkrētais ieraksts satur atbilstošo URL. Nedaudz pievēršot uzmanību, var redzēt šajā operācijā pārvietošanos pa masīva elementiem (vai tabulas rindām) un apstāšanos, izpildot nosacījumu (atbilstību). Neko neaizmirstat? Jā, EXISTS.

Tā kā uz recording_data.urls var atsaukties no ārpuses apakšvaicājuma konteksta, kad tas notiek, mēs varam atgriezties pie mūsu vecā drauga EXISTS un iesaiņot to apakšvaicījumā.

Apvienojot visu kopā, mēs iegūstam galīgo optimizēto vaicājumu:

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

Un mēs esam beigu laiks izpildes Laiks: 1898.717 ms Laiks svinēt?!?

Ne tik ātri! Vispirms jānoskaidro pareizība. Es biju ļoti skeptisks attiecībā uz EXISTS optimizāciju, jo tā maina loģiku uz agrāku pabeigšanu. Mums jābūt pārliecinātiem, ka mēs nepievienojām nezināmu kļūdu vaicājumā.

Vienkārša pārbaude bija izpildīt count(*) un lēni, un ātri vaicājumi lielam dažādu datu kopu skaitam. Pēc tam, maza datu apakškopa, es manuāli pārbaudīju visus rezultātus.

Visas pārbaudes deva stabilus pozitīvus rezultātus. Mēs visu salabojām!

Izvilkti mācības

No šīs vēstures var gūt daudz mācību:

1. Vaicājumu plāni nepasaka visu stāstu, bet var dot pavedienus
2. Galvenie aizdomās turētie ne vienmēr ir īstie vainīgie
3. Lēnus vaicājumus var sadalīt, lai izolētu šaurus vietas
4. Ne visas optimizācijas pēc būtības ir reduktīvas
5. Izmantošana EXIST, kur tas ir iespējams, var radīt strauju veiktspējas pieaugumu

Izvade

Mēs esam pārgājuši no vaicājuma laika ~24 minūtes līdz 2 sekundēm — ļoti būtisks veiktspējas pieaugums! Lai arī šis raksts ir bijis garš, visi eksperimenti, ko mēs darījām, notika vienā dienā un, pēc aplēsēm, prasīja no 1,5 līdz 2 stundām optimizācijām un testēšanai.

SQL ir brīnišķīga valoda, ja nebaidāties no tās, bet mēģināt to saprast un izmantot. Ar labu izpratni par to, kā SQL vaicājumi tiek izpildīti, kā DB ģenerē vaicājumu plānus, kā darbojas indeksi un vienkārši datu lielumu, ar kuru tiekat galā, jūs varat ļoti veiksmīgi attīstīt vaicājumu optimizāciju. Nav mazsvarīgi, tomēr, turpināt izmēģināt dažādus piegājienus un lēnām sadalīt problēmu, atrodot šaurās vietas.

Labākā daļa par šo rezultātu sasniegšanu ir acīmredzamais redzams darba ātruma uzlabojums — kad ziņojums, kas iepriekš pat nenāca atvērt, tagad ielādējas gandrīz uzreiz.

Īpaša pateicība maniem kolēģiem komandā Aditya Mishra, Aditya Gaur un Varun Malhotra par smadzeņu vētru un Dinkar Pandir par to, ka atrada svarīgu kļūdu mūsu gala pieprasījumā, pirms mēs ar to galīgi šķīrāmies!

Avots: habr.com