Einführung

PagerDuty veröffentlichte Anfang 2014 die Multi-User-Alarmierung, die es ermöglicht, bei einem Vorfall mehrere Personen zu benachrichtigen und zuzuweisen. Neben der Zuweisung mehrerer Benutzer zu einem Vorfall ermöglicht die Multi-User-Alarmierung auch die Bestätiger mehrerer Vorfälle. Dieser Beitrag zeigt die Änderungen an unserem Datenmodell zur Implementierung der Multi-User-Alarmierung und die daraus resultierenden Verbesserungen unserer SQL-Abfragen zur Aufrechterhaltung ihrer Leistung.

Datenmodellmigrationen

Die Logik und das Datenmodell hinter unserem Vorfallprozess sind Teil einer Rails-App. Vor der Migration sah unser Datenmodell folgendermaßen aus:

 Klasse   Vorfall  < ActiveRecord::Basis  hat_eins  :acknowledgening_user  hat_eins  :zugewiesener_Benutzer 

   # Zeitobjekte  bestätigt_um gelöst_um  Ende 

Nach der Einführung der Mehrbenutzer-Alarmierung sah das Datenmodell folgendermaßen aus:

 Klasse   Vorfall  < ActiveRecord::Basis  hat_viele  :Danksagungen  ,  Klassenname:   :Vorfallbestätigung  ,  abhängig:   :alles_löschen  ,  Umkehrung von:   :Vorfall  ,  Befehl:   :erstellt am  hat_viele  :acknowledging_users  ,  durch:   :Danksagungen  ,  Quelle:   :Benutzer  hat_viele  :Aufgaben  ,  Klassenname:   :Vorfallzuweisung  ,  abhängig:   :zerstören  ,  Umkehrung von:   :Vorfall  hat_viele  :zugewiesen_an_Benutzer  ,  durch:   :Aufgaben  ,  Quelle:   :Benutzer 

   # Zeitobjekt, acknowledged_at verschoben nach IncidentsAcknowledgement.created_at  gelöst_um  Ende 

Wo Vorfallbestätigung Und IncidentsAssignment sind klassische Join-Tabellen:

 Klasse   Vorfallbestätigung  < ActiveRecord::Basis  gehört zu  :Vorfall  ,  Umkehrung von:   :Danksagungen  gehört zu  :Benutzer 
 Ende 

 Klasse   IncidentsAssignment  < ActiveRecord::Basis  gehört zu  :Vorfall  ,  Umkehrung von:   :Aufgaben  gehört zu  :Benutzer 
 Ende 

Oberflächlich betrachtet scheint diese Art der Migration unkompliziert: Aus einer 1:1-Beziehung wird eine 1:n-Beziehung. Ein Kinderspiel, direkt aus dem Grundkurs Datenmodellierung, oder?

Nicht wirklich.

Zu den häufigsten Abfragen von PagerDuty gehört das Suchen aller Vorfälle für ein Konto mit einem bestimmten Status: ausgelöst, bestätigt oder gelöst. Wir erläutern, wie wir jede dieser Abfragen migriert haben, beginnend mit der einfachsten.

Behobene Vorfälle

Die Abfrage nach gelösten Incidents hat sich mit Multi-User Alerting nicht geändert. Ein Incident kann nur einmal gelöst werden, egal ob mit oder ohne Multi-User Alerting. Daraus ergeben sich folgende Bereiche:

 Umfang  :gelöst  , Wo.  nicht  (  gelöst_am:   Null  ) Umfang  :ungelöst  , Wo(  gelöst_am:   Null  ) 

Ausgelöste Vorfälle

Vor der Multi-User-Alarmierung war der ausgelöste Bereich:

 Umfang  :ausgelöst  , ungelöst.wo(  bestätigt am:   Null  ) 

Ein ausgelöster Vorfall war lediglich einer, bei dem beide bestätigt_um Und gelöst_um war Null . Bei der Mehrbenutzerwarnung kann ein Vorfall mehrere Bestätigungen haben, daher musste dieser Bereich geändert werden. Wir brauchten eine Abfrage, um ungelöste Vorfälle zu finden, die genau null Bestätigungen hatten.

Wie findet man in SQL heraus, ob eine Join-Tabelle genau null Assoziationen hat? LINKSVERBINDUNG Natürlich!

Zur Auffrischung: Ein Left Join garantiert, dass die Ergebnismenge enthält alle der Zeilen aus der Originaltabelle.

