🥇Het verhaal van één SQL-onderzoek

Afgelopen december ontving ik een interessant bugrapport van het VWO-ondersteuningsteam. De laadtijd voor een van de analyserapporten voor een grote zakelijke klant leek onbetaalbaar. En aangezien dit mijn verantwoordelijkheidsgebied is, heb ik mij meteen gericht op het oplossen van het probleem.

prehistorie

Om duidelijk te maken waar ik het over heb, zal ik wat vertellen over het vwo. Dit is een platform waarmee je verschillende gerichte campagnes op je websites kunt lanceren: voer A/B-experimenten uit, track bezoekers en conversies, analyseer de salesfunnel, toon heatmaps en speel bezoekopnames af.

Maar het belangrijkste aan het platform is rapportage. Alle bovenstaande functies zijn met elkaar verbonden. En voor zakelijke klanten zou een enorme hoeveelheid informatie eenvoudigweg nutteloos zijn zonder een krachtig platform dat deze in analytische vorm presenteert.

Met behulp van het platform kunt u een willekeurige zoekopdracht uitvoeren op een grote dataset. Hier is een eenvoudig voorbeeld:

Toon alle klikken op pagina "abc.com" VAN <datum d1> TOT <datum d2> voor mensen die Chrome hebben gebruikt OF (in Europa gevestigd EN een iPhone hebben gebruikt)

Besteed aandacht aan Booleaanse operatoren. Ze zijn beschikbaar voor clients in de query-interface om willekeurig complexe query's uit te voeren om monsters te verkrijgen.

Langzaam verzoek

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

Toon alle sessierecords voor gebruikers die een pagina hebben bezocht met een URL die '/jobs' bevat

Deze site had heel veel verkeer en we hebben speciaal daarvoor meer dan een miljoen unieke URL's opgeslagen. En ze wilden een vrij eenvoudige URL-sjabloon vinden die verband hield met hun bedrijfsmodel.

Vooronderzoek

Laten we eens kijken wat er in de database gebeurt. Hieronder vindt u de originele 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 < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

En hier zijn de tijden:

Geplande tijd: 1.480 ms Uitvoeringstijd: 1431924.650 ms

De zoekopdracht heeft 150 rijen gecrawld. De queryplanner liet een aantal interessante details zien, maar geen duidelijke knelpunten.

Laten we het verzoek verder bestuderen. Zoals je ziet doet hij dat wel JOIN drie tafels:

sessies: om sessie-informatie weer te geven: browser, user-agent, land, enzovoort.
opnamegegevens: geregistreerde URL's, pagina's, duur van bezoeken
urls: Om te voorkomen dat extreem grote URL's worden gedupliceerd, slaan we ze op in een aparte tabel.

Houd er ook rekening mee dat al onze tabellen al zijn gepartitioneerd op account_id. Op deze manier wordt een situatie uitgesloten waarin één bijzonder grote rekening problemen veroorzaakt voor anderen.

Op zoek naar aanwijzingen

Bij nadere inspectie zien wij dat er iets mis is met een bepaald verzoek. Het is de moeite waard om deze regel eens nader te bekijken:

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

De eerste gedachte was: misschien omdat ILIKE op al deze lange URL's (we hebben er meer dan 1,4 miljoen uniek URL's verzameld voor dit account) kunnen de prestaties achteruitgaan.

Maar nee, dat is niet het punt!

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

Time: 5231.765 ms

Het zoeken naar een sjabloon zelf duurt slechts 5 seconden. Zoeken naar een patroon in een miljoen unieke URL's is duidelijk geen probleem.

De volgende verdachte op de lijst zijn er meerdere JOIN. Misschien heeft hun overmatig gebruik de vertraging veroorzaakt? Gebruikelijk 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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

En dit was ook niet ons geval. JOINHet bleek behoorlijk snel te zijn.

Het verkleinen van de kring van verdachten

Ik was klaar om de query te wijzigen om mogelijke prestatieverbeteringen te bereiken. Mijn team en ik hebben twee hoofdideeën ontwikkeld:

Gebruik EXISTS voor de subquery-URL: We wilden nogmaals controleren of er problemen waren met de subquery voor de URL's. Een manier om dit te bereiken is door simpelweg te gebruiken EXISTS. EXISTS kan verbetert de prestaties aanzienlijk, omdat het onmiddellijk eindigt zodra het de enige tekenreeks vindt die aan de voorwaarde voldoet.

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

Wel, ja. Subquery wanneer ingepakt EXISTS, maakt alles supersnel. De volgende logische vraag is waarom het verzoek met JOIN-ami en de subquery zelf afzonderlijk snel zijn, maar samen verschrikkelijk traag?

Verplaats de subquery naar de CTE : Als de zoekopdracht op zichzelf snel is, kunnen we eenvoudigweg eerst het snelle resultaat berekenen en dit vervolgens aan de hoofdzoekopdracht doorgeven

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;

Maar het ging nog steeds erg langzaam.

Het vinden van de dader

Al die tijd flitste er een klein ding voor mijn ogen, dat ik voortdurend opzij veegde. Maar omdat er niets anders meer was, besloot ik ook naar haar te kijken. ik heb het over && exploitant. Doei EXISTS gewoon betere prestaties && was de enige overgebleven gemeenschappelijke factor in alle versies van de langzame query.

