Executando verificação de segurança...
29
dedraks
2 min de leitura ·

Como otimizei um problema de desempenho de um sistema na empresa.

Na empresa temos um sistema que gera em torno de 50 milhões de registros no banco de dados por dia.
Esses registros precisam ficar guardados por 1 ano.
Então temos 50 milhões x 365(6) dias, o que nos dá aproximadamente 18 BILHÕES de registros.
Fui chamado pra ver se conseguiria otimizar esse sistema porque as consultas estavam extremamente lentas.
Para se ter uma idéia, uma simples busca estava demorando várias horas para entregar os resultados.

O sistema utilizava o Postgres como SGBD.
O sistema grava os dados de madrugada.
Os usuários acessam o sistema durante o dia.
Não há relacionamentos entre as tabelas.
Não há escritas quando os usuários estão fazendo as consultas.
A empresa mantém os arquivos com os dados brutos, que geram os dados para o banco.

Com base no explicado acima, decidi por utilizar o MariaDB com a tabela de dados usando a engine MyISAM.
Só com essa mudança, consultas que demoravam horas, passaram a ser executadas em uns 30 minutos.
Mas eu ainda não fiquei satisfeito.
Me reuni com os usuários do sistema e elegemos quais colunas são mais utilizadas nas buscas, desse modo as adicionei como índices.
Com essa mudança as consultas parassaram a ser executas em 3 a 5 minutos.
Mas eu ainda não fiquei satisfeito.
Conversando mais com os usuários, descobri que eles sempre selecionavam a data em que precisavam dos dados.
Então pensei: "Vou dividir para conquistar".
Criei 366 bancos dentro do SGBD, um pra cada dia do ano, e fiz um roteamento no sistema para selecionar o banco correto para leitura/escrita dos dados.
Agora as consultas não ocorrem em um unverso de 18 bilhões de registros, mas em "apenas" 50 milhões.
Com isso as consultas agora retornam em menos de 1 segundo quando se utiliza alguma coluna indexada.
Quando se busca em uma coluna não indexada as consultas demoram em torno de 20 a 30 segundos
Então sugeri aos usuários que, mesmo que precisem buscar dados nas colunas não indexadas, adicionem pelo menos uma coluna indexada na busca.

Com isso resolvi o problema de desempenho do sistema que demovara horas para executar uma busca para resultados instantâneos.

Carregando publicação patrocinada...
2
walacysilva

Gostei bastante do seu posto amigo! Curto muito esses relatos de devs melhorando performance(apesar de "ser" fullstack, eu me inclino mais pro backend). Mas você pode me falar porque mudou de Postgres para MariaDB? O Postgres já é um dos mais otimizados e preparados para grandes volumes de dados e tals. :)

2
rwerlich

Podemos ver o que o autor do post vai falar, mas o cenário descrito com horário de escrita ser fixo a noite e as tabelas não terem relacionamentos vai bem de encontro com a engine do MyISAM.

A engine padrão do MySQL/MariaDB até onde eu saiba é a InnoDB, foi uma sacada muito boa o uso do MyISAM.

O Postgres talvez tenha uma abordagem de engine similar.

2
KitsuneSemCalda

Esse foi um post muito bacana sobre otimização. Mas sinceramente, foi uma gambiarra sem igual.

O próprio PostgreSQL possui um sistema de particionamento nativo. Bastava criar uma tabela particionada por data e o banco lidaria em restringir consultas automaticamente para sua partição correta.

Além disso daria para usar índices parciais, ou índices BRIN que teria um efeito semelhante ao que você alcançou.

De qualquer forma, essa foi uma ideia bastante criativa para solucionar seu problema

2
dedraks
Autor
Autor

Opa.
Obrigado pela resposta.
Eu fiz testes com o sistema de particionamento do Postgres mas não obtive resultados satisfatórios.
O problema era que em uma parte era preciso fazer um count nos registros pra fins de paginação e em nenhum dos meus testes eu consegui resultados aceitáveis com o postgres.

1
KitsuneSemCalda

Entendo. No caso, eu evitaria usar o "Count" diretamente e, ao invés disso, criaria uma tabela separada para gerenciar a contagem de registros de forma incremental, o que tornaria a operação de paginação muito mais eficiente.

