ProHoster > Blogs > Administrācija > Stāsts par vienu SQL izmeklÄ“Å”anu

Stāsts par vienu SQL izmeklÄ“Å”anu

PagājuŔā gada decembrÄ« es saņēmu interesantu kļūdu ziņojumu no VWO atbalsta komandas. Liela korporatÄ«vā klienta viena no analÄ«tisko pārskatu ielādes laiks Ŕķita pārmērÄ«gs. Un tā kā Ŕī ir mana atbildÄ«bas joma, es nekavējoties pievērsos problēmas risināŔanai.

Aizvēsture

Lai bÅ«tu skaidrs, par ko es runāju, pastāstÄ«Å”u nedaudz par VWO. Å Ä« ir platforma, ar kuras palÄ«dzÄ«bu savās vietnēs varat uzsākt dažādas mērÄ·tiecÄ«gas kampaņas: veikt A/B eksperimentus, izsekot apmeklētājiem un reklāmguvumiem, analizēt pārdoÅ”anas piltuvi, parādÄ«t siltuma kartes un atskaņot apmeklējumu ierakstus.

Bet vissvarÄ«gākā lieta platformā ir ziņoÅ”ana. Visas iepriekÅ” minētās funkcijas ir savstarpēji saistÄ«tas. Un korporatÄ«vajiem klientiem milzÄ«gs informācijas apjoms bÅ«tu vienkārÅ”i bezjēdzÄ«gs bez jaudÄ«gas platformas, kas to parāda analÄ«tiskā formā.

Izmantojot platformu, varat veikt izlases veida vaicājumu lielai datu kopai. Å eit ir vienkārÅ”s piemērs:

RādÄ«t visus klikŔķus lapā "abc.com" NO <datums d1> LÄŖDZ <date d2> personām, kuras izmantoja Chrome VAI (atrodas Eiropā UN izmantoja iPhone)

Pievērsiet uzmanību Būla operatoriem. Tie ir pieejami klientiem vaicājumu saskarnē, lai veiktu patvaļīgi sarežģītus vaicājumus, lai iegūtu paraugus.

Lēns pieprasījums

Attiecīgais klients mēģināja darīt kaut ko tādu, kam intuitīvi jādarbojas ātri:

Rādīt visus sesijas ierakstus lietotājiem, kuri apmeklēja jebkuru lapu, kuras URL satur "/jobs"

Å ai vietnei bija daudz trafika, un mēs tikai tai saglabājām vairāk nekā miljonu unikālu URL. Un viņi vēlējās atrast diezgan vienkārÅ”u URL veidni, kas bÅ«tu saistÄ«ta ar viņu uzņēmējdarbÄ«bas modeli.

IepriekŔēja izmeklÄ“Å”ana

Paskatīsimies, kas notiek datu bāzē. Zemāk ir sākotnējais lēnais SQL vaicā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 ;

Un Ŕeit ir laiki:

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

Vaicājums pārmeklēja 150 tÅ«kstoÅ”us rindu. Vaicājumu plānotājs parādÄ«ja dažas interesantas detaļas, bet nekādu acÄ«mredzamu vājo vietu.

IzpētÄ«sim pieprasÄ«jumu tālāk. Kā redzat, viņŔ to dara JOIN trÄ«s tabulas:

  1. sesijas: lai parādÄ«tu informāciju par sesiju: ā€‹ā€‹pārlÅ«kprogramma, lietotāja aÄ£ents, valsts un tā tālāk.
  2. ierakstÄ«Å”anas_dati: ierakstÄ«tie URL, lapas, apmeklējumu ilgums
  3. URL: lai izvairÄ«tos no ārkārtÄ«gi lielu URL dublÄ“Å”anas, mēs tos saglabājam atseviŔķā tabulā.

Ņemiet vērā arÄ« to, ka visas mÅ«su tabulas jau ir sadalÄ«tas account_id. Tādējādi tiek izslēgta situācija, kad viens Ä«paÅ”i liels konts rada problēmas citiem.

Meklē norādes

Paskatoties tuvāk, redzam, ka ar konkrēto pieprasÄ«jumu kaut kas nav kārtÄ«bā. Ir vērts sÄ«kāk aplÅ«kot Å”o rindu:

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

Pirmā doma bija, ka varbÅ«t tāpēc ILIKE visos Å”ajos garajos URL (mums ir vairāk nekā 1,4 miljoni unikāls Å”im kontam apkopotie URL) veiktspēja var ciest.

Bet nē, tas nav galvenais!

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

Time: 5231.765 ms

