🥇La rakonto pri unu SQL-enketo

La pasintan decembron mi ricevis interesan cimraporton de la subtena teamo de VWO. La ŝarĝotempo por unu el la analizaj raportoj por granda kompania kliento ŝajnis malpermesa. Kaj ĉar ĉi tio estas mia respondeca kampo, mi tuj koncentriĝis pri solvi la problemon.

antaŭhistorio

Por klarigi, pri kio mi parolas, mi rakontos al vi iomete pri VWO. Ĉi tio estas platformo, per kiu vi povas lanĉi diversajn celitajn kampanjojn en viaj retejoj: fari A/B-eksperimentojn, spuri vizitantojn kaj konvertiĝojn, analizi la vendan funelon, montri varmajn mapojn kaj ludi vizitregistraĵojn.

Sed la plej grava afero pri la platformo estas raportado. Ĉiuj ĉi-supraj funkcioj estas interligitaj. Kaj por kompaniaj klientoj, grandega kvanto da informoj estus simple senutila sen potenca platformo, kiu prezentas ĝin en analitika formo.

Uzante la platformon, vi povas fari hazardan demandon sur granda datuma aro. Jen simpla ekzemplo:

Montru ĉiujn klakojn sur paĝo "abc.com" DE <dato d1> AL <dato d2> por homoj, kiuj uzis Chrome AŬ (situanta en Eŭropo KAJ uzis iPhone)

Atentu buleajn operatorojn. Ili estas haveblaj al klientoj en la demanda interfaco por fari arbitre kompleksajn demandojn por akiri specimenojn.

Malrapida peto

La koncerna kliento provis fari ion, kio intuicie devus funkcii rapide:

Montru ĉiujn sesiajn registrojn por uzantoj kiuj vizitis ajnan paĝon kun URL enhavanta "/jobs"

Ĉi tiu retejo havis multan trafikon kaj ni stokis pli ol milionon da unikaj URL-oj nur por ĝi. Kaj ili volis trovi sufiĉe simplan URL-ŝablonon, kiu rilatas al sia komerca modelo.

Antaŭesploro

Ni rigardu kio okazas en la datumbazo. Malsupre estas la origina malrapida SQL-demando:

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 ;

Kaj jen la tempoj:

Planita tempo: 1.480 ms Ekzekuta tempo: 1431924.650 ms

La demando rampis 150 mil vicojn. La demandplanisto montris kelkajn interesajn detalojn, sed neniujn evidentajn proplempunktojn.

Ni studu plu la peton. Kiel vi povas vidi, li faras JOIN tri tabloj:

kunsidoj: por montri seanformojn: retumilo, uzantagento, lando ktp.
registrad_datenoj: registritaj URL-oj, paĝoj, daŭro de vizitoj
uriloj: Por eviti duobligi ege grandajn URL-ojn, ni konservas ilin en aparta tabelo.

Rimarku ankaŭ, ke ĉiuj niaj tabloj estas jam dividitaj per account_id. Tiel, situacio kie unu aparte granda konto kaŭzas problemojn por aliaj estas ekskludita.

Serĉante spurojn

Post pli proksima inspektado, ni vidas, ke io misas kun aparta peto. Indas rigardi pli detale ĉi tiun linion:

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

La unua penso estis tio eble ĉar ILIKE sur ĉiuj ĉi tiuj longaj URL (ni havas pli ol 1,4 milionojn unika URLoj kolektitaj por ĉi tiu konto) agado povas suferi.

Sed ne, tio ne estas la afero!

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

Time: 5231.765 ms

La ŝablona serĉpeto mem daŭras nur 5 sekundojn. Serĉi ŝablonon en miliono da unikaj URL-oj klare ne estas problemo.

La sekva suspektato en la listo estas pluraj JOIN. Eble ilia troa uzado kaŭzis la malrapidiĝon? Kutime JOIN'j estas la plej evidentaj kandidatoj por agado-problemoj, sed mi ne kredis, ke nia kazo estis tipa.

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

Kaj ĉi tio ankaŭ ne estis nia kazo. JOINmontriĝis sufiĉe rapida.

Malvastigante la rondon de suspektatoj

Mi estis preta komenci ŝanĝi la demandon por atingi ajnajn eblajn rendimentajn plibonigojn. Mia teamo kaj mi evoluigis 2 ĉefajn ideojn:

Uzu EXISTS por subdemanda URL: Ni volis kontroli denove ĉu estas problemoj kun la subdemando por la URL-oj. Unu maniero por atingi ĉi tion estas simple uzi EXISTS. EXISTS povas multe plibonigi rendimenton ĉar ĝi finiĝas tuj kiam ĝi trovas la solan ŝnuron kiu kongruas kun la kondiĉo.

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, jes. Subdemando kiam envolvite EXISTS, faras ĉion superrapide. La sekva logika demando estas kial la peto kun JOIN-ami kaj la subdemando mem estas rapidaj individue, sed estas terure malrapidaj kune?

Movante la subdemandon al la CTE : Se la demando estas rapida memstare, ni povas simple kalkuli la rapidan rezulton unue kaj poste provizi ĝin al la ĉefa demando

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;

Sed ĝi estis ankoraŭ tre malrapida.

Trovi la kulpulon

Dum ĉi tiu tempo, unu etaĵo ekbrilis antaŭ miaj okuloj, kiujn mi senĉese flankenbalis. Sed ĉar nenio alia restis, mi decidis ankaŭ rigardi ŝin. Mi parolas pri && operatoro. Adiaŭ EXISTS nur plibonigita rendimento && estis la nura restanta komuna faktoro tra ĉiuj versioj de la malrapida demando.

Rigardante dokumentado, ni vidas tion && uzata kiam vi bezonas trovi komunajn elementojn inter du tabeloj.

