(Resolvido) Problemas na performance utilizando triggers / storeprocedures.

[byquico]

Bom dia,

Pessoal estou com um problema de performasse que confesso que não estou entendendo o que possa ser. Tenho uma base com + ou - 1,5 Gb.
Depois de muito tentar entender o que esta acontecendo consegui perceber que quando eu faço um update em uma tabela com + de 1100000 de registros ela leva 0.016 sec para ser executada porem quando eu levo ela pra dentro de uma "Stored Procedures" ela leva 3.167 sec para ser executado.

CALL byp_completeagent(37, 2, '1426122402.376438');
CREATE DEFINER=`%` PROCEDURE `byp_completeagent`(_calltime integer, _origposition integer, _callid VARCHAR(20))

BEGIN
update byphone_aux SET calltime = _calltime, origposition = _origposition, orighangup = 'B' WHERE callid = _callid;
END

E se eu fizer a seguinte alteração na procedure ela leva os mesmos 0.016 sec para ser executada.

CALL byp_completeagent(37, 2, '1426122402.376438');
CREATE DEFINER=`%` PROCEDURE `byp_completeagent`(_calltime integer, _origposition integer, _callid VARCHAR(20))
BEGIN
update byphone_aux SET calltime = 37, origposition = 2, orighangup = 'B' WHERE callid = '1426122402.376438';
END

alguém já passou por essa situação pra dar uma luz ???

[Denis Courcy]

Tente assim:

CREATE DEFINER=`%` PROCEDURE `byp_completeagent`(IN _calltime integer, IN _origposition integer, IN _callid VARCHAR(20))
BEGIN
   DECLARE msg VARCHAR(1000) DEFAULT "sem mensagem";
   DECLARE excecao SMALLINT DEFAULT 0;
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET excecao = 1;  
 START TRANSACTION;
 update byphone_aux SET calltime = _calltime, origposition = _origposition, orighangup = 'B' WHERE callid = _callid;
 IF excecao = 1 THEN
    SET msg = "Erro na grvacao";
    ROLLBACK;
 ELSE
    COMMIT;
    SET msg = "Gravado com sucesso";
 END IF;
 SELECT msg; 
END

Vai forçar a gravação em disco;

[byquico]

Obrigado pela ajuda Denis Courcy,

Fiz os testes como você sugeriu, e ainda continua muito lenta o update dentro da procedure,

Acredito que o bug relatado http://bugs.mysql.com/bug.php?id=61401seja o mesmo problema que estou tendo.

Pelo visto terei que fazer todo o controle na aplicação.

Editado Março 14, 2015 por byquico

[Denis Courcy]

Não há como instalar uma versão anterior do MySQL? Tal como 5.5.2-m2?

Seria mais prático do que alterar sua aplicação e você poderia aguardar até o lançamento de uma nova release que tivesse este problema resolvido.

Pense.

[byquico]

No BD de produção estou usando a versão 5.1.49 Debian.

Instalei no meu computador a versão 5.6.22 Win64 e apresentou a mesma lentidão.

Seguindo seu pensamento vou instalar a primeira versão 5.0 pra ver se não vai apresentar o problema.

Vai ser muito trabalhoso e oneroso fazer essa mudança, mas se não resolver ... é um sistema de alta disponibilidade e este problema esta me prejudicando muito.

Abrç.

Fiz o teste com a versão 5.0 a performasse foi medonha quase 5 segundos, a versão 5.5.2-m2 manteve os 3.5 segundos como as versões anteriormente testadas.

Mais alguma luz ? Fiz uma limpeza* na base pra conseguirem ir trabalhando mas não vou ter que restaurar as informações o quanto antes.

Editado Março 15, 2015 por byquico

[Denis Courcy]

Agora que você falou de linux, verifique este tópico: https://www.scriptbrasil.com.br/forum/topic/178477-demora-em-comitar-dados-servidor-linux-mysql-demorando-atualizar/

[byquico]

Companheiro,

Eu sempre faço testes no Linux e No Windows, como eu tinha dito, os testes foram feitos no Windows 64.

Tive urgências em outros projetos mais importantes e tive que pausar esse (apagando mais históricos), mas se interessar monto um ambiente de testes.

Abrç. e mais uma vez obrigado pela ajuda.

[byquico]

[RESOLVIDO]

Depois desses três anos finalmente consegui descobrir qual que era o problema venho compartilhar o a solução do meu problema. 

Nesses três anos eu sempre vinha mantendo o volume da tabela de dados pequeno para a lentidão não atrapalhar o processo, porem, recentemente tive outro problema de lentidão no mysql em um select comum. Por se tratar de um select com inner join comum com aproximadamente 100K cada tabela, ele estava demorando a retornar de 1 a 3 seg em cada consulta. Depois de muita analise e testes até que percebi que o Collation  de uma tabela estava utf8 e da outra latin1. Fendo a correção para o mesmo tipo tudo voltou a normalidade. 

E a luz finalmente brilhou, e meu problema de lentidão com a triggers se fez resolver por um passe de magica. 

:/ agora a explicação logica...
Relembrando: O update é simples demorava 0,016 seg pra ser executado, mas quando eu o colocava dentro da trigguer ele ia para 3 seg em media 187,5 x a mais.

Fato: Quando você utiliza tipo collation diferentes o mysql "não te chama de jumento", mas, para rodar ele coloca implicitamente
*COLLATE latin1_general*, então meu update ficava assim: "update byphone_aux SET calltime = _calltime, origposition = _origposition, orighangup = 'B' WHERE callid  COLLATE latin1_general = _callid;" Com isso o mysql é obrigado a percorrer todos os registros fazendo a alteração para ver se acha o registro que você precisa.