Como o MySQL usa a memória

O MySQL aloca buffers globais na inicialização do servidor e eles são compartilhados entre todas as conexões. A maior parte da memória do MySQL é consumida pelos buffers globais, por exemplo, innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size etc.

O pool de buffer do InnoDB normalmente é o maior consumidor de memória em uma instância do MySQL. A configuração dele é feita usando o parâmetro innodb_buffer_pool_size. Ele é usado para armazenar em cache os dados e índices da tabela, o buffer de alterações, o índice de hash adaptável e outras estruturas internas. O Cloud SQL para MySQL, a oferta gerenciada do Google Cloud para MySQL, configura innodb_buffer_pool_size até 72% da memória da instância, dependendo do tamanho dela. 

O InnoDB reserva mais memória para buffers e estruturas de dados associadas. O total de memória alocada é aproximadamente 10% maior do que o tamanho especificado do pool de buffer. É possível verificar o uso da memória do pool de buffer do InnoDB na saída mostrar status do Innodb do mecanismo\G.

mysql> mostrar status do mecanismo do Innodb\G

…

----------------------

POOL E MEMÓRIA DE BUFFER

----------------------

Total de memória grande alocada: 11511349248

…

O buffer de registro do InnoDB é usado para armazenar as alterações a serem gravadas nos arquivos de registro redo do InnoDB no disco. Ele é configurado usando innodb_log_buffer_size. O valor padrão da comunidade do MySQL é 16 MB, e o Cloud SQL para MySQL usa o mesmo.

O buffer de chave é usado pelo MySQL para armazenar em cache os índices MyISAM na memória. Ele é configurado usando key_buffer_size. O valor padrão da comunidade do MySQL é de 8 MB, e o Cloud SQL para MySQL usa o mesmo. O Cloud SQL para MySQL não é compatível com tabelas MyISAM. Portanto, não há problema em deixá-lo como padrão.

A variável de configuração query_cache_size define a quantidade de memória alocada para armazenar em cache os resultados de consultas. Ela é desativada por padrão no MySQL Community 5.7 e no Cloud SQL para MySQL 5.7. 

O cache de consulta era conhecido por problemas graves de escalonabilidade. Por isso, ele foi descontinuado no MySQL 5.7.20 e removido no MySQL 8.0. Se você ainda o usa para sua instância do MySQL 5.7, verifique se ele é realmente útil para sua carga de trabalho. Consulte este blog para mais informações. 

O MySQL aloca caches globais compartilhados entre todas as conexões, que são alocados dinamicamente, e as variáveis de configuração definem o limite máximo para eles.

O MySQL usa o cache de tabelas para acelerar a abertura de tabelas. O cache de tabela do MySQL é dividido em duas partes: um cache de tabelas abertas e um de definições de tabela, configuradas usando table_open_cache e table_definition_cache, respectivamente. 

table_open_cache é um cache em memória para armazenar o descritor do arquivo das tabelas abertas por todas as linhas de execução conectadas. Aumentar esse valor aumenta o número de descritores de arquivo exigidos pelo programa mysqld, também conhecido como servidor MySQL. Verifique se o sistema operacional pode processar o número de descritores de arquivos abertos implícitos pela configuração table_open_cache.

Várias sessões de cliente podem acessar a tabela especificada simultaneamente, e a tabela é aberta de forma independente para cada sessão de cliente simultânea. É por isso que pode haver contagens de tabelas abertas maiores que o número de tabelas no servidor. Quando o cache da tabela está cheio, o servidor libera as tabelas que não estão em uso atualmente, começando com a tabela menos usada recentemente.

O table_definition_cache é um cache em memória para armazenar as definições da tabela. Ele é global e compartilhado entre todas as conexões.

O CloudSQL para MySQL 5.7 usa 2000 e 1400 como padrões para table_open_cache e table_definition_cache.

O CloudSQL para MySQL 8.0 usa 4000 e 2000 como padrões para table_open_cache e table_definition_cache.

Para cada conexão de cliente, o MySQL atribui uma linha de execução dedicada que executa todas as consultas e retorna o resultado ao cliente até que ele se desconecte. O MySQL armazena em cache as linhas de execução para que não precise criar e destruir linhas de execução para cada conexão. O número de linhas de execução no cache é configurado usando a variável thread_cache_size.

O CloudSQL para MySQL usa 48 como padrão para thread_cache_size.

O InnoDB tem o próprio cache para armazenar definições de tabelas. Ele é diferente do cache de tabela aberta e do cache de definição de tabela. É possível verificar a memória alocada para o dicionário de dados do InnoDB na saída mostrar status do innodb\G do mecanismo.

----------------------

POOL E MEMÓRIA DE BUFFER

----------------------

…

Memória de dicionário alocada 65816817

A configuração table_definition_cache define um limite flexível no número de instâncias da tabela no cache do dicionário de dados do InnoDB, se o número de instâncias da tabela no cache do dicionário de dados do InnoDB exceder o limite de table_definition_cache, o mecanismo LRU começará a marcar instâncias de tabela para remoção e, por fim, as removerá do cache.