En la originala peto ĉi tio estas:

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

Tio signifas, ke ni faras ŝablonan serĉon sur niaj URL-oj, tiam trovi la intersekciĝon kun ĉiuj URL-oj kun komunaj afiŝoj. Ĉi tio estas iom konfuza ĉar "urls" ĉi tie ne rilatas al la tabelo enhavanta ĉiujn URL-ojn, sed al la "urls" kolumno en la tabelo recording_data.

Kun kreskantaj suspektoj rilate &&, mi provis trovi konfirmon por ili en la demandplano generita EXPLAIN ANALYZE (Mi jam havis planon konservita, sed mi kutime pli komfortas eksperimenti en SQL ol provi kompreni la opakecon de konsultplanistoj).

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

Estis pluraj linioj de filtriloj nur de &&. Kio signifis, ke ĉi tiu operacio estis ne nur multekosta, sed ankaŭ farita plurajn fojojn.

Mi provis ĉi tion izolante la kondiĉon

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

Ĉi tiu demando estis malrapida. Ĉar la JOIN-s estas rapidaj kaj subdemandoj estas rapidaj, nur restis && operatoro.

Ĉi tio estas nur ŝlosila operacio. Ni ĉiam bezonas serĉi la tutan suban tabelon de URL-oj por serĉi ŝablonon, kaj ni ĉiam bezonas trovi intersekciĝojn. Ni ne povas serĉi rekte per URL-rekordoj, ĉar ĉi tiuj estas nur identigiloj al kiuj referencas urls.

Survoje al solvo

&& malrapida ĉar ambaŭ aroj estas grandegaj. La operacio estos relative rapida se mi anstataŭigos urls sur { "http://google.com/", "http://wingify.com/" }.

Mi komencis serĉi manieron fari fiksitan intersekciĝon en Postgres sen uzi &&, sed sen granda sukceso.

En la fino, ni decidis nur solvi la problemon izole: donu al mi ĉion urls linioj por kiuj la URL kongruas kun la ŝablono. Sen aldonaj kondiĉoj estos -

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

Anstataŭe JOIN sintakso mi ĵus uzis subdemandon kaj vastigis recording_data.urls tabelo por ke vi povu rekte apliki la kondiĉon en WHERE.

La plej grava afero ĉi tie estas tio && uzata por kontroli ĉu donita eniro enhavas kongruan URL. Se vi strebas iomete, vi povas vidi, ke ĉi tiu operacio moviĝas tra la elementoj de tabelo (aŭ vicoj de tabelo) kaj ĉesas kiam kondiĉo (kongruo) estas plenumita. Ĉu vi memorigas nenion? Jes, EXISTS.

Ekde recording_data.urls povas esti referencitaj de ekster la subdemanda kunteksto, kiam ĉi tio okazas ni povas refali sur nia malnova amiko EXISTS kaj envolvu la subdemandon per ĝi.

Kunigante ĉion, ni ricevas la finan optimumigitan demandon:

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

Kaj la fina plumbotempo Time: 1898.717 ms Tempo por festi?!?

Ne tiel rapide! Unue vi devas kontroli la ĝustecon. Mi estis ege suspektema pri EXISTS optimumigo ĉar ĝi ŝanĝas la logikon por fini pli frue. Ni devas certigi, ke ni ne aldonis neevidentan eraron al la peto.

Simpla provo estis kuri count(*) pri kaj malrapidaj kaj rapidaj demandoj por granda nombro da malsamaj datumaj aroj. Poste, por malgranda subaro de la datumoj, mi permane kontrolis, ke ĉiuj rezultoj estis ĝustaj.

Ĉiuj testoj donis konstante pozitivajn rezultojn. Ni riparis ĉion!

Lecionoj lernitaj

Estas multaj lecionoj por lerni de ĉi tiu rakonto:

Demandplanoj ne rakontas la tutan historion, sed ili povas provizi indicojn
La ĉefaj suspektatoj ne ĉiam estas la veraj kulpuloj
Malrapidaj demandoj povas esti malkonstruitaj por izoli proplempunktojn
Ne ĉiuj optimumigoj estas reduktivaj en naturo
Uzo EXIST, kie eble, povas konduki al dramecaj pliiĝoj en produktiveco

konkludo

Ni pasis de demanda tempo de ~24 minutoj al 2 sekundoj - sufiĉe grava rendimento pliiĝo! Kvankam ĉi tiu artikolo aperis granda, ĉiuj eksperimentoj, kiujn ni faris, okazis en unu tago, kaj oni taksis, ke ili daŭris inter 1,5 kaj 2 horojn por optimumigoj kaj testado.

SQL estas mirinda lingvo se vi ne timas ĝin, sed provu lerni kaj uzi ĝin. Havante bonan komprenon pri kiel SQL-demandoj estas ekzekutitaj, kiel la datumbazo generas demandplanojn, kiel indeksoj funkcias, kaj simple la grandecon de la datumoj, kiujn vi traktas, vi povas esti tre sukcesa ĉe optimumigo de demandoj. Same gravas, tamen, daŭre provi malsamajn alirojn kaj malrapide rompi la problemon, trovante la botelojn.

La plej bona parto pri atingi tiajn rezultojn estas la rimarkinda, videbla rapidecplibonigo - kie raporto, kiu antaŭe eĉ ne ŝarĝus, nun ŝarĝas preskaŭ tuj.

Specialan dankon al miaj kamaradoj laŭ ordono de Aditya Mishra, Aditya Gauru и Varun Malhotra por cerbumado kaj Dinkar Pandir por trovi gravan eraron en nia fina peto antaŭ ol ni finfine adiaŭis ĝin!

fonto: www.habr.com

La rakonto de unu SQL-enketo