Microservice-Architektur hat, wie alles auf dieser Welt, ihre Vor- und Nachteile. Manche Prozesse werden dadurch einfacher, andere schwieriger. Und um Veränderungen schneller und besser skalierbar zu machen, müssen Sie Opfer bringen. Einer davon ist die zunehmende Komplexität der Analytik. Wenn in einem Monolithen alle Betriebsanalysen auf SQL-Abfragen an ein analytisches Replikat reduziert werden können, verfügt in einer Multiservice-Architektur jeder Dienst über eine eigene Datenbank, und es scheint, dass eine einzige Abfrage nicht durchgeführt werden kann (oder vielleicht doch?). Wer sich dafür interessiert, wie wir das Problem der Operational Analytics in unserem Unternehmen gelöst haben und wie wir gelernt haben, mit dieser Lösung zu leben, ist herzlich willkommen.

Mein Name ist Pavel Sivash, bei DomClick arbeite ich in einem Team, das für die Pflege des analytischen Data Warehouse verantwortlich ist. Herkömmlicherweise können unsere Aktivitäten dem Data Engineering zugeordnet werden, tatsächlich ist das Aufgabenspektrum jedoch viel breiter. Es gibt ETL/ELT-Standards für Data Engineering, Unterstützung und Anpassung von Tools zur Datenanalyse und Entwicklung eigener Tools. Insbesondere für die operative Berichterstattung haben wir uns entschieden, so zu tun, als hätten wir einen Monolithen, und den Analysten eine Datenbank zur Verfügung zu stellen, die alle benötigten Daten enthält.

Im Allgemeinen haben wir verschiedene Optionen in Betracht gezogen. Es war möglich, ein vollwertiges Repository zu erstellen – wir haben es sogar versucht, aber um ehrlich zu sein, konnten wir ziemlich häufige Änderungen in der Logik nicht mit dem eher langsamen Prozess des Aufbaus eines Repositorys und der Vornahme von Änderungen daran kombinieren (falls es jemandem gelingen sollte). , schreibt in die Kommentare wie). Man konnte den Analysten sagen: „Leute, lernt Python und geht zu analytischen Nachbildungen“, aber dies ist eine zusätzliche Anforderung für die Rekrutierung, und es schien, dass dies nach Möglichkeit vermieden werden sollte. Wir haben uns entschieden, die FDW-Technologie (Foreign Data Wrapper) zu verwenden: Im Wesentlichen handelt es sich hierbei um einen Standard-Dblink, der dem SQL-Standard entspricht, jedoch über eine eigene, viel praktischere Schnittstelle verfügt. Darauf aufbauend haben wir eine Lösung gefunden, die sich schließlich durchgesetzt hat, und wir haben uns darauf geeinigt. Seine Details sind Gegenstand eines separaten Artikels, vielleicht sogar mehrerer, da ich über vieles sprechen möchte: von der Synchronisierung von Datenbankschemata bis hin zur Zugriffskontrolle und Depersonalisierung personenbezogener Daten. Es muss auch ein Vorbehalt gemacht werden, dass diese Lösung kein Ersatz für echte analytische Datenbanken und Repositories ist; sie löst nur ein spezifisches Problem.

Auf der obersten Ebene sieht es so aus:

Es gibt eine PostgreSQL-Datenbank, in der Benutzer ihre Arbeitsdaten speichern können, und vor allem sind analytische Replikate aller Dienste über FDW mit dieser Datenbank verbunden. Dadurch ist es möglich, eine Abfrage an mehrere Datenbanken zu schreiben, egal um welche es sich handelt: PostgreSQL, MySQL, MongoDB oder etwas anderes (Datei, API, wenn plötzlich kein passender Wrapper da ist, können Sie Ihren eigenen schreiben). Nun ja, alles scheint großartig! Trennen wir uns?

Wenn alles so schnell und einfach enden würde, gäbe es wahrscheinlich keinen Artikel.

Es ist wichtig, sich darüber im Klaren zu sein, wie Postgres Anfragen an Remote-Server verarbeitet. Das erscheint logisch, wird aber oft nicht beachtet: Postgres unterteilt die Anfrage in Teile, die unabhängig auf Remote-Servern ausgeführt werden, sammelt diese Daten und führt die endgültigen Berechnungen selbst durch, sodass die Geschwindigkeit der Abfrageausführung stark davon abhängt wie es geschrieben steht. Es sollte auch beachtet werden: Wenn die Daten von einem Remote-Server ankommen, haben sie keine Indizes mehr, es gibt nichts, was dem Planer helfen könnte, daher können nur wir selbst ihm helfen und ihn beraten. Und genau darüber möchte ich ausführlicher sprechen.

Eine einfache Abfrage und ein Plan dazu

Um zu zeigen, wie Postgres eine Tabelle mit 6 Millionen Zeilen auf einem Remote-Server abfragt, schauen wir uns einen einfachen Plan an.

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 ms

Mithilfe der VERBOSE-Anweisung können wir die Abfrage sehen, die an den Remote-Server gesendet wird, und deren Ergebnisse wir zur weiteren Verarbeitung erhalten (RemoteSQL-Zeile).

Gehen wir noch einen Schritt weiter und fügen unserer Anfrage mehrere Filter hinzu: einen für boolean Feld, eines nach Vorkommen Zeitstempel im Intervall und nacheinander 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 ms

Hier liegt der Punkt, auf den Sie beim Schreiben von Abfragen achten müssen. Die Filter wurden nicht auf den Remote-Server übertragen, was bedeutet, dass Postgres zur Ausführung alle 6 Millionen Zeilen herauszieht, um dann lokal zu filtern (Filterzeile) und eine Aggregation durchzuführen. Der Schlüssel zum Erfolg besteht darin, eine Abfrage zu schreiben, damit die Filter auf den Remote-Computer übertragen werden und wir nur die erforderlichen Zeilen empfangen und aggregieren.