Na realidade eu partiria de algumas premissas de manutenção do banco de dados como:

  1. Índices por data (366 por ano) com Time-To-Live (TTL) de 1 ano:

    • Os dados com mais de um ano poderiam ser transferidos para Amazon RDS para fins de compliance, sem a necessidade de manter esses índices ativos no banco principal, o que ajuda a evitar o aumento descontrolado do tamanho do banco.
    • Como os índices não são interdependentes, acredito que seria mais fácil atualizar um índice por vez, em vez de manter e gerenciar 366 bancos separados. Isso facilita a manutenção e permite um gerenciamento mais eficiente.

  2. Sub tabela de contagem para cada índice, para facilitar a paginação sem usar o caro COUNT:

    • Caso não haja registros no índice de um determinado dia, começaria a contagem com o valor 0.
    • Se já houver registros, utilizaria um COUNT inicial para pegar o valor atual da contagem, e em seguida, a contagem seria mantida e atualizada via transações incrementais para cada novo registro.

Assim, sempre que for necessário realizar paginação, a contagem já estará disponível de forma simples e rápida, com um SELECT.

Mas, obviamente, isso é especulação minha com base nas informações fornecidas. Foi mais um exercício lógico sobre como eu estruturaria a arquitetura no PostgreSQL, do que uma solução definitiva para a sua situação.

1
viniciusxyz

Estava pensando exatamente nisso, tenho um cenário parecido, porém eu tenho 20MM de registros ao dia que tenho que manter durante 5 anos, sem particionamento eu teria me arrebentado.

1
Silvair

Achei interessante a sua abordagem.

Talvez, se você criasse uma coluna com o ano do registro e o marcasse como um índice (além dos outros índices que você criou) e ao fazer pesquisas, incluir dinamicamente na query que é enviada para o SGBD, os índices dos anos englobados pelo intervalo de dadas especificado pelo usuário, poderia surtir o mesmo efeito, sem precisar do esforço de engenharia e infra, para manter réplicas dos bancos de dados por intervalo de tempo.

1
Selitto

Indices ajudam muito, semana passada estavamos vendo uma consulta demorando 35 minutos, estava faltando um indice pelo timestamp, só isso já otimizou a consulta para 15 segundos. Tinha pensando em fazer outra tabela, mover só os dados que precisava para ela, mas no fim o indice resolveu.

1
kenpark2009

Interessante, eu também tive que atualizar meu sistema, como lê e grava dados ao mesmo tempo eu tive que dividir por dispositivos e adicionar índices. Mas só usei uma tabela mesmo. Usei partições no mysql com innodb, por causa da leitura e gravação ao mesmo tempo. Agora tenho um sistema que me dá o histórico dos dispositivos muito rápido e sem sacrificar o desempenho.

1
1
valkhantech

também fiquei curioso, essa foi a primeira coisa que me veio em mente quando falou em particionar por dia, e que não haveria de ter que fazer roteamento nem mudança no sistema.

1
ethi

interessante. vou ver se aplico alguma dessas soluções na empresa que tô atuando.
nosso db tá um pouco maior que o seu (atualmente em 40bi registros) e diariamente são gerados entre 100 e 300Mi de novos registros dependendo do dia, estou usando o pache kafka pra servir de fila temporária já que o banco de dados não tava dando conta de todos os inserts no momento que chegava os dados.
estamos utilizando o MySQL porém já com planos de migrar pro Oracle Autonomous Transaction em cloud.

1
1
CleitonCarvalho

Parabéns pela solução, e fez pensar e estudar mais sobre índice no banco de dados, pois nao utilizo eles nos meus projetos, e com esses relatos eu vejo o quanto importante ele pode ser.
Agradeço de mais por compartilhar conosco.

1
2
marcosnaofazisso

Pois é! Fiquei com essa mesma dúvida, primeira vez que vi uma solução assim e faz sentido com o modelo de negócio/necessidade da empresa dele

Mas como se mantém? 😅

0
valkhantech

custo é o mesmo, só que ele teve o custo operacional de mudar o sistema para apontar para esses demais bancos, se ele foi pra uma abordagem mais simplista ele provavelmente está usando o mesmo sgbd com o mesmo usuário e senha para todos os 366 databases

1
valterpatrick

Gostei da estratégia, interessante e resolveu o problema.
O ideal é sempre ter um banco de dados só, mas ter 1 pra cada dia foi a primeira vez que vi, mas tendo em vista a regra de negócio da sua empresa, é o ideal.

Pelo o que entendi os dados são gravados brutos e durante a madrugada para serem acessados durante o dia. Uma estratégia seria utilizar procedures para levar registros de varias tabelas para criar uma tabela com os dados que serão de fatos utilizados e com menos relacionamentos possíveis, assim você teria consultas rápidas tbm.

0
1
-1