Problemas de performance com indicie unico

[André H. Ferreira 0]

Olá pessoa

 

Estou trabalhando em um projeto inovador que possivelmente em breve está online para todos, vou inicialmente fala do projeto e da arquitetura e depois do meu problema SERIO.

 

O Projeto

O projeto é de um comparador de preços em tempo real, semelhante ao buscapé porem atualiza os preços por ajax, além disso teremos Crawleys rodando varias lojas para pegar produtos novos, com isso teremos como verifica quando o preço caiu ou subiu de cada produto de várias lojas web, no começo iremos cubrir Submarino, Americas, Magazine Luiza, Shoptime, Polishop entre outros.

 

A arquitetura e recursos

O projeto é totalmente desenvolvido em PHP 5 e Javascript/Ajax, Atualmente temos cerca de 12 maquinas reservadas ao processo de bot nas lojas, esse processo foi separado em 2 etapas, alem disso temos um servidor de banco de dados bombado (2 Xeon, 8Gb de memória...).

 

Link extrator: Um bot que é responsável por varrer um link. No caso eu passo uma url inicial e o bot vai verificando todos os links dessa url, armazena os link que ele ainda não visitou, e vai aos poucos andando por esses links que ele vai achando... este processo acumula vários links a serem processados por isso são abertos vários bots cada vez que o extrator está sendo rodado. Outra função do extrator é verifica se determinado link é um produto da loja, para isso ele usa filtros especificos para cada loja, identificando que é um produto ele armazena akele link em uma outra tabela.

 

Spider: Um bot que recebe uma lista de links de produtos encontrados pelo extrator, entra em casa link e verificas informações do produto, no caso hoje é armazenado, nome, preço, parcelamento, cod. do produto, imagem e data hora da pesquisa. (Esse bot já esta totalmente perfeito e não apresenta problemas de performance, o que é necessário é um link de internet muito bom para aguentar)

 

Cada processo pode ser rodado em várias maquinas e cada uma dessas maquinas possuem bancos locais para armazenar links que devem ser visitados(Esse processo é funtamental para desempenho em rede VPN que é oq estamos usando), Esse conceito foi criado pois temos 6 locais com GVT de 10 ~ 30MB e resolvemos dividir o peso, com isso ganhamos internet mais perdemos velocidade de comunicação entre bot e servidor principal.

 

O bot enviam pacotes de registros contendo de 1000 a 5000 links a cada 5min, o problema é que são de 20 a 50 bots por maquina e serão 12 maquianas, calcula ai a quantidade de dados...

 

O Problema

 

Cada banco local possui uma tabela chamada link_extrator_controller essa tabela é responsavel por controlar quais links já foram visitados e quais ainda falta ser visitado, antes essa tabela ficava no banco principal porem so tornou impossivel usar essa arquitetura via VPN, essa tabela é pequena mais a informação que trafegua nela por segundo é muito grande, por isso foram criados vários indicies nos campos mais consultados.

 

Quando o extrator verifica que um link é de produto ele vai armazenando em cache ate chega um timeout que hoje é de 5min, quando ocorre isso ele manda um pacote com todos os links que ele acumulo nesses 5min, ai que ta meu maior problema.... na tabela que recebe essa informação temos o campo url, e o campo hash, esse campo hash deve ser unico para evitar links duplicados(esse campo hash foi criado para evitar comparação de strings no campo url), ate ai beleza mais quando eu crio um indicie para esse campo UNIQUE, o tempo para inserir novos registro fica muito lerdo, devido a quantidade de registro que eu tenho q comprar antes de inserir, isso ao longo do tempo fica pior ainda...

 

Depois de muito bater cabeça to tentando a ajuda da comunidade

Agradeço a atenção

[Shrek-BH 0]

Mas se você já criou o campo UNIQUE não tem como você inserir os dados com REPLACE? Pois você disse que demora pela quantidade de registros que precisa comparar.

 