Pats veidnes meklÄ“Å”anas pieprasÄ«jums aizņem tikai 5 sekundes. Rakstura meklÄ“Å”ana miljonos unikālu URL noteikti nav problēma.

Nākamie aizdomās turamie sarakstā ir vairāki JOIN. VarbÅ«t to pārmērÄ«ga izmantoÅ”ana ir izraisÄ«jusi palēnināŔanos? Parasti JOIN's ir visredzamākie veiktspējas problēmu kandidāti, taču 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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Un tas arī nebija mūsu gadījums. JOIN's izrādījās diezgan ātrs.

Aizdomās turamo loka saŔaurināŔanās

Es biju gatavs sākt mainīt vaicājumu, lai panāktu iespējamos veiktspējas uzlabojumus. Es un mana komanda izstrādājām 2 galvenās idejas:

  • ApakÅ”vaicājuma URL izmantojiet EXISTS: mēs vēlējāmies vēlreiz pārbaudÄ«t, vai nav raduŔās problēmas ar vietrāžu URL apakÅ”vaicājumu. Viens veids, kā to panākt, ir vienkārÅ”i izmantot EXISTS. EXISTS var ievērojami uzlabo veiktspēju, jo tas beidzas uzreiz, tiklÄ«dz tiek atrasta vienÄ«gā virkne, kas atbilst nosacÄ«jumam.

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

Nu jā. ApakÅ”vaicājums, kad tas ir iesaiņots EXISTS, padara visu super ātru. Nākamais loÄ£iskais jautājums, kāpēc pieprasÄ«jums ar JOIN-ami un pats apakÅ”vaicājums ir ātrs atseviŔķi, bet kopā Å”ausmÄ«gi lēni?

  • ApakÅ”vaicājuma pārvietoÅ”ana uz CTE : ja vaicājums ir ātrs pats par sevi, mēs varam vienkārÅ”i vispirms aprēķināt ātro rezultātu un pēc tam nodroÅ”inā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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

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

Vainīgā atraŔana

Visu Å”o laiku manu acu priekŔā pazibēja viens sÄ«kums, ko nemitÄ«gi sviedu malā. Bet tā kā nekas cits neatlika, nolēmu paskatÄ«ties arÄ« uz viņu. Es runāju par && operators. Uz redzÄ“Å”anos EXISTS tikai uzlabota veiktspēja && bija vienÄ«gais atlikuÅ”ais kopÄ«gais faktors visās lēnā vaicājuma versijās.

Skatoties uz dokumentācija, mēs to redzam && izmanto, ja jāatrod kopīgi elementi starp diviem masīviem.

Sākotnējā pieprasījumā tas ir:

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

Tas nozÄ«mē, ka mēs savos vietrāžos URL veicam raksta meklÄ“Å”anu, pēc tam atrodam krustojumu ar visiem URL, kuros ir izplatÄ«tas ziņas. Tas ir nedaudz mulsinoÅ”i, jo Å”eit "urls" attiecas nevis uz tabulu, kurā ir visi URL, bet gan uz tabulas kolonnu "urls". recording_data.

Ar pieaugoŔām aizdomām par &&, mēģināju tiem atrast apstiprinājumu izveidotajā vaicājumu plānā EXPLAIN ANALYZE (Man jau bija saglabāts plāns, bet man parasti ir ērtāk eksperimentēt ar SQL, nevis mēģināt saprast vaicājumu plānotāju necaurredzamÄ«bu).

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

Bija vairākas filtru rindas tikai no &&. Kas nozÄ«mēja, ka Ŕī operācija bija ne tikai dārga, bet arÄ« veikta vairākas reizes.

Es to pārbaudīju, izolējot stāvokli

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

Å is vaicājums bija lēns. Tāpēc ka JOIN-s ir ātri un apakÅ”vaicājumi ir ātri, vienÄ«gais, kas palicis, bija && operators.

Å Ä« ir tikai galvenā darbÄ«ba. Mums vienmēr ir jāmeklē visa pamatā esoŔā URL tabula, lai meklētu modeli, un mums vienmēr ir jāatrod krustojumi. Mēs nevaram meklēt tieÅ”i pēc URL ierakstiem, jo ā€‹ā€‹tie ir tikai ID, kas attiecas uz urls.

Ceļā uz risinājumu

&& lēni, jo abi komplekti ir milzÄ«gi. Ja nomainÄ«Å”u, operācija bÅ«s salÄ«dzinoÅ”i ātra urls par { "http://google.com/", "http://wingify.com/" }.

