L'arquitectura de microserveis, com tot en aquest món, té els seus pros i contres. Alguns processos es fan més fàcils amb ell, d'altres més difícils. I pel bé de la velocitat del canvi i una millor escalabilitat, cal fer sacrificis. Un d'ells és la creixent complexitat de l'anàlisi. Si en un monòlit tota l'anàlisi operativa es pot reduir a consultes SQL a una rèplica analítica, aleshores en una arquitectura multiservei cada servei té la seva pròpia base de dades i sembla que no es pot fer una consulta (o potser sí?). Per a aquells que estiguin interessats en com hem resolt el problema de l'anàlisi operativa a la nostra empresa i com hem après a conviure amb aquesta solució: benvinguts.

Em dic Pavel Sivash, a DomClick treballo en un equip que s'encarrega de mantenir el magatzem de dades analítiques. Convencionalment, les nostres activitats es poden classificar com a enginyeria de dades, però, de fet, el ventall de tasques és molt més ampli. Hi ha estàndards ETL/ELT per a l'enginyeria de dades, suport i adaptació d'eines per a l'anàlisi de dades i desenvolupament de les vostres pròpies eines. En particular, per als informes operatius, vam decidir "fingir" que tenim un monòlit i donar als analistes una base de dades que contindrà totes les dades que necessiten.

En general, hem considerat diferents opcions. Va ser possible construir un repositori complet, fins i tot ho vam provar, però, per ser honest, no vam poder combinar canvis de lògica força freqüents amb el procés força lent de construir un dipòsit i fer-hi canvis (si algú ho va aconseguir. , escriviu als comentaris com). Va ser possible dir als analistes: "Nois, apreneu python i aneu a rèpliques analítiques", però aquest és un requisit addicional per a la contractació, i semblava que s'hauria d'evitar si és possible. Vam decidir provar d'utilitzar la tecnologia FDW (Foreign Data Wrapper): bàsicament, es tracta d'un dblink estàndard, que es troba a l'estàndard SQL, però amb la seva pròpia interfície molt més còmoda. A partir d'això, vam fer una solució, que finalment va agafar força, i ens hi vam decidir. Els seus detalls són el tema d'un article a part, i potser més d'un, ja que vull parlar de moltes coses: des de la sincronització d'esquemes de bases de dades fins al control d'accés i la despersonalització de les dades personals. També cal fer una reserva que aquesta solució no substitueix les bases de dades i repositoris analítics reals, sinó que soluciona només un problema concret.

Al nivell superior es veu així:

Hi ha una base de dades PostgreSQL on els usuaris poden emmagatzemar les seves dades de treball i, el més important, les rèpliques analítiques de tots els serveis estan connectades a aquesta base de dades mitjançant FDW. Això fa possible escriure una consulta a diverses bases de dades, i no importa quina sigui: PostgreSQL, MySQL, MongoDB o una altra cosa (fitxer, API, si de sobte no hi ha cap embolcall adequat, podeu escriure el vostre). Bé, tot sembla genial! Estem trencant?

Si tot acabés tan ràpid i senzill, probablement, no hi hauria cap article.

És important tenir clar com Postgres processa les sol·licituds als servidors remots. Això sembla lògic, però sovint la gent no hi presta atenció: Postgres divideix la sol·licitud en parts que s'executen de manera independent en servidors remots, recull aquestes dades i realitza els càlculs finals, de manera que la velocitat d'execució de la consulta dependrà en gran mesura de com està escrit. També cal tenir en compte: quan les dades arriben d'un servidor remot, ja no té índexs, no hi ha res que ajudi al planificador, per tant, només nosaltres mateixos podem ajudar-lo i assessorar-lo. I això és exactament del que vull parlar amb més detall.

Una consulta senzilla i un pla amb ella

Per mostrar com Postgres consulta una taula de 6 milions de files en un servidor remot, mirem un pla senzill.

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

L'ús de la instrucció VERBOSE ens permet veure la consulta que s'enviarà al servidor remot i els resultats de la qual rebrem per a un posterior processament (línia RemoteSQL).

Anem una mica més enllà i afegim diversos filtres a la nostra sol·licitud: un per booleà camp, un per ocurrència data i hora en l'interval i un per 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

