CURRENT_TEST: main.1st 100831 17:36:19 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead. 100831 17:36:19 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 100831 17:36:19 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use Solaris atomic functions InnoDB: Compressed tables use zlib 1.2.3 100831 17:36:19 InnoDB: highest supported file format is Barracuda. 100831 17:36:19 InnoDB 1.2.0 started; log sequence number 1595675 100831 17:36:19 [Warning] /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/jbuild-optimized/install/bin/mysqld: unknown option '--loose-skip-ndbcluster' 100831 17:36:19 [Warning] /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/jbuild-optimized/install/bin/mysqld: unknown variable 'loose-falcon-debug-mask=2' 100831 17:36:19 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ea99e67-b50d-11df-8a22-b53c0e90a99d. 100831 17:36:19 [Note] Event Scheduler: Loaded 0 events 100831 17:36:19 [Note] /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/jbuild-optimized/install/bin/mysqld: ready for connections. Version: '5.6.99-m5-log' socket: '/tmp/a0asqle8a9/master.sock' port: 10220 Source distribution InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 241.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:41:35 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 242.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 242 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:41:55 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 262.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 262 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 272.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:42:15 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 282.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 282 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:42:35 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 302.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 302 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 303.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:42:55 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 322.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 322 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 334.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:43:15 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 342.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 342 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:43:35 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 362.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 362 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 365.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:43:55 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 382.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 382 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 396.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:44:15 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 402.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 402 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:44:35 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 422.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 422 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 428.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:44:55 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 442.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 442 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 459.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:45:15 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 462.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 462 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:45:35 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 482.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 482 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 490.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:45:55 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 502.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 502 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 521.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:46:15 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 522.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 522 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:46:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 538.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 538 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 552.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:46:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 558.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 558 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:47:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 578.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 578 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 583.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:47:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 598.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 598 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 614.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:47:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 618.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 618 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:48:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 638.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 638 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 645.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:48:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 658.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 658 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 676.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:48:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 678.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 678 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:49:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 698.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 698 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 707.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:49:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 718.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 718 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 738.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:49:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 738.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 738 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:50:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 758.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 758 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 769.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:50:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 778.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 778 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:50:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 798.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 798 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 800.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:51:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 818.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 818 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 831.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:51:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 838.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 838 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:51:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 858.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 858 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 862.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:52:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 878.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 878 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 893.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:52:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 898.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 898 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100831 17:52:51 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 918.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 918 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 924.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100831 17:53:11 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 128 1_second, 72 sleeps, 8 10_second, 59 background, 57 flush srv_master_thread log flush and writes: 76 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56, signal count 58 --Thread 23 has waited at /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1136 for 938.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 24af948 created in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 182 a writer (thread id 23) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 1059 Last time write locked in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/btr/btr0sea.c line 636 Mutex spin waits 33, rounds 181, OS waits 3 RW-shared spins 32, rounds 960, OS waits 32 RW-excl spins 0, rounds 663, OS waits 21 Spin rounds per wait: 5.48 mutex, 30.00 RW-shared, 663.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2290 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 228F, ACTIVE 938 sec, OS thread id 23 mysql tables in use 5, locked 0 , holds adaptive hash latch MySQL thread id 6, query id 13945 localhost 127.0.0.1 root Sending data SELECT ( ( table1 . `col_int_nokey` ) * ( table1 . `col_int_nokey` ) ) AS field1 FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 INNER JOIN C AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_int_nokey` = table2 . `col_int_key` ) ) WHERE ( table3 . `col_int_key` != SOME ( SELECT SUBQUERY1_t1 . `col_int_nokey` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 LEFT OUTER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) ) WHERE SUBQUERY1_t2 . `col_int_nokey` > table1 . `col_int_nokey` ) ) OR ( table1 . `col_int_key` Trx read view will not see trx with id >= 2290, sees < 2290 -------- 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 155 OS file reads, 371 OS file writes, 285 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 276707, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2002672 Log flushed up to 2002672 Last checkpoint at 2002672 0 pending log writes, 0 pending chkp writes 116 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137428992; in additional pool allocated 0 Dictionary memory allocated 141552 Buffer pool size 8192 Free buffers 7936 Database pages 254 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 144, created 110, written 336 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 LRU len: 254, 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 2 read views open inside InnoDB Main thread id 16, state: waiting for server activity Number of rows inserted 1243, updated 0, deleted 0, read 4843934 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 100831 17:53:27 InnoDB: Assertion failure in thread 14 in file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/storage/innobase/srv/srv0srv.c line 2370 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 100831 17:53:27 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576 read_buffer_size=131072 max_used_connections=2 max_threads=151 thread_count=2 connection_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60679 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/jbuild-optimized/install/bin/mysqld'my_print_stacktrace+0x27 [0x131dc2f] /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/jbuild-optimized/install/bin/mysqld'handle_segfault+0x20e [0xb17cae] /lib/amd64/libc.so.1'__sighndlr+0x6 [0xfffffd7fff27b076] /lib/amd64/libc.so.1'call_user_handler+0x2a7 [0xfffffd7fff26dfaf] /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting/jbuild-optimized/install/bin/mysqld'srv_error_monitor_thread+0x1dd [0xecaf85] [Signal 11 (SEGV)] /lib/amd64/libc.so.1'_thrp_setup+0x8d [0xfffffd7fff27acf5] /lib/amd64/libc.so.1'_lwp_start+0x0 [0xfffffd7fff27afb0] Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Writing a core file