On 7/24/2015 4:35 PM, Camilo Vieira wrote:
Hi,
My MySQL server is performing very slow inserts. Does somebody could help
me to understand what's happening?
... snip ...
---TRANSACTION 31D6D74, ACTIVE 27107 sec
mysql tables in use 8, locked 8
7470 lock struct(s), heap size 801208, 849088 row lock(s)
MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
10.180.17.252 root Copying to tmp table
insert into CONFERENCIA_ENCALHE
(data, preco_capa_informado, qtde, qtde_informada,
chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
movimento_estoque_id, movimento_estoque_cota_id,
produto_edicao_id,juramentada)
(select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
from
movimento_estoque_cota_memoria mec,
movimento_estoque_memoria me,
chamada_encalhe ce,
chamada_encalhe_cota cec,
controle_conferencia_encalhe cce,
controle_conferencia_encalhe_cota ccec,
produto_edicao pe
where
mec.tipo_movimento_id =
...snip...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 28294038
Buffer pool size 131071
Free buffers 0
Database pages 123957
Old database pages 45737
Here are the two things I noticed:
1) You are using a subquery in this INSERT command instead of a naked
SELECT. How long does it take that query to execute in isolation
(outside of an INSERT command) ?
The correct syntax would be to skip the parentheses around the SELECT
portion of the command. Instead of this,
insert into CONFERENCIA_ENCALHE(...) (select distinct data_recolhimento,
pe.preco_previsto, mec.qtde, ...
, do this,
insert into CONFERENCIA_ENCALHE(...) SELECT distinct data_recolhimento,
pe.preco_previsto, mec.qtde, ...
2) You have allocated very little memory to your InnoDB Buffer Pool.
131071 pages = 2GB. Depending on how much data you are attempting to
first SELECT from seven tables then INSERT into the other, you may be
forcing the system to do a lot of disk-level intermediate storage which
is much slower than buffering that same information in memory. However,
that may be all the RAM you can spare for MySQL. If that is so, then
there is little you can do about this particular part of the problem.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
