Como analisar problemas de desempenho do MySQL

Blog

Você tem problemas com a carga excessiva do servidor MySQL ou percebe que algumas consultas demoram muito para serem executadas? Veja como começar a analisar problemas de desempenho do MySQL.

Antes de começar, você deve estar ciente de que cada servidor e conjunto de dados tem seus próprios desafios de desempenho. Este artigo destina-se a fornecer um guia geral sobre como verificar a configuração do servidor e consultas individuais para problemas ocultos.

índice

  1. Lidar com a carga do servidor
  2. Personalização do tamanho do buffer
  3. Análise de consulta lenta
  4. O papel dos índices
  5. Conclusão

Lidar com a carga do servidor

A indicação mais óbvia de que algo está errado geralmente vem de uma desaceleração geral. Se você tiver períodos prolongados de alto uso de recursos, modificar o arquivo de configuração do MySQL pode levar a configurações mais ideais.

Um bom lugar para começar é rodar MySQLTuner. Este script testa automaticamente seu servidor MySQL em relação a 300 indicadores de desempenho possíveis. Produza uma lista de dicas que podem ajudá-lo a obter mais do seu ambiente.

MySQLTuner é distribuído como um script Perl, então você precisará do Perl instalado em seu sistema. Use os seguintes comandos para baixar e executar o MySQLTuner:

 wget http://mysqltuner.pl/ -O mysqltuner.pl chmod +x mysqltuner.pl ./mysqltuner.pl --host 127.0.0.1 --username root --pass mysql-password

A sintaxe de conexão é semelhante à linha de comando do cliente mysql . Você precisará fazer login como usuário root para que o script tenha acesso total ao seu servidor.

MySQLTuner é útil porque sua classificação é baseada no servidor que hospeda seu conjunto de dados. No entanto, o resultado é apenas sugestivo: nem todas as recomendações terão impacto e algumas podem reduzir o desempenho.

MySQLTuner é um script somente leitura. Ele não fará nenhuma alteração na configuração do seu servidor. Se você aceitar uma sugestão, você deve atualizar manualmente a variável indicada em seu arquivo de configuração do MySQL. A localização deste arquivo varia dependendo da distribuição do sistema operacional. Locais comuns incluem /etc/mysql/my.cnf is /etc/mysql/mysql.conf.d/mysqld.cnf .

Após alterar uma variável, reinicie o servidor MySQL:

 sudo /etc/init.d/mysql restart

Agora você precisa deixar o servidor funcionar normalmente por um tempo. Você pode então executar o MySQLTuner novamente para reavaliar o desempenho do servidor. Pode sugerir outra mudança para a mesma variável. Continue fazendo alterações, mas certifique-se de equilibrar todas as configurações. Você não poderá necessariamente definir cada variável para o valor sugerido sem que novas sugestões apareçam. A saída do MySQLTuner recomenda deixar o servidor funcionando por 24 horas para obter dados de avaliação precisos.

Personalização do tamanho do buffer

