ProHoster > Blog > Beheer > Het verhaal van een SQL-onderzoek

Het verhaal van een SQL-onderzoek

In december vorig jaar ontving ik een interessante foutmelding van het VWO-ondersteuningsteam. De laadtijd van een van de analytische rapporten voor een grote zakelijke klant leek ongewoon lang. Aangezien dit binnen mijn verantwoordelijkheid valt, concentreerde ik me onmiddellijk op het oplossen van het probleem.

Achtergrond

Om duidelijk te maken waar het om gaat, zal ik kort iets vertellen over VWO. Dit is een platform waarmee je verschillende gerichte campagnes op je websites kunt uitvoeren: A/B-experimenten uitvoeren, bezoekers en conversies volgen, verkoopfunnels analyseren, heatmaps weergeven en sessierecords afspelen.

Maar het belangrijkste van het platform is rapportage. Al deze functies zijn met elkaar verbonden. Voor zakelijke klanten zou een enorme hoeveelheid informatie eenvoudigweg nutteloos zijn zonder een krachtig platform dat deze presenteert voor analyse.

Met het platform kun je willekeurige verzoeken doen op een grote dataset. Hier is een eenvoudig voorbeeld:

Toon alle klikken op de pagina "abc.com"
VAN <datum d1> TOT <datum d2>
voor mensen die
Chrome gebruikten OF
(in Europa waren EN iPhone gebruikten)

Let op de logische operators. Deze zijn beschikbaar voor klanten in de verzoekinterface, zodat ze zo complex mogelijke verzoeken kunnen doen om selecties te verkrijgen.

Langzame aanvraag

De betrokken klant probeerde iets te doen dat intuïtief snel zou moeten werken:

Laat alle sessierecords zien
voor gebruikers die een pagina hebben bezocht
met een URL die "\/jobs" bevat

Deze site had een enorme hoeveelheid verkeer, en we bewaarden meer dan een miljoen unieke URL-adressen alleen al voor deze site. En ze wilden een vrij eenvoudig URL-sjabloon vinden dat betrekking had op hun bedrijfsmodel.

Voorlopig onderzoek

Laten we eens kijken naar wat er in de database gebeurt. Hieronder staat de oorspronkelijke langzame SQL-query:

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

Hier zijn de tijden:

Geplande tijd: 1.480 ms
Uitvoertijd: 1431924.650 ms

De query doorzocht 150.000 rijen. De queryplanner toonde een paar interessante details, maar geen duidelijke knelpunten.

Laten we de query verder bestuderen. Zoals te zien is, maakt hij JOIN drie tabellen aan:

  1. sessions: om sessie-informatie weer te geven: browser, user agent, land, enzovoort.
  2. recording_data: geregistreerde URL's, pagina's, duur van bezoeken
  3. urls: om duplicatie van extreem lange URL's te voorkomen, bewaren we ze in een aparte tabel.

Let ook op dat al onze tabellen al zijn gescheiden op account_id. Dit voorkomt dat, door één bijzonder groot account, andere accounts problemen ondervinden.

Op zoek naar aanwijzingen

Bij nader inzien zien we dat er iets niet klopt met deze specifieke query. We moeten deze regel nader bekijken:

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

De eerste gedachte was dat het mogelijk aan ILIKE ligt, aangezien al deze lange URL's (we hebben meer dan 1,4 miljoen unieke URL's verzameld voor dit account) de prestaties zouden kunnen beïnvloeden.

Maar nee — dat is het niet!

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

Tijd: 5231.765 ms

De zoekopdracht op basis van het patroon duurt slechts 5 seconden. Zoeken op een miljoen unieke URL's blijkt duidelijk geen probleem te zijn.

De volgende verdachte op de lijst — meerdere JOIN. Misschien heeft hun overmatig gebruik geleid tot vertraging? Gewoonlijk JOIN's zijn de meest voor de hand liggende kandidaten voor prestatieproblemen, maar ik geloofde niet dat ons geval typisch was.

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

