Re: MySQL 5.5 Slow performance to insert
- Original Message - From: Camilo Vieira camilo.vie...@gmail.com Subject: Re: MySQL 5.5 Slow performance to insert $ ./mysqltuner.pl --user root --pass abril@123 Thank you for that password :-) I don't particularly like MySQLtuner myself, it makes assumptions about your workload that are, imo, false more often than not. Anyway... [!!] Total fragmented tables: 284 *shrug* Small issue on modern storage, pretty much no issue on solid state. [!!] Key buffer used: 18.2% (3M used / 16M cache) *shrug* You could shrink that if you have no MyISAM at all, but it's a neglectable amount of memory. [!!] Query cache is disabled Which is pretty much irrelevant for an insert workload :-) [!!] Table cache hit rate: 1% (400 open / 30K opened) That might be worth growing. Exact numbers are hard to point at, use munin or similar to figure out the growth rate of opened_tables. [!!] InnoDB buffer pool / data size: 4.0G/52.4G If possible, size your buffer pool to at least your active dataset. This will have a considerable impact on your initial insert speed. [!!] InnoDB buffer pool instances: 1 From 5.6 (I think) this defaults to 8. The benefit is less contention for mutexes and the like. Upgrading to 5.6 is worthwile in most cases anyway. [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) Hmm. That's peculiar for an insert workload... did you run that after (or during) the workload? Your original mail also doesn't seem to specify the workload beyond 'inserts'. Details? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.5 Slow performance to insert
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,
Re: MySQL 5.5 Slow performance to insert
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
Re: MySQL 5.5 Slow performance to insert
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 80054211; asc B ;; 1: len 8; hex 8009743f; 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 80053f0d; asc ? ;; 1: len 6; hex 031aba25; asc %;; 2: len 7; hex 85561a1a14; ascV ;; 3: len 9; hex 80; asc ;; 4: len 9; hex 80003c; asc ;; 5: len 8; hex 8044; ascD;; 6: len 8; hex 8001007d; 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
MySQL 5.5 Slow performance to insert
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 80054211; asc B ;; 1: len 8; hex 8009743f; 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 80053f0d; asc ? ;; 1: len 6; hex 031aba25; asc %;; 2: len 7; hex 85561a1a14; ascV ;; 3: len 9; hex 80; asc ;; 4: len 9; hex 80003c; asc ;; 5: len 8; hex 8044; ascD;; 6: len 8; hex 8001007d; 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
