Introduction

PagerDuty a publié l'alerte multi-utilisateur début 2014, qui permettait de notifier et d'affecter plusieurs personnes lorsqu'un incident est déclenché. En plus d'affecter plusieurs utilisateurs à un incident, les alertes multi-utilisateurs permettent également à un incident d'avoir plusieurs accusés de réception. Cet article démontrera les modifications que nous avons apportées à notre modèle de données pour implémenter des alertes multi-utilisateurs et la sophistication qui en résulte ajoutée à nos requêtes SQL pour maintenir leurs performances.

Migrations de modèles de données

La logique et le modèle de données qui sous-tendent notre processus d'incident existent dans le cadre d'une application Rails. Avant la migration, notre modèle de données ressemblait à ceci :

 classe   Incident  < ActiveRecord::Base  en a un  :accusant_de_reconnaissance_utilisateur  en a un  :utilisateur_assigné 

   # Objets temporels  reconnu_à résolu_à  fin 

Après le déploiement des alertes multi-utilisateurs, le modèle de données ressemblait à ceci :

 classe   Incident  < ActiveRecord::Base  a beaucoup  :remerciements  ,  nom du cours:   :IncidentsRemerciements  ,  dépendant:   :supprimer_tout  ,  inverse_de:   :incident  ,  commande:   :créé à  a beaucoup  :accusant_de_reconnaissance_des_utilisateurs  ,  à travers:   :remerciements  ,  source:   :utilisateur  a beaucoup  :affectations  ,  nom du cours:   :Devoirs sur les incidents  ,  dépendant:   :détruire  ,  inverse_de:   :incident  a beaucoup  :assigné_aux_utilisateurs  ,  à travers:   :affectations  ,  source:   :utilisateur 

   # Objet de temps, acknowledge_at déplacé vers IncidentsAcknowledgement.created_at  résolu_à  fin 

où IncidentsReconnaissance et Affectation des incidents sont des tables de jointure classiques :

 classe   IncidentsReconnaissance  < ActiveRecord::Base  appartient à  :incident  ,  inverse_de:   :remerciements  appartient à  :utilisateur 
 fin 

 classe   Affectation des incidents  < ActiveRecord::Base  appartient à  :incident  ,  inverse_de:   :affectations  appartient à  :utilisateur 
 fin 

À première vue, ce type de migration semble simple : une relation 1:1 devient une relation 1:plusieurs. Une véritable promenade de santé, tout droit sortie du cours de modélisation des données 101, n'est-ce pas ?

Pas vraiment.

Certaines des requêtes les plus courantes effectuées par PagerDuty nécessitent de rechercher tous les incidents d'un compte avec un état donné : déclenché, reconnu ou résolu. Nous verrons comment nous avons migré chacune de ces requêtes, en commençant par la plus simple.

Incidents résolus

La requête pour les incidents résolus n'a pas changé du tout avec l'alerte multi-utilisateur. Un incident ne peut être résolu qu'une seule fois, avec ou sans alerte multi-utilisateur. Cela conduit aux portées suivantes :

 portée  :résolu  , où.  pas  (  résolu_à:   néant  ) portée  :non résolu  , où(  résolu_à:   néant  ) 

Incidents déclenchés

Avant l'alerte multi-utilisateur, la portée déclenchée était :

 portée  :déclenché  , non résolu.où(  reconnu_à:   néant  ) 

Un incident déclenché était simplement un incident où les deux reconnu_à et résolu_à étaient néant . Dans le cadre des alertes multi-utilisateurs, un incident peut avoir plusieurs accusés de réception. Il était donc nécessaire de modifier cette portée. Nous avions besoin d'une requête pour trouver les incidents non résolus qui n'avaient exactement aucun accusé de réception.

En SQL, comment savoir si une table de jointure a exactement zéro association ? JOINT GAUCHE bien sûr!

Pour rappel, une jointure gauche garantit que l'ensemble de résultats contient tous des lignes de la table d'origine.

Par exemple, considérons les deux incidents suivants, pour lesquels on a quelques remerciements :

 # Incidents id | description | resolve_at ---------------------------------- 1 | CPU high | NULL 2 | Serveur en feu | NULL # Remerciements id | incident_id | user_id ----------------------------- 100 | 2 | 9875 101 | 2 | 9876 