Alterar o tamanho do buffer e do cache pode fornecer uma melhoria notável no desempenho. O padrão do MySQL é um tamanho de buffer relativamente pequeno, que não funcionará bem para cargas de trabalho maiores. Os valores são gravados no arquivo de configuração do MySQL usando K , M ou G para indicar o contêiner (por exemplo, 512M significa 512 megabytes).

  • innodb_buffer_pool_size : Como regra geral, isso geralmente é definido para 70-80% da memória disponível. Define o tamanho do pool usado para armazenar consultas em tabelas InnoDB. Tente torná-lo pelo menos tão grande quanto o tamanho total do seu conjunto de dados, desde que você tenha memória suficiente disponível.
  • innodb_buffer_pool_instances : Um valor entre 1 e 64, definindo o número de conjuntos de buffers InnoDB a serem usados. Cada página armazenada no buffer pool é atribuída aleatoriamente a uma das instâncias. Mais instâncias podem melhorar a simultaneidade.
  • innodb_log_file_size : tamanho máximo dos arquivos de log de redo em um grupo de logs. Esses arquivos são usados ​​durante a recuperação de desastres para restaurar transações incompletas. Valores mais altos melhoram o desempenho, mas aumentam o tempo de recuperação em caso de acidente.
  • key_buffer_size : É semelhante ao innodb_buffer_pool_size , mas é usado para tabelas MyISAM. Observe que se você estiver usando exclusivamente tabelas MyISAM ou InnoDB, você deve definir a variável relevante de acordo e alterar a outra para um tamanho relativamente baixo, como 32M . Caso contrário, você estará desperdiçando RAM fornecendo amplo espaço de buffer para um tipo de tabela não utilizado.
  • join_buffer_size : Define o tamanho do buffer usado para junções sem índices. Aumentar o tamanho desse buffer acelerará as consultas que usam junções não indexadas. Definir o valor muito alto pode causar problemas de memória, pois um buffer de junção é alocado para cada junção completa entre tabelas. Junções complexas entre várias tabelas exigem vários buffers, cada join_buffer_size , que pode consumir rapidamente uma grande quantidade de RAM. O valor padrão é 256K .
  • sort_buffer_size : Como join_buffer_size , mas aplicável a operações de classificação usando filesort . Valores mais altos podem acelerar a classificação de grandes conjuntos de resultados, mas correm o risco de aumentar o uso de memória em um servidor muito ocupado.

Quaisquer alterações no tamanho do conjunto de dados e nos recursos de hardware do servidor devem sempre ser consideradas. Definir esses valores muito baixos afetará o desempenho da consulta, enquanto configurá-los muito altos pode causar uso excessivo de memória ou até mesmo ficar sem memória. O MySQLTuner emitirá avisos se a configuração correr o risco de consumir toda a memória disponível do sistema.

Análise de consulta lenta

A ativação do log de consultas lentas fornece informações sobre consultas com desempenho insatisfatório. Você pode fazer isso a partir de uma sessão de shell do MySQL:

 sudo mysql SET GLOBAL slow_query_on = "On"; SET GLOBAL slow_query_log_file = "/slow-query.log"; SET GLOBAL long_query_time = 5;

Essa configuração registrará qualquer consulta que demore mais de cinco segundos para ser executada em /slow-query.log . Revise esse arquivo periodicamente para identificar consultas de longa duração.

Depois de encontrar uma consulta problemática, você pode usar uma instrução EXPLAIN para ter uma ideia do que está causando a lentidão. Prefixe a consulta com EXPLAIN e execute o comando em um shell MySQL. T obter a saída tabulada mostrando como o MySQL tenta executar a consulta.

A saída EXPLAIN inclui informações sobre os índices disponíveis, as chaves usadas e o número de registros avaliados. Interpretação EXPLAIN os dados são um tópico em si. Um guia detalhado sobre o significado de cada campo está disponível na Documentação do MySQL.

Pode ser mais fácil usar o MySQL Workbench para executar um arquivo EXPLAIN graficamente. Isso pode ajudá-lo a visualizar a abordagem do mecanismo de banco de dados para recuperar o conjunto de resultados. No MySQL Workbench, pressione Ctrl + T para abrir uma nova guia de consulta. Escreva sua consulta e pressione Ctrl + Alt + X para executá-la como um arquivo EXPLAIN (não é necessário adicionar o prefixo EXPLAIN manualmente). No painel de resultados, você verá o plano de execução visual que destaca as operações envolvidas.

O papel dos índices

É importante garantir que o conjunto de dados contenha índices adequados. O uso adequado de índices aumenta muito o desempenho da consulta.

 SELECT * FROM users WHERE Email = ' [email protected] ' ;

Esta consulta deve ter um índice no campo users.Email . Sem um índice, o MySQL precisaria realizar uma varredura completa da tabela, o que resultaria em uma varredura lenta de cada registro.

Com o índice, o mecanismo de banco de dados pode identificar registros muito mais rapidamente. Ele faz isso criando uma nova estrutura de dados que contém o valor do campo e um ponteiro para o registro de origem. Os ponteiros podem então ser ordenados para que o MySQL possa saltar diretamente para os dados relevantes.

