Hi Luis, I have got the output below:
$ ./mysqltuner.pl --user root --pass abril@123 [OK] Logged in using credentials passed on the command line >> MySQLTuner 1.5.0 - Major Hayden <ma...@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.2 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 9K (Tables: 9) [--] Data in InnoDB tables: 52G (Tables: 3468) [--] Data in MEMORY tables: 30M (Tables: 44) [!!] Total fragmented tables: 284 -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 17h 35m 17s (1M q [5.796 qps], 23K conn, TX: 690M, RX: 1B) [--] Reads / Writes: 99% / 1% [--] Binary logging is disabled [--] Total buffers: 4.1G global + 2.7M per thread (1000 max threads) [OK] Maximum reached memory usage: 4.1G (28.09% of installed RAM) [OK] Maximum possible memory usage: 6.7G (45.63% of installed RAM) [OK] Slow queries: 0% (11/1M) [OK] Highest usage of available connections: 2% (22/1000) [OK] Aborted connections: 0.01% (3/23713) [!!] Key buffer used: 18.2% (3M used / 16M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/109.0K [OK] Read Key buffer hit rate: 100.0% (4K cached / 0 reads) [OK] Write Key buffer hit rate: 100.0% (1K cached / 0 writes) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts) [OK] Temporary tables created on disk: 5% (3K on disk / 76K total) [OK] Thread cache hit rate: 99% (24 created / 23K connections) [!!] Table cache hit rate: 1% (400 open / 30K opened) [OK] Open file limit used: 0% (2/65K) [OK] Table locks acquired immediately: 100% (289K immediate / 289K locks) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [!!] InnoDB buffer pool / data size: 4.0G/52.4G [!!] InnoDB buffer pool instances: 1 [OK] InnoDB Used buffer: 100.00% (262144 used/ 262144 total) [OK] InnoDB Read buffer efficiency: 99.99% (1899718337 hits/ 1899815994 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 13143 writes) -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Remove Anonymous User account - there is 2 Anonymous account. Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Restrict Host for user@% to user@SpecificDNSorIp Enable the slow query log to troubleshoot bad queries Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (>= 8M) table_open_cache (> 400) innodb_buffer_pool_size (>= 52G) if possible. innodb_buffer_pool_instances(=4) 2015-07-24 18:28 GMT-03:00 Luis Daniel Lucio Quiroz < luis.daniel.lu...@gmail.com>: > How big is your innodb buffer in compare all innodb? > > Try running the mysqltuner script, it will give you useful information > On Jul 24, 2015 4:40 PM, "Camilo Vieira" <camilo.vie...@gmail.com> wrote: > >> 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 >> ============================ >> >