Isso significa que o número de instâncias da tabela no cache do dicionário de dados do InnoDB será sempre menor que o limite de table_definition_cache? Esse não é o caso. Instâncias de tabela com relações de chave externa não são colocadas na lista de LRU. Elas permanecem em cache e fazem com que as instâncias da tabela cresçam além do limite de table_definition_cache, o que leva ao uso de memória adicional. A memória consumida pelas tabelas com relações de chave externa é liberada somente no evento de encerramento/reinicialização do MySQL. Esse problema existe no MySQL 5.7 e no 8.0, e há um bug verificado.

Se a instância do MySQL tiver um grande número de tabelas com relações de chave externa, o cache do dicionário de dados do InnoDB poderá consumir vários GB de memória. Muitas vezes, isso é ignorado durante a configuração de buffers/caches do MySQL e pode ser um dos motivos para problemas inesperados de alto uso de memória ou falta de memória.

Uma saída de amostra mostrando uma pequena instância consumindo 4,16 GB para o cache do dicionário de dados do InnoDB.

$ mysql -e "mostrar status do mecanismo do InnoDB\G" | grep -i memory

POOL E MEMÓRIA DE BUFFER

Total de memória grande alocada: 7696023552

Memória de dicionário alocada 4465193358

Outro recurso do MySQL que consome memória são os buffers de sessão. Esses buffers são alocados por sessão e, em alguns casos, é possível alocar várias instâncias deles para uma única consulta (join_buffer_size, em específico).

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

Esses buffers são alocados somente quando uma consulta precisa deles (para classificação, mesclas, verificações de tabela completa/índice e muito mais), mas quando são necessários, eles são alocados para o tamanho original, mesmo que uma parte muito pequena seja necessária. Definir esses buffers como um valor alto pode resultar em desperdício de memória.

Os valores padrão são os mesmos na comunidade do MySQL e no Cloud SQL para MySQL.

O MySQL usa o cache de registros binários para manter as alterações feitas no registro binário enquanto uma transação está em execução. Ele é configurado usando binlog_cache_size. Ele será alocado para cada cliente se a geração de registros binários estiver ativada (log_bin=ON).

O valor padrão de binlog_cache_size é o mesmo na comunidade do MySQL e no Cloud SQL para MySQL.

O MySQL cria tabelas temporárias internas para armazenar o resultado intermediário ao processar alguns tipos de consultas, como GROUP BY, ORDER BY, DISTINCT e UNION. Essas tabelas temporárias internas são criadas primeiro na memória e convertidas em tabelas no disco quando o tamanho máximo é atingido. O tamanho máximo das tabelas temporárias internas é determinado como o mínimo das variáveis tmp_table_size e max_heap_table_size. 

Os valores padrão de tmp_table_size e max_heap_table_size são os mesmos na comunidade do MySQL e no Cloud SQL para MySQL.

Observação: uma vez que os buffers por sessão e as tabelas temporárias na memória alocam memória separadamente para cada conexão, o uso geral da memória pode ser muito alto se um grande número de conexões precisar deles. Não é recomendável definir esses valores muito altos. Teste para encontrar o melhor valor para sua carga de trabalho.

Cada linha de execução requer pouca memória para gerenciar a conexão do cliente. As variáveis a seguir controlam o tamanho delas.

• thread_stack: o tamanho da pilha de cada linha de execução (o padrão é 256 KB).
• net_buffer_length: cada cliente está associado a um buffer de conexão e ao buffer de resultado de net_buffer_length. Isso pode aumentar até o tamanho de max_allowed_packet.

Se performance_schema estiver ativado, ele ajudará no monitoramento da execução do servidor MySQL em nível baixo. O Performance_schema aloca memória de forma dinâmica e é liberado apenas quando o MySQL é encerrado/reiniciado.

O Cloud SQL para MySQL permite ativar performance_schema nas instâncias com RAM de 15 GB ou mais. Por padrão, ele é ativado a partir da versão 8.0.26 do MySQL. O Performance_schema é desativado por padrão nas versões 5.6, 5.7 e 8.0.18 do MySQL. Ele pode ser ativado usando sinalizações do banco de dados.

O Cloud SQL para MySQL configura automaticamente os parâmetros relacionados à memória para um bom desempenho pronto para uso. A instância ainda poderá ter problemas de memória insuficiente (OOM, na sigla em inglês) se a carga de trabalho exigir caches maiores para suportar um grande número de tabelas e/ou conexões. Muitas linhas de execução que alocam buffers de sessão simultaneamente também podem levar a problemas relacionados à memória. Quanto mais memória for alocada para buffers/caches globais, menos memória estará disponível para conexões, buffers de sessão e vice-versa. O segredo é encontrar um bom equilíbrio.

Se a carga de trabalho exigir caches/buffers de sessão maiores, é possível reduzir o tamanho do buffer global com innodb_buffer_pool_size. É possível alterar os parâmetros de configuração das instâncias do Cloud SQL para MySQL usando sinalizações de banco de dados. Se você ainda tiver problemas de desempenho ou OOM, faça upgrade do tamanho da instância para aumentar a memória.