Das ist irgendein boolescher Scheiß

Mit booleschen Feldern ist alles einfach. In der ursprünglichen Anfrage lag das Problem beim Betreiber is. Wenn Sie es ersetzen durch =, dann erhalten wir folgendes Ergebnis:

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("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: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Wie Sie sehen, flog der Filter auf einen Remote-Server und die Ausführungszeit wurde von 27 auf 19 Sekunden reduziert.

Es ist erwähnenswert, dass der Betreiber is vom Betreiber abweichend = weil es mit dem Nullwert funktionieren kann. Das bedeutet es ist nicht wahr belässt die Werte False und Null im Filter, wohingegen != Stimmt hinterlässt nur falsche Werte. Daher beim Austausch des Bedieners ist nicht Zwei Bedingungen mit dem OR-Operator sollten an den Filter übergeben werden, zum Beispiel: WHERE (col != True) OR (col ist null).

Wir haben uns mit dem Booleschen Wert befasst, machen wir weiter. Lassen Sie uns zunächst den Booleschen Filter in seine ursprüngliche Form zurückversetzen, um die Auswirkungen anderer Änderungen unabhängig zu berücksichtigen.

Zeitstempel? hz

Im Allgemeinen muss man oft damit experimentieren, wie man eine Anfrage richtig schreibt, an der Remote-Server beteiligt sind, und erst dann nach einer Erklärung dafür suchen, warum dies geschieht. Im Internet finden sich hierzu kaum Informationen. In Experimenten haben wir herausgefunden, dass ein fester Datumsfilter mit einem Knall zum Remote-Server fliegt, aber wenn wir das Datum dynamisch festlegen möchten, zum Beispiel now() oder CURRENT_DATE, passiert dies nicht. In unserem Beispiel haben wir einen Filter hinzugefügt, sodass die Spalte „created_at“ Daten für genau einen Monat in der Vergangenheit enthielt (ZWISCHEN CURRENT_DATE – INTERVAL „1 Monate“ UND CURRENT_DATE – INTERVAL „7 Monate“). Was haben wir in diesem Fall gemacht?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 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.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 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))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Wir haben den Planer angewiesen, das Datum in der Unterabfrage im Voraus zu berechnen und die vorgefertigte Variable an den Filter zu übergeben. Und dieser Hinweis brachte uns ein hervorragendes Ergebnis, die Anfrage wurde fast sechsmal schneller!

Auch hier ist es wichtig, vorsichtig zu sein: Der Datentyp in der Unterabfrage muss mit dem Datentyp des Felds übereinstimmen, nach dem wir filtern, andernfalls wird der Planer entscheiden, dass aufgrund der unterschiedlichen Typen zunächst alle Daten abgerufen werden müssen die Daten und filtern sie lokal.

Lassen Sie uns den Datumsfilter auf seinen ursprünglichen Wert zurücksetzen.

Freddy vs. Jsonb

Im Allgemeinen haben boolesche Felder und Datumsangaben unsere Abfrage bereits ausreichend beschleunigt, es blieb jedoch noch ein weiterer Datentyp übrig. Der Kampf mit der Filterung ist ehrlich gesagt noch nicht vorbei, obwohl es auch hier Erfolge gibt. So haben wir es also geschafft, den Filter zu umgehen jsonb Feld an den Remote-Server.

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"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) 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: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

Anstatt Operatoren zu filtern, müssen Sie die Anwesenheit eines Operators nutzen jsonb in einem anderen. 7 Sekunden statt ursprünglich 29. Bisher ist dies die einzige erfolgreiche Möglichkeit, Filter per zu übertragen jsonb auf einen Remote-Server, aber hier ist es wichtig, eine Einschränkung zu berücksichtigen: Wir verwenden Version 9.6 der Datenbank, aber bis Ende April planen wir, die letzten Tests abzuschließen und auf Version 12 umzusteigen. Sobald wir das Update durchgeführt haben, werden wir darüber schreiben, wie es sich ausgewirkt hat, denn es gibt eine ganze Reihe von Änderungen, auf die man viel hoffen kann: json_path, neues CTE-Verhalten, Pushdown (existiert seit Version 10). Ich möchte es unbedingt bald ausprobieren.

Mach ihn fertig

Wir haben getestet, wie sich jede Änderung einzeln auf die Anfragegeschwindigkeit auswirkte. Sehen wir uns nun an, 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 <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"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 < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

Ja, die Anfrage sieht komplizierter aus, dies ist eine erzwungene Gebühr, aber die Ausführungsgeschwindigkeit beträgt 2 Sekunden, was mehr als zehnmal schneller ist! Und wir sprechen von einer einfachen Abfrage für einen relativ kleinen Datensatz. Auf echte Nachfrage erhielten wir eine Erhöhung um das bis zu mehrere Hundertfache.

Um es zusammenzufassen: Wenn Sie PostgreSQL mit FDW verwenden, überprüfen Sie immer, ob alle Filter an den Remote-Server gesendet werden, und Sie werden zufrieden sein ... Zumindest bis Sie Verknüpfungen zwischen Tabellen von verschiedenen Servern erhalten. Aber das ist eine Geschichte für einen anderen Artikel.

Vielen Dank für Ihre Aufmerksamkeit! Ich freue mich über Fragen, Kommentare und Geschichten über Ihre Erfahrungen in den Kommentaren.

Source: habr.com