Outra coisa, não sei se você sabe, que me ajuda muito quando preciso incluir muitos registros.

Isto:

   $query1 = 'INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo4_1")';
   $resultado1 = mysql_query($query1, $conexao);
   $query1 = 'INSERT INTO tabela VALUES("campo1_2","campo2_2","campo3_2","campo4_2")';
   $resultado1 = mysql_query($query1, $conexao);
   $query1 = 'INSERT INTO tabela VALUES("campo1_3","campo2_3","campo3_3","campo4_3")';
   $resultado1 = mysql_query($query1, $conexao);

É muito mais lento do que isto:

   $query1 = 'INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1"),("campo1_2","campo2_2","campo3_2","campo4_2"),("campo1_3","campo2_3","campo3_3","campo4_3")';
   $resultado1 = mysql_query($query1, $conexao);

Cuidado que as variáveis com os INSERT´s têm um limite de caracteres heim!!!

[André H. Ferreira 0]

Mas se você já criou o campo UNIQUE não tem como você inserir os dados com REPLACE? Pois você disse que demora pela quantidade de registros que precisa comparar.

 

Outra coisa, não sei se você sabe, que me ajuda muito quando preciso incluir muitos registros.

Isto:

   $query1 = 'INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo4_1")';
   $resultado1 = mysql_query($query1, $conexao);
   $query1 = 'INSERT INTO tabela VALUES("campo1_2","campo2_2","campo3_2","campo4_2")';
   $resultado1 = mysql_query($query1, $conexao);
   $query1 = 'INSERT INTO tabela VALUES("campo1_3","campo2_3","campo3_3","campo4_3")';
   $resultado1 = mysql_query($query1, $conexao);

É muito mais lento do que isto:

   $query1 = 'INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1"),("campo1_2","campo2_2","campo3_2","campo4_2"),("campo1_3","campo2_3","campo3_3","campo4_3")';
   $resultado1 = mysql_query($query1, $conexao);

Cuidado que as variáveis com os INSERT´s têm um limite de caracteres heim!!!

 

O processo é feito em bloco mais esse não é o problema principal, meu problema é como melhorar a performance do campo unique quando preciso trabalhar com escala de milhão de registros?

 

Abaixo como o processo de bloco funciona, outra coisa a configuração do banco de dados o limite de cache por Query é bem alto por isso não tenho problemas enviar blocos com 5000 registros.

 

BEGIN;
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1");
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1");
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1");
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1");
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1");
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1");
COMMMIT;

[Shrek-BH 0]

Mas o problema não está na hora que você vai inserir os pacotes com os dados? Eu achei que o que estava lento era os insert´s dos pacotes.

O que está lento é quando você vai buscar os dados na tabela?

 

O que você quis dizer com "ate ai beleza mais quando eu crio um indicie para esse campo UNIQUE, o tempo para inserir novos registro fica muito lerdo, devido a quantidade de registro que eu tenho q comprar antes de inserir, isso ao longo do tempo fica pior ainda..."?

Além dos blocos de INSERT´s você tem outro lugar que insira os dados e aí que você faz essas comparações?

 

O que eu quis dizer aí em cima é que mesmo no seu bloco de INSERT´s tem como você otimizar e muito o seu time de inserção, ainda mais com tantos registros assim

BEGIN;
INSERT INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1");
COMMMIT;

E para evitar essas comparações (que suponho que seja somente para o campo UNIQUE) pensei em algo assim:

BEGIN;
REPLACE INTO tabela VALUES("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1"),
("campo1_1","campo2_1","campo3_1","campo3_1");
COMMMIT;

Se não for no INSERT o problema e sim na hora de fazer a busca mostra a query que está demorando.

[hinom 5]

retire o índice da coluna definida como UNIQUE

 

obs: qual a versão do mysql ?

 

se puder, também poste a DDL das tabelas que estão com lentidão