Hi Tharindu, Please find the attach text file which contains output of above command.
Cheers! On Fri, Aug 14, 2015 at 1:03 PM, Tharindu Edirisinghe <tharin...@wso2.com> wrote: > Hi Ramith, > > It seems this is not the exact same issue reported in [1]. For > investigating further can you connect to mysql database from terminal/mysql > client and run the following command and share the output with us. We > should be able to see the database level errors caused from the output of > this command [2]. > > *SHOW ENGINE INNODB STATUS;* > > [1] https://wso2.org/jira/browse/CARBON-15265 > [2] https://dev.mysql.com/doc/refman/5.5/en/show-engine.html > > Thanks, > TharinduE > > On Fri, Aug 14, 2015 at 12:44 PM, Ramith Jayasinghe <ram...@wso2.com> > wrote: > >> $subject. Could we have a look? >> >> -- >> Ramith Jayasinghe >> Technical Lead >> WSO2 Inc., http://wso2.com >> lean.enterprise.middleware >> >> E: ram...@wso2.com >> P: +94 777542851 >> >> > > > -- > > Tharindu Edirisinghe > Software Engineer | WSO2 Inc > Identity Server Team > Blog : tharindue.blogspot.com > mobile : +94 775 181586 > > > > _______________________________________________ > Dev mailing list > Dev@wso2.org > http://wso2.org/cgi-bin/mailman/listinfo/dev > > -- Indika Sampath Senior Software Engineer WSO2 Inc. http://wso2.com Phone: +94 716 424 744 Blog: http://indikasampath.blogspot.com/
===================================== 150814 15:36:05 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1596 1_second, 1596 sleeps, 149 10_second, 230 background, 230 flush srv_master_thread log flush and writes: 2684 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1095161, signal count 634173 Mutex spin waits 6839180, rounds 34975078, OS waits 893318 RW-shared spins 594744, rounds 10468101, OS waits 156838 RW-excl spins 214442, rounds 2477679, OS waits 35423 Spin rounds per wait: 5.11 mutex, 17.60 RW-shared, 11.55 RW-excl ------------------------ LATEST DETECTED DEADLOCK ------------------------ 150814 15:05:40 *** (1) TRANSACTION: TRANSACTION 1FC34F9, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 10 lock struct(s), heap size 1248, 7 row lock(s), undo log entries 2 MySQL thread id 234, OS thread handle 0x9f4, query id 19097990 192.168.1.20 root updating DELETE FROM UM_HYBRID_ROLE WHERE UM_ROLE_NAME = 'Q_tmp_192_168_1_21_41372_1' AND UM_TENANT_ID=-1234 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 113 page no 3 n bits 136 index `PRIMARY` of table `carbondb`.`um_hybrid_role` trx id 1FC34F9 lock_mode X locks rec but not gap waiting Record lock, heap no 47 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 4; hex 7ffffb2e; asc .;; 2: len 6; hex 000001fc3174; asc 1t;; 3: len 7; hex a500000e810110; asc ;; 4: len 26; hex 515f746d705f3139325f3136385f315f32315f34313338325f31; asc Q_tmp_192_168_1_21_41382_1;; *** (2) TRANSACTION: TRANSACTION 1FC34FA, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 484 mysql tables in use 1, locked 1 8 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1 MySQL thread id 205, OS thread handle 0x170c, query id 19097991 192.168.1.20 root updating DELETE FROM UM_HYBRID_ROLE WHERE UM_ROLE_NAME = 'Q_tmp_192_168_1_21_41382_1' AND UM_TENANT_ID=-1234 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 113 page no 3 n bits 136 index `PRIMARY` of table `carbondb`.`um_hybrid_role` trx id 1FC34FA lock mode S locks rec but not gap Record lock, heap no 47 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 4; hex 7ffffb2e; asc .;; 2: len 6; hex 000001fc3174; asc 1t;; 3: len 7; hex a500000e810110; asc ;; 4: len 26; hex 515f746d705f3139325f3136385f315f32315f34313338325f31; asc Q_tmp_192_168_1_21_41382_1;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 113 page no 3 n bits 136 index `PRIMARY` of table `carbondb`.`um_hybrid_role` trx id 1FC34FA lock_mode X locks rec but not gap waiting Record lock, heap no 34 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 4; hex 8000005b; asc [;; 1: len 4; hex 7ffffb2e; asc .;; 2: len 6; hex 000001fc34f9; asc 4 ;; 3: len 7; hex 7d001c40080351; asc } @ Q;; 4: len 26; hex 515f746d705f3139325f3136385f315f32315f34313337325f31; asc Q_tmp_192_168_1_21_41372_1;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 1FC3744 Purge done for trx's n:o < 1FC3739 undo n:o < 0 History list length 768 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 244, OS thread handle 0x12f0, query id 19102478 192.168.1.52 root checking query cache for query /* ApplicationName=DBeaver Enterprise 3.4.2 - Main */ SHOW ENGINE INNODB STATUS ---TRANSACTION 1FC34B3, not started MySQL thread id 240, OS thread handle 0x4c0, query id 19097711 192.168.1.20 root ---TRANSACTION 1FC3471, not started MySQL thread id 239, OS thread handle 0x8b4, query id 19097456 192.168.1.20 root ---TRANSACTION 1FC3477, not started MySQL thread id 238, OS thread handle 0xe90, query id 19097482 192.168.1.20 root ---TRANSACTION 1FC349F, not started MySQL thread id 221, OS thread handle 0x5c8, query id 19097678 192.168.1.20 root ---TRANSACTION 1FC3512, not started MySQL thread id 200, OS thread handle 0x16e0, query id 19098091 192.168.1.20 root ---TRANSACTION 1FC36B4, not started MySQL thread id 193, OS thread handle 0x1790, query id 19100073 192.168.1.20 root ---TRANSACTION 1FC3743, not started MySQL thread id 192, OS thread handle 0x13a4, query id 19102477 192.168.1.20 root ---TRANSACTION 1FC2A42, not started MySQL thread id 177, OS thread handle 0x172c, query id 19063062 192.168.1.20 root ---TRANSACTION 1FC234F, not started MySQL thread id 176, OS thread handle 0x145c, query id 19043388 192.168.1.20 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (read thread) I/O thread 7 state: wait Windows aio (read thread) I/O thread 8 state: wait Windows aio (read thread) I/O thread 9 state: wait Windows aio (read thread) I/O thread 10 state: wait Windows aio (read thread) I/O thread 11 state: wait Windows aio (read thread) I/O thread 12 state: wait Windows aio (read thread) I/O thread 13 state: wait Windows aio (read thread) I/O thread 14 state: wait Windows aio (read thread) I/O thread 15 state: wait Windows aio (read thread) I/O thread 16 state: wait Windows aio (read thread) I/O thread 17 state: wait Windows aio (read thread) I/O thread 18 state: wait Windows aio (read thread) I/O thread 19 state: wait Windows aio (read thread) I/O thread 20 state: wait Windows aio (read thread) I/O thread 21 state: wait Windows aio (read thread) I/O thread 22 state: wait Windows aio (read thread) I/O thread 23 state: wait Windows aio (read thread) I/O thread 24 state: wait Windows aio (read thread) I/O thread 25 state: wait Windows aio (read thread) I/O thread 26 state: wait Windows aio (read thread) I/O thread 27 state: wait Windows aio (read thread) I/O thread 28 state: wait Windows aio (read thread) I/O thread 29 state: wait Windows aio (read thread) I/O thread 30 state: wait Windows aio (read thread) I/O thread 31 state: wait Windows aio (read thread) I/O thread 32 state: wait Windows aio (read thread) I/O thread 33 state: wait Windows aio (read thread) I/O thread 34 state: wait Windows aio (read thread) I/O thread 35 state: wait Windows aio (read thread) I/O thread 36 state: wait Windows aio (read thread) I/O thread 37 state: wait Windows aio (read thread) I/O thread 38 state: wait Windows aio (read thread) I/O thread 39 state: wait Windows aio (read thread) I/O thread 40 state: wait Windows aio (read thread) I/O thread 41 state: wait Windows aio (read thread) I/O thread 42 state: wait Windows aio (read thread) I/O thread 43 state: wait Windows aio (read thread) I/O thread 44 state: wait Windows aio (read thread) I/O thread 45 state: wait Windows aio (read thread) I/O thread 46 state: wait Windows aio (read thread) I/O thread 47 state: wait Windows aio (read thread) I/O thread 48 state: wait Windows aio (read thread) I/O thread 49 state: wait Windows aio (read thread) I/O thread 50 state: wait Windows aio (read thread) I/O thread 51 state: wait Windows aio (read thread) I/O thread 52 state: wait Windows aio (read thread) I/O thread 53 state: wait Windows aio (read thread) I/O thread 54 state: wait Windows aio (read thread) I/O thread 55 state: wait Windows aio (read thread) I/O thread 56 state: wait Windows aio (read thread) I/O thread 57 state: wait Windows aio (read thread) I/O thread 58 state: wait Windows aio (read thread) I/O thread 59 state: wait Windows aio (read thread) I/O thread 60 state: wait Windows aio (read thread) I/O thread 61 state: wait Windows aio (read thread) I/O thread 62 state: wait Windows aio (read thread) I/O thread 63 state: wait Windows aio (read thread) I/O thread 64 state: wait Windows aio (read thread) I/O thread 65 state: wait Windows aio (read thread) I/O thread 66 state: wait Windows aio (write thread) I/O thread 67 state: wait Windows aio (write thread) I/O thread 68 state: wait Windows aio (write thread) I/O thread 69 state: wait Windows aio (write thread) I/O thread 70 state: wait Windows aio (write thread) I/O thread 71 state: wait Windows aio (write thread) I/O thread 72 state: wait Windows aio (write thread) I/O thread 73 state: wait Windows aio (write thread) I/O thread 74 state: wait Windows aio (write thread) I/O thread 75 state: wait Windows aio (write thread) I/O thread 76 state: wait Windows aio (write thread) I/O thread 77 state: wait Windows aio (write thread) I/O thread 78 state: wait Windows aio (write thread) I/O thread 79 state: wait Windows aio (write thread) I/O thread 80 state: wait Windows aio (write thread) I/O thread 81 state: wait Windows aio (write thread) I/O thread 82 state: wait Windows aio (write thread) I/O thread 83 state: wait Windows aio (write thread) I/O thread 84 state: wait Windows aio (write thread) I/O thread 85 state: wait Windows aio (write thread) I/O thread 86 state: wait Windows aio (write thread) I/O thread 87 state: wait Windows aio (write thread) I/O thread 88 state: wait Windows aio (write thread) I/O thread 89 state: wait Windows aio (write thread) I/O thread 90 state: wait Windows aio (write thread) I/O thread 91 state: wait Windows aio (write thread) I/O thread 92 state: wait Windows aio (write thread) I/O thread 93 state: wait Windows aio (write thread) I/O thread 94 state: wait Windows aio (write thread) I/O thread 95 state: wait Windows aio (write thread) I/O thread 96 state: wait Windows aio (write thread) I/O thread 97 state: wait Windows aio (write thread) I/O thread 98 state: wait Windows aio (write thread) I/O thread 99 state: wait Windows aio (write thread) I/O thread 100 state: wait Windows aio (write thread) I/O thread 101 state: wait Windows aio (write thread) I/O thread 102 state: wait Windows aio (write thread) I/O thread 103 state: wait Windows aio (write thread) I/O thread 104 state: wait Windows aio (write thread) I/O thread 105 state: wait Windows aio (write thread) I/O thread 106 state: wait Windows aio (write thread) I/O thread 107 state: wait Windows aio (write thread) I/O thread 108 state: wait Windows aio (write thread) I/O thread 109 state: wait Windows aio (write thread) I/O thread 110 state: wait Windows aio (write thread) I/O thread 111 state: wait Windows aio (write thread) I/O thread 112 state: wait Windows aio (write thread) I/O thread 113 state: wait Windows aio (write thread) I/O thread 114 state: wait Windows aio (write thread) I/O thread 115 state: wait Windows aio (write thread) I/O thread 116 state: wait Windows aio (write thread) I/O thread 117 state: wait Windows aio (write thread) I/O thread 118 state: wait Windows aio (write thread) I/O thread 119 state: wait Windows aio (write thread) I/O thread 120 state: wait Windows aio (write thread) I/O thread 121 state: wait Windows aio (write thread) I/O thread 122 state: wait Windows aio (write thread) I/O thread 123 state: wait Windows aio (write thread) I/O thread 124 state: wait Windows aio (write thread) I/O thread 125 state: wait Windows aio (write thread) I/O thread 126 state: wait Windows aio (write thread) I/O thread 127 state: wait Windows aio (write thread) I/O thread 128 state: wait Windows aio (write thread) I/O thread 129 state: wait Windows aio (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 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 1074 OS file reads, 2460395 OS file writes, 555246 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 2894, seg size 2896, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 25499819, node heap has 8 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 293634900571 Log flushed up to 293634900571 Last checkpoint at 293634900571 0 pending log writes, 0 pending chkp writes 246332 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 13186891776; in additional pool allocated 0 Dictionary memory allocated 376899 Buffer pool size 786432 Free buffers 755914 Database pages 30510 Old database pages 11180 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 475, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 1027, created 29483, written 1975918 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 30510, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 196608 Free buffers 180723 Database pages 15882 Old database pages 5842 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 128, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 852, created 15030, written 599121 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 15882, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 196608 Free buffers 191882 Database pages 4724 Old database pages 1723 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 64, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 22, created 4702, written 438915 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4724, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 196608 Free buffers 191683 Database pages 4923 Old database pages 1797 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 34, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 45, created 4878, written 454826 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4923, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 196608 Free buffers 191626 Database pages 4981 Old database pages 1818 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 249, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 108, created 4873, written 483056 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4981, 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 id 2316, state: waiting for server activity Number of rows inserted 8016454, updated 248, deleted 8001102, read 13535649 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
_______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
