Como analisar problemas de desempenho do MySQL

Enfrentando carga excessiva do servidor MySQL ou percebendo que algumas consultas demoram muito para serem executadas? Veja como começar ao investigar problemas de desempenho do MySQL.

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

Lidando com a carga do servidor

A indicação mais óbvia de que algo está errado geralmente vem de uma lentidão geral. Se você estiver observando períodos sustentados de alta utilização de recursos, ajustar o arquivo de configuração do MySQL pode levar a configurações mais otimizadas.

Um bom lugar para começar é executar o MySQLTuner. Este script avalia automaticamente seu servidor MySQL em relação a 300 indicadores de desempenho possíveis. Ele produzirá uma lista de sugestões que podem ajudá-lo a obter mais do seu ambiente.

O 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 [/ PRE ]

        
        
        
 
 A sintaxe de conexão é semelhante ao cliente de linha de comando mysql. Você deve se conectar como usuário root para que o script tenha acesso total ao seu servidor. 
 
 
 O MySQLTuner é útil porque sua avaliação se baseia no servidor que hospeda o conjunto de dados. No entanto, o resultado é meramente sugestivo — nem todas as recomendações terão um impacto e algumas podem realmente reduzir o desempenho. 

        
        
        
 
 MySQLTuner é um script puramente somente leitura. Ele não fará nenhuma alteração nas configurações do seu servidor. Se você aceitar uma sugestão, deverá atualizar manualmente a variável indicada em seu arquivo de configuração do MySQL. A localização desse arquivo varia de acordo com a distribuição do sistema operacional. Os locais comuns incluem /etc/mysql/my. cnf e /etc/mysql/mysql. conf.d/mysqld. cnf. 
 Depois de alterar uma variável, reinicie o servidor MySQL: 
 
 sudo /etc/init.d/mysql restart 

        
        
        
 
 Agora você deve deixar o servidor para funcionar normalmente por um tempo. Você pode então executar o MySQLTuner novamente para reavaliar o desempenho do servidor. Isso pode sugerir outra mudança na mesma variável. Continue fazendo alterações, mas certifique-se de equilibrar todas as configurações. Você não conseguirá necessariamente definir cada variável com seu valor sugerido sem que novas sugestões apareçam. A saída do MySQLTuner informa que você deve deixar o servidor funcionando por 24 horas para obter dados de avaliação precisos. 
 Personalizando tamanhos de buffer 
 Alterar os tamanhos do buffer e do cache pode proporcionar uma melhoria significativa no desempenho. O padrão do MySQL é tamanhos de buffer relativamente pequenos, o que não funcionará bem para cargas de trabalho maiores. Os valores são escritos no arquivo de configuração do MySQL usando K, M ou G para indicar a unidade de armazenamento (por exemplo, 512M significa 512 megabytes). 
 
        
        
         
 
 
 innodb_buffer_pool_size: Como regra geral, isso geralmente é definido para 70-80% de sua memória disponível. Ele define o tamanho do pool usado para fazer consultas em buffer às tabelas InnoDB. Tente fazer com que isso seja pelo menos tão grande quanto o tamanho total de 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 pools de buffer InnoDB para operar. Cada página armazenada no buffer pool é atribuída aleatoriamente a uma das instâncias. Um número maior de instâncias pode melhorar a simultaneidade.
 innodb_log_file_size: tamanho máximo dos arquivos de log de redo em um grupo de log. Esses arquivos são usados ​​durante a recuperação de falha para restaurar transações incompletas. Valores maiores melhoram o desempenho, mas aumentam o tempo de recuperação em caso de falha.
 key_buffer_size: É semelhante a innodb_buffer_pool_size, mas é usado para tabelas MyISAM. Observe que se você estiver usando exclusivamente as tabelas MyISAM ou InnoDB, deverá definir a variável relevante de acordo e alterar a outra para um tamanho relativamente baixo, como 32M. Caso contrário, você estaria desperdiçando RAM ao provisionar um grande espaço de buffer para um tipo de tabela não utilizado.
 join_buffer_size: Isso define o tamanho do buffer usado para joins sem índices. Aumentar o tamanho desse buffer irá acelerar as consultas, que usam junções não indexadas. Definir um valor muito alto pode levar a problemas de memória, pois um buffer de junção é alocado para cada junção completa entre as tabelas. Junções complexas entre várias tabelas precisarão de vários buffers, cada um com a capacidade de join_buffer_size, o 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 podem aumentar o uso de memória em um servidor altamente ativo.
 Você deve sempre avaliar todas as alterações em relação ao tamanho do seu conjunto de dados e aos recursos de hardware do seu servidor. Definir esses valores muito baixos afetará o desempenho da consulta, enquanto, inversamente, defini-los muito altos pode levar ao uso excessivo da memória ou até mesmo ao esgotamento da memória. O MySQLTuner emitirá avisos se sua configuração correr o risco de consumir toda a memória disponível do sistema. 
 Analisando consultas lentas 

        
        
        
 
 Habilitar o log de consulta lenta fornece uma visão sobre consultas de baixo desempenho. Você pode fazer isso em uma sessão de shell do MySQL: 
 
 sudo mysql SET GLOBAL slow_query_on = "Ligado"; SET GLOBAL slow_query_log_file = "/slow-query. log"; SET GLOBAL long_query_time = 5; 
 Esta configuração registrará qualquer consulta que leve mais de cinco segundos para ser executada em /slow-query. log. Examine este arquivo periodicamente para identificar consultas de longa duração. 

        
        
        
 
 
 
 Depois de encontrar uma consulta problemática, você pode usar uma instrução EXPLAIN para obter informações sobre o que está causando a lentidão. Prefixe a consulta com EXPLAIN e execute o comando em um shell do MySQL. Você obterá uma saída tabulada mostrando como o MySQL planeja executar a consulta. 
 A saída de EXPLAIN inclui informações sobre os índices disponíveis, as chaves usadas e o número de registros avaliados. Interpretar dados de EXPLAIN é um tópico em si. Orientação detalhada 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 EXPLAIN graficamente. Isso pode ajudá-lo a visualizar a abordagem do mecanismo de banco de dados para buscar 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 EXPLAIN (você não precisa adicionar o prefixo EXPLAIN manualmente.). No painel de resultados, você verá o plano de execução visual destacando as operações envolvidas. 
 O papel dos índices 

        
        
        
 
 É importante garantir que o seu conjunto de dados contenha os índices apropriados. O uso correto de índices aumenta substancialmente o desempenho da consulta. 
 SELECIONE * DE usuários ONDE Email ='example@example. com '; 
 Esta consulta deve ter um índice no campo users. Email. Sem um índice, o MySQL precisaria realizar uma verificação de tabela completa, causando um exame lento de cada registro. 

        
        
        
 
 Com o índice, o mecanismo de banco de dados é capaz de identificar os registros com muito mais rapidez. 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 ser classificados para que o MySQL possa ir direto para os dados relevantes. 
 Para adicionar um índice a um campo existente, use a instrução ADD INDEX com ALTER TABLE: 
 ALTERTABLE my_table ADDINDEX my_index & # 40; my_field & # 41 ;; 

        
        
        
 
 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 = 1AND y = 2ORDERBY z;   ALTERTABLE my_table ADDINDEX cover_index & # 40; x, y, z & # 41 ;; 

        
        
        
 
 Ao usar índices de cobertura, a ordem dos campos é importante. Se você consultasse WHERE z = 1 ORDER BY x, o índice criado acima não seria usado. 
 Você deve se certificar de que os campos usados ​​nas cláusulas WHERE ou JOIN ... ON são cobertos por um índice. Consultar campos não indexados pode rapidamente se tornar um gargalo de desempenho. No entanto, tenha cuidado ao indexar todos os campos — se você &’ nunca consultará esse campo, o índice é uma sobrecarga desnecessária que ainda deve ser mantida pelo MySQL. 
 Você pode identificar as consultas que se beneficiariam de um índice ativando o registro de consultas não indexadas. Siga as instruções acima para habilitar o log de consulta lenta. Você deve então executar SET GLOBAL log_queries_not_using_indexes = "On" a partir de um shell MySQL. Isso iniciará o registro de consultas com índices ausentes no log de consultas lentas. As consultas não indexadas serão incluídas mesmo se não excederem o tempo de consulta lenta configurado. 
 
        
        
         
 
 Conclusão 
 Não existe uma abordagem universal para melhorar o desempenho do MySQL. As etapas que você precisará realizar dependerão dos recursos do seu servidor, do tamanho do seu conjunto de dados e do nível de contenção de recursos causado por outras cargas de trabalho em execução na máquina. 
 Você também não deve ignorar a camada do seu aplicativo — pode não ser o MySQL a raiz dos seus problemas de desempenho. Inspecione a maneira como seu código faz consultas. Se houver looping excessivo, como usar uma consulta em uma rotina N + 1, refatorar esse código pode ser muito mais impactante do que microgerenciar seu servidor MySQL.