Imagine que você é um padeiro que precisa fatiar os pães produzidos. Agora multiplique isso por 3.000. É assim que se sente quando sua tabela MySQL cresce para 30 milhões de linhas. De repente, aquela consulta rápida vira uma espera eterna, como esperar o pão crescer em pleno inverno.
Por que 30 milhões de registros são diferentes?
Uma tabela pequena é como sua lista de compras – você encontra tudo rapidamente. Contudo, 30 milhões de registros são como a biblioteca de Alexandria: encontrar um livro específico exige organização e estratégia. O MySQL precisa de ajustes especiais para não ficar sobrecarregado, similar a como um padeiro profissional precisa de equipamentos industriais para produzir milhares de pães diariamente.
Preparando o terreno: configurando o MySQL para grandes volumes
Antes de mergulhar nos dados, precisamos ajustar nosso “forno” para assar pães em larga escala. As configurações padrão do MySQL são como um forno doméstico – perfeito para um bolo, mas insuficiente para uma padaria industrial.
Passos:
- Feche o MySQL
- abra o CMD como ADMINISTRADOR
- Parando o MySQL pelo CMD
|
1 |
net stop MySQL80 |
- Navegar para a pasta de configuração onde está o arquivo my.ini
|
1 2 |
# Possível local cd C:\ProgramData\MySQL\MySQL Server 8.0 |
- faça um Backup do my.ini original
|
1 2 3 |
# Abra o arquivo de configuração e notepad my.ini # salve em outro local com nome backup_my_ini.txt ANTES DE CONTINUAR |
- Acessando my.ini em modeo edição
|
1 2 |
# Editar o arquivo de configuração notepad my.ini |
- Minha receita para a minha situação
Assim como diferentes tipos de pão exigem temperaturas e tempos específicos, diferentes cargas de trabalho precisam de configurações personalizadas. Principalmente, focaremos no innodb_buffer_pool_size – a memória que o MySQL usa para armazenar dados frequentemente acessados.
No meu caso tenho uma tabela vendas_medicamentos com 30468924 de linhas que preciso fazer o group by por ANO_VENDA, MUNICIPIO_VENDA, PRINCIPIO_ATIVO.
O meu my.ini ficou desta forma:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
[client] default-character-set = utf8mb4 port=3306 [mysql] no-beep [mysqld] # ====================== # CONFIGURAÇÕES OTIMIZADAS PARA 41M+ LINHAS # ====================== # Buffer Pool (CRÍTICO - use 2GB para segurança) innodb_buffer_pool_size = 2G # Logs para grandes transações innodb_log_file_size = 256M innodb_log_buffer_size = 32M # Performance vs Durability (importante para ETL) innodb_flush_log_at_trx_commit = 2 # Memória para operações GROUP BY tmp_table_size = 256M max_heap_table_size = 256M # Buffers de ordenação sort_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 2M join_buffer_size = 4M # Timeouts maiores para queries longas net_read_timeout = 1800 net_write_timeout = 1800 wait_timeout = 28800 interactive_timeout = 28800 # Otimizações para queries grandes max_allowed_packet = 256M # Máximo de conexões max_connections = 100 # ====================== # CONFIGURAÇÕES GERAIS # ====================== port=3306 datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data character-set-server=utf8mb4 collation-server = utf8mb4_unicode_ci authentication_policy=*,, default-storage-engine=INNODB sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" log-output=FILE general-log=0 general_log_file="TORRECOUGAR-AMJ.log" slow-query-log=1 slow_query_log_file="TORRECOUGAR-AMJ-slow.log" long_query_time=10 log-error="TORRECOUGAR-AMJ.err" log-bin="TORRECOUGAR-AMJ-bin" server-id=1 lower_case_table_names=1 secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" max_connections=151 table_open_cache=4000 temptable_max_ram=1G internal_tmp_mem_storage_engine=TempTable myisam_max_sort_file_size=2146435072 myisam_sort_buffer_size=153M key_buffer_size=8M # ⚠️ CONFIGURAÇÕES COMENTADAS - JÁ DEFINIDAS ACIMA # read_buffer_size=128K # read_rnd_buffer_size=256K # innodb_flush_log_at_trx_commit=1 # innodb_log_buffer_size=16M # innodb_buffer_pool_size=128M innodb_redo_log_capacity=100M innodb_thread_concurrency=9 innodb_autoextend_increment=64 innodb_buffer_pool_instances=8 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 flush_time=0 # CONFIGURAÇÕES ESSENCIAIS PARA QUERIES GRANDES max_execution_time = 0 innodb_buffer_pool_chunk_size = 128M # ⚠️ CONFIGURAÇÕES COMENTADAS - JÁ DEFINIDAS ACIMA # join_buffer_size=256K max_allowed_packet=64M max_connect_errors=100 open_files_limit=8161 # ⚠️ CONFIGURAÇÃO COMENTADA - JÁ DEFINIDA ACIMA # sort_buffer_size=256K binlog_row_event_max_size=8K sync_source_info=10000 sync_relay_log=10000 mysqlx_port=33060 |
Passo 8 -Reinicie o servidor de BD MySQL no CMD
|
1 2 |
# Iniciar MySQL net start MySQL80 |
- No MySQL execute:
SET SESSION net_read_timeout = 1800;
SET SESSION net_write_timeout = 1800;
SET GLOBAL max_allowed_packet = 268435456;
Estratégias inteligentes para trabalhar com dados massivos
Trabalhar com 30 milhões de registros exige a mesma paciência e estratégia que um alpinista precisa para escalar o Everest. Você não sobe de uma vez – divide em acampamentos-base.
Atualizações em lotes: dividir para conquistar
Atualizar 30 milhões de registros de uma vez é como tentar assinar 10.000 pães no mesmo forno. Eventualmente, algo queima. Por isso, dividimos em lotes menores:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
""" Atualização em lotes - assinando pães em fornadas menores """ UPDATE trampo.VENDAS_MEDICAMENTOS SET principio_ativo = 'SEM INFORMAÇÃO' WHERE principio_ativo = '' LIMIT 1000; # Apenas 1000 por vez # Execute repetidamente até terminar # Verifique quantos faltam com: SELECT COUNT(*) FROM trampo.VENDAS_MEDICAMENTOS WHERE principio_ativo = ''; |
Consultas filtradas: buscando agulhas no palheiro
Encontrar dados específicos em 30 milhões de registros exige filtros inteligentes, similar a como um matemático usa equações para resolver problemas complexos:
|
1 2 3 4 5 6 7 8 9 10 11 |
""" SEMPRE use WHERE para filtrar - não tente fazer tudo de uma vez """ SELECT ANO_VENDA, MUNICIPIO_VENDA, PRINCIPIO_ATIVO, SUM(QTD_VENDIDA) as TOTAL_VENDIDO FROM trampo.vendas_medicamentos WHERE MUNICIPIO_VENDA = 'Niterói' GROUP BY ANO_VENDA, MUNICIPIO_VENDA, PRINCIPIO_ATIVO; |
Os detalhes que fazem diferença
Assim como a qualidade da farinha afeta o pão, pequenos detalhes na configuração impactam drasticamente o desempenho. Inegavelmente, o innodb_buffer_pool_size é o mais importante – ele determina quantos dados ficam na memória RAM, que é milhões de vezes mais rápida que o disco.
- innodb_buffer_pool_size: Use 50-80% da RAM disponível
- innodb_flush_log_at_trx_commit = 2: Acelera escrita mas reduz segurança em caso de queda de energia
- Índices: Como índice de livro – aceleram buscas mas desaceleram inserções
Perguntas que os iniciantes fazem
Você deve estar se perguntando: “Por que não uso configurações gigantescas desde o início?” Analogamente a como um padeiro não usa fermento em excesso, configurações muito grandes podem travar seu servidor. Comece conservador e ajuste conforme necessário.
Uma confusão comum é pensar que mais RAM sempre resolve tudo. Surpreendentemente, sem os índices corretos, é como ter uma Ferrari em um congestionamento – o poder está lá, mas você não consegue usar.
Para onde ir agora?
Comece aplicando as configurações básicas e testando com consultas pequenas. Posteriormente, monitore o desempenho usando o slow query log para identificar gargalos. Lembre-se: otimização de banco de dados é uma jornada, não um destino.
Assuntos relacionados
- Complexidade algorítmica e notação Big O
- Estatística descritiva para análise de dados
- Probabilidade e distribuições de dados
- Otimização matemática e trade-offs
- Álgebra relacional e teoria de conjuntos
Referências que valem a pena
- Documentação oficial de otimização do MySQL
- Blog da Percona – especialistas em performance MySQL
- Código fonte do MySQL no GitHub
Dominar grandes volumes de dados é como dominar a arte da panificação: requer prática, paciência e os ingredientes certos. Agora você tem a receita – mãos à obra!