Tijd: 147.851 ms

En ook dat was niet ons geval. JOIN's bleken erg snel te zijn.

De kring van verdachten verkleinen

Ik was bereid om de query te wijzigen om eventuele prestatieverbeteringen te bereiken. Mijn team en ik ontwikkelden 2 belangrijke ideeën:

  • Gebruik EXISTS voor de subquery voor URL's: We wilden nogmaals controleren of er problemen waren met de subquery voor de URL's. Een manier om dit te bereiken is simpelweg te gebruiken EXISTS. EXISTS kan verbeterde prestaties aanzienlijk, aangezien het onmiddellijk eindigt wanneer het de enige rij volgens de voorwaarde vindt.

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 rij)
Tijd: 1636.637 ms

Ja. De subquery, wanneer verpakt in EXISTS, maakt alles super snel. De volgende logische vraag is waarom de query met JOIN-en en de subquery zelf snel zijn als ze afzonderlijk worden uitgevoerd, maar samen vreselijk traag zijn?

  • We verplaatsen de subquery naar CTE : als de query op zichzelf snel is, kunnen we gewoon eerst het snelle resultaat berekenen en het vervolgens aan de hoofdquery geven.

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;

Maar dit was nog steeds erg traag.

We vinden de schuldige.

De hele tijd flitste er één klein ding voor mijn ogen, waar ik constant voor wegkeek. Maar omdat er verder niets meer overbleef, besloot ik het ook maar te bekijken. Ik heb het over && de operator. Tot nu toe EXISTS verbeterde de prestaties, && was de enige resterende gemene deler in alle versies van de trage query.

Kijkend naar documentatie, zien we dat && wordt gebruikt wanneer we gemeenschappelijke elementen tussen twee arrays moeten vinden.

In de originele query is dit:

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

Wat betekent dat we een patroon zoeken in onze urls, en vervolgens de intersectie vinden met alle urls met gemeenschappelijke records. Dit is een beetje verwarrend, aangezien "urls" hier niet verwijst naar de tabel die alle URL's bevat, maar naar de kolom "urls" in de tabel recording_data.

Met groeiende vermoedens over &&, probeerde ik ze te bevestigen in het queryplan dat gegenereerd werd. EXPLAIN ANALYZE (ik had al een opgeslagen plan, maar ik vind het meestal gemakkelijker om in SQL te experimenteren dan om de ondoorzichtigheid van queryplanners te begrijpen).

Filter: ((urls && ($0)::text[]) EN (r_time > '2018-12-17 12:17:23+00'::timestamp met tijdszone) EN (r_time = '5'::double precision) EN (num_of_pages > 0))
                           Rijen verwijderd door filter: 52710

Er waren verschillende filterregels alleen van &&. Wat betekende dat deze operatie niet alleen kostbaar was, maar ook meerdere keren werd uitgevoerd.

Ik controleerde dit door de voorwaarde te isoleren

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

Deze query werd langzaam uitgevoerd. Aangezien JOIN-en subqueries snel zijn, bleef alleen de && operator over.

Maar dit is wel de sleuteloperatie. We moeten altijd in de principale URL-tabel zoeken om op het patroon te zoeken, en we moeten altijd intersecties vinden. We kunnen niet direct in de URL-records zoeken, omdat dit gewoon ID's zijn die verwijzen naar urls.

Op weg naar de oplossing

&& langzaam is, omdat beide sets enorm zijn. De operatie zal relatief snel zijn als ik vervang urls en een werkende opdracht krijgen. { "http://google.com/", "http://wingify.com/" }.

Ik begon te zoeken naar een manier om in Postgres verzamelingen te kruisen zonder gebruik te maken van &&, maar zonder veel succes.

Uiteindelijk besloten we gewoon het probleem geïsoleerd op te lossen: geef me alle urls rijen waarvoor de url aan het patroon voldoet. Zonder extra voorwaarden zal dit zijn — 

