Einführung

PagerDuty hat Anfang 2014 Multi-User Alerting veröffentlicht, das die Benachrichtigung und Zuweisung mehrerer Personen bei Auslösung eines Vorfalls ermöglicht. Neben der Zuweisung mehrerer Benutzer zu einem Vorfall ermöglicht Multi-User Alerting auch, dass ein Vorfall mehrere Bestätiger hat. Dieser Beitrag zeigt die Änderungen, die wir an unserem Datenmodell vorgenommen haben, um Multi-User Alerting zu implementieren, und die daraus resultierende Verfeinerung unserer SQL-Abfragen, um ihre Leistung aufrechtzuerhalten.

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  :Benutzer bestätigen  hat_eins  :zugewiesener_Benutzer 

   # Zeitobjekte  bestätigt_am gelöst_am  Ende 

Nach der Einführung der Mehrbenutzeralarmierung 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:   :hergestellt in  hat viele  :Benutzer bestätigen  ,  durch:   :Danksagungen  ,  Quelle:   :Benutzer  hat viele  :Zuordnungen  ,  Klassenname:   :Vorfallzuweisung  ,  abhängig:   :zerstören  ,  Umkehrung von:   :Vorfall  hat viele  :den Benutzern zugewiesen  ,  durch:   :Zuordnungen  ,  Quelle:   :Benutzer 

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

Wo VorfälleBestätigung Und VorfälleZuweisung sind klassische Join-Tabellen:

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

 Klasse   VorfälleZuweisung  < ActiveRecord::Basis  gehört  :Vorfall  ,  Umkehrung von:   :Zuordnungen  gehört  :Benutzer 
 Ende 

Oberflächlich betrachtet scheint diese Art der Migration unkompliziert zu sein: 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, die PagerDuty durchführt, gehört das Auffinden aller Vorfälle für ein Konto mit einem bestimmten Status: ausgelöst, bestätigt oder gelöst. Wir besprechen, wie wir jede dieser Abfragen migriert haben, beginnend mit der einfachsten.

Behobene Vorfälle

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

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

Ausgelöste Vorfälle

Vor der Mehrbenutzeralarmierung war der ausgelöste Umfang:

 Umfang  :Ausgelöst  , ungelöst.wo(  bestätigt_am:   Null  ) 

Ein ausgelöster Vorfall war lediglich einer, bei dem beide bestätigt_at Und gelöst_am war Null . Bei Multi-User-Alarmierung kann ein Vorfall mehrere Bestätigungen haben, daher musste dieser Umfang 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? LINKS VERBINDEN 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 Anerkennungen enthält:

 # Vorfall-ID | Beschreibung | behoben 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  LINKE ÄUSSERE VERBINDUNG  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 

Der Ergebnissatz 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 Daraus ergibt sich die folgende, unkomplizierte Geltungsbereichsdefinition:

 Umfang  :Ausgelöst  , sindel_left_join(  :Danksagungen  ). Wo(  Vorfallbestätigungen:  {  Vorfall-ID:   Null  }). ungelöst 

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

   auf jeden   selbst.arel_join  (  Verband  ,  Verbindungstyp  ) Vorfall_t  =  arel_table Assoziationsmodell  =  über Assoziation nachdenken(Assoziation).klass Assoziation_t  =  Prädikat association_model.arel_table  =  Verein_t[  :Vorfall-ID  ].eq(Vorfall_t[  :Ausweis  ]) verbindet(Vorfall_t.beitreten(Assoziation_t, Verbindungstyp).auf(Prädikat).Verbindungsquellen)  Ende 

   auf jeden   selbst.arel_inner_join  (  Verband  ) arel_join(Assoziation,  Arel  ::  Knoten  ::  InnerJoin  )  Ende 

   auf jeden   selbst.arel_left_join  (  Verband  ) arel_join(Assoziation,  Arel  ::  Knoten  ::  Äußerer Join  )  Ende 

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

 WÄHLEN   `  Vorfälle.*  ` 
 AUS  Vorfälle  LINKE ÄUSSERE VERBINDUNG  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 bestand der Umfang bestätigter Vorfälle wie folgt:

 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 LINKS VERBINDEN mit acknowledgements.incident_id IST NICHT NULL können wir einfach ein altmodisches Innerer Join .

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

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

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

 WÄHLEN  Vorfälle.  * 
 AUS  Vorfälle  Innerer 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 

Der Ergebnisset 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 für jede 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 ist die Verwendung .uniq in Rails:

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

was sich in folgendes SQL übersetzen lässt:

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

Leider ist die obige Abfrage nicht sehr leistungsfähig. Bei Konten mit einer großen Anzahl bestätigter Vorfälle stellten wir fest, dass diese Abfrage zwei- bis dreimal langsamer war als die direkte (wenn auch falsche) Abfrage. Der Erläuterungsplan für die obige Abfrage zeigte Mit temporären , was darauf hinweist, dass MySQL eine temporäre Tabelle um die unterschiedlichen 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 in die temporäre Tabelle zur Deduplizierung.