Kijken naar de documentatie, we zien dat && gebruikt wanneer u gemeenschappelijke elementen tussen twee arrays moet vinden.

In het oorspronkelijke verzoek is dit:

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

Dat betekent dat we een patroonzoekopdracht uitvoeren op onze URL's en vervolgens het kruispunt vinden met alle URL's met veelvoorkomende berichten. Dit is een beetje verwarrend omdat "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 &&, Ik heb geprobeerd bevestiging hiervoor te vinden in het gegenereerde queryplan EXPLAIN ANALYZE (Ik had al een plan opgeslagen, maar ik voel me meestal meer op mijn gemak bij het experimenteren in SQL dan bij het begrijpen van de ondoorzichtigheid van queryplanners).

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

Er waren alleen verschillende lijnen met filters &&. Wat betekende dat deze operatie niet alleen duur was, maar ook meerdere keren werd uitgevoerd.

Ik heb dit getest door de aandoening te isoleren

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

Deze zoekopdracht verliep traag. Omdat de JOIN-s zijn snel en subquery's zijn snel, het enige dat overblijft was && exploitant.

Dit is slechts een sleuteloperatie. We moeten altijd de hele onderliggende tabel met URL's doorzoeken om naar een patroon te zoeken, en we moeten altijd kruispunten vinden. We kunnen niet rechtstreeks zoeken op URL-records, omdat dit slechts ID's zijn die verwijzen naar urls.

Op weg naar een oplossing

&& langzaam omdat beide sets enorm zijn. Als ik vervang, zal de operatie relatief snel verlopen urls op { "http://google.com/", "http://wingify.com/" }.

Ik ging op zoek naar een manier om een kruispunt in Postgres in te stellen zonder gebruik te maken van &&, maar zonder veel succes.

Uiteindelijk besloten we het probleem gewoon geïsoleerd op te lossen: geef me alles urls regels waarvan de URL overeenkomt met het patroon. Zonder aanvullende voorwaarden zal het -

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

In plaats van JOIN syntaxis Ik heb zojuist een subquery gebruikt en uitgebreid recording_data.urls array, zodat u de voorwaarde rechtstreeks kunt toepassen WHERE.

Het belangrijkste hier is dat && gebruikt om te controleren of een bepaald item een overeenkomende URL bevat. Als u een beetje samenknijpt, ziet u dat deze bewerking door de elementen van een array (of rijen van een tabel) beweegt en stopt wanneer aan een voorwaarde (match) is voldaan. Herinnert u zich nergens aan? Ja, EXISTS.

Sinds recording_data.urls kan worden verwezen van buiten de subquery-context, wanneer dit gebeurt, kunnen we terugvallen op onze oude vriend EXISTS en verpak de subquery ermee.

Als we alles bij elkaar optellen, krijgen we de uiteindelijke geoptimaliseerde zoekopdracht:

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

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

Niet zo snel! Eerst moet u de juistheid controleren. Ik was er enorm wantrouwig over EXISTS optimalisatie omdat het de logica verandert om eerder te voltooien. We moeten er zeker van zijn dat we geen niet voor de hand liggende fout aan het verzoek hebben toegevoegd.

Er moest een eenvoudige test worden uitgevoerd count(*) op zowel langzame als snelle queries voor een groot aantal verschillende datasets. Vervolgens heb ik voor een kleine subset van de gegevens handmatig geverifieerd of alle resultaten correct waren.

Alle tests gaven consistent positieve resultaten. Wij hebben alles gerepareerd!

Les geleerd

Er zijn veel lessen te trekken uit dit verhaal:

Queryplannen vertellen niet het hele verhaal, maar kunnen wel aanwijzingen geven
De hoofdverdachten zijn niet altijd de echte daders
Langzame query's kunnen worden opgesplitst om knelpunten te isoleren
Niet alle optimalisaties zijn reductief van aard
Gebruiken EXIST, waar mogelijk, kan leiden tot dramatische productiviteitsstijgingen

Uitgang

We gingen van een zoektijd van ~24 minuten naar 2 seconden - een behoorlijk aanzienlijke prestatieverbetering! Hoewel dit artikel groot uitkwam, vonden alle experimenten die we deden op één dag plaats, en naar schatting duurde het tussen de 1,5 en 2 uur voor optimalisaties en testen.

SQL is een prachtige taal als je er niet bang voor bent, maar probeer het te leren en te gebruiken. Door een goed begrip te hebben van hoe SQL-query's worden uitgevoerd, hoe de database queryplannen genereert, hoe indexen werken en eenvoudigweg de omvang van de gegevens waarmee u te maken heeft, kunt u zeer succesvol zijn in het optimaliseren van query's. Het is echter net zo belangrijk om verschillende benaderingen te blijven uitproberen en het probleem langzaam op te lossen en de knelpunten te vinden.

Het beste van het bereiken van dit soort resultaten is de merkbare, zichtbare snelheidsverbetering; een rapport dat voorheen niet eens wilde laden, wordt nu vrijwel onmiddellijk geladen.

Speciale dank aan mijn kameraden op bevel van Aditya Mishra, Aditya Gauru и Varun Malhotra voor brainstormen en Dinakar Pandir voor het vinden van een belangrijke fout in ons laatste verzoek voordat we er eindelijk afscheid van namen!

Bron: www.habr.com

Het verhaal van een SQL-onderzoek