- Mattermost nutzt Elasticsearch, um die Datenbanklast zu senken und deutlich schnellere Suchergebnisse zu liefern
- Damit Elasticsearch korrekt funktioniert, müssen alle durchsuchbaren Daten indexiert werden
- Für bereits indexierte Daten ist die spätere Indexierung neuer Beiträge und Dateien recht schnell
- Eine sehr große Datenbank (100 Millionen Posts) jedoch von Grund auf vollständig zu indexieren, ist extrem langsam (nach 18 Stunden nicht einmal zur Hälfte fertig und mit der Zeit immer langsamer)
- Anhand eines Diagramms der Laufzeit pro Datenbankaufruf wurde festgestellt, dass die SQL-Abfrage der Methode
PostStore.GetPostsBatchForIndexing das Problem ist
- Diese Abfrage sortiert Posts grundsätzlich nach dem Erstellungs-Timestamp und gibt die nächsten N Posts zurück, die neuer als ein vorgegebener Timestamp sind
- Der Indexierungsjob führt diese Abfrage wiederholt aus, bis alle Posts indexiert sind
- Mit
EXPLAIN (ANALYZE, BUFFERS) wurde der Ausführungsplan der Abfrage analysiert:
- Beim Index-Scan auf der Tabelle Posts wurden 40 Millionen Blöcke verarbeitet, um die Filterbedingung anzuwenden (309 GB)
- Der JOIN mit der Tabelle Channels war nicht das Problem
- Wenn aus der OR-Bedingung in der WHERE-Klausel nur
Posts.CreateAt > ?1 angewendet wird, wird die Abfrage deutlich schneller (30 ms)
- Fügt man dort die Bedingung
Posts.CreateAt = ?1 AND Posts.Id > ?2 hinzu, wird sie extrem schnell (0,047 ms)
- Ursachenanalyse:
- Die ursprüngliche Abfrage durchlief alle Zeilen von Posts und filterte sie anschließend heraus, während die geänderte Abfrage nur den Index prüft und nur die benötigten Zeilen entnimmt
- Dass die Abfrage mit der Zeit immer langsamer wurde, lag daran, dass immer mehr Zeilen herausgefiltert werden mussten
- Lösung:
- Nutzung der Row-Constructor-Comparison-Funktion von PostgreSQL und Änderung der Bedingung zu
(Posts.CreateAt, Posts.Id) > (?1, ?2)
- Mit dieser Änderung sank die Ausführungszeit der Abfrage drastisch auf 34 Millisekunden
- In MySQL lief die geänderte Abfrage jedoch im Gegenteil langsamer. Da dort die ursprüngliche Abfrage schneller war, wurde der Code so verzweigt, dass je nach Datenbank unterschiedliche Abfragen verwendet werden
- Erkenntnisse:
- Bei
EXPLAIN immer die Option BUFFERS verwenden
- Statt Filter möglichst
Index Cond nutzen
- Davon ausgehen, dass PostgreSQL und MySQL sich fast immer unterschiedlich verhalten
- Fazit
- Durch die Optimierung konnte die Ausführungszeit der Abfrage um mehr als das 1.000-Fache reduziert werden
- Diese Optimierung wurde in Mattermost v9.7.0 und v9.5 ESR übernommen
- Durch die Optimierungsarbeit konnten viele Erkenntnisse gewonnen werden
3 Kommentare
Wie auch im allerletzten Beitrag steht, wirkt der Titel dieses Artikels ein wenig wie Clickbait ... wenn man ihn etwas praxisnäher formulieren wollte, dann vielleicht
„Praxisbeispiel für den Einsatz von PostgreSQL, gelernt durch Fehler“
oder so ..
Hm … persönlich hätte ich bei einem Artikel auf diesem Niveau, der sich explizit auf ein bestimmtes Unternehmen/Produkt bezieht, eher deutlich weniger Vertrauen in dieses Produkt.
Die Struktur ist zwar klar und übersichtlich, aber es ist schade, dass der darin enthaltene technische Mehrwert etwas gering wirkt.
Nachdem ich diesen Beitrag gelesen habe, ist mein Vertrauen eher noch weiter gesunken. Es handelt sich schließlich um ein kostenpflichtig verkauftes Produkt, das eine Funktion ohne Tests zur Verarbeitung großer Datenmengen veröffentlicht hat. Ich finde, einen so einfachen Index hätte man nicht schon in der Phase der Funktionsentwicklung einrichten müssen? Es wirkt, als seien viele Schritte im Softwareentwicklungsprozess ausgelassen worden.