ProHoster > Blogg > Administration > Historien om en SQL-utredning

Historien om en SQL-utredning

I december förra Äret fick jag en intressant felrapport frÄn VWO-supportteamet. Laddningstiden för en av de analytiska rapporterna för en stor företagsklient verkade orimligt lÄng. Eftersom det Àr mitt ansvarsomrÄde fokuserade jag genast pÄ att lösa problemet.

Bakgrund

För att klargöra vad det handlar om, ska jag kort sÀga nÄgra ord om VWO. Det Àr en plattform som gör det möjligt att köra olika riktade kampanjer pÄ sina webbplatser: genomföra A/B-experiment, spÄra besökare och konverteringar, göra analys av försÀljningstratten, visa vÀrmekartor och spela upp besöksinspelningar.

Men det viktigaste med plattformen Àr rapportgenerering. Alla ovanstÄende funktioner Àr kopplade till varandra. För företag Àr en stor mÀngd information praktiskt taget vÀrdelös utan en kraftfull plattform som presenterar den för analys.

Genom att anvÀnda plattformen kan man göra skrÀddarsydda förfrÄgningar pÄ stora datamÀngder. HÀr Àr ett enkelt exempel:

Visa alla klick pÄ sidan "abc.com"
FRÅN <datum d1> TILL <datum d2>
för personer som
anvÀnt Chrome ELLER
(varit i Europa OCH anvÀnt iPhone)

Observera de booleska operatorerna. De Àr tillgÀngliga för kunder i förfrÄgningsgrÀnssnittet för att göra hur komplexa förfrÄgningar som helst för att fÄ utvalda data.

LÄngsam förfrÄgan

Klienten det handlar om försökte göra nÄgot som intuitivt borde gÄ snabbt:

Visa alla sessioner
för anvÀndare som besökt vilken sida som helst
med en URL som innehÄller "\/jobs"

Denna webbplats hade en enorm mÀngd trafik och vi hade lagrat över en miljon unika URL-adresser bara för den. De ville hitta en ganska enkel mall av URL:er relaterad till deras affÀrsmodell.

PreliminÀr utredning

LÄt oss titta pÄ vad som hÀnder i databasen. HÀr Àr den ursprungliga lÄngsamma SQL-frÄgan:

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 ;

HÀr Àr tiderna:

BerÀknad tid: 1.480 ms
Exekveringstid: 1431924.650 ms

FrÄgan gick igenom 150 000 rader. FrÄgeschemalÀggaren visade nÄgra intressanta detaljer, men inga uppenbara flaskhalsar.

LÄt oss studera frÄgan vidare. Som vi kan se gör den JOIN tre tabeller:

  1. sessions: för att visa sessionsinformation: webblÀsare, anvÀndaragenter, land och sÄ vidare.
  2. recording_data: inspelade URL:er, sidor, besökens varaktighet
  3. urls: för att undvika duplicering av extremt stora URL:er, lagrar vi dem i en separat tabell.

Observera ocksÄ att alla vÄra tabeller redan Àr partitionerade efter account_id. PÄ sÄ sÀtt utesluts situationen dÀr ett sÀrskilt stort konto orsakar problem för andra.

I jakt pÄ ledtrÄdar

Vid en nÀrmare granskning ser vi att nÄgot Àr fel med den specifika frÄgan. Det Àr vÀrt att titta pÄ denna rad:

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

Den första tanken var att det kanske berodde pÄ ILIKE pÄ alla dessa lÄnga URL:er (vi har mer Àn 1,4 miljoner unika URL-adresser som samlades in för detta konto) kanske prestandan kunde pÄverkas.

Men, nej - det Àr inte det!

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

Tid: 5231.765 ms

SjÀlva sökfrÄgan tar bara 5 sekunder. Sökningen efter mönster pÄ en miljon unika URL:er Àr uppenbarligen inte ett problem.

NĂ€sta misstĂ€nkte pĂ„ listan - flera JOIN. Kanske deras överdrivna anvĂ€ndning ledde till en nedgĂ„ng? Vanligtvis JOIN‘er Ă€r de mest uppenbara kandidaterna för prestandaproblem, men jag trodde inte att vĂ„rt fall var typiskt.

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 rad)

Tid: 147.851 ms

Och det var heller inte vĂ„rt fall. JOIN‘er visade sig vara ganska snabba.

Vi smalnar av cirkeln av misstÀnkta

Jag var redo att börja Àndra frÄgan för att uppnÄ eventuella prestandaförbÀttringar. Vi i teamet utvecklade 2 huvudidéer:

VÄLJ
	count(*) 
FRÅN 
    acc_{account_id}.urls som recordings_urls,
    acc_{account_id}.recording_data som recording_data,
    acc_{account_id}.sessions som sessions
