Ajuste de MySQL no Magento 2: configurações e queries lentas

A maior parte do trabalho de performance no Magento 2 começa na aplicação, e é por aí mesmo que se deve começar. Mesmo assim, uma loja com o banco mal configurado continua lenta por melhor que seja o código. Os indexers levam horas, o grid do admin trava, o checkout fica esperando lock. Este artigo cobre as configurações de MySQL e MariaDB que importam para o Magento Open Source e o Adobe Commerce 2.4.8, como achar as queries que realmente machucam e alguns requisitos específicos do Magento que passam batido fácil.

Primeiro, as versões suportadas

Antes de ajustar qualquer coisa, confira se a versão do banco é uma que o Magento 2.4.8 suporta. Os requisitos de sistema do 2.4.8 listam MySQL 8.4 e MariaDB 11.4. As versões 2.4 mais antigas suportavam MySQL 8.0 e versões anteriores do MariaDB, então depois de um upgrade o banco muitas vezes continua na versão velha. Rodar uma versão não suportada pode funcionar por um tempo e depois falhar de formas estranhas durante o setup:upgrade ou sob carga. Consulte a página oficial de requisitos de sistema para ver a lista exata da release que você usa.

Garanta também que o servidor de banco não divide memória com todo o resto. Em setups pequenos MySQL, PHP-FPM, Redis, OpenSearch e Varnish costumam rodar na mesma máquina, e cada um é configurado como se tivesse o servidor inteiro só para ele. Ajustar o MySQL nessa situação significa primeiro decidir quanta RAM ele realmente recebe.

O buffer pool do InnoDB

A configuração mais importante é o innodb_buffer_pool_size. O InnoDB guarda páginas de dados e de índices nessa área de memória. Se o working set da loja cabe nela, a maioria das leituras nunca toca o disco.

Em um servidor de banco dedicado, o ponto de partida usual é cerca de 70 por cento da RAM. Em um servidor compartilhado, dimensione a partir dos dados reais. Esta query mostra o tamanho dos dados e dos índices:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_gb DESC;

Se o seu schema do Magento tem 6 GB, um buffer pool de 8 GB deixa espaço para crescer. Um buffer pool de 1 GB significa que o InnoDB lê do disco o dia inteiro. Lembre que boa parte de um banco Magento típico são tabelas de log e de histórico que quase nunca são lidas, então o working set costuma ser menor que o total. Limpar essas tabelas, assunto mais abaixo, também reduz o que o buffer pool precisa guardar.

Dá para acompanhar o desempenho do buffer pool com:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads conta as leituras que precisaram ir ao disco. Innodb_buffer_pool_read_requests conta todas as leituras lógicas. O primeiro número deve ser uma fração minúscula do segundo.

Uma configuração inicial

Este é um ponto de partida razoável para um servidor MySQL 8.4 dedicado com 16 GB de RAM rodando uma loja Magento. Trate cada número como um valor inicial para medir, não como recomendação para o seu servidor.

[mysqld]
innodb_buffer_pool_size = 11G
innodb_redo_log_capacity = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_file_per_table = 1

max_connections = 300
table_open_cache = 4000
table_definition_cache = 4000
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_slow_admin_statements = 1

log_bin_trust_function_creators = 1
binlog_expire_logs_seconds = 259200

Algumas notas sobre as linhas menos óbvias.

innodb_redo_log_capacity

No MySQL 8.0.30 e posteriores, o innodb_redo_log_capacity substitui o antigo par innodb_log_file_size e innodb_log_files_in_group. Um redo log pequeno demais força checkpoints frequentes durante escritas grandes, o que você sente em importações e reindexações completas. No MariaDB a configuração continua sendo innodb_log_file_size.

innodb_flush_log_at_trx_commit

O valor padrão 1 faz flush do redo log no disco a cada commit. Com 2, ele escreve no commit mas faz flush cerca de uma vez por segundo. Você pode perder até um segundo de transações se o sistema operacional cair, não só o MySQL. Muitas lojas aceitam essa troca pela velocidade de escrita, principalmente em discos lentos. Se você não aceita perda nenhuma, mantenha em 1 e compre um storage mais rápido.

tmp_table_size e max_heap_table_size

Queries do Magento com GROUP BY, ordenação sobre tabelas EAV com join e agregações da navegação em camadas criam tabelas temporárias internas. Quando passam desses limites, elas vão para o disco. Compare Created_tmp_disk_tables com Created_tmp_tables no global status. Aumente os dois valores juntos, já que o menor vence, mas não coloque valores enormes. Eles valem por tabela temporária, e muitas conexões podem usá-las ao mesmo tempo.

max_connections

Cada worker do PHP-FPM, processo de cron e consumer de queue pode segurar uma conexão. Some tudo. Se o pm.max_children somado em todos os nós web é 200 e você roda consumers e cron, 300 é um teto sensato. Colocar vários milhares não ajuda. Só deixa o servidor aceitar mais trabalho do que consegue atender.

Sem query cache

O query cache foi removido no MySQL 8.0, então configs antigas com query_cache_size não sobem no MySQL 8.4. No MariaDB ele ainda existe, mas deixe desligado. O Magento escreve o tempo todo, e cada escrita em uma tabela invalida as queries dela em cache, então o cache acaba só aumentando a contenção de lock.

