ProHoster > blogg > administration > Historien om en SQL-undersökning

Historien om en SQL-undersökning

I december förra året fick jag en intressant felrapport från VWOs supportteam. Laddningstiden för en av analysrapporterna för en stor företagskund verkade oöverkomlig. Och eftersom detta är mitt ansvarsområde fokuserade jag omedelbart på att lösa problemet.

förhistoria

För att göra det tydligt vad jag pratar om ska jag berätta lite om VWO. Det här är en plattform med vilken du kan lansera olika riktade kampanjer på dina webbplatser: genomföra A/B-experiment, spåra besökare och konverteringar, analysera försäljningstratten, visa värmekartor och spela upp besöksinspelningar.

Men det viktigaste med plattformen är rapportering. Alla ovanstående funktioner är sammankopplade. Och för företagskunder skulle en enorm mängd information helt enkelt vara värdelös utan en kraftfull plattform som presenterar den i analytisk form.

Med hjälp av plattformen kan du göra en slumpmässig fråga på en stor datamängd. 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 (finns i Europa OCH använt en iPhone)

Var uppmärksam på booleska operatorer. De är tillgängliga för klienter i frågegränssnittet för att göra godtyckligt komplexa frågor för att få prover.

Långsam begäran

Klienten i fråga försökte göra något som intuitivt borde fungera snabbt:

Visa alla sessionsposter för användare som besökte en sida med en URL som innehåller "/jobs"

Den här webbplatsen hade massor av trafik och vi lagrade över en miljon unika webbadresser bara för den. Och de ville hitta en ganska enkel URL-mall som var relaterad till deras affärsmodell.

Preliminär utredning

Låt oss ta en titt på vad som händer i databasen. Nedan ä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 ;

Och här är tiderna:

Planerad tid: 1.480 ms Utförandetid: 1431924.650 ms

Frågan genomsökte 150 tusen rader. Frågeplaneraren visade ett par intressanta detaljer, men inga uppenbara flaskhalsar.

Låt oss studera begäran vidare. Som ni ser så gör han det JOIN tre bord:

  1. sessioner: för att visa sessionsinformation: webbläsare, användaragent, land och så vidare.
  2. recording_data: registrerade webbadresser, sidor, besökslängd
  3. webbadresser: För att undvika att duplicera extremt stora webbadresser lagrar vi dem i en separat tabell.

Observera också att alla våra bord redan är partitionerade av account_id. På så sätt utesluts en situation där ett särskilt stort konto orsakar problem för andra.

Letar efter ledtrådar

Vid närmare granskning ser vi att något är fel med en viss förfrågan. Det är värt att titta närmare på den här raden:

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

Första tanken var att kanske pga ILIKE på alla dessa långa webbadresser (vi har över 1,4 miljoner unik Webbadresser som samlas in för det här kontot) kan påverkas.

Men nej, det är inte meningen!

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

Time: 5231.765 ms

Själva mallsökningsbegäran tar bara 5 sekunder. Att söka efter ett mönster i en miljon unika webbadresser är helt klart inget problem.

Nästa misstänkte på listan är flera JOIN. Kanske har deras överanvändning orsakat avmattningen? Vanligtvis JOINs är de mest uppenbara kandidaterna för prestationsproblem, 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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Och detta var inte heller vårt fall. JOINDet visade sig vara ganska snabbt.

Minskar kretsen av misstänkta

Jag var redo att börja ändra frågan för att uppnå eventuella prestandaförbättringar. Mitt team och jag utvecklade två huvudidéer:

  • Använd EXISTS för underfrågans URL: Vi ville kontrollera igen om det fanns några problem med underfrågan för webbadresserna. Ett sätt att uppnå detta är att helt enkelt använda EXISTS. EXISTS kan förbättra prestandan avsevärt eftersom den slutar omedelbart så snart den hittar den enda strängen som matchar villkoret.

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

Men ja. Underfråga när den är inslagen EXISTS, gör allt supersnabbt. Nästa logiska fråga är varför begäran med JOIN-ami och själva underfrågan är snabba var för sig, men är fruktansvärt långsamma tillsammans?

  • Flytta underfrågan till CTE : Om frågan är snabb på egen hand kan vi helt enkelt beräkna det snabba resultatet först och sedan ge det till huvudfrågan

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;

Men det gick fortfarande väldigt långsamt.

Att hitta den skyldige

Hela den här tiden blinkade en liten sak framför mina ögon, som jag hela tiden borstade undan. Men eftersom det inte fanns något mer kvar, bestämde jag mig för att titta på henne också. jag talar om && operatör. Hejdå EXISTS bara förbättrad prestanda && var den enda kvarvarande gemensamma faktorn för alla versioner av den långsamma frågan.

Tittar på dokumentation, vi ser det && används när du behöver hitta gemensamma element mellan två arrayer.

