É um problema comum ter instâncias a consumir muita memória ou a deparar-se com eventos de falta de memória (OOM). Uma instância de base de dados em execução com uma utilização elevada de memória causa frequentemente problemas de desempenho, paragens ou até mesmo indisponibilidade da base de dados.
Alguns blocos de memória do MySQL são usados globalmente. Isto significa que todas as cargas de trabalho de consultas partilham localizações de memória, estão ocupadas sempre e só são libertadas quando o processo do MySQL é interrompido. Alguns blocos de memória baseiam-se na sessão, o que significa que, assim que a sessão é fechada, a memória usada por essa sessão também é libertada para o sistema.
Sempre que houver uma utilização elevada de memória por parte de uma instância do Cloud SQL for MySQL, o Cloud SQL recomenda que identifique a consulta ou o processo que está a usar muita memória e a liberte. O consumo de memória do MySQL está dividido em três partes principais:
- Threads e consumo de memória de processos
- Consumo de memória do buffer
- Consumo de memória da cache
Threads e consumo de memória de processos
Cada sessão de utilizador consome memória consoante as consultas em execução, os buffers ou a cache usados por essa sessão e é controlada pelos parâmetros de sessão do MySQL. Os principais parâmetros incluem:
thread_stacknet_buffer_lengthread_buffer_sizeread_rnd_buffer_sizesort_buffer_sizejoin_buffer_sizemax_heap_table_sizetmp_table_size
Se houver N consultas em execução num determinado momento, cada consulta consome memória de acordo com estes parâmetros durante a sessão.
Consumo de memória do buffer
Esta 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.
Consumo de memória da cache
A memória de cache inclui uma cache de consultas, que é usada para guardar as consultas e os respetivos
resultados para uma obtenção de dados mais rápida das mesmas consultas subsequentes. Também inclui a cache binlog para reter as alterações feitas ao registo binário enquanto a transação está em execução e é controlada por binlog_cache_size.
Outro consumo de memória
A memória também é usada por operações de junção e ordenação. Se as suas consultas usarem operações de junção ou ordenação, essas consultas usam memória com base em join_buffer_size e sort_buffer_size.
Além disso, se ativar o esquema de desempenho, este consome memória. Para verificar a utilização 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 pode configurar para monitorizar a utilização de memória através do esquema de desempenho. Para saber mais, consulte a documentação do MySQL.
O parâmetro relacionado com o MyISAM para a 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 secções seguintes oferecem algumas recomendações para uma utilização ideal da memória.
Use o Explorador de métricas para identificar a utilização de memória
Pode rever a utilização de memória de uma instância com a métrica database/memory/components.usage no Explorador de métricas.
Em geral, se tiver menos de 10% de memória em database/memory/components.cache e database/memory/components.free combinados, o risco de um evento OOM é elevado.
Para monitorizar a utilização de memória e evitar eventos de falta de memória,
recomendamos que configure uma política de alertas
com uma condição de limite métrico no database/memory/components.usage.
A tabela seguinte mostra a relação entre a memória da instância e o limite de alerta recomendado:
| Memória da instância | Limite de alerta recomendado |
|---|---|
| Inferior ou igual a 16 GB | 90% |
| Mais de 16 GB | 95% |
Calcule o consumo de memória
Calcule a utilização máxima de memória pela sua base de dados MySQL para selecionar o tipo de instância adequado para a sua base de dados MySQL. Use a seguinte fórmula:
Utilização máxima 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)
Seguem-se os parâmetros usados na fórmula:
innodb_buffer_pool_size: o tamanho em bytes do conjunto de buffers, a área de memória onde o InnoDB armazena em cache os dados de tabelas e índices.innodb_additional_mem_pool_size: o tamanho em bytes de um conjunto 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 da memória intermédia que o InnoDB usa para escrever nos ficheiros de registo no disco.tmp_table_size: o tamanho máximo das tabelas temporárias internas na memória criadas pelo motor de armazenamento MEMORY e, a partir do MySQL 8.0.28, pelo motor de armazenamento TempTable.Key_buffer_size: o tamanho da memória intermédia usada para blocos de índice. Os blocos de índice para tabelas MyISAM são armazenados em buffer e são partilhados por todos os threads.Read_buffer_size: cada thread que faz uma análise sequencial de uma tabela MyISAM atribui um buffer deste tamanho (em bytes) para cada tabela que analisa.Read_rnd_buffer_size: esta variável é usada para leituras de tabelas MyISAM, para qualquer motor de armazenamento e para a otimização de leitura de vários intervalos.Sort_buffer_size: cada sessão que tem de executar uma ordenação atribui um buffer deste tamanho. sort_buffer_size não é específico de nenhum motor de armazenamento e aplica-se de forma geral para otimização.Join_buffer_size: o tamanho mínimo da memória intermédia usada para análises simples de índices, análises de índices de intervalo e junções que não usam índices e, por isso, executam análises completas de tabelas.Max_connections: o número máximo permitido de ligações de clientes simultâneas.
Resolva problemas de consumo elevado de memória
Execute
SHOW PROCESSLISTpara ver as consultas em curso que estão a consumir memória. Apresenta todas as threads ligadas e as respetivas declarações SQL em execução, e tenta otimizá-las. Preste atenção às colunas de estado e duração.mysql> SHOW [FULL] PROCESSLIST;Consulte
SHOW ENGINE INNODB STATUSna secçãoBUFFER POOL AND MEMORYpara ver a utilização atual da memória e do conjunto de buffers, o que pode ajudar a definir o tamanho do conjunto 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 0Use o comando
SHOW variablesdo MySQL para verificar os valores do contador, que lhe dão informações como o número de tabelas temporárias, o número de threads, o número de caches de tabelas, as páginas sujas, as tabelas abertas e a utilização do conjunto de buffers.mysql> SHOW variables like 'VARIABLE_NAME'
Aplicar alterações
Depois de analisar a utilização de memória por diferentes componentes, defina a flag adequada na sua base de dados MySQL. Para alterar a flag na instância do Cloud SQL para MySQL, pode usar a Trusted Cloud consola ou a CLI gcloud. Para alterar o valor da flag através da Trusted Cloud consola, edite a secção Flags, selecione a flag e introduza o novo valor.
Por último, se a utilização de memória continuar elevada e considerar que a execução de consultas e os valores de flags estão otimizados, pondere aumentar o tamanho da instância para evitar o erro OOM.