DÄR
    recording_data.usp_id = sessions.usp_id
    OCH  (  1 = 1  )
    OCH sessions.referrer_id = recordings_urls.id
    OCH  (existerar(select id frÄn acc_{account_id}.urls dÀr url  ILIKE '%enterprise_customer.com/jobs%'))
    OCH r_time > to_timestamp(1547585600)
    OCH r_time =5
    OCH recording_data.num_of_pages > 0 ;
 count
 32519
(1 rad)
Tid: 1636.637 ms

Ja. En underfrÄga, nÀr den Àr inslagen i EXISTS, gör allt supersnabbt. Den nÀsta logiska frÄgan Àr varför frÄgan med JOIN-er och underfrÄgan i sig Àr snabba var för sig, men extremt lÄngsamma tillsammans?

  • Flyttar underfrĂ„gan till CTE : om frĂ„gan Ă€r snabb i sig sjĂ€lv kan vi helt enkelt först berĂ€kna det snabba resultatet och sedan överlĂ€mna det till huvudfrĂ„gan.

MED matching_urls SOM (
    vÀlj id::text frÄn acc_{account_id}.urls dÀr url  ILIKE  '%enterprise_customer.com/jobs%'
)

VÄLJ 
    count(*) FRÅN acc_{account_id}.urls som recordings_urls, 
    acc_{account_id}.recording_data som recording_data, 
    acc_{account_id}.sessions som sessions,
    matching_urls
DÄR 
    recording_data.usp_id = sessions.usp_id 
    OCH  (  1 = 1  )  
    OCH sessions.referrer_id = recordings_urls.id
    OCH (urls && array(VÄLJ id frĂ„n matching_urls)::text[])
    OCH r_time > to_timestamp(1542585600) 
    OCH r_time =5 
    OCH recording_data.num_of_pages > 0;

Men det var fortfarande mycket lÄngsamt.

Hittar syndaren

Under hela tiden har en liten detalj förblivit i mitt bakhuvud, som jag stÀndigt bortförde. Men eftersom jag inte hade nÄgot annat kvar, bestÀmde jag mig för att Àven kolla den. Jag pratar om && operatören. Medan EXISTS bara förbÀttrade prestandan, && var den enda ÄterstÄende gemensamma faktorn i alla versioner av den lÄngsamma förfrÄgan.

Tittar vi pÄ dokumentationen, ser vi att && anvÀnds nÀr man behöver hitta gemensamma element mellan tvÄ arrayer.

I den ursprungliga frÄgan Àr det:

