Die Microservices-Architektur hat, wie alles in dieser Welt, ihre Vor- und Nachteile. Einige Prozesse werden einfacher, andere komplexer. Um eine schnellere Veränderung und bessere Skalierbarkeit zu erreichen, müssen jedoch Opfer gebracht werden. Eines davon ist die Komplexität der Analytik. Während man im Monolithen alle operativen Analysen auf SQL-Abfragen an die analytische Replikation reduzieren kann, hat jeder Dienst in einer Multi-Service-Architektur seine eigene Datenbank, und es scheint, als könnte man nicht mit einer einzigen Abfrage auskommen (oder vielleicht doch?). Für diejenigen, die sich interessieren, wie wir das Problem der operativen Analytik in unserem Unternehmen gelöst haben und gelernt haben, mit dieser Lösung zu leben – willkommen.
Mein Name ist Pawel Siwasch, ich arbeite bei DomKlik in einem Team, das für die Pflege des analytischen Data Warehouses verantwortlich ist. Unsere Tätigkeit kann bedingt der Datenengineering zugeordnet werden, ist aber in Wirklichkeit viel vielfältiger. Neben den Standardaufgaben im Datenengineering wie ETL/ELT umfasst es die Unterstützung und Anpassung von Werkzeugen zur Datenanalyse sowie die Entwicklung eigener Werkzeuge. Insbesondere für die operative Berichterstattung haben wir beschlossen, so zu tun, als hätten wir einen Monolithen und den Analysten eine Datenbank zur Verfügung zu stellen, in der alle für sie notwendigen Daten enthalten sind.
Wir haben insgesamt verschiedene Optionen in Betracht gezogen. Man hätte ein vollständiges Speicherhaus aufbauen können — wir haben es sogar ausprobiert, aber um ehrlich zu sein, ist es uns nicht gelungen, genug häufige Änderungen in der Logik mit dem relativ langsamen Prozess des Aufbaus des Speichers und der Anpassung daran in Einklang zu bringen (wenn jemandem dies gelungen ist, schreibt bitte in die Kommentare, wie). Man hätte den Analysten sagen können: „Leute, lernt Python und arbeitet mit den analytischen Repliken“, aber das wäre ein zusätzliches Kriterium bei der Personalauswahl gewesen, und wir dachten, dass wir dies, wenn möglich, vermeiden sollten. Wir haben uns entschieden, die FDW (Foreign Data Wrapper) Technologie auszuprobieren: im Grunde ist das ein Standard-dblink, das im SQL-Standard vorhanden ist, jedoch mit einem viel benutzerfreundlicheren Interface. Auf dieser Grundlage haben wir eine Lösung entwickelt, die sich letztendlich bewährt hat, und auf der wir geblieben sind. Die Details dazu sind Thema eines separaten Artikels oder vielleicht sogar mehrerer, denn es gibt viel zu erzählen: von der Synchronisation der Datenbankschemas bis hin zum Zugriffsmanagement und der Anonymisierung persönlicher Daten. Es ist auch wichtig zu erwähnen, dass diese Lösung keine echte analytische Datenbanken und Lager ersetzt, sie löst lediglich eine spezifische Aufgabe.
Auf oberster Ebene sieht es so aus:
Es gibt eine PostgreSQL-Datenbank, in der Benutzer ihre Arbeitsdaten speichern können, und das Wichtigste ist, dass diese Datenbank über FDW mit den analytischen Repliken aller Dienste verbunden ist. Dies ermöglicht es, eine Abfrage an mehrere Datenbanken zu stellen, wobei es unerheblich ist, ob es sich um PostgreSQL, MySQL, MongoDB oder etwas anderes handelt (eine Datei, API; falls kein passender Wrapper vorhanden ist, kann man auch eigenen schreiben). So weit, so gut! Gehen wir auseinander?
Wenn alles so schnell und einfach enden würde, dann gäbe es wahrscheinlich diesen Artikel nicht.
Es ist wichtig, genau zu verstehen, wie PostgreSQL Anfragen an entfernte Server verarbeitet. Das erscheint logisch, allerdings wird oft nicht darauf geachtet: PostgreSQL zerlegt die Anfrage in Teile, die unabhängig auf den entfernten Servern ausgeführt werden, sammelt diese Daten und führt die finalen Berechnungen selbst durch. Daher hängt die Ausführungsgeschwindigkeit der Anfrage stark davon ab, wie sie formuliert ist. Man sollte auch beachten: Wenn die Daten von einem entfernten Server kommen, sind sie bereits nicht mehr indexiert und es gibt nichts, was dem Planer helfen könnte. Daher können nur wir ihm helfen und Anweisungen geben. Genau darüber möchte ich ausführlicher berichten.
Ein einfacher Abfrage- und Plan mit ihm
Um zu zeigen, wie PostgreSQL eine Abfrage auf einer Tabelle mit 6 Millionen Zeilen auf einem Remote-Server ausführt, Server, betrachten wir einen einfachen Plan.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table;
Aggregate (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 msDie Verwendung der Anweisung VERBOSE ermöglicht es, die Abfrage zu sehen, die an den Remote-Server gesendet wird, und die Ergebnisse, die wir zur weiteren Verarbeitung erhalten (Zeile RemoteSQL).
Gehen wir einen Schritt weiter und fügen wir unserer Abfrage einige Filter hinzu: einen nach boolean Feld, einen nach Auftreten timestamp im Intervall und einen nach jsonb.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';
Aggregate (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 5046843
Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 msHier liegt genau der Punkt, auf den man beim Schreiben von Abfragen achten muss. Die Filter wurden nicht an den Remote-Server übergeben, was bedeutet, dass PostgreSQL alle 6 Millionen Zeilen abruft, um sie dann lokal zu filtern (Zeile Filter) und eine Aggregation durchzuführen. Der Schlüssel zum Erfolg liegt darin, die Abfrage so zu formulieren, dass die Filter an die Remote-Maschine übergeben werden und wir nur die benötigten Zeilen erhalten und aggregieren.
Das ist ziemlicher Bullenmist
Bei boolean-Feldern ist alles einfach. Im ursprünglichen Abfrage trat das Problem aufgrund des Operators auf, is. Wenn wir ihn durch =ersetzen, erhalten wir folgendes Ergebnis:
erklären analysieren ausführlich
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 Monate'
AND CURRENT_DATE - INTERVAL '6 Monate'
AND meta->>'source' = 'test';
Aggregat (Kosten=508010.14..508010.15 Zeilen=1 Breite=8) (tatsächliche Zeit=19064.314..19064.314 Zeilen=1 Schleifen=1)
Ausgabe: count(1)
-> Fremdscan auf fdw_schema."table" (Kosten=100.00..507988.44 Zeilen=8679 Breite=0) (tatsächliche Zeit=33.035..18951.278 Zeilen=1360025 Schleifen=1)
Ausgabe: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('jetzt'::cstring)::date - '7 Monate'::interval)) AND ("table".created_dt <= ((('jetzt'::cstring)::date)::timestamp with time zone - '6 Monate'::interval)))
Zeilen durch Filter entfernt: 3567989
Remote-SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planungszeit: 0.834 ms
Ausführungszeit: 19064.534 msWie Sie sehen können, ist der Filter auf den entfernten Server verschoben, und die Ausführungszeit hat sich von 27 auf 19 Sekunden verkürzt.
Es ist erwähnenswert, dass der Operator is sich vom Operator = dadurch unterscheidet, dass er mit dem Wert Null arbeiten kann. Das bedeutet, dass is not True im Filter die Werte False und Null belässt, während != True nur die Werte False belässt. Daher sollten bei der Ersetzung des Operators is not zwei Bedingungen mit dem Operator OR im Filter übergeben werden, zum Beispiel, WHERE (col != True) OR (col is null).
Mit Boolean haben wir es geklärt, machen wir weiter. Und lassen Sie uns den Filter für den booleschen Wert in den ursprünglichen Zustand zurückversetzen, um die Auswirkungen anderer Änderungen unabhängig zu betrachten.
timestamptz? hz
Es kommt häufig vor, dass man experimentieren muss, wie man die Abfrage richtig schreibt, die entfernte Server betrifft, und erst dann nach Erklärungen sucht, warum dies so ist. Im Internet gibt es nur sehr wenig Informationen zu diesem Thema. So haben wir in Experimenten festgestellt, dass der Filter nach einem festen Datum problemlos auf den entfernten Server übertragen wird, aber wenn wir versuchen, das Datum dynamisch festzulegen, z. B. now() oder CURRENT_DATE, passiert das nicht. In unserem Beispiel haben wir einen solchen Filter hinzugefügt, damit die Spalte created_at Daten genau für 1 Monat in der Vergangenheit enthält (BETWEEN CURRENT_DATE - INTERVAL '7 Monate' AND CURRENT_DATE - INTERVAL '6 Monate'). Was haben wir in diesem Fall unternommen?
erkläre analysiere ausführlich
SELECT count(1)
FROM fdw_schema.tabelle
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 Monate')
AND created_dt >'source' = 'test';
Aggregate (cost=306875.17..306875.18 rows=1 width=8) (effective Zeit=4789.114..4789.115 rows=1 loops=1)
Ausgabe: count(1)
InitPlan 1 (gibt $0 zurück)
-> Ergebnis (cost=0.00..0.02 rows=1 width=8) (effective Zeit=0.007..0.008 rows=1 loops=1)
Ausgabe: ((('jetzt'::cstring)::date)::timestamp with Zeitstempel - '7 Monate'::intervall)
InitPlan 2 (gibt $1 zurück)
-> Ergebnis (cost=0.00..0.02 rows=1 width=8) (effective Zeit=0.002..0.002 rows=1 loops=1)
Ausgabe: ((('jetzt'::cstring)::date)::timestamp with Zeitstempel - '6 Monate'::intervall)
-> Ausländische Abfrage auf fdw_schema."tabelle" (cost=100.02..306874.86 rows=105 width=0) (effective Zeit=23.475..4681.419 rows=1360025 loops=1)
Ausgabe: "tabelle".id, "tabelle".is_active, "tabelle".meta, "tabelle".created_dt
Filter: (("tabelle".is_active IS TRUE) AND (("tabelle".meta ->> 'source'::text) = 'test'::text))
Vom Filter entfernte Zeilen: 76934
Remote SQL: SELECT is_active, meta FROM fdw_schema.tabelle WHERE ((created_dt >= $1::timestamp with Zeitstempel)) AND ((created_dt < $2::timestamp with Zeitstempel))
Planungszeit: 0.703 ms
Ausführungszeit: 4789.379 msWir haben dem Planer geraten, das Datum im Unterabfrage im Voraus zu berechnen und die bereits vorbereitete Variable in den Filter zu übergeben. Und diese Empfehlung hat uns ein hervorragendes Ergebnis gebracht, die Abfrage wurde fast sechs Mal schneller!
Auch hier ist es wichtig, aufmerksam zu sein: der Datentyp in der Unterabfrage muss der gleiche sein wie das Feld, nach dem wir filtern, andernfalls wird der Planer feststellen, dass die Typen unterschiedlich sind und es notwendig ist, zunächst alle Daten abzurufen und sie dann lokal zu filtern.
Lassen Sie den Datumsfilter auf den ursprünglichen Wert zurücksetzen.
Freddy vs. Jsonb
Im Grunde genommen haben die booleschen Felder und Daten bereits ausreiche Beschleunigung für unsere Abfrage gebracht, jedoch blieb noch ein weiterer Datentyp. Der Kampf mit der Filterung darüber ist ehrlich gesagt noch nicht zu Ende, obwohl es auch hier Fortschritte gibt. So haben wir es geschafft, den Filter zu übermitteln über jsonb das Feld auf den entfernten Server.
erkläre analysiere ausführlich
SELECT count(1)
FROM fdw_schema.tabelle
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 Monate'
AND CURRENT_DATE - INTERVAL '6 Monate'
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (cost=245463.60..245463.61 rows=1 width=8) (effective Zeit=6727.589..6727.590 rows=1 loops=1)
Ausgabe: count(1)
-> Ausländische Abfrage auf fdw_schema."tabelle" (cost=1100.00..245459.90 rows=1478 width=0) (effective Zeit=16.213..6634.794 rows=1360025 loops=1)
Ausgabe: "tabelle".id, "tabelle".is_active, "tabelle".meta, "tabelle".created_dt
Filter: (("tabelle".is_active IS TRUE) AND ("tabelle".created_dt >= (('jetzt'::cstring)::date - '7 Monate'::intervall)) AND ("tabelle".created_dt '{"source": "test"}'::jsonb))
Planungszeit: 0.747 ms
Ausführungszeit: 6727.815 msAnstelle von Filteroperatoren sollten Operatoren zur Existenzprüfung verwendet werden jsonb in einem anderen. 7 Sekunden statt ursprünglichen 29. Bis jetzt ist dies die einzige erfolgreiche Methode zur Übertragung von Filtern nach jsonb auf einen entfernten Server, aber hier ist ein wichtiges Einschränkungen zu beachten: wir verwenden die Version 9.6, planen aber bis Ende April die letzten Tests abzuschließen und auf Version 12 umzusteigen. Sobald wir aktualisiert haben, werden wir berichten, wie sich das ausgewirkt hat, denn es gibt viele vielversprechende Änderungen wie json_path, das neue Verhalten von CTE und Push Down (seit Version 10 vorhanden). Ich kann es kaum erwarten, das auszuprobieren.
Finish him
Wir haben überprüft, wie jede Änderung die Anfragegeschwindigkeit einzeln beeinflusst. Lassen Sie uns nun ansehen, was passiert, wenn alle drei Filter korrekt geschrieben sind.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month')
AND created_dt '{"source":"test"}'::jsonb;
Aggregate (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
Output: count(1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
-> Foreign Scan on fdw_schema."table" (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 msJa, die Anfrage sieht komplizierter aus, das ist der Preis, den man zahlen muss, aber die Ausführungszeit beträgt 2 Sekunden, was mehr als 10 Mal schneller ist! Und hierbei sprechen wir von einer einfachen Anfrage an einen relativ kleinen Datensatz. Bei realen Anfragen haben wir eine Steigerung von bis zu mehreren hundert Mal erzielt.
Fassen wir zusammen: Wenn Sie PostgreSQL mit FDW verwenden, überprüfen Sie immer, ob alle Filter an den entfernten Server gesendet werden, und Sie werden glücklich sein… zumindest bis Sie zu Joins zwischen Tabellen aus verschiedenen Server. Aber das ist schon die Geschichte für einen anderen Artikel.
Danke für Ihre Aufmerksamkeit! Ich freue mich auf Fragen, Kommentare und Geschichten über Ihre Erfahrungen in den Kommentaren.
Quelle: habr.com