Betrachten Sie beispielsweise die folgenden beiden Vorfälle, von denen einer einige Bestätigungen enthält:

 # Vorfall-ID | Beschreibung | gelöst am ---------------------------------- 1 | CPU hoch | NULL 2 | Server brennt | NULL # Bestätigungs-ID | Vorfall-ID | Benutzer-ID ----------------------------- 100 | 2 | 9875 101 | 2 | 9876 

Betrachten Sie die folgende SQL-Abfrage, die Left Join verwendet:

 WÄHLEN   * 
 AUS  Vorfälle  LINKS ÄUSSERER JOIN  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 

Das Ergebnis der obigen Abfrage lautet:

 ID | Beschreibung | gelöst am | ID | Vorfall-ID | Benutzer-ID ----------------------------------------------------------------- 1 | CPU hoch | NULL | NULL | NULL | NULL 2 | Server brennt | NULL | 100 | 2 | 9875 2 | Server brennt | NULL | 101 | 2 | 9876 

Vorfall 1 (ohne Bestätigungen) ist der Vorfall, bei dem acknowledgements.incident_id IST NULL Dies führt zu der folgenden einfachen Definition des Geltungsbereichs:

 Umfang  :ausgelöst  , arel_left_join(  :Danksagungen  ). Wo(  incidents_acknowledgements:  {  Vorfall-ID:   Null  }). ungelöst 

Wo arel_left_join ist ein Helfer, der wie folgt definiert ist:

 def   self.arel_join  (  Verein  ,  Join-Typ  ) Vorfall_t  =  arel_table association_model  =  reflect_on_association(association).klass association_t  =  association_model.arel_table-Prädikat  =  Verein_t[  :Vorfall-ID  ].eq(Vorfall_t[  :Ausweis  ]) verbindet (Vorfall_t.beitreten (Assoziation_t, Beitrittstyp).auf (Prädikat).Beitrittsquellen)  Ende 

   def   self.arel_inner_join  (  Verein  ) arel_join(association,  Arel  ::  Knoten  ::  InnerJoin  )  Ende 

   def   self.arel_left_join  (  Verein  ) arel_join(association,  Arel  ::  Knoten  ::  Äußerer Join  )  Ende 

Dieser neue ausgelöste Bereich wird in das folgende SQL übersetzt

 WÄHLEN   `  Vorfälle.*  ` 
 AUS  Vorfälle  LINKS ÄUSSERER JOIN  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 WO  (  Danksagungen  .  Vorfall-ID  IST  NULL  )  UND  (gelöst_bei IS  NULL  ) 

Bestätigte Vorfälle

Vor der Multi-User-Alarmierung war der Umfang bestätigter Vorfälle:

 Umfang  :anerkannt  , ungelöst.wo(  '  acknowledged_at IST NICHT NULL  '  ) 

Die Erweiterung zur Unterstützung mehrerer Bestätigungen war ähnlich wie ausgelöst wurde erweitert. In diesem Fall suchen wir nach ungelösten Vorfällen mit mindestens einem Eintrag in der Bestätigungstabelle. Anstatt LINKSVERBINDUNG mit acknowledgements.incident_id IST NICHT NULL können wir einfach ein einfaches altes INNER JOIN .

Leider naiv mit INNER JOIN wie wir es mit ausgelösten Vorfällen getan haben, ist falsch. Zur Veranschaulichung betrachten Sie die folgende Implementierung von anerkannt :

 # Naiv und sehr kaputt  Umfang  :anerkannt  , arel_inner_join(  :Danksagungen  ). ungelöst 

Um zu sehen, warum dies falsch ist, betrachten Sie das generierte SQL:

 WÄHLEN  Vorfälle.  * 
 AUS  Vorfälle  INNER JOIN  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 WO  (gelöst_bei IS  NULL  ) 

sowie unsere Beispiel-Vorfalldaten von früher:

 # Vorfall-ID | Beschreibung | gelöst am ---------------------------------- 2 | Server brennt | NULL # Bestätigungs-ID | Vorfall-ID | Benutzer-ID ----------------------------- 100 | 2 | 9875 101 | 2 | 9876 

Das Ergebnis wird sein:

 ID | Beschreibung | gelöst am | ID | Vorfall-ID | Benutzer-ID ----------------------------------------------------------------- 2 | Server brennt | NULL | 100 | 2 | 9875 2 | Server brennt | NULL | 101 | 2 | 9876 

Im Ergebnissatz tritt jeder Vorfall einmal pro Bestätigung auf. Wir möchten jedoch, dass ein Vorfall genau einmal auftritt, wenn er mindestens eine Bestätigung hat. Daher benötigen wir eine Möglichkeit, diese doppelten Vorfälle zu entfernen. Es stellt sich heraus, dass effizient Das Entfernen von Duplikaten in SQL ist nicht so trivial, wie es scheint.

