L'histoire d'une enquête SQL

En décembre de l'année dernière, j'ai reçu un rapport intéressant sur une erreur de l'équipe de support VWO. Le temps de chargement d'un des rapports analytiques pour un grand client entreprise semblait excessivement long. Étant donné que cela relève de ma responsabilité, je me suis immédiatement concentré sur la résolution du problème.

Contexte

Pour comprendre de quoi il s'agit, je vais parler un peu de VWO. C'est une plateforme qui permet de lancer différentes campagnes ciblées sur ses sites : réaliser des expériences A/B, suivre les visiteurs et les conversions, analyser l'entonnoir de vente, afficher des cartes de chaleur et reproduire des enregistrements de visites.

Mais ce qui est le plus important dans la plateforme, c'est la création de rapports. Toutes les fonctions mentionnées ci-dessus sont interconnectées. Et pour les clients entreprise, un grand volume d'informations serait tout simplement inutile sans une plateforme puissante qui les présente sous forme d'analytique.

En utilisant la plateforme, il est possible de faire une requête libre sur un grand ensemble de données. Voici un exemple simple :

Afficher tous les clics sur la page "abc.com"
DE <date d1> À <date d2>
pour les personnes qui
utilisaient Chrome OU
(étaient en Europe ET utilisaient un iPhone)

Notez les opérateurs booléens. Ils sont disponibles pour les clients dans l'interface de requête afin de réaliser des requêtes aussi complexes que souhaité pour obtenir des échantillons.

Requête lente

Le client en question essayait de faire quelque chose qui, selon l'intuition, devrait fonctionner rapidement :

Montrez tous les enregistrements de sessions
pour les utilisateurs ayant visité n'importe quelle page
avec une URL contenant "/jobs"

Ce site avait un énorme trafic, et nous stockions plus d'un million d'URL uniques juste pour lui. Ils voulaient trouver un modèle d'URL assez simple, pertinent pour leur modèle commercial.

Enquête préliminaire

Regardons ce qui se passe dans la base de données. Voici la requête SQL lente originale :

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 ;

Voici les temps :

Temps prévu : 1,480 ms
Temps d'exécution : 1,431,924.650 ms

La requête a parcouru 150 000 lignes. Le planificateur de requêtes a montré quelques détails intéressants, mais aucune goulot d'étranglement évident.

Continuons à examiner la requête. Comme on peut le voir, elle effectue JOIN trois tables :

1. sessions: pour afficher les informations de session : navigateur, agent utilisateur, pays, etc.
2. recording_data: URLs enregistrées, pages, durée des visites
3. urls: pour éviter la duplication d'URLs exceptionnellement longues, nous les stockons dans une table séparée.

Remarquez également que toutes nos tables sont déjà divisées par account_id. Ainsi, il est évité qu'en raison d'un compte particulièrement volumineux, des problèmes se produisent pour les autres.

À la recherche d'indices

En y regardant de plus près, nous voyons qu'il y a quelque chose d'anormal dans cette requête spécifique. Il vaut la peine de porter attention à cette ligne :

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

La première pensée était que peut-être à cause de ILIKE sur toutes ces longues URLs (nous avons plus de 1,4 million de URLs uniques rassemblées pour ce compte) la performance pourrait être impactée. Mais non — ce n'est pas ça !

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 lignes)Temps : 5231.765 ms

La requête de recherche par motif ne prend que 5 secondes. La recherche par motif sur un million d'URLs uniques n'est manifestement pas un problème.

Le prochain suspect sur la liste — quelques

. Peut-être leur utilisation excessive a-t-elle contribué à ralentir ? Habituellement JOIN‘s sont les candidats les plus évidents pour les problèmes de performance, mais je ne croyais pas que notre cas était typique. JOINanalytics_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 ligne)Temps : 147.851 ms

Et cela n'était pas notre cas non plus.

Les ‘s se sont révélés très rapides. JOINRéduire le cercle des suspects

