Otimizar uso alto de memória em instâncias

É um problema comum que as instâncias consumam muita memória ou tenham eventos de falta de memória (OOM). Uma instância de banco de dados em execução com alta utilização de memória geralmente causa problemas de desempenho, interrupções ou até mesmo inatividade no banco de dados.

Alguns blocos de memória do MySQL são usados globalmente. Isso significa que todas as cargas de trabalho de consulta compartilham locais de memória, são ocupadas o tempo todo e liberadas apenas quando o processo do MySQL é interrompido. Alguns blocos de memória são baseados em sessão, o que significa que assim que a sessão é encerrada, a memória usada por ela também é liberada de volta para o sistema.

Sempre que houver alta utilização de memória por uma instância do Cloud SQL para MySQL, o Cloud SQL recomenda que você identifique a consulta ou o processo que está usando muita memória e libere. O consumo de memória do MySQL é dividido em três partes principais:

  • Consumo de memória de processos e threads
  • Consumo de memória do buffer
  • Consumo de memória cache

Consumo de memória de processos e threads

Cada sessão do usuário consome memória, dependendo das consultas em execução, dos buffers ou do cache usado por ela e é controlada pelos parâmetros da sessão do MySQL. Os principais parâmetros incluem:

  • thread_stack
  • net_buffer_length
  • read_buffer_size
  • read_rnd_buffer_size
  • sort_buffer_size
  • join_buffer_size
  • max_heap_table_size
  • tmp_table_size

Se houver N consultas em execução em um determinado momento, cada uma delas vai consumir memória de acordo com esses parâmetros durante a sessão.

Consumo de memória do buffer

Essa parte da memória é comum a todas as consultas e é controlada por parâmetros como innodb_buffer_pool_size, innodb_log_buffer_size e key_buffer_size.

O pool de buffers do InnoDB, configurado pela flag innodb_buffer_pool_size, ocupa uma quantidade significativa de memória na instância do Cloud SQL para MySQL e serve como um cache para melhorar o desempenho. Para reduzir o risco de eventos de falta de memória (OOM), ative o pool de buffers gerenciado.

Consumo de memória cache

A memória cache inclui um cache de consulta, que é usado para salvar as consultas e os resultados delas para uma recuperação de dados mais rápida das mesmas consultas subsequentes. Ele também inclui o cache binlog para manter as alterações feitas no registro binário enquanto a transação está em execução e é controlado por binlog_cache_size.

Outro consumo de memória

A memória também é usada por operações de mesclagem e ordenação. Se as consultas usarem operações de mesclagem ou ordenação, elas vão usar a memória com base em join_buffer_size e sort_buffer_size.

Além disso, se você ativar o esquema de desempenho, ele vai consumir memória. Para verificar o uso da memória pelo esquema de performance, use a seguinte consulta:

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

Há muitos instrumentos disponíveis no MySQL que podem ser configurados para monitorar o uso da memória pelo esquema de desempenho. Para saber mais, consulte a documentação do MySQL.

O parâmetro relacionado ao MyISAM para inserção de dados em massa é bulk_insert_buffer_size.

Para saber como o MySQL usa a memória, consulte a documentação do MySQL.

Recomendações

As seções a seguir oferecem algumas recomendações para o uso ideal da memória.

Ativar o pool de buffer gerenciado

Ativar o pool de buffer gerenciado ajuda a reduzir o consumo de memória do pool de buffer do InnoDB (ou innodb_buffer_pool_size) quando a memória da instância está alta. Essa redução libera memória que outros processos de banco de dados podem consumir. Se o uso da memória da instância for alto, ela poderá apresentar eventos de falta de memória (OOM). Recomendamos que você ative o pool de buffers gerenciado na sua instância para evitar eventos de falta de memória.

Quando o uso de memória se estabiliza em um valor mais baixo por 10 minutos ou mais, o MySQL aumenta o valor de innodb_buffer_pool_size de forma incremental até o valor original. Você também pode aumentar o valor da flag innodb_buffer_pool_size para um valor escolhido depois que o uso da memória se estabilizar.

Critérios de qualificação

Não é possível ativar o pool de buffers gerenciado para instâncias de núcleo compartilhado ou para o MySQL 5.6 ou MySQL 5.7.

Ativar o recurso