SELECT urls.url
FROM 
	acc_{account_id}.urls als urls,
	(SELECT unnest(recording_data.urls) AS id) ALS unrolled_urls
WAAR
	urls.id = unrolled_urls.id EN
	urls.url  ILIKE  '%jobs%'

In plaats van JOIN de syntaxis gebruikte ik gewoon een subquery en rolde recording_data.urls de array uit, zodat de voorwaarde rechtstreeks kan worden toegepast op WAAR.

Het belangrijkste hieraan is dat && wordt gebruikt om te controleren of deze opname de overeenkomstige URL bevat. Als je goed kijkt, zie je in deze operatie de iteratie over de array-elementen (of tabelrijen) en de stop bij het voldoen aan de voorwaarde (overeenkomst). Herinnert dit je aan iets? Ja, EXISTS.

Omdat je op recording_data.urls kan verwijzen vanuit de context van de subquery, wanneer dit gebeurt, kunnen we terugkeren naar onze oude vriend EXISTS en deze met de subquery omhullen.

Door alles samen te voegen, krijgen we de uiteindelijke geoptimaliseerde query:

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

En de uiteindelijke uitvoeringstijd Tijd: 1898.717 ms Tijd om te vieren?!?

Niet zo snel! Eerst moet de correctheid worden gecontroleerd. Ik was uiterst wantrouwig tegenover EXISTS optimalisatie, omdat dit de logica verandert naar een eerder einde. We moeten er zeker van zijn dat we geen onopgemerkte fout aan de query hebben toegevoegd.

Een eenvoudige controle bestond uit het uitvoeren van count(*) zowel op langzame als op snelle queries voor een groot aantal verschillende datasets. Vervolgens heb ik handmatig de correctheid van alle resultaten gecontroleerd voor een kleine subset van de gegevens.

Alle controles gaven consistent positieve resultaten. We hebben alles gerepareerd!

Geleerde lessen

Er kunnen veel lessen uit dit verhaal worden getrokken:

  1. Query-plannen vertellen niet het volledige verhaal, maar kunnen aanwijzingen geven
  2. De belangrijkste verdachten zijn niet altijd de echte boosdoeners
  3. Trage queries kunnen worden opgesplitst om knelpunten te isoleren
  4. Niet alle optimalisaties zijn van nature reducerend
  5. Gebruik van EXIST, waar mogelijk, kan leiden tot een aanzienlijke prestatieverbetering

Uitslag

We zijn van een querytijd van ~24 minuten naar 2 seconden gegaan — een zeer serieuze prestatieverbetering! Hoewel dit artikel groot is geworden, zijn alle experimenten die we deden op één dag uitgevoerd en kostten naar schatting 1,5 tot 2 uur voor optimalisatie en testen.

SQL is een wonderlijke taal, als je er niet bang voor bent, maar probeert deze te begrijpen en te gebruiken. Met een goed begrip van hoe SQL-queries worden uitgevoerd, hoe DB query-plannen genereert, hoe indexen werken en gewoon de grootte van de gegevens waarmee je te maken hebt, kun je echt uitblinken in query-optimalisatie. Even belangrijk is echter om te blijven experimenteren met verschillende benaderingen en het probleem langzaam te splitsen om knelpunten te vinden.

Het mooiste aan het behalen van dergelijke resultaten is de merkbare verbetering van de snelheid - een rapport dat voorheen niet eens geladen kon worden, laadt nu bijna onmiddellijk.

Speciale waardering aan mijn collega's in het team Aditya Mishra, Aditya Gaur en Varun Malhotra voor de brainstorm en Dinkar Pandir voor het vinden van een belangrijke fout in onze laatste aanvraag, voordat we uiteindelijk afscheid van hem namen!

Bron: habr.com

Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers 🔥 Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers | ProHoster