Por que escolhemos o MySQL para salvar logs e gerar relatórios de atividades — pelo menos por enquanto

Recentemente, aqui na Mercos, começamos um novo projeto para registrar atividades realizadas pelos usuários da nossa plataforma com o objetivo de gerar relatórios e insights para nossos clientes.

Antes de começarmos de fato o desenvolvimento da nova feature, dedicamos um tempo para pesquisas, buscando novas ferramentas e testando outras já conhecidas a fim de descobrirmos qual se adequaria melhor ao nosso caso de uso.

Durante esse processo, criamos uma issue no GitHub a fim de compilarmos os conhecimentos adquiridos afim de facilitar uma tomada de decisão no final sem que esquecêssemos de algum pró ou contra das ferramentas pesquisadas.

Neste primeiro momento, acreditávamos que uma ferramenta NoSQL seria a melhor alternativa diante do nosso caso. Desta forma, fizemos uma pequena lista de possíveis ferramentas já conhecidas:

• Google Firebase
• Google BigQuery
• Google DataStore
• AWS DynamoDB
• AWS RedShift
• Redis
• MongoDB
• Cassandra

E conforme as pesquisas avançavam, a lista ia crescendo, adicionamos Hadoop, Spark, ElasticSearch, entre outras.

Enquanto pesquisávamos, fizemos também alguns benchmarks com outras empresas para vermos como elas estavam resolvendo, ou resolveram, este tipo de problema. E aí entraram diversas novas ferramentas com stacks, eu diria, bem complexas. E neste primeiro momento queríamos tentar uma stack mais simples, que possibilitaria escalar no futuro, mas que o grau de complexidade de início fosse baixo.

É importante dizer que, durante os testes, buscávamos uma ferramenta que pudesse salvar uma quantidade de dados considerável e que fosse muito performática para realizar as consultas, visto que nosso objetivo era criar relatórios o mais real-time possível para os nossos clientes.

Durante a fase de testes, tivemos algumas decepções, parecia que uma stack simples seria impossível para o problema. Testamos o Firebase, mas não era o que precisávamos, seu propósito realmente parecia ser outro, o DynamoDB da AWS parecia promissor, porém, haviam limitações, e o poder de consulta, assim como o Firebase, não dava o que precisávamos (talvez por falta de conhecimento, mas a incerteza nos fez passar para a próxima ferramenta).

Fizemos alguns testes com o BigQuery, porém o tempo de resposta estava longe do que gostaríamos, uma tabela com pouco mais de 30 mil registros retornava sempre entre 3 e 6 segundos, e como queríamos algo o mais realtime possível, esse tempo parecia inaceitável. Testamos também o DataStore, mas caímos no mesmo problema do DynamoDB.

Buscamos alguns benchmarks e vimos que assim como o BigQuery, o RedShift da AWS não teria o tempo de resposta desejado.

Testamos o MongoDB, com mais de 30 milhões de registros, e as consultas estavam bem lentas, sabemos que poderíamos configurar para que ficasse mais performático, e aqui cairia em um novo problema, queríamos algo que fosse de fácil manutenção.

Neste momento, temos os seguintes pontos:

• Deve ser rápido para realizar consultas complexas;
• Deve possuir uma stack simples;
• Deve ser fácil de escalar;
• Deve ser de fácil manutenção.

Começamos a refletir nos pontos anteriores, e começamos a fazer algumas reconsiderações, dentre elas, de um banco de dados relacional, já que não tínhamos feito nenhum teste e descartamos muito cedo a possibilidade de usarmos o MySQL ou o PostgreSQL.

Podemos ressaltar também, que o poder de consulta do MySQL é muito superior a várias outras ferramentas que testamos, e isso foi um fator bem importante para reconsiderar o uso de um banco de dados relacional.

E para nós, um banco de dados relacional atendia muito bem a 3 dos 4 pontos ressaltados anteriormente: stack simples, fácil de escalar, fácil manutenção, e se no futuro precisarmos migrar os dados para uma ferramenta mais robusta, não teríamos grandes problemas.