Es sāku meklēt veidu, kā iestatÄ«t krustojumu Postgresā, neizmantojot &&, bet bez Ä«paÅ”iem panākumiem.

Galu galā mēs nolēmām problēmu atrisināt atseviŔķi: dodiet man visu urls rindas, kuru URL atbilst modelim. Bez papildu nosacÄ«jumiem tas bÅ«s - 

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

Tā vietā, lai JOIN sintakse Es tikko izmantoju apakÅ”vaicājumu un paplaÅ”ināju recording_data.urls masÄ«vs, lai jÅ«s varētu tieÅ”i lietot nosacÄ«jumu WHERE.

VissvarÄ«gākais Å”eit ir tas && izmanto, lai pārbaudÄ«tu, vai konkrētais ieraksts satur atbilstoÅ”u URL. Ja nedaudz paskatās, jÅ«s varat redzēt, ka Ŕī darbÄ«ba pārvietojas pa masÄ«va elementiem (vai tabulas rindām) un apstājas, kad ir izpildÄ«ts nosacÄ«jums (atbilstÄ«ba). Tev neko neatgādina? Jā, EXISTS.

KopÅ” tā laika recording_data.urls var atsaukties ārpus apakÅ”vaicājuma konteksta, kad tas notiek, mēs varam atsaukties uz savu veco draugu EXISTS un iesaiņojiet ar to apakÅ”vaicājumu.

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

Un pēdējais izpildes laiks Time: 1898.717 ms Laiks svinēt?!?

Ne tik ātri! Vispirms jums ir jāpārbauda pareizība. Es biju ārkārtīgi aizdomīgs EXISTS optimizācija, jo tā maina loģiku, lai pārtrauktu agrāk. Mums ir jāpārliecinās, ka pieprasījumam neesam pievienojuŔi nepārprotamu kļūdu.

VienkārÅ”s tests bija palaist count(*) gan lēniem, gan ātrajiem vaicājumiem lielam skaitam dažādu datu kopu. Pēc tam nelielai datu apakÅ”kopai es manuāli pārbaudÄ«ju, vai visi rezultāti ir pareizi.

Visi testi sniedza nemainīgi pozitīvus rezultātus. Mēs visu salabojām!

Gūtās mācības

No Ŕī stāsta var mācīties daudzas mācības:

  1. Vaicājumu plāni neizstāsta visu, taču tie var sniegt norādes
  2. Galvenie aizdomās turamie ne vienmēr ir patiesie vainīgie
  3. Lēnus vaicājumus var sadalīt, lai izolētu vājās vietas
  4. Ne visas optimizācijas ir reducējoÅ”as
  5. Izmantot EXIST, ja iespējams, var izraisīt dramatisku produktivitātes pieaugumu

secinājums

Mēs pārgājām no vaicājuma laika ~24 minÅ«tes uz 2 sekundēm ā€“ diezgan ievērojams veiktspējas pieaugums! Lai gan Å”is raksts iznāca liels, visi mÅ«su veiktie eksperimenti notika vienas dienas laikā, un tika lēsts, ka optimizācijai un testÄ“Å”anai bija nepiecieÅ”amas 1,5ā€“2 stundas.

SQL ir brÄ«niŔķīga valoda, ja jÅ«s no tās nebaidāties, bet mēģiniet to iemācÄ«ties un lietot. Ja jums ir laba izpratne par to, kā tiek izpildÄ«ti SQL vaicājumi, kā datu bāze Ä£enerē vaicājumu plānus, kā darbojas indeksi, un vienkārÅ”i apstrādājamo datu lielumu, jÅ«s varat ļoti veiksmÄ«gi optimizēt vaicājumus. Tomēr vienlÄ«dz svarÄ«gi ir turpināt izmēģināt dažādas pieejas un lēnām nojaukt problēmu, atrodot vājās vietas.

Labākā daļa Ŕādu rezultātu sasniegÅ”anā ir pamanāms, redzams ātruma uzlabojums ā€” tagad pārskats, kas iepriekÅ” pat netika ielādēts, tagad tiek ielādēts gandrÄ«z uzreiz.

ÄŖpaÅ”s paldies mani biedri pēc Aditjas MiÅ”ras pavēles, Aditja Gauru Šø Varuns Malhotra prāta vētrai un Dinkars Pandirs par to, ka atradām svarÄ«gu kļūdu mÅ«su pēdējā pieprasÄ«jumā, pirms mēs beidzot atvadÄ«jāmies no tās!

Avots: www.habr.com

Pievieno komentāru