Atualmente, tenho a replicação do MySQL ocorrendo entre dois mestres bidirecionalmente, um em um data center localmente, outro no Amazon EC2.
Tudo parece funcionar normalmente em termos de replicação, sem problemas em si, exceto para consultas ocasionais que causam uma colisão, mas essas são poucas e distantes entre si. Recentemente, configurei o mysql-proxy para tentar equilibrar a carga dos dois servidores, o balanceamento de carga realmente entra em ação depois que a máquina local recebe 40 ou mais conexões, ponto em que as conexões de banco de dados subsequentes são embaralhadas para a máquina EC2.
Algo que notamos recentemente é que o proxy mysql nos notificará que existem 41 conexões e iniciará seu balanceamento. No entanto, quando eu me conecto à máquina local e faço uma SHOW PROCESSLIST;conexão, ela pode fornecer apenas 30 conexões.
Alguém tem alguma ideia do porquê disso talvez?
Além disso, como resultado da emissão do SHOW PROCESSLIST;comando, notei que há muitas consultas em execução em ambas as máquinas que afirmam que estão sendo executadas há mais de 5.000 segundos. Tenho certeza de que essas consultas são "zumbis", mas alguém sabe por que elas foram criadas?
Para sua informação, estamos executando o mysql versão 5.1.54 nas versões mais recentes do ubuntu e do debian.
Qualquer ideia seria extremamente útil.
[Termo aditivo]
Acontece que não estamos usando mysql_pconnect e, de fato, usando as bibliotecas mysqli. Ainda não consegui descobrir por que isso está acontecendo e relatarei assim que descobrir.
Sou contra escrever para ambos os mestres em uma configuração de mestre duplo. Há muitas coisas que podem dar errado e podem ser complicadas de consertar -- AUTO_INCREMENTs, outras chaves duplicadas, etc.
Centenas de conexões "Sleep" praticamente não causam impacto em um servidor, portanto, limitar a 40 não é útil. 10 ou mais conexões ativas (não-Sleep) podem ser um problema. Nesse caso, eu olharia para as consultas. Normalmente, otimizar as consultas é a melhor resposta.
Observe também que toda gravação (INSERT, UPDATE, etc) feita em um mestre deve ser feita em todos os outros mestres e escravos. Então, você não pode realmente "espalhar" as escritas.
Se você tiver processos que fazem apenas leituras (SELECTs), eles devem ir para os escravos e/ou o mestre de backup, não o mestre ativo e gravável. Isso vai ajudar.
Esteja ciente do problema de "gravação crítica". Exemplo: um usuário publica um comentário em um blog, depois olha seus comentários, mas está faltando. Isso pode acontecer se a gravação for para uma máquina, mas a leitura atingir outra e a replicação estiver "atrasada".
(Meus comentários se aplicam a todas as versões e todas as APIs, não apenas 5.1 e mysqli do PHP.)
Eu fico longe de mysql_pconnect (e outros mecanismos de pooling de conexão). A inicialização/desmontagem da conexão é muito rápida no MySQL. Conexões agrupadas podem ter problemas com @variáveis, modos de transação, sql_modes, etc.