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 servidor único do Banco de Dados do Azure para 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 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 o 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 melhorar as operações do banco de dados. Para obter mais informações, consulte Como o MySQL usa a 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 da memória geralmente pode pairar 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 é uma preocupação. No entanto, você pode ter problemas de falta de memória por motivos como o que você tem:

• Configurados buffers muito grandes.
• Consultas abaixo do ideal em execução.
• Consultas executando junções e classificando grandes conjuntos de dados.
• Defina o máximo de conexões em um servidor de banco de dados muito alto.

A maioria 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 do banco de dados e os dados relacionados ao índice. O MySQL tenta acomodar o máximo possível de dados relacionados a tabelas e índices no buffer pool. Um pool de buffers maior requer menos operações de E/S sendo desviadas para o disco.

Monitorando o uso da 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 Porcentagem de memória do host ou Porcentagem de memória.

Se você notar 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 da CPU do Host, Total de Conexões e Porcentagem de E/S, para determinar se um pico repentino na carga de trabalho é a origem do problema.

É importante notar 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 da alta utilização de memória

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

Aumento das 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 rastreia o número de tabelas temporárias que estão sendo criadas durante as horas de pico. Um grande número de tabelas temporárias na memória pode rapidamente levar 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.

Nota

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 pouca memória relacionados a tabelas temporárias, considere as seguintes recomendações.

• 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 criadas. Aumentar o valor desse parâmetro e do parâmetro max_heap_table_size sem verificar seus índices pode permitir que consultas ineficientes sejam executadas sem índices e criar mais tabelas temporárias do que o necessário.
• Ajuste os valores dos parâmetros max_heap_table_size e tmp_table_size para atender às necessidades de sua 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 regularmente derramadas para o armazenamento, adicionando latência às suas consultas. Você pode rastrear tabelas temporárias derramadas no disco usando o contador de status global created_tmp_disk_tables. Ao comparar os valores das variáveis created_tmp_disk_tables e created_tmp_tables, você visualiza 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 multi-threaded, o MySQL mantém um cache de descritores de arquivos de tabela para que as tabelas possam ser abertas simultaneamente de forma independente por várias sessões. O MySQL usa alguma quantidade de memória e descritores de arquivos do sistema operacional 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 pouca memória 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 da tabela verificando a variável de status opened_tables no contador show global status. Aumente o valor desse parâmetro em incrementos para acomodar sua carga de trabalho.
• Definir table_open_cache muito baixo 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 o sistema operacional executando descritores de arquivo, levando a conexões recusadas ou falha no processamento de consultas.

Outros buffers e o cache de consulta

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

Buffer de rede (net_buffer_length)

O buffer de rede é o tamanho dos buffers de conexão e thread para cada thread de 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 tiverem um valor muito grande, 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 da tabela quando uma associação não pode usar um índice. Se o banco de dados tiver muitas junções realizadas sem índices, considere adicionar índices para uniões mais rápidas. Se não for possível 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 tenha informações relacionadas que indiquem o contrário. A memória para este buffer é atribuída por conexão. Na documentação do MySQL, o artigo Server System Variables chama a atenção para o facto de, no Linux, existirem dois limites, 256 KB e 2 MB, e que a utilização de valores maiores pode abrandar significativamente a alocação de memória. Como resultado, evite aumentar o valor da sort_buffer_size além de 2M, 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 em cache conjuntos de resultados de consulta. O parâmetro query_cache_size determina a quantidade de memória alocada para armazenar em cache os resultados da consulta. Por padrão, o cache de consulta está desabilitado. Além disso, o cache de consulta foi preterido no MySQL versão 5.7.20 e removido no MySQL versão 8.0. Se o cache de consultas estiver atualmente habilitado em sua solução, antes de desativá-lo, verifique se não há consultas confiando nele.

Calculando a taxa de acertos do 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 buffers superior a 99%.

Para calcular a taxa de acertos do pool de buffers 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 seguinte exemplo.

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, calcular a taxa de acertos do pool de buffers InnoDB para solicitações de leitura produz o seguinte resultado:

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

Buffer hit ratio = 99.99%

Além de selecionar a taxa de acertos do cache do buffer de instruções, para qualquer instrução DML, as gravações no Pool de Buffers do InnoDB acontecem em segundo plano. No entanto, se for necessário ler ou criar uma página e não houver páginas limpas disponíveis, também é necessário esperar que as páginas sejam liberadas primeiro.

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

Recomendações

• Certifique-se de que seu banco de dados tenha recursos suficientes alocados para executar suas consultas. Às vezes, talvez seja necessário aumentar o tamanho da instância para obter mais memória física para que os buffers e caches se adaptem à sua carga de trabalho.
• Evite transações grandes ou de longa duração, dividindo-as em transações menores.
• Use alertas "Porcentagem de memória do host" para receber notificações se o sistema exceder qualquer um dos limites especificados.
• Use o Query Performance Insights ou as Pastas de Trabalho do Azure para identificar consultas problemáticas ou de execução lenta e, em seguida, otimize-as.
• Para servidores de banco de dados de produção, colete diagnósticos em intervalos regulares para garantir que tudo esteja funcionando sem problemas. Caso contrário, solucione e resolva quaisquer problemas identificados.

Próximos passos

Para encontrar respostas de colegas para suas perguntas mais importantes ou para postar ou responder a uma pergunta, visite Stack Overflow.