SQL UNTERSCHEIDBAR / Schienen einzigartig

Der einfachste Ansatz besteht darin, .uniq in Rails:

 # Naiv und sehr kaputt  Umfang  :anerkannt  , arel_inner_join(  :Danksagungen  ). ungelöst. einzigartig 

was sich in folgendes SQL übersetzen lässt:

 AUSWÄHLEN  Vorfälle.  * 
 AUS  Vorfälle  INNER JOIN  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 WO  (gelöst_bei IS  NULL  ) 

Leider ist die obige Abfrage nicht sehr performant. Bei Konten mit einer großen Anzahl bestätigter Vorfälle war diese Abfrage zwei- bis dreimal langsamer als die einfache (wenn auch fehlerhafte) Abfrage. Der Erklärungsplan für die obige Abfrage zeigte Mit temporären , was darauf hinweist, dass MySQL eine temporäre Tabelle um die einzelnen Zeilen zu berechnen. Wir vermuten, dass ein großer Teil der Verlangsamung auf MySQL-Kopiervorgänge zurückzuführen ist. alle der Spalten jeder Zeile zur Deduplizierung in die temporäre Tabelle.

Verwenden GRUPPE NACH

Da wir vermuten, dass die Verlangsamung durch das Kopieren aller Spalten verursacht wurde ( VERSCHIEDENE Vorfälle.* ), haben wir versucht, einen Weg zu finden, UNTERSCHEIDBAR auf nur einer Spalte ausgeführt. GRUPPE NACH kann genau für diesen Zweck verwendet werden, daher haben wir als Ausgangspunkt die folgende Abfrage versucht:

 WÄHLEN  Vorfälle.  * 
 AUS  Vorfälle  INNER JOIN  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 WO  (gelöst_bei IS  NULL  )  GRUPPE NACH   Vorfall  .  Ausweis 

Bei Konten mit einer großen Anzahl bestätigter Vorfälle war diese Abfrage deutlich schneller als die Verwendung von UNTERSCHEIDBAR . Darüber hinaus enthielt der EXPLAIN-Plan für diese Query keine Mit temporären Leider gab es auch bei dieser Abfrage ein Problem aufgrund der Implementierung von .zählen in Rails.

Der Rails-Bereich, der die vorherige Abfrage generiert hat, ist:

 # Das funktioniert, macht aber andere Dinge kaputt  Umfang  :anerkannt  , arel_inner_join(  :Danksagungen  ). ungelöst. Gruppe(  '  Vorfall-ID  '  ) 

GRUPPE NACH beeinflusst die Ergebnisse von Aggregatfunktionen, insbesondere ZÄHLEN Um in Rails die Gesamtzahl der Vorfälle aus einem Bereich zu zählen, verwenden Sie zählen (z.B, Incident.acknowledged.count ). Allerdings mit Gruppe , .zählen gibt die Anzahl zurück pro Gruppe , nicht die gesamt zählen. Mit anderen Worten,
anstatt eine Ganzzahl zurückzugeben:

 Vorfall  .acknowledged.count =>  7 

Der Rückgabewert ist eine Karte der Vorfall-ID, die für diesen Vorfall gezählt werden soll.

 Vorfall  .acknowledged.count => {  19  =>  1  ,  20  =>  1  ,  21  =>  1  } 

Viele Stellen in unserer App gehen davon aus, dass das Anrufen zählen gibt eine Ganzzahl zurück, daher war eine Änderung dieses Verhaltens nicht wünschenswert. Das Umschließen der GRUPPE NACH Abfrage in einer eigenen Unterabfrage stellte das korrekte Verhalten von zählen , aber das machte die Leistungssteigerung zunichte, da wieder eine temporäre Tabelle erforderlich war. So nah und doch so fern.

Verwenden Sie eine zweite LINKSVERBINDUNG

Der Kern des anfänglichen Problems des Beitritts Danksagungen direkt gegen Vorfälle war, dass ein einzelner Vorfall mehrere Bestätigungen haben konnte, was zu doppelten Vorfällen im zusammengeführten Ergebnis führte.

