Solucionar problemas de pouca memória no Banco de Dados do Azure para MySQL - Servidor Flexível

APLICA-SE A:Banco de Dados do Azure para MySQL – Servidor Único Banco de Dados do Azure para MySQL – Servidor Flexível

Importante

O Banco de Dados do Azure para servidor único MySQL está no caminho de desativação. É altamente recomendável que você atualize para o Banco de Dados do Azure para o servidor flexível MySQL. Para obter mais informações sobre como migrar para o Banco de Dados do Azure para o servidor flexível MySQL, consulte O que está acontecendo com o Banco de Dados do Azure para Servidor Único MySQL?

Para ajudar a garantir que uma instância de servidor flexível do Banco de Dados do Azure para MySQL tenha um desempenho ideal, é muito importante ter a alocação e a utilização de memória apropriadas. Por padrão, quando você cria uma instância do Banco de Dados do Azure para servidor flexível MySQL, a memória física disponível depende da camada e do tamanho selecionados para sua carga de trabalho. Além disso, a memória é alocada para buffers e caches para aprimorar as operações de banco de dados. Para obter mais informações, consulte Como o MySQL usa memória.

Observe que o Banco de Dados do Azure para servidor flexível MySQL consome memória para obter o máximo de acerto de cache possível. Como resultado, a utilização de memória geralmente é entre 80 e 90% da memória física disponível de uma instância. A menos que haja um problema com o progresso da carga de trabalho de consulta, isso não é um problema. No entanto, você pode encontrar problemas de memória insuficiente por motivos como o que você tem:

• Buffers muito grandes configurados.
• Consultas em execução abaixo do ideal.
• Consultas executando junções e classificando grandes conjuntos de dados.
• Definição conexões máximas muito altas em um servidor de banco de dados.

A maior parte da memória de um servidor é usada pelos buffers e caches globais do InnoDB, que incluem componentes como innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size e query_cache_size.

O valor do parâmetro innodb_buffer_pool_size especifica a área de memória na qual o InnoDB armazena em cache as tabelas de banco de dados e os dados relacionados ao índice. O MySQL tenta acomodar o máximo possível de dados relacionados à tabela e ao índice no pool de buffers. Um pool de buffers maior requer menos operações de E/S sendo desviadas para o disco.

Monitorar o uso de memória

O servidor flexível do Banco de Dados do Azure para MySQL fornece uma variedade de métricas para avaliar o desempenho de sua instância de banco de dados. Para entender melhor a utilização da memória do servidor de banco de dados, exiba as métricas Percentual de Memória do Host ou Percentual de Memória.

Se você observar que a utilização da memória aumentou repentinamente e que a memória disponível está caindo rapidamente, monitore outras métricas, como Porcentagem de CPU do Host, Total de Conexões e Percentual de E/S, para determinar se um aumento repentino na carga de trabalho é a origem do problema.

É importante observar que cada conexão estabelecida com o servidor de banco de dados requer a alocação de alguma quantidade de memória. Como resultado, um aumento nas conexões de banco de dados pode causar escassez de memória.

Causas de utilização de memória alta

Vamos examinar mais algumas causas de utilização de memória alta no MySQL. Essas causas dependem das características da carga de trabalho.

Um aumento nas tabelas temporárias

O MySQL usa "tabelas temporárias", que são um tipo especial de tabela projetada para armazenar um conjunto de resultados temporário. As tabelas temporárias podem ser reutilizadas várias vezes durante uma sessão. Como todas as tabelas temporárias criadas são locais para uma sessão, sessões diferentes podem ter tabelas temporárias diferentes. Em sistemas de produção com muitas sessões executando compilações de grandes conjuntos de resultados temporários, você deve verificar regularmente o contador de status global created_tmp_tables, que acompanha o número de tabelas temporárias que estão sendo criadas durante os horários de pico. Um grande número de tabelas temporárias na memória pode levar rapidamente a pouca memória disponível em uma instância do Banco de Dados do Azure para servidor flexível MySQL.

