A arquitetura de microsserviços, como tudo neste mundo, tem seus prós e contras. Alguns processos se tornam mais fáceis com ele, outros mais difíceis. E por causa da velocidade da mudança e melhor escalabilidade, você precisa fazer sacrifícios. Um deles é a complexidade da análise. Se em um monólito todas as análises operacionais podem ser reduzidas a consultas SQL para uma réplica analítica, em uma arquitetura multisserviço cada serviço tem seu próprio banco de dados e parece que uma consulta não é suficiente (ou será?). Para quem está interessado em saber como resolvemos o problema de análise operacional em nossa empresa e como aprendemos a conviver com essa solução - seja bem-vindo.
Meu nome é Pavel Sivash, na DomClick trabalho em uma equipe responsável pela manutenção do data warehouse analítico. Convencionalmente, nossas atividades podem ser atribuídas à engenharia de dados, mas, na verdade, o leque de tarefas é muito mais amplo. Existem padrões de engenharia de dados ETL/ELT, suporte e adaptação de ferramentas de análise de dados e desenvolvimento de ferramentas próprias. Em particular, para relatórios operacionais, decidimos “fingir” que temos um monólito e fornecer aos analistas um banco de dados que conterá todos os dados de que precisam.
Em geral, consideramos diferentes opções. Foi possível construir um repositório completo - até tentamos, mas, para ser sincero, não conseguimos fazer amizade com mudanças bastante frequentes na lógica com um processo bastante lento de construir um repositório e fazer alterações nele ( se alguém conseguiu, escreva nos comentários como). Você poderia dizer aos analistas: “Pessoal, aprenda python e vá para as linhas analíticas”, mas esse é um requisito adicional de recrutamento e parecia que isso deveria ser evitado, se possível. Decidimos tentar usar a tecnologia FDW (Foreign Data Wrapper): na verdade, este é um dblink padrão, que está no padrão SQL, mas com sua interface muito mais conveniente. Com base nisso, tomamos uma decisão, que acabou criando raízes, decidimos por ela. Seus detalhes são tema de um artigo separado, e talvez mais de um, porque quero falar muito sobre: desde a sincronização do esquema do banco de dados até o controle de acesso e despersonalização dos dados pessoais. Também deve ser notado que esta solução não substitui bancos de dados e repositórios analíticos reais, apenas resolve um problema específico.
No nível superior, fica assim:
Existe um banco de dados PostgreSQL onde os usuários podem armazenar seus dados de trabalho e, o mais importante, as réplicas analíticas de todos os serviços são conectadas a esse banco de dados via FDW. Isso permite escrever uma consulta em vários bancos de dados, não importa o que seja: PostgreSQL, MySQL, MongoDB ou qualquer outra coisa (arquivo, API, se de repente não houver um wrapper adequado, você pode escrever o seu próprio). Bem, parece estar tudo ótimo! Rompendo?
Se tudo terminasse de forma tão rápida e simples, provavelmente o artigo não existiria.
É importante esclarecer como o postgres lida com as solicitações para servidores remotos. Isso parece lógico, mas muitas vezes as pessoas não prestam atenção nisso: o postgres divide a consulta em partes que são executadas independentemente em servidores remotos, coleta esses dados e realiza os cálculos finais sozinho, então a velocidade de execução da consulta dependerá muito de como está escrito. Ressalte-se também: quando os dados vêm de um servidor remoto, eles não possuem mais índices, não há nada que ajude o agendador, portanto, somente nós mesmos podemos ajudar e sugerir. E é sobre isso que quero falar com mais detalhes.
Um pedido simples e um plano com ele
Para mostrar como o Postgres consulta uma tabela de 6 milhões de linhas em um servidor remoto, vejamos um plano simples.
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 msO uso da instrução VERBOSE permite que você veja a consulta que será enviada ao servidor remoto e cujos resultados receberemos para processamento posterior (string RemoteSQL).
Vamos um pouco mais longe e adicionamos vários filtros à nossa consulta: um por booleano campo, um por entrada timestamp por intervalo e um por JSON.
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É aqui que está o momento, ao qual você precisa prestar atenção ao escrever consultas. Os filtros não foram transferidos para o servidor remoto, o que significa que, para executá-lo, o postgres puxa todas as 6 milhões de linhas para filtrar localmente (a linha Filter) e realizar a agregação posteriormente. A chave para o sucesso é escrever uma consulta para que os filtros sejam transmitidos para a máquina remota e recebamos e agreguemos apenas as linhas necessárias.
Isso é uma booleanshit
Com campos booleanos, tudo é simples. Na consulta original, o problema era devido ao operador is. Se o substituirmos por =, então 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 você pode ver, o filtro voou para o servidor remoto e o tempo de execução foi reduzido de 27 para 19 segundos.
Vale ressaltar que o operador is diferente da operadora = aquele que pode trabalhar com o valor Null. Significa que não é verdade no filtro deixará os valores False e Null, enquanto != Verdadeiro deixará apenas valores falsos. Portanto, ao substituir o operador não é você deve passar duas condições para o filtro com o operador OR, por exemplo, WHERE (col != True) OR (col é nulo).
Com o booleano descoberto, seguindo em frente. Enquanto isso, vamos retornar o filtro por valor booleano à sua forma original para considerar independentemente o efeito de outras alterações.
carimbo de data/hora? Hz
Em geral, muitas vezes você precisa experimentar como escrever corretamente uma consulta que envolve servidores remotos e só então procurar uma explicação de por que isso está acontecendo. Muito pouca informação sobre isso pode ser encontrada na Internet. Então, em experimentos, descobrimos que um filtro de data fixa voa para um servidor remoto com um estrondo, mas quando queremos definir a data dinamicamente, por exemplo, now() ou CURRENT_DATE, isso não acontece. Em nosso exemplo, adicionamos um filtro para que a coluna created_at contenha dados de exatamente 1 mês passado (BETWEEN CURRENT_DATE - INTERVAL '7 months' AND CURRENT_DATE - INTERVAL '6 months'). O que fizemos 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 msSolicitamos ao planejador que calcule a data com antecedência na subconsulta e passe a variável já preparada para o filtro. E essa dica nos deu um ótimo resultado, a consulta ficou quase 6 vezes mais rápida!
Novamente, é importante ter cuidado aqui: o tipo de dados na subconsulta deve ser o mesmo do campo pelo qual filtramos, caso contrário, o planejador decidirá que, como os tipos são diferentes e é necessário primeiro obter todos os dados e filtrá-los localmente.
Vamos retornar o filtro por data ao seu valor original.
Fredy vs. jsonb
Em geral, campos booleanos e datas já aceleraram bastante nossa consulta, mas havia mais um tipo de dado. A batalha com a filtragem por ele, para ser sincero, ainda não acabou, embora haja sucessos aqui também. Então, aqui está como conseguimos passar o filtro por JSON campo para um 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 msEm vez de filtrar operadores, você deve usar a presença de um operador. JSON em um diferente. 7 segundos em vez dos 29 originais. Até agora, esta é a única opção bem-sucedida para transferir filtros por JSON para um servidor remoto, mas aqui é importante levar em consideração uma limitação: usamos a versão 9.6 do banco de dados, mas até o final de abril planejamos concluir os últimos testes e passar para a versão 12. Assim que atualizarmos, escreveremos como isso afetou, porque há muitas mudanças para as quais há muitas esperanças: json_path, novo comportamento CTE, push down (existente na versão 10). Eu realmente quero experimentar em breve.
Acabe com ele
Verificamos como cada alteração afeta a velocidade da consulta individualmente. Vamos agora ver o que acontece quando todos os três filtros são escritos corretamente.
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 msSim, a consulta parece mais complicada, é um preço forçado, mas a velocidade de execução é de 2 segundos, o que é mais de 10 vezes mais rápido! E estamos falando de uma consulta simples em um conjunto de dados relativamente pequeno. Em solicitações reais, recebemos um aumento de até várias centenas de vezes.
Resumindo: se você estiver usando PostgreSQL com FDW, sempre verifique se todos os filtros são enviados para o servidor remoto e você ficará feliz... Pelo menos até chegar às junções entre tabelas de servidores diferentes. Mas isso é história para outro artigo.
Obrigado pela sua atenção! Eu adoraria ouvir perguntas, comentários e histórias sobre suas experiências nos comentários.
Fonte: habr.com