Para ativar o pool de buffers gerenciado na instância, defina a flag innodb_cloudsql_managed_buffer_pool como on. Para mais informações sobre a configuração de flags de banco de dados, consulte Definir uma flag de banco de dados.

Alterar o valor da flag innodb_cloudsql_managed_buffer_pool não exige uma reinicialização da instância do Cloud SQL.

Se você ativou o pool de buffer gerenciado e o consumo de memória da instância exceder a porcentagem de limite padrão da memória alocada, o Cloud SQL começará a reduzir o tamanho do innodb_buffer_pool_size. Essa porcentagem de limite padrão varia entre 90% e 97%, dependendo da capacidade de RAM da sua instância. Para modificar o limite, defina a flag innodb_cloudsql_managed_buffer_pool_threshold_pct como uma porcentagem diferente. Por exemplo, para ajustar o limite para 97%, use o seguinte comando:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

É possível definir a flag innodb_cloudsql_managed_buffer_pool_threshold_pct como um valor inteiro entre 50 e 99. Não é necessário reiniciar a instância do Cloud SQL para mudar o valor do limite de uso da memória.

Lógica de ajuste

O pool de buffers gerenciado não reduz o innodb_buffer_pool_size a um tamanho mínimo fixo e predeterminado. Em vez disso, ele reduz o tamanho de maneira iterativa e dinâmica até que a utilização total da memória da instância caia abaixo da porcentagem de limite configurada (innodb_cloudsql_managed_buffer_pool_threshold_pct). Ele reduz o pool de buffers ajustando o valor da flag innodb_buffer_pool_size, aproveitando a funcionalidade de redimensionamento do pool de buffers integrada do InnoDB.

Para evitar que o innodb_buffer_pool_size diminua para um tamanho que afetaria muito o desempenho quando o uso da memória permanece alto apesar da redução, o recurso usa um limite mínimo de segurança interno. Os valores representam a porcentagem da memória total da instância que precisa ser alocada para o pool de buffer.

Tamanho do contêiner do MySQL Tamanho mínimo do pool de buffer
1025 a 2048 MB 35%
2049–6528 MB 30%
6529–11315 MB 40%
11316–22630 MB 45%
Outros tamanhos (padrão) 50%

A quantidade de redução de innodb_buffer_pool_size depende da capacidade de memória da instância de banco de dados. A tabela a seguir mostra a porcentagem de diminuição para cada tamanho de contêiner:

Tamanho do contêiner do MySQL Redução percentual
1025 a 2048 MB 15%
2049–6528 MB 11%
6529–11315 MB 8%
11316–22630 MB 6%
Outros tamanhos (padrão) 5%

Depois de calcular o novo valor reduzido, o pool de buffers gerenciado arredonda innodb_buffer_pool_size para o múltiplo mais próximo dos valores innodb_buffer_pool_instances e innodb_buffer_pool_chunk_size.

Quando o pool de buffers gerenciado faz ajustes no valor de innodb_buffer_pool_size, as mudanças não são refletidas no console Google Cloud . Para ver o valor atual de innodb_buffer_pool_size quando o pool de buffers gerenciado está ativado, use o cliente MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Limitações

Reduzir o tamanho do pool de buffers não impede erros de falta de memória em todos os casos. Por exemplo, algumas cargas de trabalho podem consumir memória de maneira insustentável ou aumentar a uma taxa repentina, algumas instâncias do Cloud SQL podem estar subprovisionadas ou o pool de buffers pode não estar aquecido. O Cloud SQL pode não conseguir liberar memória rápido o suficiente para acomodar mudanças repentinas na carga de trabalho de memória. Além disso, o Cloud SQL não pode acomodar valores mal configurados de outras flags de memória.

Monitoramento

É possível monitorar o pool de buffers gerenciado no registro de erros do MySQL. No Análise de registros, filtre o registro mysql.err para entradas com o prefixo Managed Buffer Pool Plugin: ou Tuner Plugin: e encontre os eventos de ajuste mais recentes.

Quando o pool de buffers gerenciado é iniciado pela primeira vez, ele emite um registro como este:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...

Os registros a seguir mostram exemplos de diminuições automáticas no innodb_buffer_pool_size:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.

É possível configurar métricas com base em registros para acompanhar eventos de ajuste do pool de buffers gerenciado ao longo do tempo.