Verwenden GRUPPIERE NACH

Bei dem Verdacht, dass die Verlangsamung durch das Kopieren aller Spalten verursacht wurde ( UNTERSCHIEDLICHE Vorfälle.* ), haben wir versucht, einen Weg zu finden, UNTERSCHEIDBAR nur auf einer Spalte ausführen. GRUPPIERE 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  Innerer Join  Danksagungen  An   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 WO  (gelöst_bei IS  NULL  )  GRUPPIERE NACH   Vorfall  .  Ausweis 

Bei Konten mit einer großen Anzahl bestätigter Vorfälle war diese Abfrage deutlich schneller als die Verwendung von UNTERSCHEIDBAR . Außerdem 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 Schienen.

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

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

GRUPPIERE 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, Anzahl der bestätigten Vorfälle ). Doch mit Gruppe , .zählen gibt die Anzahl zurück pro Gruppe , nicht der gesamt zählen. Mit anderen Worten,
anstatt eine Ganzzahl zurück zu bekommen:

 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 der Anruf zählen gibt eine Ganzzahl zurück, daher war eine Änderung dieses Verhaltens nicht wünschenswert. Das Umschließen der GRUPPIERE 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 dran und doch so fern.

Verwenden Sie eine zweite LINKS VERBINDEN

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 gemeinsamen Ergebnis führte.

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

 WÄHLEN  Vorfälle.  *   AUS  Vorfälle  Innerer Join  Danksagungen  AN   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 LINKE ÄUSSERE VERBINDUNG  Danksagungen  ALS  Anerkennungen_uniq  AN 
   Danksagungen  .  Vorfall-ID   =   Anerkennungen_uniq  .  Vorfall-ID   UND 
   Danksagungen  .  Ausweis   <   Anerkennungen_uniq  .  Ausweis 
 WO  (  Vorfälle  .  gelöst_am  IST  NULL  )  UND  (  Anerkennungen_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  ,  Wissen  .  Ausweis  ,  Anerkennungen_uniq  .  Ausweis   AUS  Vorfälle  Innerer Join  Danksagungen  AN   Danksagungen  .  Vorfall-ID   =   Vorfälle  .  Ausweis 
 LINKE ÄUSSERE VERBINDUNG  Danksagungen  ALS  Anerkennungen_uniq  AN 
   Danksagungen  .  Vorfall-ID   =   Anerkennungen_uniq  .  Vorfall-ID   UND 
   Danksagungen  .  Ausweis   <   Anerkennungen_uniq  .  Ausweis 
 WO  (  Vorfälle  .  gelöst_am  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 | 

Von dem MySQL-Handbuch , Die LINKE ÄUSSERE VERBINDUNG funktioniert auf der Grundlage, dass, wenn Anerkennungen.id ist auf seinem Höchstwert, es gibt keine Anerkennungen_uniq.id mit einem höheren Wert und der Anerkennungen_uniq Zeilenwerte werden NULL . Nick Kallen, Autor von Arel, nennt diese Technik „eine der großartigsten, umwerfendsten SQL-Abfragen“ .

Das Tolle an dieser Abfrage ist, dass sie genauso leistungsfähig ist wie die GRUPPIERE NACH Version und erfordert keine temporäre Tabelle. Darüber hinaus wirkt sich diese Abfrage nicht negativ auf zählen in Rails. Perfekt!

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

   auf jeden   left_join_as_distinct  (  Verband  ) Assoziationsmodell  =  reflect_on_association(association).klass-Tabelle  =  Association_model.arel_table table_alias  =  Tabelle.alias(  '  #{  Tabellenname  }  _einzigartig  '  ) Prädikat  =  Tisch[  :Vorfall-ID  ].eq(Tabellenalias[  :Vorfall-ID  ]) Prädikat  =  Prädikat.  Und  (Tisch[  :Ausweis  ].lt(Tabellenalias[  :Ausweis  ])) Quellen beitreten  =  Tabelle.join(Tabellenalias,  Arel  ::  Knoten  ::  Äußerer Join  ).on(Prädikat).join_sources verbindet(join_sources).where(Tabellenalias[  :Ausweis  ].eq(  Null  ))  Ende  Umfang  :anerkannt  , sindel_inner_join(  :Danksagungen  ).links_beitreten_als_unterschiedlich(  :Danksagungen  ). ungelöst 

Abschluss

Der Left Join-Operator von SQL erwies sich als wertvolles Werkzeug zum Schreiben korrekter und effizienter Abfragen für Multi-User-Alarme. Seine Verwendung war typisch für den Bereich der ausgelösten 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 bestätigter Vorfälle, wo es als effizienter Proxy für UNTERSCHEIDBAR um einzigartige Vorfälle zu finden mit mindestens ein Wissen.

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 Herausforderungen wie diese reizen, PagerDuty stellt ein .

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