A arquitectura de microservizos, como todo neste mundo, ten os seus pros e contras. Algúns procesos fanse máis fáciles con el, outros máis difíciles. E en aras da velocidade de cambio e unha mellor escalabilidade, cómpre facer sacrificios. Un deles é a crecente complexidade da analítica. Se nun monólito toda a analítica operativa pode reducirse a consultas SQL a unha réplica analítica, entón nunha arquitectura multiservizo cada servizo ten a súa propia base de datos e parece que non se pode facer unha consulta (ou quizais si?). Para aqueles que estean interesados en como resolvemos o problema da analítica operativa na nosa empresa e como aprendemos a vivir con esta solución, benvidos.
Chámome Pavel Sivash, en DomClick traballo nun equipo que se encarga de manter o almacén de datos analíticos. Convencionalmente, as nosas actividades pódense clasificar como enxeñería de datos, pero, de feito, o abano de tarefas é moito máis amplo. Existen estándares ETL/ELT para enxeñaría de datos, soporte e adaptación de ferramentas para análise de datos e desenvolvemento das súas propias ferramentas. En particular, para os informes operativos, decidimos "finxir" que temos un monólito e dar aos analistas unha base de datos que conterá todos os datos que necesitan.
En xeral, consideramos diferentes opcións. Foi posible construír un repositorio completo, incluso o intentamos, pero, para ser honesto, non fomos capaces de combinar cambios bastante frecuentes na lóxica co proceso bastante lento de construír un repositorio e facer cambios nel (se alguén conseguiu , escribe nos comentarios como). Era posible dicirlles aos analistas: "Rapaces, aprenden python e van a réplicas analíticas", pero este é un requisito adicional para a contratación, e parecía que debería evitarse se é posible. Decidimos probar a usar a tecnoloxía FDW (Foreign Data Wrapper): esencialmente, este é un dblink estándar, que está no estándar SQL, pero cunha interface moito máis cómoda. Partindo del, fixemos unha solución, que acabou conseguindo e decidimos. Os seus detalles son o tema dun artigo aparte, e quizais máis dun, xa que quero falar de moitas cousas: desde a sincronización de esquemas de bases de datos ata o control de acceso e a despersonalización dos datos persoais. Tamén é necesario facer unha reserva de que esta solución non é un substituto de bases de datos e repositorios analíticos reais; só resolve un problema específico.
No nivel superior vese así:
Hai unha base de datos PostgreSQL onde os usuarios poden almacenar os seus datos de traballo e, o máis importante, as réplicas analíticas de todos os servizos están conectadas a esta base de datos a través de FDW. Isto fai posible escribir unha consulta en varias bases de datos, e non importa cal sexa: PostgreSQL, MySQL, MongoDB ou outra cousa (ficheiro, API, se de súpeto non hai un envoltorio axeitado, podes escribir o teu propio). Ben, todo parece xenial! Estamos a romper?
Se todo rematase tan rápido e sinxelo, probablemente non habería un artigo.
É importante ter claro como Postgres procesa as solicitudes aos servidores remotos. Isto parece lóxico, pero moitas veces a xente non lle presta atención: Postgres divide a solicitude en partes que se executan de forma independente en servidores remotos, recolle estes datos e realiza por si mesmo os cálculos finais, polo que a velocidade de execución da consulta dependerá en gran medida de como está escrito. Tamén hai que ter en conta: cando chegan os datos dun servidor remoto, xa non ten índices, non hai nada que axude ao planificador, polo tanto, só nós mesmos podemos axudalo e aconsellalo. E isto é exactamente do que quero falar con máis detalle.
Unha consulta sinxela e un plan con ela
Para mostrar como Postgres consulta unha táboa de 6 millóns de filas nun servidor remoto, vexamos un plan sinxelo.
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 msUsar a instrución VERBOSE permítenos ver a consulta que se enviará ao servidor remoto e os resultados da cal imos recibir para o seu posterior procesamento (liña RemoteSQL).
Imos un pouco máis alá e engadimos varios filtros á nosa solicitude: un para booleano campo, un por ocorrencia marca de tempo no intervalo e un por 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 msAquí é o punto no que debes prestar atención ao escribir consultas. Os filtros non se transferiron ao servidor remoto, o que significa que para executalo, Postgres saca os 6 millóns de filas para despois filtrar localmente (fila Filtrar) e realizar a agregación. A clave do éxito é escribir unha consulta para que os filtros sexan transferidos á máquina remota e só recibamos e agreguemos as filas necesarias.
Iso é unha merda booleana
Con campos booleanos todo é sinxelo. Na solicitude orixinal, o problema debeuse ao operador is. Se o substitúe por =, entón obtemos o seguinte resultado:
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 msComo podes ver, o filtro voou a un servidor remoto e o tempo de execución reduciuse de 27 a 19 segundos.
Paga a pena notar que o operador is diferente do operador = porque pode funcionar co valor Nulo. Quere dicir que non é verdade deixará os valores Falso e Nulo no filtro, mentres que != Verdade deixará só valores falsos. Polo tanto, ao substituír o operador non é dúas condicións co operador OR deben pasarse ao filtro, por exemplo, WHERE (col != verdadeiro) OU (col é nulo).
Lidamos co booleano, sigamos. De momento, imos devolver o filtro booleano á súa forma orixinal para considerar de forma independente o efecto doutros cambios.
timestamptz? hz
En xeral, moitas veces ten que probar como escribir correctamente unha solicitude que involucre servidores remotos e só entón buscar unha explicación de por que isto ocorre. Moi pouca información sobre isto pódese atopar en Internet. Entón, nos experimentos descubrimos que un filtro de data fixa voa ao servidor remoto cun golpe, pero cando queremos establecer a data de forma dinámica, por exemplo, agora() ou CURRENT_DATE, isto non ocorre. No noso exemplo, engadimos un filtro para que a columna created_at contiña datos de exactamente 1 mes no pasado (ENTRE CURRENT_DATE - INTERVAL '7 months' E CURRENT_DATE - INTERVAL '6 months'). Que fixemos neste caso?
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 msDixémoslle ao planificador que calculase a data na subconsulta con antelación e pasase a variable preparada ao filtro. E esta pista deunos un excelente resultado, a solicitude fíxose case 6 veces máis rápida!
De novo, é importante ter coidado aquí: o tipo de datos na subconsulta debe ser o mesmo que o do campo no que estamos a filtrar, se non, o planificador decidirá que, dado que os tipos son diferentes, é necesario primeiro obter todos os datos e filtralos localmente.
Imos devolver o filtro de data ao seu valor orixinal.
Freddy vs. Jsonb
En xeral, os campos e datas booleanos xa aceleraron o suficiente a nosa consulta, pero quedaba un tipo de datos máis. A batalla co filtrado por ela, sinceramente, aínda non rematou, aínda que aquí tamén hai éxito. Entón, así conseguimos pasar o filtro jsonb campo ao servidor remoto.
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 msEn lugar de filtrar operadores, debes usar a presenza dun operador jsonb nun diferente. 7 segundos en lugar dos 29 orixinais. Ata agora esta é a única opción exitosa para transmitir filtros a través jsonb a un servidor remoto, pero aquí é importante ter en conta unha limitación: estamos a usar a versión 9.6 da base de datos, pero a finais de abril temos previsto completar as últimas probas e pasar á versión 12. Unha vez que actualicemos, escribiremos sobre como afectou, porque hai moitos cambios nos que hai moita esperanza: json_path, novo comportamento CTE, push down (existente dende a versión 10). Teño moitas ganas de probalo pronto.
Acaba con el
Probamos como cada cambio afectaba a velocidade das solicitudes individualmente. Vexamos agora que pasa cando os tres filtros están escritos correctamente.
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 msSi, a solicitude parece máis complicada, esta é unha taxa forzada, pero a velocidade de execución é de 2 segundos, o que é máis de 10 veces máis rápido. E estamos a falar dunha consulta sinxela contra un conxunto de datos relativamente pequeno. En solicitudes reais, recibimos un aumento de ata varios centos de veces.
En resumo: se usas PostgreSQL con FDW, comproba sempre que todos os filtros se envían ao servidor remoto, e estarás contento... Polo menos ata chegar a unións entre táboas de distintos servidores. Pero esa é unha historia para outro artigo.
Grazas pola súa atención! Encantaríame escoitar preguntas, comentarios e historias sobre as túas experiencias nos comentarios.
Fonte: www.habr.com