Considérez la requête SQL suivante qui utilise une jointure gauche :

 SÉLECTIONNER   * 
 DEPUIS  incidents  JOINTURE EXTERNE GAUCHE  Remerciements  sur   Remerciements  .  incident_id   =   incidents  .  identifiant 

Le résultat de la requête ci-dessus est :

 id | description | résolu_à | id | incident_id | user_id ----------------------------------------------------------------- 1 | CPU élevé | NULL | NULL | NULL | NULL 2 | Serveur en feu | NULL | 100 | 2 | 9875 2 | Serveur en feu | NULL | 101 | 2 | 9876 

L'incident 1 (avec zéro accusé de réception) est celui où Remerciements.incident_id est NULL . Cela conduit à la définition simple de la portée suivante :

 portée  :déclenché  , arel_left_join(  :remerciements  ). où(  incidents_remerciements :  {  incident_id:   néant  }). non résolu 

où arel_left_join est un assistant défini comme :

   déf   self.arel_join  (  association  ,  type_de_jointure  ) incident_t  =  modèle_association arel_table  =  réfléchir_sur_association(association).klass association_t  =  prédicat association_model.arel_table  =  association_t[  :incident_id  ].eq(incident_t[  :identifiant  ]) rejoint(incident_t.join(association_t, join_type).on(prédicat).join_sources)  fin 

   déf   auto-jointure.arel_inner_join  (  association  ) arel_join(association,  Arel  ::  Nœuds  ::  Jointure interne  )  fin 

   déf   self.arel_left_join  (  association  ) arel_join(association,  Arel  ::  Nœuds  ::  Jointure externe  )  fin 

Cette nouvelle portée déclenchée se traduit par le SQL suivant

 SÉLECTIONNER   `  incidents.*  ` 
 DEPUIS  incidents  JOINTURE EXTERNE GAUCHE  Remerciements  sur   Remerciements  .  incident_id   =   incidents  .  identifiant 
 OÙ  (  Remerciements  .  incident_id  EST  NUL  )  ET  (résolu_at est  NUL  ) 

Incidents reconnus

