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.
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
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. :)
Algumas dúvidas surgiram com base nas ações tomadas. A primeira delas, chegou a pensar em partições baseadas por mês ao invés de diárias?
Segregar dados gigantes, sempre é a melhor solução!
Interessante a solução. Fiquei com dúvida quanto ao custo de manter os 366 bancos, como ficou essa questão?
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.
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.
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.
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.
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.
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.
Boa noite, vc tentou usar o timescale no postgres? ou banco nosql como questDB ou clickhouse?
Isso sim é um programador, parabéns mano!