cleaning up
Hi, There are lot mysql transactions with the "cleaning up" state on my Mysql 5.6.31: TRANSACTIONS Trx id counter 26782150 Purge done for trx's n:o < 26774181 undo n:o < 0 state: running but idle History list length 739 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 6337, OS thread handle 0x7f13d08f2700, query id 126598020 localhost root init show engine innodb status ---TRANSACTION 26782140, not started MySQL thread id 6334, OS thread handle 0x7f13c9e59700, query id 126597972 x.y.5.30 bnu cleaning up ---TRANSACTION 26782003, not started MySQL thread id 6333, OS thread handle 0x7f13d05b1700, query id 126597189 x.y.5.30 nat cleaning up ---TRANSACTION 26770094, not started MySQL thread id 6332, OS thread handle 0x7f13d048b700, query id 126517953 x.y.5.30 gyn cleaning up ---TRANSACTION 26770095, not started MySQL thread id 6331, OS thread handle 0x7f13c9d64700, query id 126517957 x.y.5.30 gyn cleaning up ---TRANSACTION 26782142, not started MySQL thread id 6328, OS thread handle 0x7f13d0119700, query id 126597982 10.212.10.113 douglas cleaning up ---TRANSACTION 0, not started MySQL thread id 6327, OS thread handle 0x7f13d079b700, query id 126589014 10.37.28.96 cesar cleaning up ---TRANSACTION 26759054, not started MySQL thread id 6325, OS thread handle 0x7f13c9e28700, query id 126591159 10.212.10.158 camilo cleaning up ---TRANSACTION 0, not started MySQL thread id 6324, OS thread handle 0x7f13d0270700, query id 126591158 10.212.10.158 camilo cleaning up ---TRANSACTION 26779776, not started MySQL thread id 6322, OS thread handle 0x7f13d4bd5700, query id 126582047 x.y.5.30 nat cleaning up ---TRANSACTION 0, not started MySQL thread id 6321, OS thread handle 0x7f13ca0a5700, query id 126597896 localhost root cleaning up ---TRANSACTION 26780918, not started MySQL thread id 6308, OS thread handle 0x7f13c9fb0700, query id 126590660 10.212.10.113 douglas cleaning up ---TRANSACTION 26721560, not started MySQL thread id 6281, OS thread handle 0x7f13d0396700, query id 126223696 10.212.10.96 wrpaiva cleaning up ---TRANSACTION 0, not started MySQL thread id 6280, OS thread handle 0x7f13d0303700, query id 126223692 10.212.10.96 wrpaiva cleaning up ---TRANSACTION 26777804, not started MySQL thread id 6300, OS thread handle 0x7f13d0644700, query id 126568164 x.y.5.30 bnu cleaning up ---TRANSACTION 26781215, not started MySQL thread id 6299, OS thread handle 0x7f13c9dc6700, query id 126592399 x.y.5.30 gyn cleaning up ---TRANSACTION 26782147, not started MySQL thread id 6297, OS thread handle 0x7f13d00b7700, query id 126598007 x.y.5.30 nat cleaning up ---TRANSACTION 26689786, not started MySQL thread id 6296, OS thread handle 0x7f13d499e700, query id 126302499 172.25.23.93 odemir cleaning up ---TRANSACTION 26777803, not started MySQL thread id 6295, OS thread handle 0x7f13d04ed700, query id 126568163 x.y.5.30 bnu cleaning up ---TRANSACTION 26782073, not started MySQL thread id 6294, OS thread handle 0x7f13d020e700, query id 126597605 x.y.5.30 bnu cleaning up ---TRANSACTION 26774701, not started MySQL thread id 6293, OS thread handle 0x7f13ca074700, query id 126547915 x.y.5.30 nat cleaning up ---TRANSACTION 26780762, not started MySQL thread id 6291, OS thread handle 0x7f13d496d700, query id 126589795 x.y.5.30 gyn cleaning up ---TRANSACTION 26782146, not started MySQL thread id 6290, OS thread handle 0x7f13ca138700, query id 126598002 x.y.5.30 gyn cleaning up ---TRANSACTION 26780390, not started MySQL thread id 6289, OS thread handle 0x7f13d08c1700, query id 126586679 x.y.5.30 gyn cleaning up ---TRANSACTION 26770105, not started MySQL thread id 5964, OS thread handle 0x7f13ca1cb700, query id 126518017 x.y.5.30 gyn cleaning up ---TRANSACTION 26777808, not started MySQL thread id 5963, OS thread handle 0x7f13d07cc700, query id 126568189 x.y.5.30 bnu cleaning up ---TRANSACTION 26774294, not started MySQL thread id 5942, OS thread handle 0x7f13c9eec700, query id 126545162 x.y.2.22 douglas cleaning up ---TRANSACTION 26780109, not started MySQL thread id 5832, OS thread handle 0x7f13d48a9700, query id 126589829 x.y.5.30 gyn cleaning up ---TRANSACTION 26777802, not started MySQL thread id 5822, OS thread handle 0x7f13ca012700, query id 126568156 x.y.5.30 bnu cleaning up ---TRANSACTION 26782149, not started MySQL thread id 5314, OS thread handle 0x7f13d017b700, query id 126598019 x.y.5.30 nhb cleaning up ---TRANSACTION 26782143, not started MySQL thread id 5246, OS thread handle 0x7f13c9d95700, query id 126597987 x.y.2.22 douglas cleaning up ---TRANSACTION 26779477, not started MySQL thread id 5237, OS thread handle 0x7f13d076a700, query id 126580069 x.y.5.30 nhb cleaning up ---TRANSACTION 26772820, ACTIVE 675 sec 18 lock struct(s), heap size 2936, 15 row lock(s), undo log entries 6 MySQL thread id 6326, OS thread handle 0x7f13c9ebb700, query id 126589015 10.37.28.96 cesar cleaning up Trx read view will not see trx with id >= 26773666, sees < 26773666 FILE I/O -
Fwd: GTID SUSPENDING
Hi folks! I have noticed the GTID thread is sunpending since I upgraded the MySQL from 5.5 to 5.7. I have tryed to disable GTI using : SET @@GLOBAL.GTID_MODE = OFF; But the problem persists... Somebody could clarify this situation for me? Thanks!!
Mysql 5.5 Dead Locks
Hi, I'm receiving MySQL 5.5 deadlocks. I enabled the innodb lock monitor using this command: CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB; I got the following output: LATEST DETECTED DEADLOCK 151007 13:58:36 *** (1) TRANSACTION: TRANSACTION 13F6957A, ACTIVE 10 sec starting index read mysql tables in use 2, locked 2 LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 MySQL thread id 15115, OS thread handle 0x2b1b51a55700, query id 150823016 10.129.28.111 awsuser Updating update LANCAMENTO set ALTERADO_INTERFACE=1, DATA_CRIACAO='2015-08-28', DATA_FIN_MAT_DISTRIB=null, DATA_LCTO_DISTRIBUIDOR='2015-08-24', DATA_LCTO_PREVISTA='2015-08-24', DATA_REC_DISTRIB='2015-10-23', DATA_REC_PREVISTA='2015-10-23', DATA_STATUS='2015-10-07 11:01:43', ESTUDO_ID=null, EXPEDICAO_ID=641, JURAMENTADO=null, NUMERO_LANCAMENTO=1, NUMERO_REPROGRAMACOES=null, PERIODO_LANCAMENTO_PARCIAL_ID=null, PRODUTO_EDICAO_ID=464, REPARTE=2700, REPARTE_PROMOCIONAL=0, SEQUENCIA_MATRIZ=null, STATUS='EXPEDIDO', TIPO_LANCAMENTO='LANCAMENTO', USUARIO_ID=11 where ID=464 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 48034 page no 15 n bits 216 index `PRIMARY` of table `db_00757350`.`lancamento` trx id 13F6957A lock_mode X locks rec but not gap waiting Record lock, heap no 144 PHYSICAL RECORD: n_fields 24; compact format; info bits 0 0: len 8; hex 81d0; asc ;; 1: len 6; hex 13f6933b; asc ;;; 2: len 7; hex 0b2c6629e2; asc,f) ;; 3: len 1; hex 81; asc ;; 4: len 3; hex 8fbf1c; asc;; 5: len 3; hex 8fbf18; asc;; 6: len 3; hex 8fbf18; asc;; 7: len 3; hex 8fbf57; asc W;; 8: len 3; hex 8fbf57; asc W;; 9: SQL NULL; 10: len 8; hex 80001253c59ee3d4; ascS;; 11: len 9; hex 800a8c; asc ;; 12: len 9; hex 80; asc ;; 13: SQL NULL; 14: len 8; hex 455850454449444f; asc EXPEDIDO;; 15: len 10; hex 4c414e43414d454e544f; asc LANCAMENTO;; 16: len 8; hex 81d0; asc ;; 17: len 8; hex 8281; asc ;; 18: len 8; hex 800b; asc ;; 19: len 4; hex 8001; asc ;; 20: SQL NULL; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; Somebody could help me to understand this result? Thanks!
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
>> 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" 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 rou
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 reque
Fwd: MySQL 5.5 Slow performance after slave server fail
Hi, I have two servers Mysql 5.5 with master to master replication. The second server failed and I needed to remove it to repair the operating system. After this incident the application users have been notice that the application response have been very slow. Both servers have RAID 1 (mirroring) managed by operating system. Could you help me to understand what's happening? Follow the innodb status: mysql> show engine innodb status \G *** 1. row *** Type: InnoDB Name: Status: = 141105 17:43:07 INNODB MONITOR OUTPUT = Per second averages calculated from the last 36 seconds - BACKGROUND THREAD - srv_master_thread loops: 4704 1_second, 4703 sleeps, 470 10_second, 2 background, 2 flush srv_master_thread log flush and writes: 4740 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 194, signal count 201 Mutex spin waits 280, rounds 819, OS waits 9 RW-shared spins 82, rounds 2324, OS waits 74 RW-excl spins 8, rounds 3533, OS waits 110 Spin rounds per wait: 2.92 mutex, 28.34 RW-shared, 441.62 RW-excl TRANSACTIONS Trx id counter 82EC Purge done for trx's n:o < 7A75 undo n:o < 0 History list length 32 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 301, OS thread handle 0x7ff94db71700, query id 32201 localhost root show engine innodb status ---TRANSACTION 80D6, not started MySQL thread id 41, OS thread handle 0x7ff94dba2700, query id 29440 localhost 127.0.0.1 root ---TRANSACTION 82EB, not started MySQL thread id 40, OS thread handle 0x7ff94dbd3700, query id 32200 localhost 127.0.0.1 root 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 19995 OS file reads, 12493 OS file writes, 8901 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 2140, seg size 2142, 122 merges merged operations: insert 482, delete mark 661, delete 2 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 671 buffer(s) 1.06 hash searches/s, 5.28 non-hash searches/s --- LOG --- Log sequence number 4276155092 Log flushed up to 4276155092 Last checkpoint at 4276155092 0 pending log writes, 0 pending chkp writes 8644 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 2978314 Buffer pool size 8192 Free buffers 0 Database pages 7521 Old database pages 2756 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 13227, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 20400, created 52, written 5785 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: 7521, unzip_LRU len: 0 I/O sum[0]: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. 870, id 140708344538880, state: sleeping Number of rows inserted 4256, updated 8, deleted 3101, read 6737576 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2.11 reads/s END OF INNODB MONITOR OUTPUT
