Aprendizados com MySQL e gigabytes de dados

Seguem alguns aprendizados ao trabalhar com volumes de dezenas de Gigabytes com MySQL:

Nota 1) Usar o NMOM para monitorar o computador durante a execução das consultas é uma boa forma de identificar se o gargalo está na memória RAM ou no IO do HD;

Nota 2) Sobre os gargalos de banco de dados, existem vários tipos, mas vou destacar três: CPU, Memória RAM e HD.

  • Gargalo HD: O desempenho cai muito porque o PC fica muito tempo em espera, buscando os dados no HD. Esse problema é mais frequente com HD`s tradicionais. O uso de SSD basicamente elimina tempo de i/o destas operações;
  • Gargalo Memória RAM: Quando o conjunto de dados que o MySQL precisa operar para um join ou group by, cabe na memória ram, o processamento é muito mais rápido. Garantir uma memória RAM alta é fabuloso;
  • Gargalo na CPU: Segundo os últimos testes, o gargalo maior ficou aqui. Vale notar que parte do problema é que o MySQL não faz uso dos múltiplos nós de processadores modernos, então, cabe a nós tentarmos paralelizarmos manualmente certas operações.

Nota 3) Estudar os tipos primitivos do MySQL sempre vale o esforço e ajuda a otimizar muito o tamanho final das tabelas.

Nota 4) Embora antigo, MyISAM continua sendo um bom formato para operações de processamento e análise pois é extremamente rápida para inserir milhões de dados e o tamanho em disco fica muito menor do que com a InnoDB. Mas só vale a pena quando é um uso de estudos e análises, ou tabelas somente leitura.

Nota 5) Quando precisar otimizar o tamanho de uma tabela podemos cuidar para que o tipo de coluna utilizado ocupe o mínimo de bytes possível. Uma ferramenta do MySQL que ajuda muito nestes casos é a Procedure Analyse que informa os tipos recomendados para uma coluna em função do seu valor mínimo e máximo. Você pode usar com a consulta: “select * from nome_tabela PROCEDURE ANALYSE()”

Nota 6) Ainda neste tema de estimativa de tamanho das tabelas, Quando houver possibilidade de uma tabela chegar nas dezenas de milhares de registros, é essencial que você faça uma boa estimativa do tamanho em disco que esta tabela poderá chegar. Isto lhe ajudará a prevenir dificuldades como ocupar todo o seu espaço em disco ou memória ram disponível.  É importante também calcular o tamanho dos índices.

Nota 7) Particionamento: esta técnica nos permite dividir uma tabela horizontal ou verticalmente para que todas as consultas e procedimentos sejam feitos em pedaços menores de arquivos. A documentação do MySQL nos mostra como particionar uma tabela verticalmente utilizando colunas de tipos inteiros, existem muitos slides e materiais em inglês sobre como realizar essa partição automaticamente, mas há também quem defenda que o resultado pode ser mais rápido realizando esse particionamento manualmente.

Nota 8) Tipo das Tabelas: Sempre verifique a utilidade de cada tipo de tabela do seu SGBD, em alguns casos, tabelas mais simples, muitas vezes desconsideradas por terem menos recursos serão muito mais rápidas para a sua necessidade. Em MySQL por exemplo, tabelas do tipo Archive podem ser muito mais compactas do que tabelas MyISAM (mas não são indexadas);

Nota 9) Tipo dos Dados: Um INT ocupa 4 bytes. Se você tiver uma tabela 5 campos inteiros com  1 milhão de registros, são 20 milhões de bytes de espaço ocupado, equivalente a 19 megabytes (sem contar índices). Agora, se por acaso você puder utilizar tinyint (1byte) para 3 destas 5 colunas, serão 11.000.000 milhões de bytes, uma economia de  mais de 8 megabytes (veja aqui o tamanho dos campos numéricos). Talvez lhe pareça pouco, mas imagine otimizações como esta em diversas tabelas, de vários sistemas, acumulando registros ao longo dos anos..isso pode resultar em uma economia realmente grande. Veja aqui o tamanho ocupado por cada tipo de dado.

Nota 10) Not Nul sempre que possível: Use campos nulos sempre que precisar, porém, quando eles não forem necessários, marque a coluna como not null. Isso economiza um bite por registro/coluna e muito processamento, principalmente nos índices.

Alguns links (antigos, mas que já me ajudaram muito):