J'étais prêt à commencer à modifier la requête pour atteindre des améliorations de performance possibles. Avec l'équipe, nous avons élaboré 2 idées principales :

Utiliser EXISTS pour la sous-requête URL

• : Nous voulions vérifier à nouveau s'il n'y avait pas de problèmes avec la sous-requête pour les URLs. Une des façons d'y parvenir est simplement de utiliser: Nous voulions vérifier à nouveau s'il y avait des problèmes avec la requête pour les URL. L'une des façons d'y parvenir est simplement d'utiliser soient « vrais », mais. soient « vrais », mais peut améliorer considérablement les performances car il se termine dès qu'il trouve la première ligne correspondant à la condition.

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 row)
Time: 1636.637 ms

Eh bien. La sous-requête, lorsqu'elle est encapsulée dans soient « vrais », mais, rend tout super rapide. La question logique suivante est : pourquoi la requête avec JOIN-s et la sous-requête elle-même sont rapides séparément, mais ralentissent terriblement ensemble ?

• Déplaçons la sous-requête dans un CTE : si la requête est rapide en elle-même, nous pouvons simplement d'abord calculer le résultat rapide, puis le fournir à la requête principale.

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;

Mais cela restait encore très lent.

Trouvons le coupable

Tout ce temps, une petite chose me trottait dans la tête à laquelle je me suis constamment dérobé. Mais puisque je n'avais plus rien d'autre, j'ai décidé de m'y attarder. Je parle de && l'opérateur. Pendant que soient « vrais », mais a simplement amélioré les performances, && était le seul facteur commun restant dans toutes les versions de la requête lente.

En regardant documentation, nous voyons que && est utilisé lorsque nous devons trouver des éléments communs entre deux tableaux.

Dans la requête originale, c'est :

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

Ce qui signifie que nous effectuons une recherche par motif sur nos URLs, puis trouvons l'intersection avec toutes les URLs ayant des enregistrements en commun. C'est un peu confus, car « urls » ici ne fait pas référence à la table contenant toutes les adresses URL, mais à la colonne « urls » dans la table recording_data.

Avec des soupçons grandissants envers &&, j'ai tenté de les confirmer dans le plan de requête généré. EXPLAIN ANALYZE (j'avais déjà un plan enregistré, mais je préfère généralement expérimenter avec SQL plutôt que d'essayer de comprendre les opacités des planificateurs de requêtes).

Filtre : ((urls && ($0)::text[]) ET (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) ET (r_time = '5'::double precision) ET (num_of_pages > 0))
                           Lignes supprimées par le filtre : 52710

Il y avait plusieurs lignes de filtres uniquement depuis &&. Ce qui signifiait que cette opération non seulement coûtait cher, mais était également exécutée plusieurs fois.

J'ai vérifié cela en isolant la condition

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

Cette requête s'exécutait lentement. Puisque JOIN-s sont rapides et les sous-requêtes sont rapides, il ne restait que && l'opérateur.

C'est juste que c'est l'opération clé. Nous devons toujours rechercher dans l'ensemble principal des URLs pour rechercher par motif, et nous devons toujours trouver des intersections. Nous ne pouvons pas rechercher directement dans les enregistrements d'urls, car ce ne sont que des identifiants faisant référence à urls.

En chemin vers la solution

&& lente, car les deux ensembles sont énormes. L'opération sera relativement rapide si je remplace urls sur { "http://google.com/", "http://wingify.com/" }.

J'ai commencé à chercher un moyen de faire en Postgres l'intersection de plusieurs ensembles sans utiliser &&, mais sans grand succès.

Finalement, nous avons décidé de simplement résoudre le problème de manière isolée : donne-moi toutes les urls lignes où l'url correspond au motif. Sans conditions supplémentaires, ce sera — 

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

Au lieu de JOIN syntaxe, j'ai simplement utilisé une sous-requête et déballé recording_data.urls le tableau, afin qu'il soit possible d'appliquer directement la condition dans OÙ.

