Tuning MySQL e MariaDB para sites: como calcular innodb_buffer_pool_size e max_connections, ativar o slow query log e usar o MySQLTuner.

Para que serve

A configuração padrão do MySQL e do MariaDB é pensada para máquinas pequenas e usa uma fração mínima da memória de um servidor dedicado. Poucos ajustes, sobretudo no buffer do InnoDB, costumam trazer o maior ganho. Este guia de tuning MySQL (vale também para o MariaDB) mostra quais parâmetros mexer, como calcular os valores e como encontrar as consultas lentas, que são a causa real da maioria dos problemas de desempenho.

Pré-requisitos

Passo a passo do tuning MySQL e MariaDB

1. Veja o ponto de partida

mariadb -e "SELECT VERSION();
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT ROUND(SUM(data_length+index_length)/1024/1024) AS total_mb FROM information_schema.tables;
SELECT engine, COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema') GROUP BY engine;"

No MySQL, troque mariadb por mysql. Se aparecerem tabelas MyISAM em sites, considere convertê-las para InnoDB (ALTER TABLE nome ENGINE=InnoDB;, com backup e fora do horário de pico): o MyISAM trava a tabela inteira a cada escrita e não se recupera bem de desligamentos abruptos.

2. Crie um arquivo de configuração próprio

Em vez de editar os arquivos do pacote, crie um que seja lido por último:

  • MariaDB (Debian/Ubuntu): /etc/mysql/mariadb.conf.d/99-tuning.cnf
  • MySQL (Ubuntu): /etc/mysql/mysql.conf.d/99-tuning.cnf
  • AlmaLinux/Rocky: /etc/my.cnf.d/99-tuning.cnf

Exemplo para uma VM com 16 GB de RAM dedicada a banco e aplicação web:

[mysqld]
# Memória principal do InnoDB: dados e índices em cache
innodb_buffer_pool_size = 8G

# Redo log maior suaviza picos de escrita
innodb_log_file_size = 1G           # MariaDB
# innodb_redo_log_capacity = 2G     # MySQL 8.0.30 ou superior (use no lugar da linha acima)

# Disco NVMe aguenta mais operações de I/O que o padrão supõe
innodb_io_capacity = 2000

# Conexões e tabelas temporárias
max_connections = 200
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4000

# Consultas lentas
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

3. Como escolher cada valor

  • innodb_buffer_pool_size: o ajuste mais importante. Em servidor só de banco, de 60% a 75% da RAM. Em servidor que roda banco e sites juntos, some a memória do PHP-FPM (pm.max_children × memória por processo), do Redis e do sistema, e dê ao buffer o que sobrar, sem passar do tamanho total dos dados medido no passo 1 com uma folga para crescimento.
  • max_connections: não aumente "por garantia". Cada conexão reserva memória própria. Para sites PHP, o número realista é próximo da soma dos pm.max_children de todos os pools que usam o banco. Confira o pico real com SHOW GLOBAL STATUS LIKE 'Max_used_connections';.
  • tmp_table_size e max_heap_table_size: devem ter o mesmo valor. Se Created_tmp_disk_tables crescer muito em relação a Created_tmp_tables, aumente com moderação, porque o valor vale por consulta.
  • innodb_flush_log_at_trx_commit: deixe no padrão (1) para ERPs, lojas e qualquer sistema financeiro. Valores 0 ou 2 aceleram escritas, mas podem perder o último segundo de transações numa queda.
  • Query cache: foi removido do MySQL 8 e vem desligado no MariaDB. Não ative; em sites com escrita constante, ele mais atrapalha do que ajuda. Use cache na aplicação (Redis).

4. Aplique e reinicie

mkdir -p /var/log/mysql && chown mysql:mysql /var/log/mysql
systemctl restart mariadb      # ou: systemctl restart mysql
systemctl status mariadb --no-pager
Reiniciar o banco interrompe as aplicações por alguns segundos. Faça fora do horário de pico. Se o serviço não subir, o motivo estará em journalctl -u mariadb -n 50; corrija ou remova o 99-tuning.cnf e inicie de novo.

