127x sem trocar uma linha de código

O que eu encontrei

Em agosto um pipeline de ocupação quebrou e eu passei alguns dias dentro dele. Quase tudo isso é outra história. Um dos achados laterais foi o próprio banco: um MySQL com uns 2 GB de dados lidos o dia inteiro e o innodb_buffer_pool_size em 128 MB, o valor que vem na instalação. Isso é muito comum. O servidor funciona, ninguém reclama, e o padrão fica lá por anos.

Com um pool desse tamanho, a maior parte das leituras ia para o disco: uns 12 milhões de leituras de disco por dia. Uma consulta que o time usava todo dia levava uns sete minutos e estourava o timeout.

Confira o seu

Duas consultas dizem se você está no mesmo lugar. A primeira mostra o tamanho dos dados por schema; a segunda, com que frequência o InnoDB precisou ir ao disco:

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

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS pool_mb,
       1 - r.v / q.v AS hit_ratio
FROM (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
     (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') q;

Se o pool for muito menor que o volume lido no dia a dia, comece por ele. Os contadores acumulam desde o último reinício, então, se quiser a taxa atual, olhe duas vezes com alguns minutos de intervalo.

Três ondas

Mudei seis parâmetros, e para mim a ordem importa mais que os valores, porque cada grupo tem um dono diferente para o risco.

-- onda 1: com o banco no ar, nada a perder
SET PERSIST innodb_buffer_pool_size  = 4294967296;  -- 128 MB -> 4 GB, dois dias depois 8 GB
SET PERSIST innodb_redo_log_capacity = 1073741824;  -- 100 MB -> 1 GB (8.0.30+)
SET PERSIST innodb_io_capacity       = 1000;        -- padrão 200
SET PERSIST innodb_io_capacity_max   = 4000;        -- padrão 2000

-- onda 2: troca durabilidade por velocidade, precisa do ok do negócio
SET PERSIST innodb_flush_log_at_trx_commit = 2;     -- padrão 1
SET PERSIST sync_binlog                    = 100;   -- padrão 1

-- onda 3: só vale depois do reinício
SET PERSIST_ONLY innodb_flush_method = 'O_DIRECT';

A onda 1 não tem contrapartida num servidor com RAM sobrando. O buffer pool muda de tamanho com o banco no ar desde o 5.7; leva um tempo em segundo plano, e dá para acompanhar em Innodb_buffer_pool_resize_status.

A onda 2 é outra conversa. Com innodb_flush_log_at_trx_commit = 2, o commit entrega o log ao sistema operacional e a gravação em disco acontece mais ou menos uma vez por segundo. Se só o mysqld cair, não se perde nada; se a máquina perder energia, dá para perder até cerca de um segundo de commits. sync_binlog = 100 é a mesma ideia para o binlog, até 100 transações. Aqui as tabelas são recalculadas pelo pipeline a cada ciclo, então um segundo perdido custa um ciclo. Mesmo assim, esse tipo de decisão não cabe a quem opera o banco sozinho: perguntei a quem responde pelo negócio, e a resposta foi sim. O servidor fazia umas 267 transações pequenas por segundo em autocommit, e os fsyncs caíram de ~500/s para ~4/s.

Por último vem o O_DIRECT, para o InnoDB parar de guardar as mesmas páginas duas vezes (no pool e no cache do sistema operacional). Ele exige reinício, então deixei armado com PERSIST_ONLY e esperei uma janela.

O reinício

Domingo, depois da meia-noite, com o cron do pipeline pausado. O MySQL voltou em 11 segundos, e voltou somente leitura. Esse eu esperava, estava no checklist: algumas configurações persistidas de um arranjo antigo de replicação (super_read_only e group replication no boot) continuavam lá. Antes de qualquer reinício, vale olhar o que está persistido:

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables;

Eu tinha os dois comandos prontos, SET GLOBAL super_read_only = OFF e SET GLOBAL read_only = OFF, e a escrita ficou parada por uns 50 segundos. Não removi essas configurações persistidas porque elas são do time de infraestrutura; deixei registrado que qualquer reinício futuro repete o mesmo efeito, e como sair dele.

O número honesto

Sete minutos para 3,3 segundos dá 127x. Só que, nos mesmos dias, removi cerca de 1,66 milhão de linhas de lixo que o pipeline quebrado tinha deixado, e aquela tabela ficou 40% menor. Não consigo separar com exatidão quanto veio do tuning e quanto veio da limpeza. Os outros números são só do tuning: hit ratio de 99,99%, o disco passou de milhões de leituras por dia para quase só escrita, e o servidor aguentou uns 3.900 inserts por segundo onde antes o teto ficava entre 500 e 1.000.

Esses parâmetros não saíram da documentação. Saíram dos anos em que eu cuidava de uns 60 servidores próprios. Eu ditei para o agente, que executou e conferiu um por um. Naquela época eu costumava dar ao pool algo como 75% da RAM; aqui parei em 8 GB, porque para esses dados já bastava.

Arquivado pelo problema que resolve

• mysql
• innodb
• buffer pool
• tuning de performance
• durabilidade
• operação