Gravação pesada no cluster Galera - tabela bloqueada, cluster praticamente inutilizável

Question

Eu configurei o Galera Cluster em 3 nós. Funciona perfeitamente para ler dados. Eu fiz um aplicativo simples para fazer algum teste no cluster. Infelizmente, devo dizer que o Cluster falha totalmente quando tento escrever alguma coisa. Talvez possa ser configurado de forma diferente ou eu errado?

Eu tenho um procedimento armazenado simples:

CREATE PROCEDURE testproc(IN p_idWorker INTEGER)
BEGIN
  DECLARE t_id INT DEFAULT -1;
  DECLARE t_counter INT ; 
  UPDATE test SET idWorker = p_idWorker WHERE counter = 0 AND idWorker IS NULL limit 1;
  SELECT id FROM test WHERE idWorker = p_idWorker LIMIT 1 INTO t_id;
  SELECT ABS(MAX(counter)/MIN(counter)) FROM TEST INTO t_counter;
  SELECT COUNT(*) FROM test WHERE counter = 0 INTO t_counter;
  IF t_id >= 0 THEN
    UPDATE test SET counter = counter + 1 WHERE id = t_id;
    UPDATE test SET idWorker = NULL WHERE id = t_id;
    SELECT t_counter AS res;
  ELSE
  SELECT 'end' AS res;
  END IF;
END $$

Agora meu aplicativo C # simples cria, por exemplo, 3 clientes MySQL em encadeamentos separados e cada um executa o procedimento a cada 100ms até que não haja nenhum registro em que a coluna 'contador' = 0.

Infelizmente - depois de cerca de 10 segundos, o problema é ruim. Nos servidores, há o processo 'query_end' que nunca termina. Depois disso - você não pode atualizar na tabela de teste, o MySQL retorna:

ERRO 1205 (HY000): Tempo limite de espera de bloqueio excedido; tente reiniciar a transação

Você não pode nem mesmo reiniciar o mysql. O que você pode fazer é reiniciar o servidor, às vezes, todo o cluster. O Galera Cluster é tão pouco confiável quando você faz gravações / atualizações em grande escala? Difícil de acreditar.

    

Answer 1

Tivemos praticamente o mesmo problema - quando estávamos atualizando, ele falhou com o tempo limite de bloqueio. Nossa estratégia atual é usar 1 servidor para os processos em segundo plano e as gravações em massa e usar os outros 2 servidores para os servidores da web ao vivo.

Isso é muito fácil se você estiver usando o HAProxy - mas tivemos que fazer algumas alterações em nosso código também.

Até agora, parece funcionar muito melhor, mas definitivamente vamos ver se está funcionando bem em algumas semanas (quando não nos deparamos com os mesmos problemas que tivemos).

Algumas notas da nossa experiência:

Algumas semanas depois, posso dizer que as mudanças realmente fizeram uma grande diferença. Acho que a mudança mais importante foi analisar os processos em segundo plano e programá-los, para que eles não se sobreponham (ou não se sobreponham).

Mudando o servidor, então apenas um servidor é usado principalmente para gravação e os outros dois servidores são usados para leitura, melhorando nossa experiência com o usuário durante os processos pesados de segundo plano.

O terceiro passo que fizemos foi melhorar nossos processos em segundo plano. Durante uma transação, o programador abandonou a tabela e a reconstruiu a partir do zero. Alteramos isso para verificar primeiro se uma alteração é necessária e, em seguida, atualizamos a linha. Isso melhorou o desempenho deste processo extremamente.

Nossa experiência é que a leitura é muito rápida em um cluster Galera, mas a escrita pode ser bem lenta, especialmente se você estiver fazendo toneladas de operações de gravação. Tivemos alguns soluços no começo e tivemos que reimportar toda a tabela - isso foi um grande assassino para o banco de dados. Outra coisa que travou nosso servidor duas vezes foi que o binlog encheu os discos do servidor, que travou o servidor. Certifique-se também de alterar todos os bancos de dados para tabelas Innodb, caso contrário, a perda de dados é possível. Um de nossos programadores definiu todas as tabelas de registro para o MyISAM - digamos que perdemos alguns de nossos logs neste processo.

Mas, afinal, posso dizer que Galera está trabalhando muito bem agora. É especialmente bom se você tiver que atualizar o servidor de banco de dados ou fazer alguma outra manutenção, já que não é um grande problema se você está desligando um nó para fazer alguma manutenção.

    

Answer 2

Eu sei que é tarde, mas vou deixar um pouco da minha experiência no cluster Galera aqui. Nosso aplicativo está fazendo cerca de 160-200 inserções / segundo e fazemos o máximo de leituras. De noite é muito menos, mas durante o dia esta é a nossa média e pode atingir uma taxa de contratação. Nós provavelmente não estão no mesmo contexto do seu aplicativo, mas o que nos ajudou no momento Nós fizemos a transição do MySQL padrão para o Galera para configurar nosso aplicativo para auto commit de cada transação e isso removeu instantaneamente o comportamento que você está descrevendo nesta questão.

Em python usando a biblioteca PySQLPool, tivemos que adicionar uma linha como esta ao nosso wrapper de consulta:

PySQLPool.getNewQuery(self.connection, commitOnEnd=True)

Então, outro problema que enfrentamos foi que ele não estava escrevendo rápido o suficiente para as tabelas. Uma maneira que descobrimos para torná-lo rápido o suficiente foi usar o innodb_flush_log_at_trx_commit opção. Como podemos ter uma perda de 1 segundo da transação, estabelecemos a configuração em my.cnf assim:

innodb_flush_log_at_trx_commit  = 0

Com estas duas configurações simples, estamos agora em produção há mais de um ano usando o Galera e os 3 servidores estão agindo bem com o nosso aplicativo.

Melhor.