🥇L'histoire d'une enquête SQL

En décembre dernier, j'ai reçu un rapport de bug intéressant de la part de l'équipe d'assistance VWO. Le temps de chargement de l’un des rapports d’analyse d’une grande entreprise cliente semblait prohibitif. Et comme c'est mon domaine de responsabilité, je me suis immédiatement concentré sur la résolution du problème.

Préhistoire

Pour que ce soit clair de quoi je parle, je vais vous parler un peu de VWO. Il s'agit d'une plateforme avec laquelle vous pouvez lancer diverses campagnes ciblées sur vos sites Web : mener des expériences A/B, suivre les visiteurs et les conversions, analyser l'entonnoir de vente, afficher des cartes thermiques et diffuser des enregistrements de visites.

Mais la chose la plus importante à propos de la plateforme, ce sont les rapports. Toutes les fonctions ci-dessus sont interconnectées. Et pour les entreprises clientes, une énorme quantité d’informations serait tout simplement inutile sans une plateforme puissante qui les présente sous forme d’analyse.

Grâce à la plateforme, vous pouvez effectuer une requête aléatoire sur un grand ensemble de données. Voici un exemple simple :

Afficher tous les clics sur la page "abc.com" DU <date d1> AU <date d2> pour les personnes ayant utilisé Chrome OU (situées en Europe ET utilisant un iPhone)

Faites attention aux opérateurs booléens. Ils sont à la disposition des clients dans l'interface de requête pour effectuer des requêtes arbitrairement complexes afin d'obtenir des échantillons.

Demande lente

Le client en question essayait de faire quelque chose qui, intuitivement, devrait fonctionner rapidement :

Afficher tous les enregistrements de session pour les utilisateurs qui ont visité une page avec une URL contenant "/jobs"

Ce site avait une tonne de trafic et nous stockions plus d'un million d'URL uniques rien que pour lui. Et ils voulaient trouver un modèle d’URL assez simple et lié à leur modèle économique.

Enquête préliminaire

Jetons un coup d'œil à ce qui se passe dans la base de données. Vous trouverez ci-dessous la requête SQL lente d'origine :

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 ;

Et voici les horaires :

Temps prévu : 1.480 ms Temps d'exécution : 1431924.650 ms

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

Étudions la demande plus en détail. Comme vous pouvez le voir, il le fait JOIN trois tableaux :

brainstorming: pour afficher les informations de session : navigateur, agent utilisateur, pays, etc.
données_d'enregistrement: URL enregistrées, pages, durée des visites
urls: Pour éviter de dupliquer des URL extrêmement volumineuses, nous les stockons dans un tableau séparé.

Notez également que toutes nos tables sont déjà partitionnées par account_id. De cette façon, une situation dans laquelle un compte particulièrement important cause des problèmes aux autres est exclue.

À la recherche d'indices

En y regardant de plus près, nous constatons que quelque chose ne va pas avec une demande particulière. Cela vaut la peine de regarder de plus près cette ligne :

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

La première pensée a été que c'était peut-être parce que ILIKE sur toutes ces URL longues (nous en avons plus de 1,4 millions) unique URL collectées pour ce compte), les performances peuvent en souffrir.

Mais non, ce n’est pas le sujet !

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

Time: 5231.765 ms

La demande de recherche de modèle elle-même ne prend que 5 secondes. Rechercher un modèle dans un million d’URL uniques n’est clairement pas un problème.

Le prochain suspect sur la liste est plusieurs JOIN. Peut-être que leur surexploitation est à l’origine du ralentissement ? Généralement JOINsont les candidats les plus évidents aux problèmes de performances, mais je ne pensais pas que notre cas était typique.

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

Et ce n’était pas non plus notre cas. JOINCela s'est avéré assez rapide.

Réduire le cercle des suspects

J'étais prêt à commencer à modifier la requête pour obtenir d'éventuelles améliorations de performances. Mon équipe et moi avons développé 2 idées principales :

Utiliser EXISTS pour l'URL de sous-requête: Nous voulions vérifier à nouveau s'il y avait des problèmes avec la sous-requête pour les URL. Une façon d'y parvenir est d'utiliser simplement EXISTS. EXISTS может améliore considérablement les performances puisqu'il se termine immédiatement dès qu'il trouve la seule chaîne qui correspond à 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 < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Hé bien oui. Sous-requête une fois enveloppée EXISTS, rend tout super rapide. La prochaine question logique est de savoir pourquoi la demande avec JOIN-ami et la sous-requête elle-même sont rapides individuellement, mais sont terriblement lentes ensemble ?

Déplacer la sous-requête vers le CTE : Si la requête est rapide en elle-même, nous pouvons simplement calculer d'abord 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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Mais c'était encore très lent.

Trouver le coupable

Pendant tout ce temps, une petite chose brillait devant mes yeux, que j'écartais constamment. Mais comme il ne restait plus rien, j'ai décidé de la regarder aussi. Je parle de && opérateur. Au revoir EXISTS juste des performances améliorées && était le seul facteur commun restant à toutes les versions de la requête lente.

