PHP + MySQL Timeout

[Rodrigo Brouck 0]

Olá,

 

Estou com um problema que me parece simples, mas verifiquei diversas coisas na web e nada está resolvendo.

 

Tenho uma QUERY com um processamento grande em MySQL chamada em programação PHP.

 

Esta mesma query, utilizando um MySQL Client, funciona perfeitamente.

 

Creio que exista algum parâmetro ou configuração PHP ou MySQL que esteva expirando e o processamento ou a conexão, simplesmente caia e dê o erro na consulta.

 

Alguém poderia me ajudar?

 

MUITO OBRIGADO!

[Gabriel Heming 766]

Se for o que eu entendi.

 

O php possui um tempo de execução de script, por padrão é 30 segundos. Em servidores online, normalmente passa-se para 180 segundos, mas varia.

 

Você pode utilizar essa função set_time_limit ou alterar no próprio php.ini

[Rodrigo Brouck 0]

Olá Gabriel,

 

Primeiramente, muito obrigado pela sua disposição junto ao meu problema.

 

Sobre a sua sugestão, gostaria de saber se o parâmetro no script do PHP, ou seja, dentro do código, sobrepõe o arquivo de configuração php.ini.

Pois, tenho o arquivo php.ini com o max_execution_time ajustado para 120000.

 

Ainda sim ... tenho problemas.

 

Somente para saber, rodando no HeidiSQL mostra um processamento de 70.372 sec a VIEW sem nenhuma aplicação de filtro na cláusula WHERE. Obviamente a solicitação no programa tem filtros. Sendo assim, o processamento seria ainda menor.

 

O que sugere?

[Gabriel Heming 766]

Mesmo com esse tempo de execução retorna time_out?

Rode novamente, e poste o erro aqui, quero dar uma conferida.

[Rodrigo Brouck 0]

Gabriel,

 

Sim ... mas o erro retorna de forma mascarada.

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ../public_html/functions/func_dados.php on line 569

 

Esse erro seria como se o campo acessado pela instrução MySQL não existisse ou tivesse incorreto.

Mas na verdade, se eu reduzir o banco de dados, funciona.

 

O relatório em questão que acessa a VIEW funciona perfeitamente.

Com uma base um pouco maior, não funciona.

[Gabriel Heming 766]

você já rodou todo o script em partes pelo PHP? Tenho a sensação de que não é timeout.

 

faça assim, ande você realiza a consulta, inclua.

mysql_query('...') or die( mysql_error() );

 

Há outra diretiva que você pode alterar no php.ini

 

mysql.connect_timeout -> padrão 60 segundos

[Rodrigo Brouck 0]

Olá Gabriel,

 

Seguindo sua sugestão, coloquei o comando 'or die' na instrução mysql_query, mas ... não retorna nenhum erro.

 

Colocando um die($sql) para avaliar a instrução SQL, tenho uma instrução bem simples.

 

SELECT * FROM diabetes_programs_savings WHERE id_filial = '16' AND id_program = '7'

 

Como falei anteriormente, se simplesmente reduzir o conteúdo da base de dados, ou seja, apagar algumas linhas, a consulta funciona perfeitamente.

 

E a mesma instrução no MySQL Client (HeidiSQL), funciona perfeitamente.

[Gabriel Heming 766]

De quantos registros estamos falando?

[Rodrigo Brouck 0]

Olá Gabriel,

 

Em termos de registros, a consulta retorna apenas 2 registros.

 

MAS, tenho 3 VIEWs em cascata de processamento.

Este relatório é um relatório final de processamento.

 

Ele pega uma VIEW com números.

Multiplica por uma VIEW de custos.

E gero uma VIEW de somatório em 10 anos.

 

Faço isso para outra situação.

 

E, a QUERY em questão é a subtração de um grupo de VIEWs com o outro grupo.

Para mostrar a diferença entre os números.

 

"id_filial" "id_program" "id_cost_type" "savings_total"

