Historique d'une seule enquête SQL

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

Contexte

Pour bien comprendre de quoi je parle, je vais vous parler un peu de VWO. Il s'agit d'une plate-forme avec laquelle vous pouvez exécuter diverses campagnes ciblées sur vos sites: mener des expériences A / B, suivre les visiteurs et les conversions, analyser les entonnoirs de vente, afficher les cartes thermiques et lire les enregistrements des visites.

Mais la chose la plus importante dans la plateforme est le reporting. Toutes les fonctions ci-dessus sont interconnectées. Et pour les entreprises clientes, un large éventail d'informations serait tout simplement inutile sans une plate-forme puissante les présentant sous la forme d'analyses.

En utilisant la plate-forme, vous pouvez effectuer une demande arbitraire sur un grand ensemble de données. Voici un exemple simple:

  Afficher tous les clics sur abc.com
 DU <date d1> AU <date d2>
 pour les personnes qui
 Chrome utilisé OU
 (étaient en Europe et utilisaient l'iPhone) 

Faites attention aux opérateurs booléens. Ils sont disponibles pour les clients dans l'interface de requête pour effectuer des requêtes arbitrairement complexes pour récupérer des échantillons.

Demande lente

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

  Afficher toutes les notes de session
 pour les utilisateurs visitant n'importe quelle page
 avec URL où il y a "/ jobs" 

Il y avait beaucoup de trafic sur ce site, et nous avons stocké plus d'un million d'URL uniques juste pour cela. Et ils voulaient trouver un modèle d'URL assez simple lié à leur modèle commercial.

Enquête préliminaire

Voyons ce qui se passe dans la base de données. Voici 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
 Délai: 1431924.650 ms 

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

Étudions davantage la requête. Comme vous pouvez le voir, il crée trois tables JOIN :

1. sessions : pour afficher les informations de session: navigateur, agent utilisateur, pays, etc.
2. Recording_data : URL enregistrées, pages, durée des visites
3. URL : pour éviter la duplication d'URL extrêmement volumineuses, nous les stockons dans une table séparée.

Notez également que toutes nos tables sont déjà divisées par account_id . Ainsi, une situation est exclue lorsque, en raison d'un compte particulièrement important, les autres ont des problèmes.

À la recherche de preuves

En y regardant de plus près, nous constatons que quelque chose dans une demande particulière n'est pas correct. Cela vaut la peine de regarder 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 en raison d' ILIKE dans toutes ces longues URL (nous avons plus de 1,4 million d'URL uniques collectées pour ce compte), les performances pourraient s'affaisser.

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. La recherche d'un modèle sur un million d'URL uniques n'est clairement pas un problème.

Le prochain suspect sur la liste est quelques JOIN . Peut-être que leur surutilisation a conduit à un ralentissement? JOIN généralement les candidats les plus évidents pour les 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. JOIN s'est avéré assez rapide.

Nous resserrons le cercle des suspects

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

• Utilisez EXISTS pour l'URL de la sous-requête : nous voulions vérifier à nouveau s'il y avait des problèmes avec la sous-requête des URL. Une façon d'y parvenir est d'utiliser simplement EXISTS . EXISTS peut grandement améliorer les performances car il se termine immédiatement dès qu'il trouve une seule ligne par 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 

Et bien oui. La sous-requête, lorsqu'elle est enveloppée dans EXISTS , rend tout super rapide. La prochaine question logique est pourquoi la requête avec JOINs et la sous-requête elle-même sont rapides individuellement, mais terriblement lentes ensemble?

• Nous déplaçons la sous-requête vers le CTE : si la demande est rapide par elle-même, nous pouvons simplement calculer le résultat rapide en premier, puis le fournir à la demande 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.

Trouvez le coupable

Pendant tout ce temps, une petite chose a clignoté devant mes yeux, dont j'ai constamment écarté. Mais comme il ne restait plus rien, j'ai décidé de la regarder. Je parle de l'opérateur && . Alors EXISTS améliorait simplement les performances, && était le seul facteur commun restant dans toutes les versions de la requête lente.

En regardant la documentation , nous voyons que && utilisé lorsque vous avez besoin de trouver des éléments communs entre deux tableaux.

Dans la demande d'origine, 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 faisons une recherche de modèle pour nos URL, puis nous trouvons l'intersection avec toutes les URL avec des enregistrements partagés. C'est un peu déroutant, car «urls» ici ne fait pas référence à une table contenant toutes les URL, mais à une colonne «urls» dans la table recording_data .

Alors que les soupçons de && , j'ai essayé de trouver une confirmation dans le plan de requête généré par EXPLAIN ANALYZE (j'avais déjà un plan enregistré, mais il est généralement plus pratique d'expérimenter SQL que d'essayer de comprendre l'opacité des planificateurs de requête).

 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 quelques lignes de filtres de && uniquement. Ce qui signifiait que cette opération était non seulement coûteuse, mais également effectué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 demande était lente. Comme les JOIN rapides et les sous-requêtes sont rapides, seul l'opérateur && reste.

Ce n'est qu'une opération clé. Nous devons toujours rechercher partout dans le tableau principal des URL pour rechercher par modèle, et nous devons toujours trouver des intersections. Nous ne pouvons pas rechercher directement les entrées d'URL, car ce ne sont que des identifiants qui pointent vers des urls .

Vers une solution

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

J'ai commencé à chercher un moyen de créer des intersections d'ensembles dans Postgres sans utiliser && , mais sans grand succès.

En fin de compte, nous avons décidé de résoudre simplement le problème de manière isolée: donnez-moi toutes les urls chaîne pour laquelle 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 la syntaxe JOIN , j'ai simplement utilisé une sous-requête et développé le tableau recording_data.urls afin que la condition puisse être directement appliquée à WHERE .

La chose la plus importante ici est que && utilisé pour vérifier si une entrée donnée contient une URL appropriée. En plissant les yeux, vous pouvez voir dans cette opération se déplacer à travers les éléments du tableau (ou des lignes du tableau) et s'arrêter lorsque la condition (correspondance) est remplie. Ça ne ressemble à rien? Ouais, EXISTS .

Étant donné que recording_data.urls peut être référencé de l'extérieur du contexte de la sous-requête lorsque cela se produit, nous pouvons revenir à notre vieil ami EXISTS et les envelopper avec une sous-requête.

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

 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 Time: 1898.717 ms exécution final Time: 1898.717 ms Il est temps de célébrer?!?

Pas si vite! Vous devez d'abord vérifier l'exactitude. J'étais extrêmement méfiant vis-à-vis de l'optimisation EXISTS , car elle change la logique à une fin antérieure. Nous devons être sûrs que nous n'avons pas ajouté une erreur non évidente à la demande.

Une vérification simple consistait à effectuer le count(*) sur les 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 l'exactitude de tous les résultats.

Tous les contrôles ont donné des résultats systématiquement positifs. Nous l'avons réparé!

Leçons apprises

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

1. Les plans de requête ne racontent pas toute l'histoire, mais peuvent donner des indices
2. Les principaux suspects ne sont pas toujours les vrais coupables
3. Les requêtes lentes peuvent être interrompues pour isoler les goulots d'étranglement
4. Toutes les optimisations ne sont pas de nature réductrice
5. Lorsque cela est possible, l'utilisation d' EXIST peut entraîner une forte augmentation de la productivité.

Conclusion

Nous sommes passés d'un temps de demande de ~ 24 minutes à 2 secondes - une augmentation très sérieuse des performances! Bien que cet article se soit avéré être volumineux, toutes les expériences que nous avons faites ont eu lieu le même jour, et selon les estimations, il a fallu de 1,5 à 2 heures pour les optimisations et les tests.

SQL est un langage merveilleux, s'il n'en a pas peur, mais essayez d'apprendre et d'utiliser. Ayant une bonne compréhension de la façon dont les requêtes SQL sont exécutées, de la façon dont la base de données génère des plans de requête, du fonctionnement des index et simplement de la taille des données que vous traitez, vous pouvez grandement réussir dans l'optimisation des requêtes. Il est tout aussi important, cependant, de continuer à essayer différentes approches et de résoudre lentement le problème, en trouvant des goulots d'étranglement.

La meilleure partie pour obtenir de tels résultats est une amélioration visible et sensible de la vitesse - lorsqu'un rapport qui n'avait même pas été téléchargé auparavant est maintenant chargé presque instantanément.

Un merci spécial à mes coéquipiers Aditya Misra , Aditya Gauru et 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!