En regardant documentation, on voit ça && utilisé lorsque vous devez trouver des éléments communs entre deux tableaux.

Dans la demande initiale, il s'agit de :

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 de modèles sur nos URL, puis trouvons l'intersection avec toutes les URL contenant des publications communes. C'est un peu déroutant car "urls" ici ne fait pas référence au tableau contenant toutes les URL, mais à la colonne "urls" du tableau. recording_data.

Avec des soupçons croissants concernant &&, j'ai essayé de trouver une confirmation pour eux dans le plan de requête généré EXPLAIN ANALYZE (J'avais déjà enregistré un plan, mais je suis généralement plus à l'aise pour expérimenter SQL que d'essayer de comprendre l'opacité des planificateurs de requêtes).

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

Il y avait plusieurs lignes de filtres uniquement de &&. Ce qui signifiait que cette opération était non seulement coûteuse, mais qu’elle était également réalisée plusieurs fois.

J'ai testé 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 était lente. Parce que le JOIN-s sont rapides et les sous-requêtes sont rapides, la seule chose qui restait était && opérateur.

Ce n'est qu'une opération clé. Nous devons toujours parcourir l’intégralité du tableau d’URL sous-jacent pour rechercher un modèle, et nous devons toujours trouver des intersections. Nous ne pouvons pas rechercher directement les enregistrements d'URL, car ce ne sont que des identifiants faisant référence à urls.

En route vers une solution

&& lent car les deux décors 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 définir une intersection dans Postgres sans utiliser &&, mais sans grand succès.

En fin de compte, nous avons décidé de résoudre le problème de manière isolée : donnez-moi tout urls lignes pour lesquelles l'URL correspond au modèle. 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, je viens d'utiliser une sous-requête et de développer recording_data.urls tableau afin que vous puissiez appliquer directement la condition dans WHERE.

La chose la plus importante ici est que && utilisé pour vérifier si une entrée donnée contient une URL correspondante. Si vous plissez un peu, vous pouvez voir que cette opération se déplace à travers les éléments d'un tableau (ou les lignes d'un tableau) et s'arrête lorsqu'une condition (correspondance) est remplie. Cela ne vous rappelle rien ? Ouais, EXISTS.

Depuis le recording_data.urls peut être référencé en dehors du contexte de la sous-requête, lorsque cela se produit, nous pouvons nous appuyer sur notre vieil ami EXISTS et enveloppez la sous-requête avec.

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

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

Et le délai final Time: 1898.717 ms C'est l'heure de fêter ça?!?

Pas si vite! Vous devez d'abord vérifier l'exactitude. J'étais extrêmement méfiant à propos EXISTS optimisation car elle modifie la logique pour qu'elle se termine plus tôt. Nous devons être sûrs que nous n'avons pas ajouté d'erreur non évidente à la demande.

Un simple test devait être exécuté count(*) sur des requêtes lentes et rapides pour un grand nombre d'ensembles de données différents. Ensuite, pour un petit sous-ensemble de données, j'ai vérifié manuellement que tous les résultats étaient corrects.

Tous les tests ont donné des résultats systématiquement positifs. Nous avons tout réparé !

Leçons apprises

De nombreuses leçons peuvent être tirées de cette histoire :

Les plans de requête ne racontent pas toute l'histoire, mais ils peuvent fournir des indices
Les principaux suspects ne sont pas toujours les vrais coupables
Les requêtes lentes peuvent être décomposées pour isoler les goulots d'étranglement
Toutes les optimisations ne sont pas de nature réductrice
l'utilisation de EXIST, lorsque cela est possible, peut conduire à des augmentations spectaculaires de la productivité

conclusion

Nous sommes passés d'un temps de requête d'environ 24 minutes à 2 secondes, soit une augmentation de performances assez significative ! Bien que cet article ait été publié en grand, toutes les expériences que nous avons réalisées se sont déroulées en une journée et on a estimé qu'elles prenaient entre 1,5 et 2 heures pour les optimisations et les tests.

SQL est un langage merveilleux si vous n’en avez pas peur, mais essayez de l’apprendre et de l’utiliser. En comprenant bien comment les requêtes SQL sont exécutées, comment la base de données génère des plans de requête, comment fonctionnent les index et simplement la taille des données que vous traitez, vous pouvez réussir à optimiser les requêtes. Il est cependant tout aussi important de continuer à essayer différentes approches et de résoudre progressivement le problème, en identifiant les goulots d’étranglement.

La meilleure partie de l'obtention de tels résultats est l'amélioration notable et visible de la vitesse - où un rapport qui auparavant ne se chargeait même pas se charge maintenant presque instantanément.

Merci spécial mes camarades aux ordres d'Aditya Mishra, Aditya Gauru и Varun Malhotra pour le brainstorming et Dinkar Pandir pour avoir trouvé une erreur importante dans notre demande finale avant de finalement lui dire au revoir !

Source: habr.com

L'histoire d'une enquête SQL