Ce qui est le plus important ici, c'est que && est utilisé pour vérifier si cette entrée contient l'URL correspondante. En plissant un peu les yeux, on peut voir dans cette opération le déplacement à travers les éléments du tableau (ou les lignes d'une table) et l'arrêt lors de l'exécution de la condition (correspondance). Ça ne vous rappelle rien ? Ah, soient « vrais », mais.

Puisque sur recording_data.urls peut être référencé en dehors du contexte de la sous-requête, lorsqu'elle se produit, nous pouvons revenir à notre vieux camarade soient « vrais », mais et l'envelopper dans la sous-requête.

En combinant le tout, nous obtenons la requête optimisée finale :

SÉLECTIONNER 
    COUNT(*) 
DE 
    acc_{account_id}.urls comme recordings_urls, 
    acc_{account_id}.recording_data comme recording_data, 
    acc_{account_id}.sessions comme sessions 
OÙ 
    recording_data.usp_id = sessions.usp_id 
    ET (  1 = 1  )  
    ET sessions.referrer_id = recordings_urls.id 
    ET r_time > to_timestamp(1542585600) 
    ET r_time =5 
    ET recording_data.num_of_pages > 0
    ET EXISTE(
        SÉLECTIONNER urls.url
        DE 
            acc_{account_id}.urls comme urls,
            (SÉLECTIONNER unnest(urls) COMME rec_url_id DE acc_{account_id}.recording_data) 
            COMME unrolled_urls
        OÙ
            urls.id = unrolled_urls.rec_url_id ET
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Et le temps d'exécution final Temps : 1898,717 ms Il est temps de célébrer?!?

Pas si vite ! D'abord, il faut vérifier la validité. J'étais extrêmement suspicieux concernant soient « vrais », mais l'optimisation, car elle modifie la logique pour une fin anticipée. Nous devons être sûrs de ne pas avoir introduit une erreur subtile dans la requête.

La simple vérification consistait à exécuter count(*) à la fois sur des requêtes lentes et rapides pour un large éventail de jeux de données. Ensuite, pour un petit sous-ensemble de données, j'ai vérifié manuellement l'exactitude de tous les résultats.

Tous les tests ont donné des résultats positifs stables. Tout est réparé !

Leçons tirées

Il y a beaucoup de leçons à tirer de cette histoire :

1. Les plans de requêtes ne racontent pas toute l'histoire, mais peuvent donner des indices
2. Les principaux suspects ne sont pas toujours les véritables coupables
3. Les requêtes lentes peuvent être décomposées pour isoler les goulets d'étranglement
4. Toutes les optimisations ne sont pas par nature réductrices
5. Utilisation EXIST, là où c'est possible, cela peut conduire à une augmentation radicale des performances

Sortie

Nous sommes passés d'un temps de requête d'environ 24 minutes à 2 secondes — une augmentation de performance assez sérieuse ! Bien que cet article soit long, toutes les expériences que nous avons menées se sont déroulées en une journée et ont pris entre 1,5 et 2 heures pour les optimisations et les tests.

SQL est un merveilleux langage, si l'on n'en a pas peur, mais que l'on cherche à le comprendre et à l'utiliser. Avec une bonne compréhension de la façon dont les requêtes SQL sont exécutées, de la manière dont la base de données génère des plans de requête, de la façon dont les index fonctionnent et simplement de la taille des données avec lesquelles vous avez affaire, vous pourrez vraiment exceller dans l'optimisation des requêtes. Il est aussi crucial de continuer à essayer différentes approches et à décomposer progressivement le problème, en identifiant les goulets d'étranglement.

La meilleure partie d'obtenir de tels résultats est l'amélioration visible et significative de la vitesse — lorsque le rapport qui ne se chargeait même pas auparavant se charge maintenant presque instantanément.

Un remerciement particulier à mes collègues de l'équipe Aditya Mishra, Aditya Gaur et Varun Malhotra pour le brainstorming et Dinkar Pandir pour avoir trouvé une erreur importante dans notre requête finale, avant que nous ne nous en séparions définitivement !

Source : habr.com