Hi, Your INSEERquery status is "Copying to tmp table", this means fetching rows which has to be inserted is slow.
You should tune SELECT statement in your insert query. Adding indexes and/or simplifying query and/or .. so on. ``` ---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 = ``` yoku0825, 2015-07-25 5:35 GMT+09:00 Camilo Vieira <camilo.vie...@gmail.com>: > Hi, > > My MySQL server is performing very slow inserts. Does somebody could help > me to understand what's happening? > > mysql> show engine innodb status \G > *************************** 1. row *************************** > Type: InnoDB > Name: > Status: > ===================================== > 150724 17:40:28 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 1 seconds > ----------------- > BACKGROUND THREAD > ----------------- > srv_master_thread loops: 173967 1_second, 173966 sleeps, 17386 10_second, > 191 background, 191 flush > srv_master_thread log flush and writes: 174022 > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 40941, signal count 55139 > Mutex spin waits 101247, rounds 886301, OS waits 24221 > RW-shared spins 29684, rounds 413636, OS waits 11014 > RW-excl spins 18765, rounds 257448, OS waits 5190 > Spin rounds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl > ------------------------ > LATEST FOREIGN KEY ERROR > ------------------------ > 150722 18:23:08 Transaction: > TRANSACTION 31AC5E3, ACTIVE 8 sec inserting > mysql tables in use 3, locked 3 > 826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries > 9932 > MySQL thread id 932, OS thread handle 0x7feaf0042700, query id 42396 > 10.180.17.252 root > insert into movimento_estoque_cota ( > ID,APROVADO_AUTOMATICAMENTE,DATA_APROVACAO,STATUS,DATA,DATA_CRIACAO,TIPO_MOVIMENTO_ID,USUARIO_ID, > QTDE,PRODUTO_EDICAO_ID,COTA_ID,ESTOQUE_PROD_COTA_ID,ORIGEM,APROVADOR_ID,LANCAMENTO_ID,status_estoque_financeiro, > PRECO_COM_DESCONTO,PRECO_VENDA,VALOR_DESCONTO,FORMA_COMERCIALIZACAO) > (select > ID,true,date(sysdate()),'APROVADO',DATA,date(sysdate()),TIPO_MOVIMENTO_ID,1, > QTDE,PRODUTO_EDICAO_ID,COTA_ID, > ESTOQUE_PROD_COTA_ID, > 'CARGA_INICIAL',1,LANCAMENTO_ID, > IF(STATUS_ESTOQUE_FINANCEIRO=1,'FINANCEIRO_PROCESSADO','FINANCEIRO_NAO_PROCESSADO'), > PRECO_COM_DESCONTO,PRECO_ > Foreign key constraint fails for table > `teste_historico`.`movimento_estoque_cota`: > , > CONSTRAINT `FK459444C362506D6B` FOREIGN KEY (`ESTOQUE_PROD_COTA_ID`) > REFERENCES `estoque_produto_cota` (`ID`) > Trying to add in child table, in index `FK459444C362506D6B` tuple: > DATA TUPLE: 2 fields; > 0: len 8; hex 8000000000054211; asc B ;; > 1: len 8; hex 800000000009743f; asc t?;; > > But in parent table `teste_historico`.`estoque_produto_cota`, in index > `PRIMARY`, > the closest match we can find is record: > PHYSICAL RECORD: n_fields 7; compact format; info bits 0 > 0: len 8; hex 8000000000053f0d; asc ? ;; > 1: len 6; hex 0000031aba25; asc %;; > 2: len 7; hex 850000561a1a14; asc V ;; > 3: len 9; hex 800000000000000000; asc ;; > 4: len 9; hex 8000000000003c0000; asc < ;; > 5: len 8; hex 8000000000000044; asc D;; > 6: len 8; hex 800000000001007d; asc };; > > ------------ > TRANSACTIONS > ------------ > Trx id counter 31DDD14 > Purge done for trx's n:o < 31D8A49 undo n:o < 0 > History list length 425 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0, not started > MySQL thread id 18036, OS thread handle 0x7fea5e591700, query id 2854409 > localhost root > show engine innodb status > ---TRANSACTION 31DDD13, not started > MySQL thread id 16024, OS thread handle 0x7fea5e4cd700, query id 2854407 > 10.129.28.111 root > ---TRANSACTION 31DC01D, not started > MySQL thread id 15932, OS thread handle 0x7fea5e52f700, query id 2812134 > 10.129.28.111 root > ---TRANSACTION 31D83B4, not started > MySQL thread id 15931, OS thread handle 0x7fea5d714700, query id 2721807 > 10.129.28.111 root > ---TRANSACTION 31D886C, not started > MySQL thread id 15930, OS thread handle 0x7fea5d776700, query id 2730189 > 10.129.28.111 root > ---TRANSACTION 31D8396, not started > MySQL thread id 15924, OS thread handle 0x7feaf0073700, query id 2721613 > 10.129.28.111 root > ---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 = > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (read thread) > I/O thread 4 state: waiting for i/o request (read thread) > I/O thread 5 state: waiting for i/o request (read thread) > I/O thread 6 state: waiting for i/o request (write thread) > I/O thread 7 state: waiting for i/o request (write thread) > I/O thread 8 state: waiting for i/o request (write thread) > I/O thread 9 state: waiting for i/o request (write thread) > Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 134189 OS file reads, 526068 OS file writes, 112377 OS fsyncs > 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf: size 1, free list len 7332, seg size 7334, 1227 merges > merged operations: > insert 41562, delete mark 251, delete 6 > discarded operations: > insert 0, delete mark 0, delete 0 > Hash table size 4425293, node heap has 7066 buffer(s) > 0.00 hash searches/s, 5.00 non-hash searches/s > --- > LOG > --- > Log sequence number 725384079667 > Log flushed up to 725384079667 > Last checkpoint at 725384079667 > 0 pending log writes, 0 pending chkp writes > 42114 log i/o's done, 0.00 log i/o's/second > ---------------------- > 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 > Modified db pages 0 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages made young 158924, not young 0 > 0.00 youngs/s, 0.00 non-youngs/s > Pages read 140574, created 491096, written 1747683 > 0.00 reads/s, 0.00 creates/s, 0.00 writes/s > Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 > Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead > 0.00/s > LRU len: 123957, unzip_LRU len: 0 > I/O sum[1]:cur[0], unzip sum[0]:cur[0] > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > 1 read views open inside InnoDB > Main thread process no. 30202, id 140644617873152, state: sleeping > Number of rows inserted 29822988, updated 26897847, deleted 1091985, read > 12517691955 > 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5250.75 reads/s > ---------------------------- > END OF INNODB MONITOR OUTPUT > ============================ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