Binary logs e triggers

Com o binary log ligado, que é o padrão no MySQL 8, criar triggers exige o privilégio SUPER ou log_bin_trust_function_creators = 1. O Magento cria triggers para cada indexer que roda no modo Update by Schedule. Sem essa configuração, passar os indexers para o modo schedule ou rodar o setup:upgrade falha com erro de privilégio em bancos hospedados onde você não recebe SUPER.

Os binary logs também crescem. Se você não usa replicação nem recuperação point in time, pode desligá-los. Se usa, defina o binlog_expire_logs_seconds para eles não encherem o disco. Disco cheio no servidor de banco é um dos motivos mais comuns de uma loja Magento cair sem nenhuma mudança de código.

Ache as queries lentas

As configurações do servidor mexem na linha de base. Os ganhos de verdade estão nas queries lentas. Com o slow query log ligado, use o pt-query-digest do Percona Toolkit para agrupar o log por padrão de query:

pt-query-digest /var/log/mysql/slow.log --limit 20 > /tmp/slow-report.txt

O relatório ordena os padrões de query pelo tempo total, que é o que você quer. Uma query de 200 ms que roda 50.000 vezes por dia importa mais que um relatório de 30 segundos que roda uma vez.

Para cada query do topo, rode EXPLAIN e procure full table scans e filesorts em tabelas grandes. No Magento, os suspeitos de sempre são:

• Código customizado filtrando uma collection por atributo ou coluna sem índice.
• Extensões de terceiros que consultam sales_order ou quote com condições em colunas sem índice.
• Collections carregadas sem page size, puxando milhares de linhas para o PHP.
• Queries com LIKE '%term%' em colunas de texto grandes.

Quando a resposta é um índice que falta, adicione pelo db_schema.xml do seu módulo, não na mão no servidor. Um índice manual some dos seus outros ambientes e pode conflitar com o schema declarativo depois. Veja o guia de declarative schema para a sintaxe.

Mantenha as tabelas enxutas

Boa parte do peso de um banco Magento é dado que ninguém lê. Pontos comuns de crescimento:

• quote e quote_item: carrinhos antigos de visitantes e clientes. O cron do Magento para quotes expirados limpa essas tabelas com base na configuração de tempo de vida do quote em Sales, Checkout, Shopping Cart. Confirme que o cron realmente roda.
• customer_visitor e as tabelas report_*: dados de visitantes e de relatórios. O Magento limpa linhas antigas de visitantes com um cron, então de novo isso depende do cron estar rodando. As tabelas de relatório podem ser esvaziadas se ninguém usa os relatórios nativos.
• Tabelas de changelog dos indexers terminadas em _cl: crescem quando os indexers estão em modo schedule e o grupo de cron do index não está processando.
• Tabelas de log de extensões de terceiros que gravam no banco cada chamada de API ou cada e-mail.

Antes de apagar qualquer coisa na mão, faça backup e confira se a tabela é usada por relatórios ou integrações. Dar truncate na tabela errada é um jeito rápido de quebrar a sincronização com o ERP.

Meça, mude uma coisa, meça de novo

O ajuste de MySQL dá errado quando a pessoa copia um arquivo de config de um fórum e muda vinte valores de uma vez. Um ciclo melhor:

1. Registre uma linha de base: resumo do slow log, hit ratio do buffer pool, tabelas temporárias em disco e tempos de resposta do seu APM.
2. Mude uma configuração ou adicione um índice.
3. Deixe rodar com tráfego real por um dia, ou pelo menos algumas horas.
4. Compare com a linha de base e mantenha ou reverta.

Ferramentas como o MySQLTuner dão uma lista útil de dicas, mas leia com senso crítico. Elas não sabem quais queries importam para a sua loja.

Para o lado da aplicação do mesmo problema, veja o checklist de páginas de categoria lentas e o guia de profiling com New Relic, que mostra quais comandos SQL dominam cada transação.

FAQ

Qual o tamanho do innodb_buffer_pool_size para o Magento 2?

Em um servidor de banco dedicado, comece em torno de 70 por cento da RAM. Em um servidor compartilhado, dimensione pelo tamanho real de dados e índices do schema do Magento mais uma folga para crescer.

Por que o Magento precisa do log_bin_trust_function_creators?

Os indexers em modo Update by Schedule usam triggers no banco. Com o binary log ligado, criar triggers exige SUPER ou essa configuração, e bancos hospedados normalmente não dão SUPER.

Qual versão do MySQL o Magento 2.4.8 suporta?

Os requisitos de sistema do 2.4.8 listam MySQL 8.4 e MariaDB 11.4. Confira a página oficial de requisitos da sua release antes de atualizar o banco.

innodb_flush_log_at_trx_commit = 2 é seguro para uma loja?

Pode perder até cerca de um segundo de transações se o servidor cair. Muitas lojas aceitam isso por escritas mais rápidas, mas se você não aceita perda nenhuma, mantenha o padrão 1.

Atendo como freelancer em Magento e Adobe Commerce, incluindo revisões de performance de banco de dados. Se precisar de ajuda com isso, fale comigo.