InnoDB: Warning: a long semaphore wait: --Thread 9 has waited at buf/buf0flu.c line 607 for 241.00 seconds the semaphore: S-lock on RW-latch at 9143aec10 created in file buf/buf0buf.c line 547 a writer (thread id 22746) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file ../../storage/innobase/include/btr0btr.ic line 28 Last time write locked in file btr/btr0pcur.c line 249 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100802 12:37:14 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 26 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 181601191, signal count 96451519 --Thread 9 has waited at buf/buf0flu.c line 607 for 253.00 seconds the semaphore: S-lock on RW-latch at 9143aec10 created in file buf/buf0buf.c line 547 a writer (thread id 22746) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file ../../storage/innobase/include/btr0btr.ic line 28 Last time write locked in file btr/btr0pcur.c line 249 Mutex spin waits 0, rounds 10128567202, OS waits 132876352 RW-shared spins 32295049, OS waits 18681903; RW-excl spins 47951670, OS waits 5967592 ------------ TRANSACTIONS ------------ Trx id counter 2 1976781511 Purge done for trx's n:o < 2 1976781509 undo n:o < 0 0 History list length 4 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 2 1976781095, not started, OS thread id 20754 MySQL thread id 56757690, query id 6841441126 Opening tables UPDATE user_shards SET last_login_time = UNIX_TIMESTAMP() WHERE user_id = 3624216 ---TRANSACTION 2 1976778811, not started, OS thread id 18909 MySQL thread id 56757689, query id 6841440616 Opening tables UPDATE user_shards SET last_login_time = UNIX_TIMESTAMP() WHERE user_id = 9531538 ---TRANSACTION 2 1976778576, not started, OS thread id 23435 MySQL thread id 56757680, query id 6841438947 Opening tables UPDATE user_shards SET last_login_time = UNIX_TIMESTAMP() WHERE user_id = 1240938 ---TRANSACTION 2 1976778366, not started, OS thread id 26405 .............. .............. .............. (here is the drop table! but the same thread had different transaction number in different innodb status output) ---TRANSACTION 0 0, not started, OS thread id 22746 MySQL thread id 56757556, query id 6841437170 ops-001 10.10.102.6 dbm drop table if exists referer_20100716 ........... ........... -------- 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 (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1274085 OS file reads, 2866684828 OS file writes, 13253637 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 2697, seg size 2699, 84443 inserts, 84443 merged recs, 77807 merges Hash table size 74374277, node heap has 119120 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 203 2984815956 Log flushed up to 203 2984711514 Last checkpoint at 203 2980142167 0 pending log writes, 0 pending chkp writes 2667142709 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 40727163418; in additional pool allocated 19809792 Dictionary memory allocated 353344 Buffer pool size 2293760 Free buffers 568871 Database pages 1605769 Modified db pages 84 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages read 1495583, created 2794839, written 281389932 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 9, state: flushing buffer pool pages Number of rows inserted 376516535, updated 1008155937, deleted 10047843, read 259288577319 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100802 12:37:30 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 181601191, signal count 96451519 --Thread 9 has waited at buf/buf0flu.c line 607 for 269.00 seconds the semaphore: S-lock on RW-latch at 9143aec10 created in file buf/buf0buf.c line 547 a writer (thread id 22746) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file ../../storage/innobase/include/btr0btr.ic line 28 Last time write locked in file btr/btr0pcur.c line 249 Mutex spin waits 0, rounds 10128567202, OS waits 132876352 RW-shared spins 32295049, OS waits 18681903; RW-excl spins 47951670, OS waits 5967592 ------------ TRANSACTIONS ------------ Trx id counter 2 1976781511 Purge done for trx's n:o < 2 1976781509 undo n:o < 0 0 History list length 4 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 2 1976781095, not started, OS thread id 20754 MySQL thread id 56757690, query id 6841441126 Opening tables UPDATE user_shards SET last_login_time = UNIX_TIMESTAMP() WHERE user_id = 3624216 ..................... ..................... -------- 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 (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1274085 OS file reads, 2866684828 OS file writes, 13253637 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 2697, seg size 2699, 84443 inserts, 84443 merged recs, 77807 merges Hash table size 74374277, node heap has 119117 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s LOG --- Log sequence number 203 2984822698 Log flushed up to 203 2984711514 Last checkpoint at 203 2980142167 0 pending log writes, 0 pending chkp writes 2667142709 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 40727163418; in additional pool allocated 19809792 Dictionary memory allocated 353344 Buffer pool size 2293760 Free buffers 568874 Database pages 1605769 Modified db pages 84 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages read 1495583, created 2794839, written 281389932 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 9, state: flushing buffer pool pages Number of rows inserted 376516535, updated 1008155937, deleted 10047843, read 259288577319 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 9 has waited at buf/buf0flu.c line 607 for 272.00 seconds the semaphore: S-lock on RW-latch at 9143aec10 created in file buf/buf0buf.c line 547 a writer (thread id 22746) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file ../../storage/innobase/include/btr0btr.ic line 28 Last time write locked in file btr/btr0pcur.c line 249 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100802 12:37:46 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 181601191, signal count 96451519 --Thread 9 has waited at buf/buf0flu.c line 607 for 285.00 seconds the semaphore: S-lock on RW-latch at 9143aec10 created in file buf/buf0buf.c line 547 a writer (thread id 22746) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file ../../storage/innobase/include/btr0btr.ic line 28 Last time write locked in file btr/btr0pcur.c line 249 Mutex spin waits 0, rounds 10128567202, OS waits 132876352 RW-shared spins 32295049, OS waits 18681903; RW-excl spins 47951670, OS waits 5967592 ------------ TRANSACTIONS ------------ Trx id counter 2 1976781511 Purge done for trx's n:o < 2 1976781509 undo n:o < 0 0 History list length 4 .... .... (similar messages repeats) ........ (in the end ....) ---TRANSACTION 2 1976781510, ACTIVE 1116 sec, OS thread id 22746 dropping table 10 lock struct(s), heap size 1216, 7 row lock(s), undo log entries 1 MySQL thread id 56757556, query id 6841437170 ops-001 10.10.102.6 dbm drop table if exists referer_20100716 -------- 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 (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1274085 OS file reads, 2866684828 OS file writes, 13253637 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 2697, seg size 2699, 84443 inserts, 84443 merged recs, 77807 merges Hash table size 74374277, node heap has 118983 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 203 2985229438 Log flushed up to 203 2984711514 Last checkpoint at 203 2980142167 0 pending log writes, 0 pending chkp writes 2667142709 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 40727163418; in additional pool allocated 19809792 Dictionary memory allocated 353344 Buffer pool size 2293760 Free buffers 569008 Database pages 1605769 Modified db pages 93 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages read 1495583, created 2794839, written 281389932 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 9, state: flushing buffer pool pages Number of rows inserted 376516535, updated 1008155937, deleted 10047843, read 259288577319 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. 100802 12:48:55 InnoDB: Assertion failure in thread 8 in file srv/srv0srv.c line 2137 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. 100802 12:48:55 - 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=268435456 read_buffer_size=2097152 max_used_connections=4706 max_threads=8000 threads_connected=3467 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 17734456 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. InnoDB: Thread 22746 stopped in file btr/btr0sea.c line 1002 InnoDB: Thread 7 stopped in file ../../storage/innobase/include/sync0sync.ic line 115 255872,1 Bot