(Resolvido) MySql lento em inserções e updates

[Rafael S]

Bom dia pessoal, estou com um problema relacionado ao desempenho do mysql, onde temos um sistema que roda no apache utilizando php.

A ideia do sistema é importar arquivos texto jogando no mysql através de insert ou update conforme o caso do registro lido. Aí vem o problema, no servidor esta importação leva cerca de 18 min, enquanto que na minha máquina leva menos de 1 min.

Para tentar descobrir o problema, verifiquei versões de dll, o httpd.conf, o php.ini e o my.ini, estão todos iguais, com alguma diferença que a memória destinada no my.ini está um pouco maior no servidor (porém é a máquina lenta).

Como não achei o problema analisando os arquivos de configuração, fiz alguns testes para determinar se o problema era no php ou no mysql, então primeiramente removi do código fonte a comunicação com o banco de dados, aí as duas máquinas passaram a ter praticamente o mesmo tempo de execução, enquanto que se eu pegar 1000 updates para teste e executar diretamente pelo phpmyadmin ou outra ferramenta acontece a discrepância, onde no servidor leva quase 30 secs, na minha máquina não leva menos de um segundo. Considerando que o problema seja no mysql coloquei executar os mesmos 1000 updates e olhei o uso de processador, no meu pc chega até 25% (uso máximo considerando que tem 4 núcleos), enquanto que no servidor chega até uns 7%.

Imaginei até que poderia ser problema de índice, mas como as duas bases de dados estão iguais, descartei essa possibilidade.

Mais um dado estranho é que esse mesmo sistema roda em clientes, e até então que instalamos em 3 locais ocorrem esse mesmo problema de lentidão, então quase estou começando a pensar que tem algo instalado na minha máquina que melhora o desempenho, ao invés de ter algo no servidor que piora. Mas ainda não estou querendo aceitar isso.

Como nunca passei por um problema similar e esgotei todas as possibilidades de testes e tentativas de resolução do problema estou pedindo ajuda de vocês.

Estou utilizando a versão 5.1.50 do mysql, tentei instalar a 5.5.15 no servidor, mas ficou com o mesmo tempo de execução.

Algumas considerações:

- Configuração do servidor:

* Core I3;

* 4GB de memória;

- Configuração da minha máquina:

* Core I3;

* 3GB de memória;

my.ini do servidor:

[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.1/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=192M
thread_cache_size=8

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=384M
key_buffer_size=175M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=13M
innodb_buffer_pool_size=1218M
innodb_log_file_size=609M
innodb_thread_concurrency=10
lower_case_table_names=0

[Denis Courcy]

Oi Rafael,

Que comando você está utilizando para a carga deste txt?

Os dados em do txt para uma tabela temporária e depois vão para a(s) tabela(s) de destino? ou vão diretamente para a(s) tabela(s) de destino?

Se você usa tabela temporária como você limpa a tabela antes da próxima carga, você usa Delete ou Truncate?

As tebalas de destino são innodb e possuem constraints com outras tabelas? Se sim você desligou o autocommit e o foreign key checks antes da carga?

Você dá lock nas tabelas de destino para que elas fiquem exclusivas durante a carga (não sofrendo concorrência e aumentando a velocidade de processamento de carga)?

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. suas instruções de carga ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

[Rafael S]

Bom dia Denis, ainda mais depois de você solucionar meu problema, vamos ao detalhes:

- Comando para a carga do txt: $linhas = file($nomeTemporario);

- Os dados vão diretamente para a tabela destino;

- A tabela de destino é innodb e não possui chave estrangeira;

- Por não conhecer ainda muito bem locks em tabelas não utilizei o recurso.

Respondidas as perguntas vamos aos resultados, primeiramente testei a execução desativando o commit automático e a verificação de chaves estrangeiras, mas como esta tabela não tinha chave estrangeira, executei novamente somente desativando o auto commit, e o resultado foi exatamente igual ao primeiro teste nas duas máquinas:

- No servidor o tempo de execução passou para 19 segs;

- Na minha máquina passou para 32 segs;

- Agora sim imagino que fez valer a configuração superior do servidor, pois o tempo de execução foi menor.

Apesar de ter resolvido o problema ainda não consegui entender o porquê minha máquina se comportava de uma forma e o servidor de outra, levando tanto tempo para executar os mesmos comandos. Uma coisa que não coloquei no primeiro post foi que no meu pc existem mais bases de dados e com mais registros que no servidor, mas isso imagino que seria um ponto para deixar mais lento e não mais rápido.

De qualquer forma muito obrigado por resolver o problema!

[Denis Courcy]

Bom dia Denis, ainda mais depois de você solucionar meu problema, vamos ao detalhes:

- Comando para a carga do txt: $linhas = file($nomeTemporario);

- Os dados vão diretamente para a tabela destino;

- A tabela de destino é innodb e não possui chave estrangeira;

- Por não conhecer ainda muito bem locks em tabelas não utilizei o recurso.

...

Apesar de ter resolvido o problema ainda não consegui entender o porquê minha máquina se comportava de uma forma e o servidor de outra, levando tanto tempo para executar os mesmos comandos. Uma coisa que não coloquei no primeiro post foi que no meu pc existem mais bases de dados e com mais registros que no servidor, mas isso imagino que seria um ponto para deixar mais lento e não mais rápido.

De qualquer forma muito obrigado por resolver o problema!

Oi Rafael,

Ainda não terminou, não.

O motivo da lentiddão pode ser porque o arquivo txt está trafegando na rede e em sua máquina está local.

faça carga do arquivo para uma área do servidor

e faça a carga do arquivo para suas tabelas.

Experimente usar o comando LOAD DATA INFILE

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

LOAD DATA  INFILE 'file_name'
    INTO TABLE tbl_name
    [CHARACTER SET charset_name] use esta linha somente se precisar mudar o charset
    COLUMNS [TERMINATED BY 'string' -- use '\t' no lugar de string se a separação das colunas for TAB
    LINES TERMINATED BY 'string'  -- use '\r\n' no ligar de string se o sist.operacional for windows use '\n' se o sist.operacional for linux

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

[Rafael S]

Oi Denis, antes de fazer o primeiro post do problema já tinha testado executar o procedimento levando o arquivo para o servidor, e não mudou em nada, até porque como é feito via upload no apache, depois de enviar o arquivo, o php trabalha localmente com o mesmo.

Na verdade não posso usar load data infile porque o txt não está em formato sql, e sim um relatório comum para impressão, o que fizemos é interpretar cada linha do relatório buscando os valores necessários para montar o sql.

De qualquer forma obrigado, se quiser posso fazer mais algum teste para ver se achamos a origem do problema.

[Denis Courcy]

Oi Denis, antes de fazer o primeiro post do problema já tinha testado executar o procedimento levando o arquivo para o servidor, e não mudou em nada, até porque como é feito via upload no apache, depois de enviar o arquivo, o php trabalha localmente com o mesmo.

Na verdade não posso usar load data infile porque o txt não está em formato sql, e sim um relatório comum para impressão, o que fizemos é interpretar cada linha do relatório buscando os valores necessários para montar o sql.

De qualquer forma obrigado, se quiser posso fazer mais algum teste para ver se achamos a origem do problema.

Neste caso talvez você possa fazer um teste carregando o txt para uma tabela temporária com load data infile

create tab_temp(
linha varchar(255)
)engine=myisam; -- se o arquivo txt for muito grande

ou

create TEMPORARY tab_temp(
linha varchar(255)
)engine=MEMORY -- se o arquivo txt for pequeno

e, depois, usando uma procedure para passar os dados para as tabelas de destino, seguindo as orientações feitas nos posts acima.

Nota:

Para destruir os dados da tabela temporária o melhor é usar DROP TABLE

para destruir os dados da tabela MyISAM o melhor é truncate table.

[Rafael S]

Bom dia Denis, agradeço a ajuda, mas esse teste fica um pouco complicado de eu fazer porque tem várias regras que foram elaboradas no php para buscar os dados, se eu tiver que fazer novamente na procedure vou "perder" um certo tempo, a não ser que a ideia seja carregar esse arquivo só para ver quanto tempo leva.

[Denis Courcy]

Bom dia Denis, agradeço a ajuda, mas esse teste fica um pouco complicado de eu fazer porque tem várias regras que foram elaboradas no php para buscar os dados, se eu tiver que fazer novamente na procedure vou "perder" um certo tempo, a não ser que a ideia seja carregar esse arquivo só para ver quanto tempo leva.

OK, então.

Tópico fechado.

Assunto reolvido.