5. Encontre as consultas lentas

Depois de um ou dois dias com o log de consultas lentas ativo, resuma o que mais pesa:

apt install -y percona-toolkit
pt-query-digest /var/log/mysql/slow.log | head -n 80

O relatório agrupa consultas parecidas e ordena pelo tempo total gasto. Para a principal da lista, rode EXPLAIN seguido da consulta: se aparecer type: ALL numa tabela grande, falta índice. Em WordPress, a culpada costuma ser a tabela wp_options com muitos registros autoload, ou plugins que consultam wp_postmeta sem índice.

Para ir além deste resumo, veja também os artigos «MySQL lento: como achar as consultas culpadas com slow query log e EXPLAIN» e «Como criar índices no MySQL e no PostgreSQL e achar os que faltam».

6. Use o MySQLTuner como segunda opinião

apt install -y mysqltuner
mysqltuner

Rode após pelo menos 24 horas de uso normal, para que as estatísticas sejam representativas. Trate as sugestões como pistas, não como ordens: aplique uma de cada vez e meça.

Como saber se funcionou

  • Confirme o valor aplicado: mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS gb;"
  • Taxa de acerto do buffer pool: compare Innodb_buffer_pool_reads (leituras do disco) com Innodb_buffer_pool_read_requests em SHOW GLOBAL STATUS. As leituras do disco devem ser uma fração mínima do total.
  • O tempo de resposta do site (e o número de entradas no slow log) cai depois de corrigir as consultas apontadas.
  • free -h ainda mostra memória disponível e não há uso intenso de swap.

Problemas comuns

  • Banco não inicia após a alteração: erro de digitação ou valor incompatível com a versão (por exemplo, innodb_redo_log_capacity no MariaDB). Veja o journal.
  • Servidor passou a usar swap ou o banco foi encerrado por OOM: o buffer pool ficou grande demais para o que mais roda na máquina. Reduza.
  • "Too many connections": antes de subir max_connections, procure conexões presas com SHOW FULL PROCESSLIST;. Normalmente é uma consulta lenta segurando as demais.
  • Banco em VM no Proxmox lento em escrita: use disco VirtIO SCSI no armazenamento NVMe, sem limitação de I/O na VM, e confira se o host não está com o disco saturado (iostat -x 1). Veja também o artigo «Banco de dados em VM no Proxmox: disco, cache e ZFS (volblocksize, recordsize e sync)».

Perguntas frequentes

Qual o valor ideal de innodb_buffer_pool_size?

Em servidor só de banco, de 60% a 75% da RAM. Quando banco e sites dividem a máquina, desconte a memória do PHP-FPM, do Redis e do sistema e use o que sobrar, sem passar muito do tamanho total dos dados.

Preciso reiniciar o MySQL para mudar o innodb_buffer_pool_size?

No MySQL 8 e no MariaDB atuais, o valor pode ser alterado em execução com SET GLOBAL, mas grave-o também no 99-tuning.cnf para valer após reiniciar. Outros parâmetros, como o tamanho do redo log no MariaDB, exigem reinício.

Devo ativar o query cache?

Não. Ele foi removido do MySQL 8 e vem desligado no MariaDB. Em sites com escrita constante, atrapalha mais do que ajuda; use cache na aplicação, como o Redis.

O MySQLTuner é confiável?

É uma boa segunda opinião, desde que rode após pelo menos 24 horas de uso normal. Aplique uma sugestão de cada vez e meça o resultado.

O tuning do MySQL e do MariaDB é igual?

Quase. Os parâmetros do InnoDB são os mesmos, mas há diferenças pontuais: o MySQL 8.0.30 ou superior usa innodb_redo_log_capacity, enquanto o MariaDB usa innodb_log_file_size.

Leitura complementar

Precisa de ajuda?

Se ficar com alguma dúvida, abra um ticket na área do cliente ou fale com o suporte pelo WhatsApp.

Esta resposta lhe foi útil? 0 Usuários acharam útil (0 Votos)

Leia também