Aquí és on rau el punt al qual cal parar atenció a l'hora d'escriure consultes. Els filtres no es van transferir al servidor remot, la qual cosa significa que per executar-lo, Postgres treu els 6 milions de files per després filtrar localment (fila de filtre) i realitzar l'agregació. La clau de l'èxit és escriure una consulta perquè els filtres es transfereixin a la màquina remota, i només rebem i agrupem les files necessàries.

Això és una merda booleana

Amb els camps booleans tot és senzill. A la sol·licitud original, el problema era degut a l'operador is. Si el substituïu per =, aleshores obtenim el resultat següent:

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

Com podeu veure, el filtre va volar a un servidor remot i el temps d'execució es va reduir de 27 a 19 segons.

Val la pena assenyalar que l'operador is diferent de l'operador = perquè pot funcionar amb el valor Null. Vol dir això no és veritat deixarà els valors Fals i Null al filtre, mentre que != Veritat només deixarà valors falsos. Per tant, en substituir l'operador no és dues condicions amb l'operador OR s'han de passar al filtre, per exemple, ON (col != Veritable) O (col és nul).

Hem tractat amb el booleà, continuem. De moment, tornem el filtre booleà a la seva forma original per tal de considerar de manera independent l'efecte d'altres canvis.

timestamptz? hz

En general, sovint heu d'experimentar com escriure correctament una sol·licitud que involucreu servidors remots i només aleshores buscar una explicació de per què passa això. Es pot trobar molt poca informació sobre això a Internet. Per tant, en experiments hem trobat que un filtre de data fixa vola al servidor remot amb un cop, però quan volem establir la data de manera dinàmica, per exemple, ara() o CURRENT_DATE, això no passa. Al nostre exemple, hem afegit un filtre perquè la columna created_at contingués dades exactament d'1 mes en el passat (ENTRE CURRENT_DATE - INTERVAL '7 months' I CURRENT_DATE - INTERVAL '6 months'). Què hem fet en aquest cas?

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

Li vam dir al planificador que calculés la data a la subconsulta amb antelació i que passés la variable ja feta al filtre. I aquesta pista ens va donar un resultat excel·lent, la sol·licitud es va fer gairebé 6 vegades més ràpida!

De nou, és important anar amb compte aquí: el tipus de dades de la subconsulta ha de ser el mateix que el del camp sobre el qual estem filtrant, en cas contrari, el planificador decidirà que com que els tipus són diferents, primer cal obtenir-los tots. les dades i filtrar-les localment.

Tornem el filtre de data al seu valor original.

Freddy vs. Jsonb

En general, els camps i les dates booleans ja han accelerat prou la nostra consulta, però quedava un tipus de dades més. La batalla per filtrar-lo, sincerament, encara no ha acabat, tot i que aquí també hi ha èxit. Així doncs, hem aconseguit passar el filtre jsonb camp al servidor remot.

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

En lloc de filtrar operadors, heu d'utilitzar la presència d'un operador jsonb en una diferent. 7 segons en lloc dels 29 originals. Fins ara aquesta és l'única opció reeixida per transmetre filtres a través jsonb a un servidor remot, però aquí és important tenir en compte una limitació: estem utilitzant la versió 9.6 de la base de dades, però a finals d'abril tenim previst completar les últimes proves i passar a la versió 12. Un cop actualitzem, escriurem sobre com ha afectat, perquè hi ha molts canvis per als quals hi ha molta esperança: json_path, nou comportament de CTE, push down (existent des de la versió 10). Tinc moltes ganes de provar-ho aviat.

Acaba amb ell

Hem provat com cada canvi afectava la velocitat de sol·licitud de manera individual. Vegem ara què passa quan els tres filtres estan escrits correctament.

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

Sí, la sol·licitud sembla més complicada, aquesta és una tarifa forçada, però la velocitat d'execució és de 2 segons, que és més de 10 vegades més ràpid! I estem parlant d'una simple consulta amb un conjunt de dades relativament petit. En sol·licituds reals, vam rebre un augment de fins a diversos centenars de vegades.

En resum: si feu servir PostgreSQL amb FDW, comproveu sempre que tots els filtres s'enviïn al servidor remot, i estareu contents... Almenys fins que arribeu a les unions entre taules de diferents servidors. Però això és una història per a un altre article.

Gràcies per la vostra atenció! M'encantaria escoltar preguntes, comentaris i històries sobre les vostres experiències als comentaris.

Font: www.habr.com