"12" "5" "1" "207439.53"

"12" "5" "2" "1663975.60"

 

Basicamente isso.

 

Esse é o GRID extraído com Ctrl+C do HeidiSQL.

 

É uma tabela bem simples que vem de um processamento bem complicado ... rsrs

[Gabriel Heming 766]

Dando uma lida em alguns fóruns, encontrei as seguintes diretivas que podem influenciar.

set_time_limit(0); 
ini_set('mysql.connect_timeout','0'); 
ini_set('max_execution_time', '0'); 

 

Como a primeira e a terceira já foram alteradas, tente a segunda.

Por padrão, ela é 60 segundos.

Já que sua consulta demora 73 segundos, deve ser a razão.

[William Bruno 1501]

@Rodrigo Brouck,

 

já tentou otimizar as outras tabelas envolvidas ?

 

 

Neste trecho aqui:

id_filial = '16' AND id_program = '7' 

se esses IDs são inteiros, então você não deveria colocar aspas.

[Rodrigo Brouck 0]

Olá Gabriel, Olá Bruno,

 

Primeiramente muito obrigado pela atenção.

 

Tentarei inserir diretamente os parâmetros que o Gabriel passou no mainfile do sistema para afetar todo os processamentos.

 

Olá Bruno,

 

Sobre otimizar, as tabelas principais estão otimizadas.

 

Neste processamento, tratam-se de VIEWs. Não sei como otimizar VIEWs.

E estas VIEWs estão em cascata.

São praticamente 6 VIEWs para chegar até esta última VIEW.

 

Por isso o processamento fica tão demorado. São muitos cálculos.

 

Se tiver alguma sugestão em relação a estrutura, podemos falar sobre isso também.

 

Fazendo um overview da coisa ...

 

Tenho uma VIEW com números (A1)

Depois uma VIEW que pega estes números e calcula um custo (B1)

Depois uma VIEW que pega esta VIEW B1 e executa um SUM na cláusula SELECT para ter uma tabela mais resumida (C1)

 

Depois faço isso novamente para ter um comparativo entre dois modelos de cálculos.

Sendo assim gero A2, B2 e C2.

 

Depois, esta é a última VIEW que pega a diferença entre C1 e C2.

Estes são os números que preciso.

 

Estas VIEWs são todas dependetes uma das outras em cascata.

 

Então, quando tenho uns 16 modelos para comparar a coisa fica meio que insustentável ... rsrs

 

---

 

Mas isso foi apenas para mostrar o volume de dados.

 

O mesmo processamento funciona perfeitamente se estivermos rodando em uma situação com apenas 2 ou 3 comparativos.

Ou seja, existe realmente algum esgotamento, seja de PHP ou MySQL que gera o erro.

 

Como falei anteriormente, usando um dbForge ou HeidiSQL a instrução MySQL funciona perfeitamente.

[Gabriel Heming 766]

Não sei exatamente como funciona a otimização das tabelas do MySQL pois é automático.

 

Entretanto, no Postgre, deve-se criar um index para cade modo de consulta. Existem 4 tipos de índices, se não me engano, mas normalmente são utilizados apenas 2: hash e btree.

 

O hash é ótimo para valores específicos (utilizando o operado '='(igual) ou '>=' e '<=') e que não sejam primary key (Não é permitido utilizar hash para PK).

Btree é ótimo para ranges (utilizando o operado '<', '>', 'BETWEEN', 'IN' e assim por diante ) e primary key.

 

 

Essa é a parte dos tipos de Índices. A outra parte é como criá-los.

Realmente não pode-se criar index para view, você deve criá-los para as tabelas de sua consulta.

 

Por exemplo:

 

Você possui a tabela Pessoa, e você vai procurar pela data de nascimento entre 01/01/1980 até 31/12/1990.

SELECT * FROM tabela_pessoa WHERE data_nascimento BETWEEN '1980-01-01' AND '1990-12-31'

