Como você rastreia e depura problemas de desempenho do mySQL?

Question

Eu tenho um sistema de servidor em execução no Mac OS X 10.4 (versão do kernel do Darwin 8.10.1). Este servidor é usado principalmente como um servidor Bugzilla, mas são alguns outros serviços baseados na web em execução (Testlink, TikiWiki).

O banco de dados do Bugzilla tem cerca de 60000 bugs, e há cerca de 300 usuários ativos no sistema.

O Bugzilla está na versão 3.0, rodando em Perl 5.8.6, Apache 1.3.33 com mySQL 5.0.38

De tempos em tempos, temos sérios problemas que o Bugzilla lança um erro no banco de dados:

Software error:

Can't connect to the database.
Error: Too many connections

Já tenho várias pistas para possíveis soluções para este problema, mas eu queria trazer uma questão mais geral sobre como depurar esses tipos de problemas?

Neste momento, configuramos o seguinte para monitorar o banco de dados mySQL:

• Um cron job que copia a lista de processos completa do mysql a cada 5 minutos
• Habilitar consultas de baixa velocidade em my.cnf para registrar consultas que demoram mais tempo de 15 segundos

Acabamos de reunir esses dados para ver se podemos encontrar um motivo para o problema "Muitas conexões".

Existem outras coisas que você pode pensar em monitorar um mySQL banco de dados e para ajudar a diagnosticar a causa raiz do problema?

    

Answer 1

Existem dois planos distintos de ataque para acompanhar ao diagnosticar esses tipos de erros:

Em primeiro lugar , existe o potencial de que é um problema relacionado ao software em uso: algo é essencialmente sugando conexões e não liberando-as de volta (seja em termos de thread, ou em um período de tempo razoável em termos de uma consulta lenta).

O log de consultas lentas é muito benéfico no diagnóstico de problemas, mas seu valor de 15 segundos é quase inútil: se uma consulta leva 15 segundos, o ponto final é muito complicado. Como regra geral, procuro por consultas que levem mais de um ou dois segundos para serem executadas. Trabalhe o que aparecer nesse log usando a palavra-chave EXPLAIN e observe o que está causando a lentidão (junções incorretas, classificação precisando de uma tabela temporária etc.) - alguma mágica inteligente com caches de consulta e índices pode ajudar se não for possível aprofundar e mexer no design de código / banco de dados.

Além disso, não negligencie o log de consulta geral no mysql. Embora você não queira deixá-lo ligado (por muito tempo) em um servidor de produção, ele pode dizer rapidamente se, em vez de uma única consulta ter uma certa idade, uma função específica no software é martelar o banco de dados com centenas de pequenas consultas. Obviamente, a única maneira de resolver esse tipo de problema é refatorando o código.

Em segundo lugar , você precisa investigar se a configuração do software é a culpa. Quantas conexões simultâneas você está experimentando? Qual é o número real de conexões máximas definidas no mysql? Pode ser algo tão simples como o apache está servindo digamos 100 solicitações simultâneas enquanto o mysql é configurado apenas para aceitar 20 conexões - obviamente algo vai dar. Se você puder avaliar a quantidade de tráfego que espera manipular, será necessário algum senso comum (e ocasionalmente uma pitada do Google para encontrar a configuração correta) para equilibrar todos os componentes.

    

Answer 2

Quantos funcionários do apache você tem? Como quais são as conexões máximas do mysql que você permitiu? Como o apache gera um processo cgi por trabalhador do httpd ao manipular o pedido, então o primeiro é maior que o último, o qual pode abrir mais conexões do que o mysql permitirá.

Eu sugeriria as seguintes configurações de log

log_slow_queries
log-queries-not-using-indexes
set-variable = long_query_time=1

    

Answer 3

Um trabalho cron para despejo é muito útil, mas caso você não tenha nada pronto para representar graficamente a coisa, eu posso recomendar munin que tem plugins MySQL para monitorar

Taxa de transferência
• consultas
• tamanho dos bancos de dados
• consultas lentas
• threads

que pode ser bastante útil para determinar picos. Eu corro em cinco minutos de intervalo por padrão.

Utilizando-o ao longo do último ano, descobri uma situação bastante interessante com ele antes, o que teria passado completamente despercebido de outra forma.