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 ============================