Isso é um range, logo, você criará um index btree para a coluna da data de nascimento.

 

CREATE INDEX meu_primeiro_indice ON tabela_pessoa USING BTREE (data_nascimento);

 

Caso você quer consultar todas as pessoas que possuam 3 filhos ou mais.

SELECT * FROM tabela_pessoa WHERE quantidade_filhos >= 3

Você deve criar como hash.

 

CREATE INDEX meu_segundo_indice ON tabela_pessoa USING HASH (quantidade_filhos);

 

E quando você quiser buscar quem tenha mais de 3 filhos e nasceu 01/01/1980 até 31/12/1990.

SELECT * FROM tabela_pessoa WHERE quantidade_filhos >= 3 AND data_nascimento BETWEEN '1980-01-01' AND '1990-12-31' 

Você deve criar um terceiro índice pelo tipo de consulta a ser realizada. Quando utiliza-se mais de uma coluna, necessariamente deve-se usar Btree.

 

 

CREATE INDEX meu_terceiro_indice ON tabela_pessoa USING BTREE( data_nascimento, quantidade_filhos);

 

Bom, isso foi o que eu aprendi realizando testes no Postgre, a diferença é absurda. Eu criava um índice, testava e comparava os resultados. Uma consulta de mais de 90 segundos (realizada através de uma view) chegou a cair para 5 segundos.

[Rodrigo Brouck 0]

Olá Gabriel,

 

Certo ... as tabelas reais (não sendo VIEWs) já estão com os indices criados.

 

Obrigado

[Gabriel Heming 766]

Achei esse artigo sobre Índices no MySQL, mas pelo que vi, não há algoritmos de indexação.

MySQL – Otimização Criando Índices

 

 

--------------------

@Edit

as tabelas reais (não sendo VIEWs) já estão com os indices criados.

Um por campo ou por agrupamento de colunas da consulta?

[Rodrigo Brouck 0]

Olá Gabriel,

 

Os índices estão ajustados de forma agrupada para as cláusulas LEFT JOIN nas consultas das VIEWs.

 

Olá Gabriel,

 

Sobre a QUERY principal, acho que o tempo não é mais problema.

A consulta passa e mostra o resultado.

 

Acontece que após a consulta, tenho umas pequenas QUERIES que traduzem os códigos para os relatórios e fazem consultas simples apenas para buscar a descrição de alguns campos relacionados.

 

Tentei colocar novamente uma instrução para conectar a base de dados, mas ainda sim ... não conecta.

 

Preciso fechar manualmente a conexão para abrir novamente ou algo do tipo?

 

Obrigado

[Gabriel Heming 766]

Não. Você pode realizar n consultas sem necessidade de reabrir a conexão. Exceto se ela 'cair'.

 

Pensando bem, não seria interessante você mudar o modo de conexão para a classe MySQLi? Ela oferece uma ótima interface de conexão e pode ser que resolva o seu problema.

[Rodrigo Brouck 0]

Olá Gabriel,

 

Já uso conexão mysqli_connect para resolver instruções SQL como 'CALL ...', mas ... abro também uma conexão mysql_connect para pequenas SELECTS.

 

Será que isso está causando problemas?

[Gabriel Heming 766]

Depende, isso "me" causa problemas... hehehe

 

Não acho interessante misturas as coisas. Qual o propósito de você utilizar os dois?

 

Mas, como nunca utilizei ambos juntos, não sei se é isso.

[Rodrigo Brouck 0]

Gabriel,

 

Muito obrigado.

 

Realmente o problema foi solucionado com as informações de configuração do PHP para set com '0'.

 

E, inseri antes de todas as consultas, uma verificação de conexão ativa.

Caso a conexão esteja perdida ele reativa a conexão.

 

Outra coisa, algumas rotinas terão, necessariamente que serem feitas com threads para processamento simultâneo.

Senão, tudo irá demorar demais no sistema.

 

Enfim ... agora tenho trabalho para semanas.

 

Muito obrigado.