Usar o Metrics Explorer para identificar o uso da memória

É possível analisar o uso de memória de uma instância com a métrica database/memory/components.usage no Metrics Explorer.

Em geral, se você tiver menos de 10% de memória em database/memory/components.cache e database/memory/components.free combinados, o risco de um evento OOM será alto. Para monitorar o uso da memória e evitar eventos OOM, recomendamos que você configure uma política de alertas com uma condição de limite de métrica em database/memory/components.usage.

A seguinte tabela mostra a relação entre a memória da instância e o limite de alertas recomendado:

Memória da instância Limite de alertas recomendado
Menor ou igual a 16 GB 90%
Mais de 16 GB 95%

Calcular o consumo de memória

Calcule o uso máximo da memória pelo seu banco de dados MySQL para selecionar o tipo de instância adequado. Use a seguinte fórmula:

Uso máximo de memória do MySQL = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)

Estes são os parâmetros usados na fórmula:

  • innodb_buffer_pool_size: o tamanho em bytes do pool de buffer, a área de memória em que o InnoDB armazena em cache os dados de tabela e índice.
  • innodb_additional_mem_pool_size: o tamanho em bytes de um pool de memória que o InnoDB usa para armazenar informações do dicionário de dados e outras estruturas de dados internas.
  • innodb_log_buffer_size: o tamanho em bytes do buffer que o InnoDB usa para gravar nos arquivos de registro no disco.
  • tmp_table_size: o tamanho máximo das tabelas temporárias na memória criadas pelo mecanismo de armazenamento MEMORY e pelo TempTable a partir do MySQL 8.0.28.
  • key_buffer_size: o tamanho do buffer usado para blocos de índice. Os blocos de índice para tabelas MyISAM são armazenados em buffer e compartilhados por todas as linhas de execução.
  • read_buffer_size: cada linha de execução que faz uma verificação sequencial de uma tabela MyISAM aloca um buffer desse tamanho (em bytes) para cada tabela verificada.
  • read_rnd_buffer_size: essa variável é usada para leituras de tabelas MyISAM, para qualquer mecanismo de armazenamento e para otimização de leitura de vários intervalos.
  • sort_buffer_size: cada sessão que precisa realizar uma classificação aloca um buffer desse tamanho. sort_buffer_size não é específico de nenhum mecanismo de armazenamento e se aplica de maneira geral para otimização.
  • join_buffer_size: o tamanho mínimo do buffer usado para verificações de índice simples, verificações de índice de intervalo e junções que não usam índices e, portanto, realizam verificações completas da tabela.
  • max_connections: o número máximo permitido de conexões simultâneas de clientes.

Resolver problemas de alto consumo de memória

  • Execute SHOW PROCESSLIST para conferir as consultas em andamento que estão consumindo memória. Ele mostra todas as linhas de execução conectadas e as instruções SQL em execução, além de tentar otimizá-las. Veja com atenção as colunas Estado e Duração.

    mysql> SHOW [FULL] PROCESSLIST;

  • Verifique SHOW ENGINE INNODB STATUS na seção BUFFER POOL AND MEMORY para conferir o pool de buffers e o uso de memória atuais, o que pode ajudar você a definir o tamanho do pool de buffers.

    mysql> SHOW ENGINE INNODB STATUS \G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 398063986; in additional pool allocated 0
Dictionary memory allocated 12056
Buffer pool size 89129
Free buffers 45671
Database pages 1367
Old database pages 0
Modified db pages 0

  • Use o comando SHOW variables do MySQL para verificar os valores do contador, que fornecem informações como o número de tabelas temporárias, o número de linhas de execução, o número de caches de tabela, páginas sujas, tabelas abertas e uso do pool de buffer.

    mysql> SHOW variables like 'VARIABLE_NAME'

Aplicar alterações

Depois de analisar o uso da memória por diferentes componentes, defina o flag apropriado no banco de dados MySQL. Para mudar o flag na instância do Cloud SQL para MySQL, use o console do Google Cloud ou a CLI gcloud. Para mudar o valor da flag usando o console do Google Cloud , edite a seção Flags, selecione a flag e digite o novo valor.

Por fim, se o uso de memória ainda estiver alto e você achar que as consultas e os valores dos flags estão otimizados, considere aumentar o tamanho da instância para evitar OOM.

A seguir