Buffer Pool no MySQL: como dimensionar corretamente (um caso real de análise de memória)

Poryanspinelli56

Recentemente enfrentei uma situação interessante em um ambiente MySQL.

O servidor possuía aproximadamente 296 GB de RAM, e o MySQL estava configurado com:

innodb_buffer_pool_size = 214 GB

Durante uma análise de infraestrutura surgiu a seguinte pergunta:

“Esse servidor realmente precisa de toda essa memória ou podemos reduzir a RAM?”

Esse tipo de questionamento é comum em ambientes grandes, principalmente quando estamos falando de servidores com centenas de gigabytes de memória.

Mas antes de simplesmente reduzir memória ou alterar o buffer pool, precisamos entender como o banco realmente está utilizando esse cache.

Neste post vou mostrar como fiz essa análise na prática.

O que é o Buffer Pool

O InnoDB Buffer Pool é a principal área de cache do MySQL.

Ele armazena em memória:

  • páginas de dados
  • páginas de índices
  • páginas modificadas (dirty pages)
  • metadados internos do InnoDB

Sempre que uma query precisa acessar dados, o MySQL primeiro verifica se aquela página já está em memória.

Se estiver, a leitura ocorre diretamente no buffer pool.

Caso contrário, o InnoDB precisa buscar a página no disco.

Quanto mais leituras forem atendidas pela memória, melhor será a performance.

O erro mais comum ao dimensionar Buffer Pool

Uma recomendação muito comum é:

Configure o buffer pool com 70% ou 80% da RAM.

Essa regra pode ser um bom ponto de partida, mas não deve ser tratada como verdade absoluta.

O MySQL não usa memória apenas no buffer pool.

Outros componentes também consomem memória:

  • conexões simultâneas
  • sort buffers
  • read buffers
  • table cache
  • performance_schema
  • memória do sistema operacional

Por isso, o dimensionamento correto precisa considerar o comportamento real do ambiente.

Como analisar o uso real do Buffer Pool

Primeiro precisamos entender quanto do buffer pool está realmente ocupado.

Podemos consultar:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';

Essas métricas mostram:

Os indicadores principais são:

  • Innodb_buffer_pool_pages_total
  • Innodb_buffer_pool_pages_data
  • Innodb_buffer_pool_pages_free
  • Innodb_buffer_pool_pages_dirty

Como cada página do InnoDB possui 16 KB, é possível calcular facilmente o uso real da memória.

Outra métrica importante: Hit Ratio

Além da ocupação do cache, também precisamos analisar a eficiência do buffer pool.

Isso é feito através do Buffer Pool Hit Ratio.

Podemos consultar:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Fórmula do hit ratio:

Hit Ratio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

Quanto maior esse valor, maior a quantidade de leituras atendidas pela memória.

Automatizando essa análise

Para facilitar essa análise, podemos usar a seguinte consulta:

SELECT
    ROUND(
        (
            1 - (
                (
                    SELECT VARIABLE_VALUE
                    FROM performance_schema.global_status
                    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
                ) /
                (
                    SELECT VARIABLE_VALUE
                    FROM performance_schema.global_status
                    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
                )
            )
        ) * 100,
        4
    ) AS hit_ratio_pct,
    ROUND(
        (
            (
                SELECT VARIABLE_VALUE
                FROM performance_schema.global_status
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'
            ) /
            (
                SELECT VARIABLE_VALUE
                FROM performance_schema.global_status
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'
            )
        ) * 100,
        2
    ) AS data_occupancy_pct,
    ROUND(
        (
            (
                SELECT VARIABLE_VALUE
                FROM performance_schema.global_status
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free'
            ) /
            (
                SELECT VARIABLE_VALUE
                FROM performance_schema.global_status
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'
            )
        ) * 100,
        2
    ) AS free_pct;

Essa consulta retorna três métricas importantes:

  • eficiência do cache
  • ocupação real do buffer pool
  • percentual de páginas livres

Resultado do ambiente analisado

Ao executar essa consulta no ambiente em questão, obtivemos:

hit_ratio_pct      : 99.9756
data_occupancy_pct : 98.59
free_pct           : 1.39

Interpretando esses números

Hit Ratio — 99.97%

Quase todas as leituras estão sendo atendidas pela memória.

Isso mostra que o buffer pool está extremamente eficiente.

Ocupação do Buffer Pool — 98.59%

Quase todo o buffer pool está ocupado com páginas de dados.

Isso indica que o banco realmente mantém um working set grande em memória.

Páginas livres — 1.39%

Existe pouca sobra de cache.

Isso indica que o buffer pool está sendo bem aproveitado.

Visualizando o cenário

Para entender melhor a relação entre memória e working set, podemos imaginar o ambiente da seguinte forma:

RAM TOTAL DO SERVIDOR
┌─────────────────────────────────────────────┐
│                                             │
│                296 GB RAM                   │
│                                             │
│   ┌─────────────────────────────────────┐   │
│   │                                     │   │
│   │         BUFFER POOL (214 GB)        │   │
│   │                                     │   │
│   │   ┌─────────────────────────────┐   │   │
│   │   │                             │   │   │
│   │   │   WORKING SET (~200 GB)     │   │   │
│   │   │                             │   │   │
│   │   └─────────────────────────────┘   │   │
│   │                                     │   │
│   └─────────────────────────────────────┘   │
│                                             │
└─────────────────────────────────────────────┘

Esse diagrama mostra que o banco realmente mantém um grande volume de dados ativos em memória.

Conclusão da análise

Ao cruzar essas três métricas, a conclusão foi clara:

  • o buffer pool não estava superdimensionado
  • o banco realmente utilizava cerca de 200 GB de dados ativos em memória

Portanto, reduzir drasticamente a memória do servidor poderia gerar:

  • aumento de leituras em disco
  • maior latência nas queries
  • aumento de I/O

Dimensionamento recomendado

Considerando o working set observado e a margem necessária para o sistema operacional e conexões, um sizing seguro seria:

Servidor entre 240 GB e 256 GB de RAM.

Reduzir para 200 GB de RAM, por exemplo, seria arriscado.

Conclusão

O tamanho do buffer pool não deve ser definido apenas por uma porcentagem da memória.

A recomendação de 70% ou 80% pode ser um ponto de partida, mas não substitui uma análise real do ambiente.

Uma análise correta deve considerar:

  • eficiência do cache
  • ocupação real do buffer pool
  • concorrência de conexões
  • comportamento do sistema operacional
  • working set do banco

Porque tuning de banco de dados não é sobre seguir regras fixas.

É sobre entender como o banco realmente está utilizando os recursos disponíveis

Deixe uma resposta