OCH  (  urls &&  array(vÀlj id frÄn acc_{account_id}.urls dÀr url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Vilket betyder att vi gör en mönstersökning pÄ vÄra URL:er, och sedan hittar vi skÀrningspunkten med alla URL:er som har gemensamma poster. Det Àr lite förvirrande eftersom 'urls' hÀr inte syftar pÄ en tabell som innehÄller alla URL:er, utan pÄ kolumnen 'urls' i tabellen recording_data.

Med ökande misstankar om &&, försökte jag hitta bevis för dem i frÄgeplanen som genererats av EXPLAIN ANALYZE (jag hade redan en sparad plan, men det Àr oftast mer bekvÀmt för mig att experimentera i SQL Àn att försöka förstÄ opaciteter hos frÄgeplanerarna).

Filter: ((urls && ($0)::text[]) OCH (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) OCH (r_time = '5'::double precision) OCH (num_of_pages > 0))
                           Rader borttagna av filter: 52710

Det fanns nÄgra rader filter bara frÄn &&. Vilket innebar att denna operation inte bara var kostsam, utan ocksÄ utfördes flera gÄnger.

Jag kontrollerade detta genom att isolera villkoret

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

Denna frÄga kördes lÄngsamt. Eftersom JOIN-er Àr snabba och subfrÄgor Àr snabba, Äterstod bara && operatorn.

Detta Àr bara den centrala operationen. Vi mÄste alltid söka i hela huvudtabellen med URL:er för att söka efter mönster, och vi mÄste alltid hitta överlappningar. Vi kan inte söka i URL-poster direkt, eftersom det bara Àr ID:n som hÀnvisar till urls.

PÄ vÀg mot en lösning

&& lÄngsam, eftersom bÄda uppsÀttningarna Àr enorma. Operationen kommer att vara relativt snabb om jag byter ut urls till { "http://google.com/", "http://wingify.com/" }.

Jag började leta efter en metod för att göra uppsÀttningsintersektioner i Postgres utan att anvÀnda &&, men utan större framgÄng.

Till slut beslutade vi oss för att bara lösa problemet isolerat: ge mig alla urls rader vars URL matchar mönstret. Utan ytterligare villkor blir det — 

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 OCH
	urls.url ILIKE '%jobs%'

IstĂ€llet för JOIN sĂ„ jag anvĂ€nde bara en subfrĂ„ga och rullade ut recording_data.urls arrayet, sĂ„ att villkoret direkt kunde tillĂ€mpas i DÄR.

Den viktiga punkten hÀr Àr att && anvÀnds för att kontrollera om den aktuella posten innehÄller den överensstÀmmande URL:en. Om man kisar lite kan man se i denna operation en rörelse genom element i arrayen (eller rader i tabellen) och en stopp nÀr villkoret (matchningen) uppfylls. KÀnns det bekant? Ah-ha, EXISTS.

Eftersom pÄ recording_data.urls kan refereras utanför kontexten av subfrÄgan, nÀr detta hÀnder kan vi ÄtergÄ till vÄr gamla vÀn EXISTS och omsluta den med en subfrÄga.

Sammanför allt fÄr vi den slutliga optimerade frÄgan:

VÄLJ 
    count(*) 
FRÅN 
    acc_{account_id}.urls som recordings_urls, 
    acc_{account_id}.recording_data som recording_data, 
    acc_{account_id}.sessions som sessions 
DÄR 
    recording_data.usp_id = sessions.usp_id 
    OCH  (  1 = 1  )  
    OCH sessions.referrer_id = recordings_urls.id 
    OCH r_time > to_timestamp(1542585600) 
    OCH r_time =5 
    OCH recording_data.num_of_pages > 0
    OCH FINNS(
        VÄLJ urls.url
        FRÅN 
            acc_{account_id}.urls som urls,
            (VÄLJ unnest(urls) SOM rec_url_id FRÅN acc_{account_id}.recording_data) 
            SOM unrolled_urls
        DÄR
            urls.id = unrolled_urls.rec_url_id OCH
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Och den slutgiltiga körningstiden Tid: 1898.717 ms Är det dags att fira?!?

Inte sÄ snabbt! Först mÄste vi kontrollera korrektheten. Jag var extremt misstÀnksam mot EXISTS optimeringen, eftersom den Àndrar logiken till en tidigare avslutning. Vi mÄste vara sÀkra pÄ att vi inte har lagt till ett subtilt fel i frÄgan.

Den enkla kontrollen bestod i att köra count(*) bÄde pÄ lÄngsamma och snabba frÄgor för en stor mÀngd olika datamÀngder. Sedan, för en liten delmÀngd av data, kontrollerade jag manuellt korrektheten av alla resultat.

Alla kontroller gav konsekvent positiva resultat. Vi har fixat allt!

Dra slutsatser

Det finns mÄnga lÀrdomar att dra frÄn denna historia:

  1. FrÄgeplaner berÀttar inte hela historien, men kan ge ledtrÄdar
  2. De frÀmsta misstÀnkta Àr inte alltid de verkliga förövarna
  3. LÄngsamma frÄgor kan delas upp för att isolera flaskhalsar
  4. Inte all optimering Àr per definition reducerande
  5. AnvÀndning EXIST, dÀr det Àr möjligt, kan leda till en kraftig ökning av prestandan

Utdata

Vi gick frĂ„n en frĂ„getid pĂ„ ~24 minuter till 2 sekunder — en mycket betydande prestandaökning! Även om denna artikel blev lĂ„ng, intrĂ€ffade alla experiment vi gjorde under en dag och uppskattades ta mellan 1,5 och 2 timmar för optimeringar och testning.

SQL Àr ett fantastiskt sprÄk, om man inte Àr rÀdd för det, utan istÀllet försöker förstÄ och anvÀnda det. Med god förstÄelse för hur SQL-frÄgor körs, hur databasen genererar frÄgeplaner, hur index fungerar och helt enkelt den datastorlek man har att göra med, kan man lyckas riktigt bra med förfrÄgningsoptimering. Det Àr ocksÄ viktigt att fortsÀtta prova olika tillvÀgagÄngssÀtt och lÄngsamt bryta ner problemet för att hitta flaskhalsar.

Den bĂ€sta delen av att uppnĂ„ sĂ„dana resultat Ă€r den mĂ€rkbara förbĂ€ttringen av hastigheten — nĂ€r en rapport som tidigare inte ens kunde laddas nu nĂ€stan laddas omedelbart.

Stort tack till mina kollegor i teamet Aditya Mishra, Aditya Gaur och Varun Malhotra för brainstorming och Dinkar Pandir för att ha hittat ett viktigt fel i vÄr slutliga förfrÄgan, innan vi slutligen skildes frÄn den!

KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster