É comum ter instâncias que consomem muita memória ou que têm 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é 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 sempre ocupadas 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 do processo
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 número de 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.
O conjunto de buffers do InnoDB, que é configurado pela flag innodb_buffer_pool_size, ocupa uma quantidade significativa de memória na sua instância do Cloud SQL para MySQL e serve como uma cache para melhorar o desempenho. Para reduzir o risco de eventos de
falta de memória (OOM), pode
ativar o conjunto de buffers gerido
(pré-visualização).
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.
Ative o conjunto de buffers gerido
Se a utilização de memória for elevada, a sua instância pode ter eventos de falta de memória (OOM). Para ajudar a evitar eventos de falta de memória,
ative o conjunto de buffers gerido para reduzir o valor de
innodb_buffer_pool_size e libertar memória.
Quando a utilização de memória estabiliza num valor inferior, o MySQL aumenta o valor de innodb_buffer_pool_size de forma incremental até ao valor original.
Não pode ativar o buffer pool gerido para instâncias de núcleo partilhado nem para o MySQL 5.6 ou o MySQL 5.7.
Para ativar o buffer pool gerido para a sua instância, defina a flag innodb_cloudsql_managed_buffer_pool como on. Para mais informações
sobre como definir flags de base de dados, consulte o artigo Defina uma flag de base de dados.
A alteração do valor da flag innodb_cloudsql_managed_buffer_pool não requer o reinício da instância do Cloud SQL.
Por predefinição, se a sua base de dados MySQL exceder 95% da memória alocada, o Cloud SQL começa a reduzir o tamanho do respetivo innodb_buffer_pool_size. Para modificar o limite de 95%, defina a flag innodb_cloudsql_managed_buffer_pool_threshold_pct para um valor percentual 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
Pode definir a flag innodb_cloudsql_managed_buffer_pool_threshold_pct para um valor inteiro entre 50 e 99. A alteração do valor do limite de utilização de memória não requer o reinício da instância do Cloud SQL.
A redução do tamanho do conjunto de buffers não pode impedir os erros de falta de memória em todos os casos. Por exemplo, algumas cargas de trabalho podem consumir memória de forma insustentável ou aumentar a uma taxa súbita, algumas instâncias do Cloud SQL podem ter um aprovisionamento insuficiente ou o conjunto de buffers pode não estar aquecido. O Cloud SQL pode não conseguir libertar memória com rapidez suficiente para acomodar alterações súbitas na carga de trabalho da memória. Além disso, o Cloud SQL não consegue acomodar valores mal configurados de outras flags de 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 das tabelas MyISAM são armazenados em buffer e 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;Verifique
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 buffer pool.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 Google Cloud consola ou a CLI gcloud. Para alterar o valor da flag através da Google 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.