conexão MySQL pico do nada

Question

Estamos executando um site em três servidores. Dois deles são servidores web com balanceamento de carga e o último é um servidor mysql dedicado. O servidor mysql está rodando o RHEL5 de 64 bits usando um kernel SMP 2.6.18-92.1.6.el5 # 1 e MySQL 5.0.45. É um servidor bastante robusto também, com um Xeon L5420 e 8 GB de RAM. As páginas php do nosso servidor web são configuradas para usar o mysqli.

Normalmente, não usamos todos os recursos que recebemos, fazemos 20 a 25 consultas por segundo durante o dia. No entanto, de vez em quando, vamos bater de cabeça no limite máximo de conexões de banco de dados e com isso nosso site coaxa. Além disso, parece acontecer à noite, onde o tráfego do site deve ser mínimo.

Começamos em 100 conexões máximas, aumentamos para 300 e ainda assim ocorre. Se isso faz diferença, notamos que às vezes há toneladas de processos MySQL adormecidos, mas nada que se conecta ao banco de dados utiliza conexões persistentes. Isso não acontece todas as noites, tivemos alguns problemas em que ele iria coaxar todas as noites e, em seguida, tudo bem por cerca de uma semana e meia até hoje.

Não temos consultas de monstros que ligam o banco de dados por alguns minutos por vez. Tentamos dar uma olhada no log de SLOW_QUERY. Temos algumas consultas que aparecem lá, mas geralmente não duram mais do que 1 ou 2 segundos e são pouco freqüentes.

Isso soa como algo em particular? Como procederíamos daqui em termos de diagnosticar o problema?

    

Answer 1

Meu palpite é que você tem algumas consultas longas em seu aplicativo. Quando elas são executadas, elas fazem com que a conexão permaneça fora do pool por um longo período (em relação ao padrão de uso usual), o que faz com que o pool se esgote, cresça e continue crescendo até o máximo. quaisquer trabalhadores restantes bloqueiam a espera de conexões para serem liberadas.

A primeira coisa será rastrear quando isso acontecer, isto é, é um evento cíclico ou aleatório. Se é o primeiro você está com sorte, como você pode estar pronto o tempo acontece. Se você não pode determinar um padrão, então você terá que ser vigilante.

Você pode descobrir isso consultando os registros de monitoramento de seu website ou sar do seu banco de dados para ver se há algum pico de correlação.

Se você pode pegar seu banco de dados quando está sob carga, você deve executar os seguintes comandos no servidor mysql

show innodb status;
show processlist;

O primeiro irá imprimir informações de diagnóstico sobre o mecanismo innodb (você está usando innodb, certo?), este último imprimirá as primeiras centenas de caracteres da consulta que estava sendo executada. Procure por consultas que estão em execução há muito tempo, consultas que geram tabelas temporárias no disco e consultas bloqueadas em um recurso.

Depois disso, o trabalho duro começa. Use EXPLAIN para estimar o custo da consulta e os recursos que ela usa. Evite consultas que exigem classificação no disco por meio de uma tabela tmp. Procure trabalhos de relatórios de longa duração ou outras tarefas de manutenção agendadas que periodicamente bloqueiam ou saturam seu banco de dados. Pode ser algo tão simples quanto a tarefa de backup ou um trabalho que acumula dados antigos de pedidos de compra.

Eu recomendo ter essas três configurações em /etc/my.cnf

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

Para um aplicativo da Web que faz de 20 a 30 solicitações por segundo, você não pode permitir que nada apareça nesses registros.

btw, IMHO é inútil aumentar o tamanho do pool de conexão além do tamanho original, pois isso só atrasará o início do esgotamento da piscina em, no máximo, alguns segundos, e só colocará mais pressão no seu banco de dados quando ele não precisa disso.

    

Answer 2

Eu já vi isso antes.

Nós tivemos um cron fazendo um mysqldump de um banco de dados com tabelas MyISM. Por causa do MyISM mysql dump iria bloquear tabelas inteiras. fazendo com que as consultas (e, portanto, as conexões) enfileirem-se.

    

Answer 3

AlexMax,    Onde você consegue resolver esse problema? Embora existam diferenças sutis, atualmente estou vendo problemas semelhantes, como você descreve aqui, com 14 servidores da Web sendo balanceados para 8 servidores mysql (cada site é codificado para um dos servidores da web). Conexões irão disparar e mais de 90% das que estão na lista de processos do mysql serão listadas como sleep com a Consulta NULL. Isso fará com que o mysql pare de permitir conexões e dure aprox. 2-3 minutos. MySQL 5.0.70 e PHP 5.28 em Quad Xeons (32bit para nós embora).