Com o MySQL, o tamanho da tabela temporária é determinado pelos valores de dois parâmetros, conforme descrito na tabela a seguir.

Parâmetro	Descrição
tmp_table_size	Especifica o tamanho máximo de tabelas temporárias internas na memória.
max_heap_table_size	Especifica o tamanho máximo para o qual as tabelas MEMORY criadas pelo usuário podem crescer.

Observação

Ao determinar o tamanho máximo de uma tabela temporária interna na memória, o MySQL considera o menor dos valores definidos para os parâmetros tmp_table_size e max_heap_table_size.

Recomendações

Para solucionar problemas de memória baixa relacionados a tabelas temporárias, considere as recomendações a seguir.

• Antes de aumentar o valor tmp_table_size, verifique se o banco de dados está indexado corretamente, especialmente para colunas envolvidas em junções e agrupadas por operações. O uso dos índices apropriados em tabelas subjacentes limita o número de tabelas temporárias que são criadas. Aumentar o valor desse parâmetro e o parâmetro max_heap_table_size sem verificar se seus índices podem permitir que consultas ineficientes sejam executadas sem índices e criar mais tabelas temporárias do que as necessárias.
• Ajuste os valores dos parâmetros max_heap_table_size e tmp_table_size para atender às necessidades da carga de trabalho.
• Se os valores definidos para os parâmetros max_heap_table_size e tmp_table_size forem muito baixos, as tabelas temporárias poderão ser despejadas no armazenamento, adicionando latência às suas consultas. Você pode acompanhar o despejo das tabelas temporárias no disco usando o contador de status global created_tmp_disk_tables. Comparando os valores das variáveis created_tmp_disk_tables e created_tmp_tables, você exibe o número de tabelas temporárias internas no disco que foram criadas com o número total de tabelas temporárias internas criadas.

Cache de tabela

Como um sistema com múltiplos threads, o MySQL mantém um cache de descritores de arquivo de tabela para que as tabelas possam ser abertas de modo simultâneo independentemente por várias sessões. O MySQL usa uma quantidade de descritores de arquivo do sistema operacional e memória para manter esse cache de tabela. A variável table_open_cache define o tamanho do cache da tabela.

Recomendações

Para solucionar problemas de memória baixa relacionados ao cache de tabela, considere as recomendações a seguir.

• O parâmetro table_open_cache especifica o número de tabelas abertas para todos os threads. Aumentar esse valor aumenta o número de descritores de arquivo que o mysqld requer. Você pode verificar se precisa aumentar o cache de tabela verificando a variável de status opened_tables no contador de status global de exibição. Aumente o valor desse parâmetro em incrementos para acomodar sua carga de trabalho.
• A configuração table_open_cache muito baixa pode fazer com que o servidor flexível do Banco de Dados do Azure para MySQL gaste mais tempo abrindo e fechando tabelas necessárias para o processamento de consultas.
• Definir esse valor muito alto pode causar o uso de mais memória e pode fazer o sistema operacional executar descritores de arquivo, ocasionando conexões recusadas ou falha ao processar consultas.

Outros buffers e o cache de consulta

Ao solucionar problemas relacionados à memória baixa, você pode trabalhar com mais alguns buffers e um cache para ajudar na resolução.

Buffer líquido (net_buffer_length)

O buffer líquido é tamanho para buffers de conexão e thread para cada thread do cliente e pode aumentar para o valor especificado para max_allowed_packet. Se uma instrução de consulta for grande, por exemplo, todas as inserções/atualizações têm um valor muito grande, então, aumentar o valor do parâmetro net_buffer_length ajudará a melhorar o desempenho.

Buffer de junção (join_buffer_size)

O buffer de junção é alocado para armazenar em cache linhas de tabela quando uma junção não pode usar um índice. Se o banco de dados tiver muitas junções executadas sem índices, considere adicionar índices para junções mais rápidas. Se você não puder adicionar índices, considere aumentar o valor do parâmetro join_buffer_size, que especifica a quantidade de memória alocada por conexão.

