[Resolvido] MySQL dá conta de 37000000000 de consultas?

[Maicon Herverton Lino 0]

Olá pessoal tudo bem? Bom sou programador a 4 anos e sempre utilizei PHP+MySQL para aplicações simples, acabei largando o JAVA+Oracle. Bem hoje trabalho numa empresa de contabilidade e tenho uma empresa para processar 1 milhão registros que precisam ser consultados 37 mil vezes, esta consulta está demorando muito e o relatório leva horas para ser exibido na tela. Criei índices a partir da cláusula WHERE, mesmo assim a demora continua, tentei liberar memória no apache para o servidor PHP trabalhar mais rápido e alterei o my.sql para altas quantidades de memórias e principalmente buffer, o problema é: Será que ele está usando mesmo tudo o que a máquina suporta? Bom se alguém puder me ajudar galera fico grato. Alguma sugestão de BD? E aquela Storage Engine, li sobre mas acho que a melhor pra mim é a InnoBD concordam? Agradeço galera.

[Prog 183]

Pode dar detalhes sobre o equipamento?

Pode compartilhar a consulta? Se puder, a estrutura das tabelas envolvidas na consulta e os indíces.

[Maicon Herverton Lino 0]

Banco:

 

CREATE TABLE IF NOT EXISTS `registro54` (
 `tipo` varchar(2) NOT NULL,
 `cnpj` varchar(14) NOT NULL,
 `modelo` varchar(2) NOT NULL,
 `serie` varchar(3) NOT NULL,
 `numero` varchar(6) NOT NULL,
 `CFOP` varchar(4) NOT NULL,
 `CST` varchar(3) NOT NULL,
 `NumItem` varchar(3) NOT NULL,
 `CodigoMercadoriaServico` varchar(14) NOT NULL,
 `Quantidade` double NOT NULL,
 `ValorMercadoriaServico` double NOT NULL,
 `ValorDescontoDespesaAcessoria` double NOT NULL,
 `BaseDeCalculoICMS` double NOT NULL,
 `BaseDeCalculoICMSST` double NOT NULL,
 `valorIPI` double NOT NULL,
 `AliquotaICMS` double NOT NULL,
 `IE` varchar(14) NOT NULL,
 `DataEmissaoRecebimento` varchar(8) NOT NULL,
 `UnidadeFederacao` varchar(2) NOT NULL,
 `CodigoApuracao` varchar(2) NOT NULL,
 `IndicadorEspecial` varchar(2) NOT NULL,
 `idEmpresa` bigint(20) NOT NULL,
 `mesAno` varchar(6) NOT NULL,
 KEY `CodigoMercadoriaServico` (`CodigoMercadoriaServico`),
 KEY `DataEmissaoRecebimento` (`DataEmissaoRecebimento`),
 KEY `idEmpresa` (`idEmpresa`),
 KEY `mesAno` (`mesAno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

Query (seleciona os 37 mil produtos)

SELECT codprod, descricao, inicial FROM produtos WHERE mesano='$mes' AND idEmpresa='$empresa'

Depois faço (verificar o extrato do produto por data, sendo que esse SQL é feito duas vezes, um pra somar o extrato até a data inicial para poder ter um estoque inicial naquela data que o usuário digitou):

"SELECT tipo, numero, serie, NumItem ,CFOP, Quantidade, DataEmissaoRecebimento, CodigoMercadoriaServico
									FROM registro54 
									WHERE CodigoMercadoriaServico='$codprod_selectDeCima' AND DataEmissaoRecebimento>= '$dataInicial' AND DataEmissaoRecebimento<= '$dataFim' AND mesano='$mes' AND idEmpresa='$empresa'
									ORDER BY DataEmissaoRecebimento, tipo

 

Configuração da Máquina:

Pentium® Dual-Core, E5800 3.20GHz

4,00GB (utilizável: 3,12Gb)

Sistema Operacional Windows 7 32 bits

[Motta 645]

Qual o tamanho da registro54 ?

Que índices ela tem ?

Quais os tipos dos campos ?

___Campos de data em geral não são bons índices.

As estatísticas estão atualizadas ?

Fez o EXPLAIN PLAN da query ?

 

Antes de ver o hardware é sempre bom ver se query não pode ser otimizada.

[Maicon Herverton Lino 0]

Qual o tamanho da registro54 ?

Resposta: 1 milhão de registros (ela terá, pois atualmente exclui eles estou fazendo uma nova programação, mas vai ser cerca de 1 milhão já testei com 950 mil registros e ficou muito lenta)

 

Que índices ela tem ?

Resposta:

KEY `CodigoMercadoriaServico` (`CodigoMercadoriaServico`),

KEY `DataEmissaoRecebimento` (`DataEmissaoRecebimento`),

KEY `idEmpresa` (`idEmpresa`),

KEY `mesAno` (`mesAno`)

 

Quais os tipos dos campos ?

Resposta:

`tipo` varchar(2) NOT NULL,

`cnpj` varchar(14) NOT NULL,

`modelo` varchar(2) NOT NULL,

`serie` varchar(3) NOT NULL,

`numero` varchar(6) NOT NULL,

`CFOP` varchar(4) NOT NULL,

`CST` varchar(3) NOT NULL,

`NumItem` varchar(3) NOT NULL,

`CodigoMercadoriaServico` varchar(14) NOT NULL,

`Quantidade` double NOT NULL,

`ValorMercadoriaServico` double NOT NULL,

`ValorDescontoDespesaAcessoria` double NOT NULL,

`BaseDeCalculoICMS` double NOT NULL,

`BaseDeCalculoICMSST` double NOT NULL,

`valorIPI` double NOT NULL,

`AliquotaICMS` double NOT NULL,

`IE` varchar(14) NOT NULL,

`DataEmissaoRecebimento` varchar(8) NOT NULL,

`UnidadeFederacao` varchar(2) NOT NULL,

`CodigoApuracao` varchar(2) NOT NULL,

`IndicadorEspecial` varchar(2) NOT NULL,

`idEmpresa` bigint(20) NOT NULL,

`mesAno` varchar(6) NOT NULL,

 

___Campos de data em geral não são bons índices.

As estatísticas estão atualizadas ?

Fez o EXPLAIN PLAN da query ?

Resposta:

EXPLAIN DE 1 PRODUTO (com poucos registros)

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE registro54 ref CodigoMercadoriaServico,DataEmissaoRecebimento,idEmpresa,mesAno CodigoMercadoriaServico 16 const 1 Using where; Using filesort

 

[Motta 645]

Tente :

 

criar este indice

 

CREATE INDEX idx_registro54 ON registro54 ('CodigoMercadoriaServico` varchar(14),`mesAno` varchar(6));

 

Obs : Não tenho certeza se a síntaxe é esta exatamente.

 

Troque a query para :

 

"SELECT tipo, numero, serie, NumItem ,CFOP, Quantidade, DataEmissaoRecebimento, CodigoMercadoriaServico
FROM registro54 
WHERE CodigoMercadoriaServico='$codprod_selectDeCima' 
AND mesano='$mes' 
AND DataEmissaoRecebimento>= '$dataInicial' 
AND DataEmissaoRecebimento<= '$dataFim' 
AND idEmpresa='$empresa'
ORDER BY DataEmissaoRecebimento, tipo

 

Atualize as estatísticas da tabela.

[Prog 183]

Tabelas MyISAM tem melhor performance para leitura do que tabelas InnoDB.

Se neste processamento você fizer "poucas" operações de insert, delete e update, pode ter melhor resultado usando MyISAM.

[Maicon Herverton Lino 0]

Fiz o que você sugeriu chefe... não entendi o que quer dizer mas é algo como criar um índice "sobre" o outro?

 

A sintaxe correta é essa:

CREATE INDEX idx_registro54 ON registro54 (`CodigoMercadoriaServico`,`mesAno`);

 

 

Quanto ao "Prog"

Eu andei lendo sobre isso, realmente para consultas o MyISAM é mais rápido, mas para bases de dados pequenas, a própria documentação do MySQL informa que o InnoDB é melhor para bases de dados grandes.

 

Fonte: Storages Engines - MySQL.

 

InnoDB...increase multi-user concurrency and performance.

 

 

Poderiam dá uma olhada no meu my.ini, modifiquei queria saber se tá certo.

port            = 3306 
socket          = "C:/xampp/mysql/mysql.sock"

[mysqld]
port= 3306
socket= "C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql" 
tmpdir="C:/xampp/tmp" 
datadir="C:/xampp/mysql/data"
pid_file="mysql.pid"
skip-external-locking
key_buffer = 200M
max_allowed_packet = 200M
table_cache = 64
sort_buffer_size = 200M
net_buffer_length = 8K
read_buffer_size = 200M
read_rnd_buffer_size = 200M
myisam_sort_buffer_size = 200M
log_error="mysql_error.log"
#bind-address="127.0.0.1"

skip-federated
server-id	= 1


# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"
#innodb_log_arch_dir = "C:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 200M
innodb_additional_mem_pool_size = 200M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

Tou precisando muito de ajuda galera, é um trabalho da empresa e o dono já fechou contrato com 40 filiais, já pensou vai dá muito registro, fico em dúvida se é o PHP limitado no apache (php.ini) também tentei mudá-lo, mas não sei se quando mudo e restarto ele realmente usa a memória que informei.

 

O que seria atualizar as estatísticas do banco?

 

Pessoal incrível a inserção da Storage MyISAM, 37 mil registros em InnoDB demorou mais de 15 minutos, já em MyISAM demorou 5 segundos kkkk. Mas o UPDATE não é bom... Aff será que não existe uma storage que seja bom em tudo?

[Motta 645]

1) O que sugeri foi criar um índice composto, mais de uma coluna, e alterei a ordem do WHERE na query.

 

2) Estatísticas são informações sobre a tabela que o otimizador de query do BD usa para tomar a decisão de como montar o plano de execução.

 

Não conheço detalhes disto no MySql mas no Oracle muitas vezes a atualização das estatísticas gera uma diferença brutal para melhor.

 

--

Com o índice a query ficou mais rápida ?

[Maicon Herverton Lino 0]

Olha gente muito obrigado, quanto a consultar não houve muitas melhoras, mas trocar InnoBD pro MYISAM me fez dá insert's na velocidade da luz kkk. Quando testar a consulta (já já) falo se houve melhoras. Valeu desde já.

[Reginaldo Mota 0]

Olha gente muito obrigado, quanto a consultar não houve muitas melhoras, mas trocar InnoBD pro MYISAM me fez dá insert's na velocidade da luz kkk. Quando testar a consulta (já já) falo se houve melhoras. Valeu desde já.

 

E então Maicon Herverton Lino, como ficou as consultas? Tiveram melhorias? Comente para nós.