I den ursprungliga begäran är detta:

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

Vilket innebär att vi gör en mönstersökning på våra webbadresser och sedan hittar skärningspunkten med alla webbadresser med vanliga inlägg. Detta är lite förvirrande eftersom "urls" här inte syftar på tabellen som innehåller alla webbadresser, utan till kolumnen "urls" i tabellen recording_data.

Med växande misstankar ang &&, försökte jag hitta bekräftelse för dem i den genererade frågeplanen EXPLAIN ANALYZE (Jag hade redan en plan sparad, men jag är vanligtvis mer bekväm med att experimentera i SQL än att försöka förstå opaciteten hos frågeplanerare).

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

Det fanns flera rader med filter bara från &&. Vilket gjorde att denna operation inte bara var dyr, utan även utförd flera gånger.

Jag testade detta genom att isolera tillståndet

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

Den här frågan var långsam. Eftersom den JOIN-s är snabba och subqueries är snabba, det enda som återstod var && operatör.

Detta är bara en nyckeloperation. Vi behöver alltid söka i hela den underliggande tabellen med webbadresser för att söka efter ett mönster, och vi behöver alltid hitta korsningar. Vi kan inte söka direkt med URL-poster, eftersom dessa bara är ID:n som refererar till urls.

På väg mot en lösning

&& långsam eftersom båda uppsättningarna är enorma. Operationen går relativt snabbt om jag byter ut urls på { "http://google.com/", "http://wingify.com/" }.

Jag började leta efter ett sätt att göra inställd korsning i Postgres utan att använda &&men utan större framgång.

Till slut bestämde vi oss för att bara lösa problemet isolerat: ge mig allt urls rader för vilka webbadressen 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 AND
	urls.url  ILIKE  '%jobs%'

I stället för JOIN syntax Jag använde bara en underfråga och utökade recording_data.urls array så att du direkt kan tillämpa villkoret i WHERE.

Det viktigaste här är det && används för att kontrollera om en given post innehåller en matchande URL. Om du kisar lite kan du se att den här operationen rör sig genom elementen i en array (eller rader i en tabell) och stannar när ett villkor (matchning) är uppfyllt. Påminner du dig inte om någonting? Ja, EXISTS.

Sedan dess recording_data.urls kan refereras utanför underfrågans sammanhang, när detta händer kan vi falla tillbaka på vår gamla vän EXISTS och linda underfrågan med den.

När vi sätter ihop allt får vi den slutliga optimerade 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  (  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%'
    );

Och den sista ledtiden Time: 1898.717 ms Dags att fira?!?

Inte så fort! Först måste du kontrollera korrektheten. Jag var extremt misstänksam EXISTS optimering eftersom det ändrar logiken för att slutföra tidigare. Vi måste vara säkra på att vi inte har lagt till ett icke-uppenbart fel i begäran.

Ett enkelt test var att köra count(*) på både långsamma och snabba frågor för ett stort antal olika datamängder. Sedan, för en liten delmängd av data, verifierade jag manuellt att alla resultat var korrekta.

Alla tester gav genomgående positiva resultat. Vi fixade allt!

Lärdomar

Det finns många lärdomar att dra av denna berättelse:

  1. Frågeplaner berättar inte hela historien, men de kan ge ledtrådar
  2. De huvudmisstänkta är inte alltid de verkliga skyldiga
  3. Långsamma frågor kan brytas ned för att isolera flaskhalsar
  4. Alla optimeringar är inte reduktiva till sin natur
  5. Använd EXIST, där det är möjligt, kan leda till dramatiska ökningar av produktiviteten

Utgång

Vi gick från en frågetid på ~24 minuter till 2 sekunder - en ganska betydande prestandaökning! Även om den här artikeln blev stor, hände alla experiment vi gjorde på en dag, och det uppskattades att de tog mellan 1,5 och 2 timmar för optimeringar och testning.

SQL är ett underbart språk om du inte är rädd för det, utan försöker lära dig och använda det. Genom att ha en god förståelse för hur SQL-frågor exekveras, hur databasen genererar frågeplaner, hur index fungerar och helt enkelt storleken på den data du har att göra med, kan du bli mycket framgångsrik med att optimera frågor. Det är dock lika viktigt att fortsätta att pröva olika tillvägagångssätt och sakta bryta ner problemet och hitta flaskhalsarna.

Det bästa med att uppnå sådana här resultat är den märkbara, synliga hastighetsförbättringen - där en rapport som tidigare inte ens skulle laddas nu laddas nästan omedelbart.

Särskilt tack till mina kamrater på befallning av Aditya Mishra, Aditya Gauru и Varun Malhotra för brainstorming och Dinkar Pandir för att vi hittade ett viktigt fel i vår sista förfrågan innan vi slutligen sa adjö till den!

Källa: will.com

Lägg en kommentar