mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 110315 9:09:59 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 40 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 217 srv_active, 0 srv_shutdown, 3921 srv_idle srv_master_thread log flush and writes: 4231 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 32299, signal count 27951 Mutex spin waits 595833, rounds 934780, OS waits 10753 RW-shared spins 21096, rounds 632089, OS waits 20721 RW-excl spins 428, rounds 24508, OS waits 756 Spin rounds per wait: 1.57 mutex, 29.96 RW-shared, 57.26 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 13FF Purge done for trx's n:o < 1394 undo n:o < 0 History list length 160 Total number of lock structs in row lock hash table 7 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started, process no 2062, OS thread id 139821332014848 MySQL thread id 45, query id 6955 localhost 127.0.0.1 root SHOW ENGINE INNODB STATUS ---TRANSACTION 0, not started, process no 2062, OS thread id 139821332547328 MySQL thread id 42, query id 6649 localhost 127.0.0.1 root Waiting for table metadata lock select sql_cache count(*) from `test`.`t4` as `t4` ignore index (idx19) natural left join `test`.`t12` as `t12` ignore index (idx97,PRIMARY,idx90) right join `test`.`t6` as `t6` on (t4.col25 && t6.col52 ) natural left join `test`.`t23` as `t23` ignore index (PRIMARY) left join `test`.`t20` as `t20` on (t4.col26 || t20.col214 ) left join `test`.`t11` as `t11` ignore index (idx80,idx72) on ( insert(( ((t4.col28) not in ((t4.col29),(t4.col33),(t4.col27),(t4.col27),(t4.col29)))),( least((t4.col33),(t4.col30),(t4.col26))),( encode((t4.col26),(t4.col28))),( @@global.log ---TRANSACTION 13CA, not started, process no 2062, OS thread id 139821332813568 MySQL thread id 41, query id 6919 localhost 127.0.0.1 root Waiting for table metadata lock explain extended select `t19`.`col202` as col202 , (( count(distinct (`t19`.`col201`))) in (( greatest((( select count(*) from `test`.`t9` as `t9` ignore index (idx55,idx48) left outer join `test`.`t26` as `t26` on (t9.col80 & @var7 ) left outer join `test`.`t10` as `t10` force index (PRIMARY,PRIMARY,idx64) on (@var4 is not false ) group by t9.col66 limit 1 )),(`t19`.`col201`),(`t19`.`col201`),(`t19`.`col202`))),( count((`t19`.`col201`))),( (case ((`t19`.`col202`)) when ((`t19`.`col201`)) then ((`t19`.`col201`))when ((`t19`.`col202`)) then ((`t19`.`col201`)) e ---TRANSACTION 1390, not started, process no 2062, OS thread id 139821333079808 MySQL thread id 40, query id 6513 localhost 127.0.0.1 root Waiting for table metadata lock delete t28 from ( select col264,a12 from `test`.`t26` as `t26` right outer join `test`.`t4` as `t4` on ( '1' ) natural join `test`.`t11` as `t11` ignore index (idx80,idx75) natural left join `test`.`t21` as `t21` use index (idx173,idx173,PRIMARY) natural left join `test`.`t27` as `t27` natural left outer join `test`.`t12` as `t12` force index (idx97,idx97) natural left outer join `test`.`t28` as `t28` right outer join `test`.`t15` as `t15` on (@var3 << t15.col168 ) cross join `test`.`t29` as `t29` force index (idx262,idx272,idx265) on (@var6 i ---TRANSACTION 13BA, not started, process no 2062, OS thread id 139821337073408 mysql tables in use 1, locked 1 MySQL thread id 25, query id 6748 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t6_before_insert` before insert on `t6` for each row begin delete t22 from ( select count(*) from `test`.`t7` as `t7` force index (idx31,PRIMARY) left outer join `test`.`t21` as `t21` on (@var7 sounds like @var3 ) limit 1 ) `x`, t22 as t22 where t22.a22>x.a7 ; update ( select count(*) from `test`.`t7` as `t7` force index (idx31,PRIMARY) left outer join `test`.`t21` as `t21` on (@var0 < t21.col223 ) limit 1 ) `x`, `test`.`t10` as `t10` set `t10`.`col81`=`x`.`a7` ; select count(*) from `test`.`t7` as `t7` force index (idx31,PRIM ---TRANSACTION 13E9, not started, process no 2062, OS thread id 139821335475968 MySQL thread id 31, query id 6826 localhost 127.0.0.1 root Waiting for table metadata lock update ignore `test`.`t25` set `col252` = 0, `col253` = 2071150030, `a25` = geomfromtext("point(90237 67715)") where `a25`>= 2249025804 limit 1 ---TRANSACTION 13DF, not started, process no 2062, OS thread id 139821334144768 mysql tables in use 1, locked 1 MySQL thread id 36, query id 6765 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t26_before_insert` before insert on `t26` for each row begin delete t14 from ( select `t5`.`col37` as col37 from `test`.`t3` as `t3` natural left outer join `test`.`t2` as `t2` right join `test`.`t5` as `t5` on (@var4 = @var6 ) inner join `test`.`t7` as `t7` force index (PRIMARY,PRIMARY) on ( updatexml((t3.col21),(t3.col23),(t3.col20)) and t3.col21 ) left outer join `test`.`t27` as `t27` force index (PRIMARY) on (t3.col23 * t27.a27 ) where t3.col21 <> t2.col9 xor not row(t5.col50 ,t3.a3 )!= row( t3.col23 , t3.col23 ) group by t5.a5, t2 ---TRANSACTION 13D2, not started, process no 2062, OS thread id 139821335209728 MySQL thread id 32, query id 6811 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t17_after_update` after update on `t17` for each row begin delete t25 from ( select group_concat(distinct (`t15`.`col170`) order by (( select count(*) from `test`.`t15` as `t15` left join `test`.`t1` as `t1` ignore index (idx1) on ( '1' ) left join `test`.`t12` as `t12` force index (PRIMARY,idx90) on (t15.col171 = @var5 ) natural left outer join `test`.`t9` as `t9` inner join `test`.`t21` as `t21` on (@var5 % t21.col221 ) natural left join `test`.`t29` as `t29` force index (idx260,idx272,idx265) right outer join `test`.`t22` as `t22 ---TRANSACTION 1341, not started, process no 2062, OS thread id 139821334943488 MySQL thread id 33, query id 6790 localhost 127.0.0.1 root Waiting for table metadata lock explain select distinctrow sql_no_cache col179 from `test`.`t27` as `t27` ignore index (idx238,PRIMARY,idx240,idx247) straight_join `test`.`t19` as `t19` force index (idx132,idx143) on (t27.col258 is not unknown ) natural right join `test`.`t4` as `t4` use index (PRIMARY) cross join `test`.`t16` as `t16` on (t27.a27 < t16.col181 ) straight_join `test`.`t21` as `t21` on (@var2 is not false ) inner join `test`.`t3` as `t3` use index (idx13,idx15,idx15) on ( '1' ) left outer join `test`.`t7` as `t7` ignore index (idx31) on ( char_length((t27.a27)) >> t27.a2 ---TRANSACTION 135D, not started, process no 2062, OS thread id 139821336540928 MySQL thread id 27, query id 6735 localhost 127.0.0.1 root Waiting for table metadata lock explain select col283,col119 from `test`.`t22` as `t22` ignore index (PRIMARY,idx188,idx189,idx181) left join `test`.`t6` as `t6` force index (PRIMARY) on (@var5 = t6.col54 ) natural left outer join `test`.`t29` as `t29` force index (PRIMARY,idx272,PRIMARY) natural join `test`.`t24` as `t24` use index (idx195,idx209) inner join `test`.`t12` as `t12` on ( '1' ) left join `test`.`t13` as `t13` on (t22.a22 <=> t13.col140 ) inner join `test`.`t26` as `t26` force index (PRIMARY) on (@var1 is unknown ) where t24.col244 & '2011-10-07 21:33:54' and not t13.col ---TRANSACTION 13CC, not started, process no 2062, OS thread id 139821333612288 MySQL thread id 38, query id 6757 localhost 127.0.0.1 root Waiting for table metadata lock delete from `test`.`t6` where `col54` >= null limit 1 ---TRANSACTION 13FA, not started, process no 2062, OS thread id 139821333878528 MySQL thread id 37, query id 6925 localhost 127.0.0.1 root Waiting for table metadata lock insert ignore into `test`.`t27` set `a27` = 'nalyiwqifiitsccp', `col258` = 3721 ---TRANSACTION 1366, not started, process no 2062, OS thread id 139821334411008 MySQL thread id 35, query id 6877 localhost 127.0.0.1 root Waiting for table metadata lock select `t8`.`col60` as col60 from `test`.`t17` as `t17` ignore index (idx119,idx119) inner join `test`.`t6` as `t6` force index (idx24,idx28,idx28) on ( convert_tz(( greatest((t17.col186),(t17.a17),(t17.col185),(t17.a17),(t17.a17))),( greatest((t17.a17),(t17.col185),(t17.col186),(t17.col186))),( export_set((t17.col185),(t17.a17),(t17.col185),(t17.col186)))) && (( bit_length((t6.col55))) not in (( ((t6.col54) not in ((t6.col55),(t6.a6),(t6.col52),(t6.col55)))),( now()),( subdate((t6.col54),interval (t6.col55) hour)),( (case when (t6.col53) then (t6.col52) else (t6.col55) end)))) ) ---TRANSACTION 13D7, not started, process no 2062, OS thread id 139821334677248 MySQL thread id 34, query id 6813 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t27_before_update` before update on `t27` for each row begin delete t29 from ( select count(*) from `test`.`t4` as `t4` force index (PRIMARY,idx19) straight_join `test`.`t22` as `t22` use index (idx185,idx180) on (t4.col26 is not unknown ) limit 1 ) `x`, t29 as t29 where t29.col282>x.col30 ; update ( select count(*) from `test`.`t4` as `t4` force index (PRIMARY,idx19) straight_join `test`.`t22` as `t22` use index (idx185,idx180) on ( uncompress(( acos((t4.col26)))) > @@global.server_id ) limit 1 ) `x`, `test`.`t3` as `t3` set `t3`.` ---TRANSACTION 13E2, not started, process no 2062, OS thread id 139821336274688 MySQL thread id 28, query id 6830 localhost 127.0.0.1 root Waiting for table metadata lock update ignore `test`.`t16` set `a16` = '', `col173` = -1075813, `col174` = from_unixtime(unix_timestamp()), `col175` = "îÊðÙÉ5j¸ÊÀ!1Z", `col176` = "Á¤ãX7ÁÍM‹¦ætß3ä", `col177` = '2011-09-11 03:56:03', `col178` = '2011-07-21', `col179` = 'mumdimdc', `col180` = null, `col181` = geomfromtext("point(-1931 -35765)"), `col182` = null, `col183` = -5246711, `col184` = -41 where `col184`<=> "ep" limit 1 ---TRANSACTION 0, not started, process no 2062, OS thread id 139821335742208 MySQL thread id 30, query id 6920 localhost 127.0.0.1 root Waiting for table metadata lock explain partitions select sql_cache all col215 from `test`.`t6` as `t6` natural left outer join `test`.`t25` as `t25` left outer join `test`.`t12` as `t12` on (@var0 & t12.col113 ) left join `test`.`t5` as `t5` on (t6.col53 is not null ) natural left outer join `test`.`t11` as `t11` cross join `test`.`t14` as `t14` on ( @@global.myisam_data_pointer_size + t6.col55 ) right outer join `test`.`t27` as `t27` ignore index (idx241) on ( (case ((t6.col52)) when ((t6.a6)) then ((t6.col54))when ((t6.a6)) then ((t6.col52)) else ((t6.col52)) end) <= timestamp((t ---TRANSACTION 13F1, not started, process no 2062, OS thread id 139821333346048 MySQL thread id 39, query id 6833 localhost 127.0.0.1 root Waiting for table metadata lock update ignore `test`.`t6` set `col52` = 8634042, `col53` = '2010-10-22', `a6` = -8.3404022104877E+18, `col55` = 255, `col54` = 74 where `col54`< null limit 1 ---TRANSACTION 13F5, not started, process no 2062, OS thread id 139821337339648 MySQL thread id 24, query id 6930 localhost 127.0.0.1 root Waiting for table metadata lock select count(*) from `test`.`t19` as `t19` right join `test`.`t12` as `t12` on ( (case (( ((t19.col199) not in ((t19.col199),(t19.col205),(t19.col202))))) when (( @@global.net_write_timeout)) then (( period_diff((t19.col200),(t19.col203))))when (( position((t19.col201) in (t19.col199)))) then (( @@global.slave_compressed_protocol)) else (( greatest((t19.col201),(t19.col203),(t19.col199),(t19.col199)))) end) | @var9 ) natural join `test`.`t29` as `t29` ignore index (idx272,idx260,idx265) natural right join `test`.`t17` as `t17` use index (idx112,idx114,idx120,idx119) righ ---TRANSACTION 1378, not started, process no 2062, OS thread id 139821338937088 MySQL thread id 18, query id 6917 localhost 127.0.0.1 root Waiting for table metadata lock select count(*) from `test`.`t19` as `t19` ignore index (idx141,idx131,idx145,idx131) natural left outer join `test`.`t25` as `t25` left join `test`.`t27` as `t27` on (t19.col199 < t27.a27 ) natural join `test`.`t29` as `t29` force index (idx260,idx266,PRIMARY) left outer join `test`.`t21` as `t21` on (t19.col202 | @var5 ) limit 1 union all select `t25`.`a25` as a25 , `t19`.`col199` as col199 from `test`.`t19` as `t19` ignore index (idx141,idx131,idx145,idx131) natural left outer join `test`.`t25` as `t25` left join `test`.`t27` as `t27` o ---TRANSACTION 13E6, not started, process no 2062, OS thread id 139821336807168 mysql tables in use 1, locked 1 MySQL thread id 26, query id 6801 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t28_before_update` before update on `t28` for each row begin delete t17 from ( select count(*) from `test`.`t15` as `t15` inner join `test`.`t23` as `t23` on ( convert(( release_lock((t15.col169))),binary) xor (case (( ((t23.a23) in ((t23.col234),(t23.a23),(t23.col235),(t23.col234))))) when (( @@global.bulk_insert_buffer_size)) then (( maketime((t23.col234),(t23.col234),(t23.col235))))when (( elt((t23.col234),(t23.col234),(t23.col235),(t23.col235),(t23.col235)))) then (( period_diff((t23.a23),(t23.col234)))) else (( greatest((t23.col235),(t23.col235),(t23.col235), ---TRANSACTION 0, not started, process no 2062, OS thread id 139821338138368 MySQL thread id 21, query id 6657 localhost 127.0.0.1 root Waiting for table metadata lock explain extended select sql_no_cache distinctrow col184 from `test`.`t27` as `t27` right outer join `test`.`t5` as `t5` on ( '1' ) cross join `test`.`t23` as `t23` on ( week((t27.col258),6) | t27.a27 ) right join `test`.`t6` as `t6` force index (idx24) on ( greatest(( char((t27.a27) using eucjpms)),( ((t27.col258) in ((t27.a27),(t27.a27),(t27.a27),(t27.a27),(t27.col258)))),( mid((t27.a27),(t27.a27),(t27.a27))),( char((t27.col258) using latin5)),( @@global.character_set_system)) or elt((t6.col52),(t6.col54),(t6.a6),(t6.a6),(t6.a6),(t6.col53)) ) natural join `test`. ---TRANSACTION 13DA, not started, process no 2062, OS thread id 139821337605888 mysql tables in use 1, locked 1 MySQL thread id 23, query id 6776 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t21_before_insert` before insert on `t21` for each row begin delete t16 from ( select count(*) from `test`.`t16` as `t16` left join `test`.`t28` as `t28` use index (PRIMARY,idx255,PRIMARY) on (t16.a16 is not false ) cross join `test`.`t26` as `t26` on (t16.col176 is true ) limit 1 ) `x`, t16 as t16 where t16.a16>x.col267 ; update ( select count(*) from `test`.`t16` as `t16` left join `test`.`t28` as `t28` use index (PRIMARY,idx255,PRIMARY) on (t16.a16 not regexp @var1 ) cross join `test`.`t26` as `t26` on (@var0 is not true ) ---TRANSACTION 13E7, not started, process no 2062, OS thread id 139821337872128 MySQL thread id 22, query id 6823 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t10_before_delete` before delete on `t10` for each row begin delete t29 from ( select count(*) from `test`.`t18` as `t18` ignore index (PRIMARY) natural left outer join `test`.`t1` as `t1` ignore index (PRIMARY,idx1) right outer join `test`.`t28` as `t28` on (t18.col197 | @var7 ) left outer join `test`.`t25` as `t25` ignore index (idx212) on (t18.col188 is true ) limit 1 ) `x`, t29 as t29 where t29.col295>x.col196 ; update ( select count(*) from `test`.`t18` as `t18` ignore index (PRIMARY) natural left outer join `test`.`t1` as `t1` ig ---TRANSACTION 1345, not started, process no 2062, OS thread id 139821339203328 MySQL thread id 17, query id 6918 localhost 127.0.0.1 root Waiting for table metadata lock explain extended select all sql_no_cache count(*) from `test`.`t26` as `t26` force index (PRIMARY,idx229,idx233,idx225) straight_join `test`.`t2` as `t2` on (@var7 is true ) left outer join `test`.`t29` as `t29` on (@var3 is not unknown ) group by t26.col256 desc, (( interval((`t2`.`col12`),(`t29`.`col286`),(`t26`.`col255`),(`t29`.`col281`),(`t2`.`col14`),(`t2`.`col10`))) in (( substring((`t29`.`col284`),(`t29`.`col281`),(`t29`.`col283`))),( greatest((`t26`.`col254`),(`t29`.`col286`),(`t26`.`a26`),(`t29`.`col288`),(`t2`.`col10`))),( interval((`t29`.`col289`),(`t2`.`col1 ---TRANSACTION 13ED, not started, process no 2062, OS thread id 139821340800768 MySQL thread id 11, query id 6819 localhost 127.0.0.1 root Waiting for table metadata lock update ignore `test`.`t27` set `a27` = '', `col258` = '17:18:58' where `col258`&& 23661 limit 1 ---TRANSACTION 13CE, not started, process no 2062, OS thread id 139821340002048 MySQL thread id 14, query id 6674 localhost 127.0.0.1 root Waiting for table metadata lock update ( select count(*) from `test`.`t18` as `t18` natural right join `test`.`t19` as `t19` left outer join `test`.`t23` as `t23` on (t18.col187 is not false ) cross join `test`.`t14` as `t14` on ( '1' ) straight_join `test`.`t5` as `t5` on ( export_set((t18.col191),(t18.col192),(t18.col193),(t18.col191)) >> ((t5.col50) not in ((t5.col39),(t5.col42),(t5.col35),(t5.col37))) ) where t5.col41 <= t18.col187 and not row(t18.col190 ,t18.col195 ,t5.col43 )>= row( t5.col50 , t19.col203 , t18.col191 )xor t18.col195 sounds like t19.col200 group by t5.col48, t18. ---TRANSACTION 137C, not started, process no 2062, OS thread id 139821341333248 MySQL thread id 9, query id 6894 localhost 127.0.0.1 root Waiting for table metadata lock explain select sql_no_cache all count(*) from `test`.`t20` as `t20` natural join `test`.`t2` as `t2` ignore index (idx7,PRIMARY) natural left join `test`.`t25` as `t25` force index (PRIMARY) left join `test`.`t4` as `t4` on ( '1' ) having `col207` & `col207` and `col207` || `col207` for update ---TRANSACTION 0, not started, process no 2062, OS thread id 139821339469568 MySQL thread id 16, query id 6482 localhost 127.0.0.1 root Waiting for table metadata lock select all count(*) from `test`.`t11` as `t11` inner join `test`.`t17` as `t17` on (t11.col91 is not true ) right outer join `test`.`t10` as `t10` ignore index (idx56,idx63,idx64,idx64) on ( repeat(( ((t11.col107) not in ((t11.col96),(t11.col92),(t11.col99),(t11.col98),(t11.a11)))),( interval((t11.col103),(t11.col96),(t11.col94),(t11.col99),(t11.col108))) mod 10) <> @var7 ) natural right join `test`.`t5` as `t5` natural join `test`.`t25` as `t25` use index (PRIMARY) right outer join `test`.`t13` as `t13` on ( ((t11.col105) in ((t11.col95),(t11.col106),(t11.col93 ---TRANSACTION 13FC, not started, process no 2062, OS thread id 139821341599488 MySQL thread id 8, query id 6928 localhost 127.0.0.1 root Waiting for table metadata lock delete from `test`.`t27` where `col258` <= 0 limit 1 ---TRANSACTION 13D1, not started, process no 2062, OS thread id 139821341865728 mysql tables in use 1, locked 1 MySQL thread id 7, query id 6756 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t16_before_delete` before delete on `t16` for each row begin delete t20 from ( select col39,col35 from `test`.`t26` as `t26` right join `test`.`t10` as `t10` on ( elt(( @@global.tmpdir),( interval((t26.col255),(t26.a26),(t26.a26),(t26.col256),(t26.col257),(t26.col255))),( ((t26.a26) - (t26.a26))),( least((t26.col254),(t26.col256),(t26.col257),(t26.a26))),( ((t26.col255) not between (t26.a26) and (t26.col257))),( get_format(datetime,'internal'))) - least((t10.a10),(t10.col87),(t10.col89),(t10.col89),(t10.col87)) ) left outer join `test`.`t5` as `t5` on (t26 ---TRANSACTION 1387, not started, process no 2062, OS thread id 139821340268288 MySQL thread id 13, query id 6504 localhost 127.0.0.1 root Waiting for table metadata lock insert ignore into `test`.`t6` set `col52` = -1.3764409700144E+18, `col53` = '2011-04-17 04:48:22', `a6` = 0, `col55` = 14038542, `col54` = '2011-10-25 15:35:45' ---TRANSACTION 13EE, not started, process no 2062, OS thread id 139821340534528 MySQL thread id 12, query id 6827 localhost 127.0.0.1 root Waiting for table metadata lock delete t15 from ( select count(*) from `test`.`t28` as `t28` use index (idx250,idx250) right outer join `test`.`t18` as `t18` on (t28.a28 & @var6 ) natural left join `test`.`t19` as `t19` right join `test`.`t26` as `t26` use index (idx220,PRIMARY,idx229) on ( datediff((t28.col264),(t28.col263)) < elt((t26.col256),(t26.col257),(t26.a26),(t26.a26)) ) left join `test`.`t2` as `t2` use index (idx8,idx8) on ( '1' ) inner join `test`.`t9` as `t9` on ( '1' ) right outer join `test`.`t4` as `t4` on ( coalesce((t28.col263),(t28.col270),(t28.col263),(t28.col266 ---TRANSACTION 13F0, not started, process no 2062, OS thread id 139821339735808 MySQL thread id 15, query id 6825 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t27_before_delete` before delete on `t27` for each row begin delete t27 from ( select count(*) from `test`.`t7` as `t7` right outer join `test`.`t11` as `t11` on (@var2 is unknown ) natural left outer join `test`.`t9` as `t9` left join `test`.`t20` as `t20` on (@var9 is not unknown ) where t9.col74 < null and not t11.col110 in( select '1' ) or t11.col108 >( select '1' ) limit 1 ) `x`, t27 as t27 where t27.col258>x.a9 ; update ( select count(*) from `test`.`t7` as `t7` right outer join `test`.`t11` as `t11` on (~t7.col58 ) ---TRANSACTION 13DE, not started, process no 2062, OS thread id 139821338670848 MySQL thread id 19, query id 6814 localhost 127.0.0.1 root Waiting for table metadata lock insert ignore into `test`.`t21` set `col219` = 0.3606, `col222` = -3.9366862609486E+18, `col224` = 1, `col225` = "…»QõT¢eŽ½O¾w’", `col226` = 669377, `col223` = '', `col218` = -1268352046, `a21` = '2010-04-17 15:10:24', `col221` = 3114, `col220` = 3062870 ---TRANSACTION 13CF, not started, process no 2062, OS thread id 139821338404608 MySQL thread id 20, query id 6767 localhost 127.0.0.1 root Waiting for table metadata lock delete t10 from ( select count(*) from `test`.`t7` as `t7` force index (idx31) natural right outer join `test`.`t12` as `t12` left join `test`.`t2` as `t2` on (@var2 not rlike @var4 ) left outer join `test`.`t11` as `t11` use index (PRIMARY,idx77,idx73) on ( get_format(time,'eur') | @var5 ) cross join `test`.`t17` as `t17` on (t7.a7 * @var5 ) inner join `test`.`t13` as `t13` on ( '1' ) group by t12.col118 limit 1 ) `x`, t10 as t10 where t10.col90>x.col102 ---TRANSACTION 13E1, not started, process no 2062, OS thread id 139821341067008 MySQL thread id 10, query id 6793 localhost 127.0.0.1 root Waiting for table metadata lock create trigger `trg_t25_after_update` after update on `t25` for each row begin delete t18 from ( select count(*) from `test`.`t11` as `t11` ignore index (idx76,idx66,idx79) right join `test`.`t28` as `t28` ignore index (idx255,idx255) on ( '1' ) limit 1 ) `x`, t18 as t18 where t18.col189>x.col268 ; update ( select count(*) from `test`.`t11` as `t11` ignore index (idx76,idx66,idx79) right join `test`.`t28` as `t28` ignore index (idx255,idx255) on (@var2 is true ) limit 1 ) `x`, `test`.`t9` as `t9` set `t9`.`col72`=`x`.`col268` ; select count(*) from ---TRANSACTION 1360, not started, process no 2062, OS thread id 139821342664448 MySQL thread id 4, query id 6931 localhost 127.0.0.1 root Waiting for table metadata lock ( select count(*) from `test`.`t29` as `t29` force index (idx266,idx265) natural left join `test`.`t13` as `t13` natural left join `test`.`t26` as `t26` natural right join `test`.`t14` as `t14` left join `test`.`t4` as `t4` force index (idx19) on ( concat_ws((t29.col289),(t29.col279),(t29.col285)) <> date_add((t4.col29),interval (t4.col24) month) ) right outer join `test`.`t22` as `t22` on (t29.col289 / @var8 ) group by t29.a29 limit 1 lock in share mode) union all ( select count(*) from `test`.`t29` as `t29` force index (idx266,idx265) natu ---TRANSACTION 133D, not started, process no 2062, OS thread id 139821342398208 MySQL thread id 5, query id 6512 localhost 127.0.0.1 root Waiting for table metadata lock delete t10 from ( select count(*) from `test`.`t17` as `t17` force index (idx119,PRIMARY,idx119) cross join `test`.`t7` as `t7` force index (PRIMARY) on ( '1' ) natural left outer join `test`.`t26` as `t26` natural left outer join `test`.`t3` as `t3` force index (idx15,idx15,PRIMARY) straight_join `test`.`t27` as `t27` ignore index (idx247) on ( insert(( cast((t17.a17) as unsigned)),( interval((t17.col186),(t17.col185),(t17.col185))),( ((t17.col185) in ((t17.col186),(t17.col186),(t17.a17),(t17.col185),(t17.a17)))),( current_timestamp())) | t17.col186 ) straight_jo ---TRANSACTION 13F3, ACTIVE 3036 sec, process no 2062, OS thread id 139821336008448 fetching rows mysql tables in use 14, locked 0 MySQL thread id 29, query id 6667 localhost 127.0.0.1 root Sending data select count(*) from `test`.`t23` as `t23` force index (PRIMARY,idx192) natural right join `test`.`t3` as `t3` ignore index (idx13,idx15) left outer join `test`.`t10` as `t10` ignore index (idx56) on ( '1' ) left outer join `test`.`t18` as `t18` on (@var7 is not false ) natural right join `test`.`t7` as `t7` force index (PRIMARY) right outer join `test`.`t1` as `t1` on (t23.col234 is not null ) inner join `test`.`t21` as `t21` force index (PRIMARY,PRIMARY,PRIMARY,idx174) on ( '1' ) limit 1 union all select col235 from `test`.`t23` as `t2 Trx read view will not see trx with id >= 13F4, sees < 1360 ---TRANSACTION 13F2, ACTIVE 3542 sec, process no 2062, OS thread id 139821342930688 mysql tables in use 12, locked 12 13 lock struct(s), heap size 3112, 126 row lock(s) MySQL thread id 3, query id 6872 localhost 127.0.0.1 root executing ( select count(*) from `test`.`t9` as `t9` force index (idx55,idx48) natural left outer join `test`.`t27` as `t27` ignore index (idx241,idx242,idx239,idx246) inner join `test`.`t17` as `t17` on (t9.col78 is not null ) right join `test`.`t22` as `t22` on ( '1' ) right join `test`.`t2` as `t2` on ( '1' ) right join `test`.`t8` as `t8` use index (PRIMARY,idx37,idx37) on ( '1' ) where t9.col73 collate latin1_bin <= '2010-08-17 10:02:17' or t9.col70 > all( select '1' ) and row(t9.col67 ,t9.col75 )< row(( select '1' ) ,( select '1' ) ) group by t9.col78 d ---TRANSACTION 1382, ACTIVE 4026 sec, process no 2062, OS thread id 139821342131968 starting index read mysql tables in use 14, locked 0 MySQL thread id 6, query id 6337 localhost 127.0.0.1 root Sending data ( select count(*) from `test`.`t7` as `t7` natural join `test`.`t25` as `t25` use index (PRIMARY) natural left join `test`.`t5` as `t5` cross join `test`.`t16` as `t16` on (@var0 is not unknown ) natural join `test`.`t18` as `t18` force index (idx128) natural left join `test`.`t29` as `t29` force index (idx267) left join `test`.`t1` as `t1` on (@var6 = t1.col3 ) limit 1 ) union all ( select count(*) from `test`.`t7` as `t7` natural join `test`.`t25` as `t25` use index (PRIMARY) natural left join `test`.`t5` as `t5` cross join `t Trx read view will not see trx with id >= 1383, sees < 1326 -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (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 82 OS file reads, 5004 OS file writes, 3553 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 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 4425293, used cells 411, node heap has 2 buffer(s) 6666.76 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 6031995 Log flushed up to 6031995 Pages flushed up to 6031995 Last checkpoint at 6031995 0 pending log writes, 0 pending chkp writes 1754 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2217738240; in additional pool allocated 0 Dictionary memory allocated 397022 Buffer pool size 131071 Free buffers 130276 Database pages 793 Old database pages 274 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 0, created 2700, written 1174 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 LRU len: 793, 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 3 read views open inside InnoDB Main thread process no. 2062, id 139817780160256, state: sleeping Number of rows inserted 583, updated 21, deleted 7, read 60206797 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 17495.14 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.34 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G *************************** 1. row *************************** trx_id: 13F3 trx_state: RUNNING trx_started: 2011-03-15 08:19:23 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 29 trx_query: select count(*) from `test`.`t23` as `t23` force index (PRIMARY,idx192) natural right join `test`.`t3` as `t3` ignore index (idx13,idx15) left outer join `test`.`t10` as `t10` ignore index (idx56) on ( '1' ) left outer join `test`.`t18` as `t18` on (@var7 is not false ) natural right join `test`.`t7` as `t7` force index (PRIMARY) right outer join `test`.`t1` as `t1` on (t23.col234 is not null ) inner join `test`.`t21` as `t21` force index (PRIMARY,PRIMARY,PRIMARY,idx174) on ( '1' ) limit 1 union all select col235 from `test`.`t23` as `t23` force index (PRIMARY,idx192) natural right join `test`.`t3` as `t3` ignore index (idx13,idx15) left outer join `test`.`t10` as `t10` ignore index (idx56) on ( '1' ) left outer join `test`.`t18` as `t18` on (@var7 regexp @var3 ) natural right join `test`.`t7` as `t7` force index (PRIMARY) right outer join `test`.`t1` as `t1` on (@var4 is true ) inner join `test`.`t21` as `t21` force ind trx_operation_state: starting index read trx_tables_in_use: 14 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 376 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 *************************** 2. row *************************** trx_id: 13F2 trx_state: RUNNING trx_started: 2011-03-15 08:10:57 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 13 trx_mysql_thread_id: 3 trx_query: ( select count(*) from `test`.`t9` as `t9` force index (idx55,idx48) natural left outer join `test`.`t27` as `t27` ignore index (idx241,idx242,idx239,idx246) inner join `test`.`t17` as `t17` on (t9.col78 is not null ) right join `test`.`t22` as `t22` on ( '1' ) right join `test`.`t2` as `t2` on ( '1' ) right join `test`.`t8` as `t8` use index (PRIMARY,idx37,idx37) on ( '1' ) where t9.col73 collate latin1_bin <= '2010-08-17 10:02:17' or t9.col70 > all( select '1' ) and row(t9.col67 ,t9.col75 )< row(( select '1' ) ,( select '1' ) ) group by t9.col78 desc, t9.col66 having `col78` | `col78` limit 1 lock in share mode) union all ( select count(*) from `test`.`t9` as `t9` force index (idx55,idx48) natural left outer join `test`.`t27` as `t27` ignore index (idx241,idx242,idx239,idx246) inner join `test`.`t17` as `t17` on ( '1' ) right join `test`.`t22` as `t22` on (@var7 + @var5 ) right join `test`.`t2` as `t2` on ( des_decrypt((t9.col6 trx_operation_state: NULL trx_tables_in_use: 12 trx_tables_locked: 12 trx_lock_structs: 13 trx_lock_memory_bytes: 3112 trx_rows_locked: 126 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 *************************** 3. row *************************** trx_id: 1382 trx_state: RUNNING trx_started: 2011-03-15 08:02:53 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 6 trx_query: ( select count(*) from `test`.`t7` as `t7` natural join `test`.`t25` as `t25` use index (PRIMARY) natural left join `test`.`t5` as `t5` cross join `test`.`t16` as `t16` on (@var0 is not unknown ) natural join `test`.`t18` as `t18` force index (idx128) natural left join `test`.`t29` as `t29` force index (idx267) left join `test`.`t1` as `t1` on (@var6 = t1.col3 ) limit 1 ) union all ( select count(*) from `test`.`t7` as `t7` natural join `test`.`t25` as `t25` use index (PRIMARY) natural left join `test`.`t5` as `t5` cross join `test`.`t16` as `t16` on ( export_set(( des_encrypt((t7.a7))),( ((t7.a7) < (t7.col58))),( ((t7.a7) in ((t7.col58),(t7.col56),(t7.col56),(t7.col56),(t7.col57))))) | des_encrypt((t16.col179)) ) natural join `test`.`t18` as `t18` force index (idx128) natural left join `test`.`t29` as `t29` force index (idx267) left join `test`.`t1` as `t1` on ( ((t7.a7) not in ((t7.col58),(t7.a7),(t7.col57),(t7.col56),(t7. trx_operation_state: starting index read trx_tables_in_use: 14 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 376 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 3 rows in set (0.00 sec) mysql> SHOW PROCESSLIST; +----+------+-----------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 3 | root | localhost:60300 | test | Query | 3562 | executing | ( select count(*) from `test`.`t9` as `t9` force index (idx55,idx48) natural left outer join `t | | 4 | root | localhost:60301 | test | Query | 2412 | Waiting for table metadata lock | ( select count(*) from `test`.`t29` as `t29` force index (idx266,idx265) natural left join `tes | | 5 | root | localhost:60302 | test | Query | 4045 | Waiting for table metadata lock | delete t10 from ( select count(*) from `test`.`t17` as `t17` force index (idx119,PRIMARY,idx119) | | 6 | root | localhost:60303 | test | Query | 4046 | Sending data | ( select count(*) from `test`.`t7` as `t7` natural join `test`.`t25` as `t25` use index (PRIMA | | 7 | root | localhost:60304 | test | Query | 3986 | Waiting for table metadata lock | create trigger `trg_t16_before_delete` before delete on `t16` for each row begin delete t20 from ( | | 8 | root | localhost:60305 | test | Query | 2939 | Waiting for table metadata lock | delete from `test`.`t27` where `col258` <= 0 limit 1 | | 9 | root | localhost:60306 | test | Query | 3056 | Waiting for table metadata lock | explain select sql_no_cache all count(*) from `test`.`t20` as `t20` natural join `test`.`t2` | | 10 | root | localhost:60307 | test | Query | 3969 | Waiting for table metadata lock | create trigger `trg_t25_after_update` after update on `t25` for each row begin delete t18 from ( se | | 11 | root | localhost:60308 | test | Query | 3941 | Waiting for table metadata lock | update ignore `test`.`t27` set `a27` = '', `col258` = '17:18:58' where `col258`&& 23661 limit 1 | | 12 | root | localhost:60309 | test | Query | 3890 | Waiting for table metadata lock | delete t15 from ( select count(*) from `test`.`t28` as `t28` use index (idx250,idx250) right o | | 13 | root | localhost:60310 | test | Query | 4045 | Waiting for table metadata lock | insert ignore into `test`.`t6` set `col52` = -1.3764409700144E+18, `col53` = '2011-04-17 04:48:22', | | 14 | root | localhost:60311 | test | Query | 4011 | Waiting for table metadata lock | update ( select count(*) from `test`.`t18` as `t18` natural right join `test`.`t19` as `t19` | | 15 | root | localhost:60312 | test | Query | 3918 | Waiting for table metadata lock | create trigger `trg_t27_before_delete` before delete on `t27` for each row begin delete t27 from ( | | 16 | root | localhost:60313 | test | Query | 4045 | Waiting for table metadata lock | select all count(*) from `test`.`t11` as `t11` inner join `test`.`t17` as `t17` on (t11.col9 | | 17 | root | localhost:60314 | test | Query | 3056 | Waiting for table metadata lock | explain extended select all sql_no_cache count(*) from `test`.`t26` as `t26` force index (PRIMAR | | 18 | root | localhost:60315 | test | Query | 3056 | Waiting for table metadata lock | select count(*) from `test`.`t19` as `t19` ignore index (idx141,idx131,idx145,idx131) natural l | | 19 | root | localhost:60316 | test | Query | 3941 | Waiting for table metadata lock | insert ignore into `test`.`t21` set `col219` = 0.3606, `col222` = -3.9366862609486E+18, `col224` = 1 | | 20 | root | localhost:60317 | test | Query | 3973 | Waiting for table metadata lock | delete t10 from ( select count(*) from `test`.`t7` as `t7` force index (idx31) natural right o | | 21 | root | localhost:60318 | test | Query | 4039 | Waiting for table metadata lock | explain extended select sql_no_cache distinctrow col184 from `test`.`t27` as `t27` right out | | 22 | root | localhost:60319 | test | Query | 3918 | Waiting for table metadata lock | create trigger `trg_t10_before_delete` before delete on `t10` for each row begin delete t29 from ( | | 23 | root | localhost:60320 | test | Query | 3969 | Waiting for table metadata lock | create trigger `trg_t21_before_insert` before insert on `t21` for each row begin delete t16 from ( | | 24 | root | localhost:60321 | test | Query | 2614 | Waiting for table metadata lock | select count(*) from `test`.`t19` as `t19` right join `test`.`t12` as `t12` on ( (case (( (( | | 25 | root | localhost:60322 | test | Query | 3991 | Waiting for table metadata lock | create trigger `trg_t6_before_insert` before insert on `t6` for each row begin delete t22 from ( se | | 26 | root | localhost:60323 | test | Query | 3969 | Waiting for table metadata lock | create trigger `trg_t28_before_update` before update on `t28` for each row begin delete t17 from ( | | 27 | root | localhost:60324 | test | Query | 3995 | Waiting for table metadata lock | explain select col283,col119 from `test`.`t22` as `t22` ignore index (PRIMARY,idx188,idx189,idx | | 28 | root | localhost:60325 | test | Query | 3890 | Waiting for table metadata lock | update ignore `test`.`t16` set `a16` = '', `col173` = -1075813, `col174` = from_unixtime(unix_timest | | 29 | root | localhost:60326 | test | Query | 4037 | Sending data | select count(*) from `test`.`t23` as `t23` force index (PRIMARY,idx192) natural right join `tes | | 30 | root | localhost:60327 | test | Query | 3056 | Waiting for table metadata lock | explain partitions select sql_cache all col215 from `test`.`t6` as `t6` natural left outer j | | 31 | root | localhost:60328 | test | Query | 3915 | Waiting for table metadata lock | update ignore `test`.`t25` set `col252` = 0, `col253` = 2071150030, `a25` = geomfromtext("point(9023 | | 32 | root | localhost:60329 | test | Query | 3943 | Waiting for table metadata lock | create trigger `trg_t17_after_update` after update on `t17` for each row begin delete t25 from ( se | | 33 | root | localhost:60330 | test | Query | 3969 | Waiting for table metadata lock | explain select distinctrow sql_no_cache col179 from `test`.`t27` as `t27` ignore index (idx238, | | 34 | root | localhost:60331 | test | Query | 3941 | Waiting for table metadata lock | create trigger `trg_t27_before_update` before update on `t27` for each row begin delete t29 from ( | | 35 | root | localhost:60332 | test | Query | 3056 | Waiting for table metadata lock | select `t8`.`col60` as col60 from `test`.`t17` as `t17` ignore index (idx119,idx119) inner jo | | 36 | root | localhost:60333 | test | Query | 3983 | Waiting for table metadata lock | create trigger `trg_t26_before_insert` before insert on `t26` for each row begin delete t14 from ( | | 37 | root | localhost:60334 | test | Query | 3004 | Waiting for table metadata lock | insert ignore into `test`.`t27` set `a27` = 'nalyiwqifiitsccp', `col258` = 3721 | | 38 | root | localhost:60335 | test | Query | 3986 | Waiting for table metadata lock | delete from `test`.`t6` where `col54` >= null limit 1 | | 39 | root | localhost:60336 | test | Query | 3888 | Waiting for table metadata lock | update ignore `test`.`t6` set `col52` = 8634042, `col53` = '2010-10-22', `a6` = -8.3404022104877E+18 | | 40 | root | localhost:60337 | test | Query | 4045 | Waiting for table metadata lock | delete t28 from ( select col264,a12 from `test`.`t26` as `t26` right outer join `test`.`t4` | | 41 | root | localhost:60338 | test | Query | 3056 | Waiting for table metadata lock | explain extended select `t19`.`col202` as col202 , (( count(distinct (`t19`.`col201`))) in (( g | | 42 | root | localhost:60339 | test | Query | 4040 | Waiting for table metadata lock | select sql_cache count(*) from `test`.`t4` as `t4` ignore index (idx19) natural left join `test | | 45 | root | localhost:48500 | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+------+-----------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ 41 rows in set (0.00 sec) mysql> notee