Fizemos alguns testes no começo comparando versões de MySQL e PostgreSQL, neste primeiro momento, apesar de resultados bem preliminares, o MySQL 5.7 se saiu melhor, e optamos por ele para fazermos o teste final.

Para iniciarmos os testes, criamos uma tabela simples para salvar eventos com a seguinte estrutura:

Para o teste, foram executadas as seguintes consultas SQLs:

E por último, para popular a base, criamos um pequeno script para gerar registros entre 50 empresa_id diferentes, que mais para frente explicarei a importância de diferentes valores para esse campo, o script também gerava logs para datas aleatórias entre 12 meses do último ano, com os campos sempre com valores aleatórios.

O teste a seguir foi realizado no infra do Google Cloud, rodando a versão 5.7 do MySQL, com disco de SSD de 20GB, 3.5 GB de memória RAM e 1 vCPU, configuração padrão da instância: db-n1-standard-1.

Teste 1–570.900 registros

Teste 2–4.567.200 registros

Teste 3–36.537.600 registros

Após o terceiro teste, percebemos que as queries 2 e 3.1 já estavam bem lentas. Desta forma, precisávamos criar uma nova estratégia para suportar tal quantidade de dados com uma resposta de consulta dentro do aceitável (milissegundos). As consultas sequer foram otimizadas, mas o foco aqui era como deixar as consultas com um melhor tempo de resposta sem alterar elas.

Foi aí que surgiram as Partitions do MySQL, que até então eram conhecidas no nosso time apenas pelo nome, mas nunca tínhamos usado em algum caso real. Depois de uma pequena pesquisa, vimos que sua utilização era bem simples, porém, apesar de simples, requeria um certo estudo para entender a fundo como funcionavam. No nosso caso, parecia fazer bastante sentido uma partição através do campo empresa_id , já que dificilmente consultaríamos duas empresas diferentes em uma mesma consulta, sendo assim, foi que fizemos:

Após longos minutos para criação das partições dentro de uma tabela que possuia milhões de registros, pudemos continuar nossa saga e executar o quarto teste, com o mesmo número de registros que o terceiro teste, porém com as partições já criadas.

Teste 4–36.537.600 registros com partições

Bom, o resultado pareceu muito bom, mas na verdade não há nada de muito incrível ao entender como as partições funcionam no MySQL, resumidamente, por baixo dos panos, a tabela evento se dividiu em 1000 pequenas partições, porém, vale ressaltar que como no script original, foram criadas apenas 50 empresas_ids diferentes, e as partições foram criadas pelo valor do empresa_id, das 1000 partições, apenas 50 possuem registros, as demais ficaram todas vazias.

Ainda segui com um próximo teste, saindo dos pouco mais de 36 milhões de registros para 73 milhões.

Teste 5–73.075.212 registros

Os resultados aqui das queries 2 e 3.1, assim como no teste anterior não foram muito bons, na verdade foram bem ruins, porém ainda melhores que o teste 3 que possui metade dos registros do teste atual.

Porém aqui, cai um ponto bem interessante, os índices criados até o momento estão bem ruins, repensamos a estratégia dos índices, e fizemos apenas para os campos necessários usando os seguintes campos: empresa_id, cliente_id, datahora e evento.

Teste 6–73.075.212 registros (com índice recriado)

Com os novos índices os resultados foram bem melhores, porém, vale ressaltar que ainda é possível otimizarmos as consultas, até as partições podem ser repensadas, seja criando através de outro campo, como por exemplo intervalo de datahora que é bastante utilizado, ou mantendo por empresa_id e criando subpartições por data, a máquina pode receber um upgrade de memória RAM, enfim, o ponto aqui é que ainda há muitas melhorias a serem realizadas.

Essa saga em busca da melhor ferramenta para armazenar e consultar os eventos geraram muitos conhecimentos, desde novas tecnologias até novos recursos como partições que poderão ser aplicadas em outras parte do nosso sistema. Outro ponto importante é que, apesar de parecer não ser a ferramenta ideal para resolver o problema de salvar e consultar métricas, o MySQL atende às nossas expectativas, ao menos, por enquanto.