Avant l'alerte multi-utilisateur, la portée des incidents reconnus était :

 portée  :reconnu  , non résolu.où(  '  acknowledge_at N'EST PAS NULL  '  ) 

L'extension de cette fonction pour prendre en charge plusieurs accusés de réception était similaire à la façon dont déclenché a été étendu. Dans ce cas, nous recherchons des incidents non résolus avec au moins une entrée dans la table des accusés de réception. Au lieu d'utiliser JOINT GAUCHE avec Remerciements.incident_id N'EST PAS NULL , nous pouvons simplement utiliser un simple vieux JOINTURE INTERNE .

Malheureusement, en utilisant naïvement JOINTURE INTERNE comme nous l'avons fait avec les incidents déclenchés est incorrect. Pour illustrer, considérons l'implémentation suivante de reconnu :

   # Naïf et très brisé  portée  :reconnu  , arel_inner_join(  :remerciements  ). non résolu 

Pour comprendre pourquoi cela est incorrect, considérez le SQL généré :

 SÉLECTIONNER  incidents.  * 
 DEPUIS  incidents  JOINTURE INTERNE  Remerciements  sur   Remerciements  .  incident_id   =   incidents  .  identifiant 
 OÙ  (résolu_at est  NUL  ) 

ainsi que nos exemples de données d’incidents antérieurs :

 # Incidents id | description | resolve_at ---------------------------------- 2 | Serveur en feu | NULL # Remerciements id | incident_id | user_id ----------------------------- 100 | 2 | 9875 101 | 2 | 9876 

Le résultat sera :

 id | description | resolve_at | id | incident_id | user_id ----------------------------------------------------------------- 2 | Serveur en feu | NULL | 100 | 2 | 9875 2 | Serveur en feu | NULL | 101 | 2 | 9876 

L'ensemble de résultats contient chaque incident survenant une fois pour chaque accusé de réception. Cependant, nous voulons qu'un incident se produise exactement une fois s'il a au moins un accusé de réception, nous avons donc besoin d'un moyen de supprimer ces incidents en double. Il s'avère que efficacement Supprimer les doublons dans SQL n'est pas aussi simple qu'il y paraît.

SQL DISTINCT / Des rails unique

L’approche la plus simple consiste à utiliser .uniq dans Rails :

   # Naïf et très brisé  portée  :reconnu  , arel_inner_join(  :remerciements  ). non résolu. uniq 

ce qui se traduit par le SQL suivant :

 SÉLECTIONNEZ DISTINCT  incidents.  * 
 DEPUIS  incidents  JOINTURE INTERNE  Remerciements  sur   Remerciements  .  incident_id   =   incidents  .  identifiant 
 OÙ  (résolu_at est  NUL  ) 

Malheureusement, la requête ci-dessus n'est pas très performante. Pour les comptes avec un grand nombre d'incidents reconnus, nous avons constaté que cette requête était 2 à 3 fois plus lente que la requête simple (bien qu'incorrecte). Le plan d'explication de la requête ci-dessus a montré Utilisation temporaire , ce qui indique que MySQL utilisait un table temporaire pour calculer les lignes distinctes. Nous soupçonnons qu'une grande partie du ralentissement est basée sur la copie MySQL tous des colonnes de chaque ligne vers la table temporaire pour la déduplication.

Utiliser PAR GROUPE

En soupçonnant que le ralentissement était causé par la copie de toutes les colonnes ( Incidents DISTINCTS.* ), nous avons essayé de trouver un moyen d'avoir DISTINCT fonctionner sur une seule colonne. PAR GROUPE peut être utilisé exactement à cette fin, nous avons donc tenté la requête suivante comme point de départ :

 SÉLECTIONNER  incidents.  * 
 DEPUIS  incidents  JOINTURE INTERNE  Remerciements  sur   Remerciements  .  incident_id   =   incidents  .  identifiant 
 OÙ  (résolu_at est  NUL  )  PAR GROUPE   incident  .  identifiant 

Pour les comptes avec un grand nombre d'incidents reconnus, cette requête était nettement plus rapide que l'utilisation de DISTINCT . De plus, le plan d'explication de cette requête ne contenait pas Utilisation temporaire . Malheureusement, cette requête avait également un problème en raison de la mise en œuvre de .compter dans Rails.

La portée Rails qui a généré la requête précédente est :

   # Cela fonctionne, mais casse d'autres choses  portée  :reconnu  , arel_inner_join(  :remerciements  ). non résolu. groupe(  '  incident.id  '  ) 

PAR GROUPE affecte les résultats des fonctions d'agrégation, en particulier COMPTER . Dans Rails, pour compter le nombre total d'incidents d'un périmètre, vous utiliserez compter (par exemple, Incident.reconnu.nombre ). Cependant, avec groupe , .compter renvoie le nombre par groupe , pas le total compter. En d'autres termes,
au lieu d'obtenir un entier renvoyé :

 Incident  .reconnu.compte =>  7 

la valeur de retour est une carte d'identifiant d'incident à compter pour cet incident

 Incident  .accusé de réception.compte => {  19  =>  1  ,  20  =>  1  ,  21  =>  1  } 

De nombreux endroits dans notre application supposent que l'appel compter renvoie un entier, il n'était donc pas souhaitable de modifier ce comportement. PAR GROUPE la requête dans sa propre sous-requête a restauré le comportement correct de compter , mais cela a tué les gains de performances puisqu'une table temporaire était à nouveau nécessaire. Si proche, et pourtant si loin.

Utilisez une seconde JOINT GAUCHE

Le nœud du problème initial de l’adhésion Remerciements directement contre incidents était qu'un seul incident pouvait avoir plusieurs accusés de réception, ce qui entraînait des incidents en double dans le résultat joint.

Et s'il existait un moyen de sélectionner un seul exemple d'accusé de réception pour chaque incident ? Le manuel MySQL décrit une technique utilisant JOINT GAUCHE pour trouver le maximum par groupe d'une certaine colonne . En utilisant cette astuce avec notre requête, nous pouvons trouver le reconnaissance avec l'identifiant max pour un incident donné. On arrive finalement à la requête suivante :

 SÉLECTIONNER  incidents.  *   DEPUIS  incidents  JOINTURE INTERNE  Remerciements  SUR   Remerciements  .  incident_id   =   incidents  .  identifiant 
 JOINTURE EXTERNE GAUCHE  Remerciements  COMME  Remerciements_uniq  SUR 
   Remerciements  .  incident_id   =   Remerciements_uniq  .  incident_id   ET 
   Remerciements  .  identifiant   <   Remerciements_uniq  .  identifiant 
 OÙ  (  incidents  .  résolu_à  EST  NUL  )  ET  (  Remerciements_uniq  .  identifiant  EST  NUL  ) 

Pour illustrer pourquoi cela fonctionne, considérons l’incident suivant avec quatre remerciements :

 # Incidents id | description | resolve_at ---------------------------------- 3 | Server inferno | NULL # Remerciements id | incident_id | user_id ----------------------------- 200 | 3 | 9875 201 | 3 | 9876 202 | 3 | 9874 203 | 3 | 9873 

et la requête suivante sans le acknowledgements_uniq.id est nul clause:

 SÉLECTIONNER   incident  .  identifiant  ,  reconnaissance  .  identifiant  ,  Remerciements_uniq  .  identifiant   DEPUIS  incidents  JOINTURE INTERNE  Remerciements  SUR   Remerciements  .  incident_id   =   incidents  .  identifiant 
 JOINTURE EXTERNE GAUCHE  Remerciements  COMME  Remerciements_uniq  SUR 
   Remerciements  .  incident_id   =   Remerciements_uniq  .  incident_id   ET 
   Remerciements  .  identifiant   <   Remerciements_uniq  .  identifiant 
 OÙ  (  incidents  .  résolu_à  EST  NUL  ) 

Le résultat est:

 incident.id | acknowledgement.id | acknowledgements_uniq.id | ------------------------------------------------------------- 3 | 200 | 201 | 3 | 200 | 202 | 3 | 200 | 203 | 3 | 201 | 202 | 3 | 201 | 203 | 3 | 202 | 203 | 3 | 203 | NULL | 

Du Manuel MySQL , le JOINTURE EXTERNE GAUCHE fonctionne sur la base que lorsque Remerciements.id est à sa valeur maximale, il n'y a pas Remerciements_uniq.id avec une plus grande valeur et la Remerciements_uniq les valeurs des lignes seront NUL . Nick Kallen, auteur d'Arel, appelle cette technique « l’une des requêtes SQL les plus époustouflantes » .

L'avantage de cette requête est qu'elle est tout aussi performante que la PAR GROUPE version, et ne nécessite pas l'utilisation d'une table temporaire. De plus, cette requête n'affecte pas négativement compter dans Rails. Parfait !

La portée finale pour Incident reconnu est:

   déf   gauche_join_as_distinct  (  association  ) modèle_association  =  tableau de classes  =  association_model.arel_table table_alias  =  table.alias(  '  #{  nom de la table  }  _uniq  '  ) prédicat  =  tableau[  :incident_id  ].eq(alias_tableau[  :incident_id  ]) prédicat  =  prédicat.  et  (tableau[  :identifiant  ].lt(alias_tableau[  :identifiant  ])) join_sources  =  table.join(alias_table,  Arel  ::  Nœuds  ::  Jointure externe  ).on(prédicat).join_sources joins(join_sources).where(table_alias[  :identifiant  ].eq(  néant  ))  fin  portée  :reconnu  , arel_inner_join(  :remerciements  ). left_join_as_distinct(  :remerciements  ). non résolu 

Conclusion

L'opérateur de jointure gauche de SQL s'est avéré être un outil précieux pour écrire des requêtes correctes et efficaces pour les alertes multi-utilisateurs. Son utilisation était typique pour le périmètre des incidents déclenchés, qui impliquait la recherche d'incidents avec zéro reconnaissances. La jointure gauche s'est également avérée polyvalente pour la portée des incidents reconnus, où elle a été utilisée comme un proxy efficace pour DISTINCT pour trouver des incidents uniques avec au moins un reconnaissance.

Pour obtenir des requêtes efficaces, il fallait profiler les requêtes SQL d'origine, comprendre le comportement de l'optimiseur de requêtes de Percona et expérimenter des requêtes alternatives. Si des problèmes comme ceux-ci vous intéressent, PagerDuty recrute .

Merci à Steve Rice et Evan Gilman pour avoir révisé les versions préliminaires de cet article.