Buffer de classificação (sort_buffer_size)

O buffer de classificação é usado para executar classificações para algumas consultas ORDER BY e GROUP BY. Se você vir muitos Sort_merge_passes por segundo na saída SHOW GLOBAL STATUS, considere aumentar o valor sort_buffer_size para acelerar as operações ORDER BY ou GROUP BY que não podem ser melhoradas usando otimização de consulta ou melhor indexação.

Evite aumentar arbitrariamente o valor sort_buffer_size, a menos que você tenha informações relacionadas que indiquem o contrário. A memória desse buffer é atribuída por conexão. Na documentação do MySQL, o artigo Variáveis do Sistema de Servidores chama isso no Linux. Há dois limites, 256 KB e 2 MB, e o uso de valores maiores pode reduzir significativamente a alocação de memória. Como resultado, evite aumentar o valor sort_buffer_size para além de 2 milhões, pois a penalidade de desempenho superará quaisquer benefícios.

Cache de consultas (query_cache_size)

O cache de consulta é uma área de memória usada para armazenar conjuntos de resultados de consulta em cache. O parâmetro query_cache_size determina a quantidade de memória alocada para armazenar em cache os resultados da consulta. O cache de consulta é desabilitado por padrão. Além disso, o cache de consulta é preterido no MySQL versão 5.7.20 e removido no MySQL versão 8.0. Se o cache de consulta estiver habilitado no momento em sua solução, antes de desabilitá-lo, verifique se não há nenhuma consulta que dependa dela.

Calcular o índice de ocorrências no cache do buffer

A taxa de acertos do cache de buffer é importante no ambiente de servidor flexível do Banco de Dados do Azure para MySQL para entender se o pool de buffers pode acomodar as solicitações de carga de trabalho ou não e, como regra geral, é uma boa prática sempre ter uma taxa de acertos de cache do pool de buffer superior a 99%.

Para calcular o índice de ocorrência do pool de buffers do InnoDB para solicitações de leitura, você pode executar o SHOW GLOBAL STATUS para recuperar contadores “Innodb_buffer_pool_read_requests” e “Innodb_buffer_pool_reads” e, em seguida, calcular o valor usando a fórmula mostrada abaixo.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Considere o exemplo a seguir.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

Usando os valores acima, computar o índice de ocorrência do pool de buffers do InnoDB para solicitações de leitura gera o seguinte resultado:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

Além de selecionar o índice de ocorrências no cache do buffer de instruções, para quaisquer instruções DML, as gravações no pool de buffers do InnoDB ocorrem em segundo plano. No entanto, se for necessário ler ou criar uma página e nenhuma página limpa estiver disponível, também será necessário esperar que as páginas sejam liberadas primeiro.

O contador Innodb_buffer_pool_wait_free conta quantas vezes isso aconteceu. O Innodb_buffer_pool_wait_free maior que 0 é um indicador forte de que o pool de buffers do InnoDB é muito pequeno e o aumento no tamanho do pool de buffers ou no tamanho da instância é necessário para acomodar as gravações que chegam ao banco de dados.

Recomendações

• Verifique se o banco de dados tem recursos suficientes alocados para executar suas consultas. Às vezes, talvez seja necessário escalar verticalmente o tamanho da instância para obter mais memória física para que os buffers e caches acomodem sua carga de trabalho.
• Evite transações grandes ou de longa execução dividindo-as em transações menores.
• Use alertas "Percentual de Memória do Host" para que você receba notificações se o sistema exceder um dos limites especificados.
• Use a Análise de Desempenho de Consultas ou as Pastas de Trabalho do Azure para identificar consultas problemáticas ou lentas e otimizá-las.
• Para servidores de banco de dados de produção, colete diagnósticos em intervalos regulares para garantir que tudo esteja em execução sem problemas. Caso contrário, solucione e resolva os problemas que você identificar.

Próximas etapas

Para localizar respostas de pares às suas perguntas mais importantes ou publicar ou responder a uma pergunta, acesse o Stack Overflow.