Was wäre, wenn es eine Möglichkeit gäbe, für jeden Vorfall eine einzelne beispielhafte Bestätigung auszuwählen? Das MySQL-Handbuch beschreibt eine Technik mit LINKSVERBINDUNG für die Suche nach gruppenweises Maximum einer bestimmten Spalte . Mit diesem Trick können wir in unserer Abfrage die Anerkennung mit der maximalen ID für einen bestimmten Vorfall. Wir gelangen schließlich zu folgender Abfrage:

 WÄHLEN  Vorfälle.  *   AUS  Vorfälle  INNER JOIN  Danksagungen  AN   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 LINKS ÄUSSERER JOIN  Danksagungen  ALS  acknowledgements_uniq  AN 
   Danksagungen  .  Vorfall-ID   =   acknowledgements_uniq  .  Vorfall-ID   UND 
   Danksagungen  .  Ausweis   <   acknowledgements_uniq  .  Ausweis 
 WO  (  Vorfälle  .  gelöst_um  IST  NULL  )  UND  (  acknowledgements_uniq  .  Ausweis  IST  NULL  ) 

Um zu veranschaulichen, warum dies funktioniert, betrachten Sie den folgenden Vorfall mit vier Bestätigungen:

 # Vorfall-ID | Beschreibung | gelöst am ---------------------------------- 3 | Server Inferno | NULL # Bestätigungs-ID | Vorfall-ID | Benutzer-ID ----------------------------- 200 | 3 | 9875 201 | 3 | 9876 202 | 3 | 9874 203 | 3 | 9873 

und die folgende Abfrage ohne die acknowledgements_uniq.id IST NULL Klausel:

 WÄHLEN   Vorfall  .  Ausweis  ,  Anerkennung  .  Ausweis  ,  acknowledgements_uniq  .  Ausweis   AUS  Vorfälle  INNER JOIN  Danksagungen  AN   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 LINKS ÄUSSERER JOIN  Danksagungen  ALS  acknowledgements_uniq  AN 
   Danksagungen  .  Vorfall-ID   =   acknowledgements_uniq  .  Vorfall-ID   UND 
   Danksagungen  .  Ausweis   <   acknowledgements_uniq  .  Ausweis 
 WO  (  Vorfälle  .  gelöst_um  IST  NULL  ) 

Das Ergebnis ist:

 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 | 

Aus dem MySQL-Handbuch , Die LINKS ÄUSSERER JOIN funktioniert auf der Grundlage, dass, wenn Anerkennungen.id ist auf seinem Maximalwert, es gibt keine acknowledgements_uniq.id mit einem höheren Wert und der acknowledgements_uniq Zeilenwerte werden NULL . Nick Kallen, Autor von Arel, nennt diese Technik „eine der großartigsten und umwerfendsten SQL-Abfragen“ .

Das Tolle an dieser Abfrage ist, dass sie genauso performant ist wie die GRUPPE NACH Version und erfordert keine temporäre Tabelle. Darüber hinaus beeinträchtigt diese Abfrage nicht zählen in Rails. Perfekt!

Der endgültige Spielraum für Vorfall bestätigt Ist:

 def   left_join_as_distinct  (  Verein  ) Assoziationsmodell  =  reflect_on_association(association).klass-Tabelle  =  association_model.arel_table Tabellenalias  =  Tabelle.alias(  '  #{  Tabellenname  }  _einzigartig  '  ) Prädikat  =  Tisch[  :Vorfall-ID  ].eq(Tabellenalias[  :Vorfall-ID  ]) Prädikat  =  Prädikat.  Und  (Tisch[  :Ausweis  ].lt(Tabellenalias[  :Ausweis  ])) join_sources  =  Tabelle.Join(Tabellenalias,  Arel  ::  Knoten  ::  Äußerer Join  ).on(Prädikat).join_sources verbindet(join_sources).where(Tabellenalias[  :Ausweis  ].eq(  Null  ))  Ende  Umfang  :anerkannt  , arel_inner_join(  :Danksagungen  ). left_join_as_distinct(  :Danksagungen  ). ungelöst 

Abschluss

Der Left Join-Operator von SQL erwies sich als wertvolles Werkzeug zum Schreiben korrekter und effizienter Abfragen für Mehrbenutzer-Alarme. Seine Verwendung war typisch für den Bereich ausgelöster Vorfälle, bei dem es darum ging, Vorfälle mit null Bestätigungen. Left Join erwies sich zusätzlich als vielseitig für den Bereich der bestätigten Vorfälle, wo es als effizienter Proxy für UNTERSCHEIDBAR um einzigartige Vorfälle zu finden mit mindestens eine Anerkennung.

Das Ableiten effizienter Abfragen umfasste das Profilieren der ursprünglichen SQL-Abfragen, das Verstehen des Verhaltens des Abfrageoptimierers von Percona und das Experimentieren mit alternativen Abfragen. Wenn Sie solche Herausforderungen reizen, PagerDuty stellt ein .

Vielen Dank an Steve Rice und Evan Gilman für die Überprüfung der Entwürfe dieses Artikels.