Para adicionar um índice a um campo existente, use a instrução ADD INDEX com ALTER TABLE :

 ALTER TABLE minha_tabela ADD INDEX meu_índice(meu_campo);

Você deve então executar OPTIMIZE TABLE my_table para indexar os dados existentes e recalcular as estatísticas da consulta.

Ao trabalhar com vários campos, você pode criar um índice de cobertura . Este é um índice que incorpora todos os campos.

 SELECT * FROM my_table WHERE x = 1 AND y = 2 ORDER BY z; ALTER TABLE minha_tabela ADD INDEX índice_cobertura ( x , y , z ) ;

Ao usar taxas de cobertura, a ordem dos campos é importante. Se você perguntou WHERE z = 1 ORDER BY x , o índice criado anteriormente não será usado.

Você deve garantir que os campos usados ​​nas cláusulas WHERE ou JOIN ... ON sejam cobertos por um índice. Consultar campos não indexados pode rapidamente se tornar um gargalo de desempenho. No entanto, tenha cuidado com a indexação descartada , se você nunca consultar esse campo, o índice não será necessário na cabeça, pois ainda precisa ser mantido pelo MySQL.

Você pode identificar consultas que se beneficiariam de um índice habilitando o log de consultas não indexadas. Siga as instruções acima para habilitar o log de consultas lentas. Então você deve executar SET GLOBAL log_queries_not_using_indexes = "On" a partir de um shell MySQL. Isso começará a registrar consultas com índices ausentes no log de consulta lenta. As consultas não indexadas serão incluídas mesmo que não excedam o tempo de consulta lenta configurado.

Conclusão

Não existe uma abordagem única para melhorar o desempenho do MySQL. As etapas a serem seguidas dependerão dos recursos do servidor, do tamanho do conjunto de dados e do nível de conflito de recursos causado por outras cargas de trabalho em execução na máquina.

Também não negligencie sua camada de aplicação: MySQL pode não ser a raiz de seus problemas de desempenho. Verifique a maneira como você consulta seu código. Se houver looping excessivo, como usar uma consulta em uma rotina N+1, refatorar esse código pode ter um impacto muito maior do que microgerenciar o servidor MySQL.

  • Como encontrar outras versões de uma imagem de site no Google Chrome
  • As 7 principais luzes da webcam

descubra mais conteúdo

O que é um amplificador integrado?

Google Pixel Watch também tem detecção de queda, mas não até o próximo ano

As melhores xícaras de café aquecidas de 2022

Como automatizar seu iPhone com base na hora, atividade ou localização

"Isso deve ter sido antes do meu tempo"

Por que a NASA enviou Snoopy para a Lua? – Revisão geek

Deixe uma resposta Cancelar resposta

Como fazer consultas no banco de dados MySQL Workbench?

Para fazer isso, primeiro selecione o banco de dados desejado no menu da coluna da esquerda clicando duas vezes nele. Em seguida, digite a consulta MySQL que deseja executar e use o botão de raio amarelo acima desse campo de texto para executar a consulta .

Como fazer consultas no MySQL?

A sintaxe básica para consultar dados no Mysql é a seguinte: SELECT ColumnList FROM Table_Name; Onde: SELECT: é o comando utilizado para obter os registros das tabelas.

Como fazer uma consulta no MySQL Workbench de várias tabelas?

Para realizar este tipo de consulta podemos utilizar duas alternativas, a sintaxe SQL 1 (SQL-86), que consiste em fazer o produto cartesiano das tabelas e adicionar um filtro para relacionar os dados que elas têm em comum, e a sintaxe SQL 2 (SQL-92 e SQL-2003) que inclui todas as cláusulas do tipo JOIN.

O que é uma consulta de banco de dados MySQL?

Em bancos de dados, uma consulta é o método para acessar os dados nos bancos de dados. Com as consultas , você pode modificar, excluir, exibir e adicionar dados em um banco de dados. Para isso, é utilizada uma linguagem de consulta . A linguagem de consulta de banco de dados mais utilizada é o SQL.

Ir arriba