Otimize o alto uso de memória em instâncias

É comum que instâncias consumam muita memória ou apresentem problemas de falta de memória (OOM). Uma instância de banco de dados em execução com alta utilização de memória costuma causar problemas de desempenho, travamentos ou até mesmo tempo de inatividade do 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 posições de memória, são ocupadas o tempo todo e são liberadas somente 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 utilizada por essa sessão também é liberada de volta para o sistema.

Sempre que houver alto uso 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 o libere. O consumo de memória do MySQL é dividido em três partes principais:

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

Consumo de memória de threads e processos

Cada sessão de usuário consome memória dependendo das consultas em execução, dos buffers ou do cache utilizados por essa sessão, e é controlada pelos parâmetros de 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 um número N de consultas em execução em um determinado momento, cada consulta consumirá memória de acordo com esses parâmetros durante a sessão.

Consumo de memória buffer

Esta parte da memória é comum para todas as consultas e é controlada por parâmetros como Innodb_buffer_pool_size , Innodb_log_buffer_size e key_buffer_size .

Consumo de memória cache

A memória cache inclui um cache de consultas, usado para salvar as consultas e seus resultados para uma recuperação mais rápida dos dados das mesmas consultas subsequentes. Também inclui o cache de log binlog para armazenar as alterações feitas no log binário enquanto a transação está em execução, sendo controlado por binlog_cache_size .

Outro consumo de memória

A memória também é usada por operações de junção e classificação. Se suas consultas usarem operações de junção ou classificação, essas consultas usarão memória com base em join_buffer_size e sort_buffer_size .

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

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

Existem muitos instrumentos disponíveis no MySQL que você pode configurar para monitorar o uso de memória por meio do 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 memória, consulte a documentação do MySQL .

Recomendações

Use o Metrics Explorer para identificar o uso de memória

Você pode revisar o uso de memória de uma instância com a métrica database/memory/components.usage no Metrics Explorer .

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

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

Memória de instância Limite de alerta recomendado
Até 100 GB 95%
100 GB a 200 GB 96%
200 GB a 300 GB 97%
Mais de 300 GB 98%

Calcular o consumo de memória

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

Uso máximo de memória 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 )

Aqui estão os parâmetros usados ​​na fórmula:

  • innodb_buffer_pool_size : O tamanho em bytes do buffer pool, a área de memória onde o InnoDB armazena em cache 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 log no disco.
  • tmp_table_size : O tamanho máximo das tabelas temporárias internas na memória criadas pelo mecanismo de armazenamento MEMORY e, a partir do MySQL 8.0.28, pelo mecanismo de armazenamento TempTable.
  • Key_buffer_size : O tamanho do buffer usado para blocos de índice. Blocos de índice para tabelas MyISAM são armazenados em buffer e compartilhados por todas as threads.
  • Read_buffer_size : Cada thread que faz uma varredura sequencial de uma tabela MyISAM aloca um buffer desse tamanho (em bytes) para cada tabela varrida.
  • Read_rnd_buffer_size : esta variável é usada para leituras de tabelas MyISAM, para qualquer mecanismo de armazenamento e para otimização de leitura multi-intervalo.
  • Sort_buffer_size : Cada sessão que deve executar uma classificação aloca um buffer deste tamanho. sort_buffer_size não é específico para nenhum mecanismo de armazenamento e se aplica de maneira geral para otimização.
  • Join_buffer_size : O tamanho mínimo do buffer usado para varreduras de índice simples, varreduras de índice de intervalo e junções que não usam índices e, portanto, executam varreduras de tabela completas.
  • Max_connections : O número máximo permitido de conexões simultâneas de clientes.

Solucionar problemas de alto consumo de memória

  • Execute SHOW PROCESSLIST para ver as consultas em andamento que estão consumindo memória. Ele exibe todas as threads conectadas e suas instruções SQL em execução e tenta otimizá-las. Preste atenção às colunas de estado e duração.

    mysql> SHOW [FULL] PROCESSLIST;

  • Marque SHOW ENGINE INNODB STATUS na seção BUFFER POOL AND MEMORY para ver o uso atual do buffer pool e da memória, o que pode ajudar a definir o tamanho do seu buffer pool.

    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 número de tabelas temporárias, número de threads, número de caches de tabelas, páginas sujas, tabelas abertas e uso do pool de buffers.

    mysql> SHOW variables like 'VARIABLE_NAME'

Aplicar alterações

Após analisar o uso de memória por diferentes componentes, defina o sinalizador apropriado em seu banco de dados MySQL. Para alterar o sinalizador na instância do Cloud SQL para MySQL, você pode usar Google Cloud console ou gcloud CLI . Para alterar o valor do sinalizador usando o Google Cloud console, edite a seção Flags , selecione o sinalizador e insira o novo valor.

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

O que vem a seguir