InnoDB: Warning: a long semaphore wait: --Thread 1258379600 has waited at row/row0purge.c line 541 for 241.00 seconds the semaphore: S-lock on RW-latch at 0xe03f60 '&dict_operation_lock' a writer (thread id 1449539920) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100802 16:41:18 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 13 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 3313528 1_second, 3313528 sleeps, 331348 10_second, 203 background, 203 flush srv_master_thread log flush and writes: 3360698 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8245950, signal count 51830931 --Thread 1258379600 has waited at row/row0purge.c line 541 for 247.00 seconds the semaphore: S-lock on RW-latch at 0xe03f60 '&dict_operation_lock' a writer (thread id 1449539920) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 79428411, rounds 328827897, OS waits 7405154 RW-shared spins 5544772, OS waits 403342; RW-excl spins 1927420, OS waits 350988 Spin rounds per wait: 4.14 mutex, 2.94 RW-shared, 36.38 RW-excl -------- 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 (read thread) I/O thread 7 state: waiting for i/o request (read thread) I/O thread 8 state: waiting for i/o request (read thread) I/O thread 9 state: waiting for i/o request (read thread) I/O thread 10 state: waiting for i/o request (write thread) I/O thread 11 state: waiting for i/o request (write thread) I/O thread 12 state: waiting for i/o request (write thread) I/O thread 13 state: waiting for i/o request (write thread) I/O thread 14 state: waiting for i/o request (write thread) I/O thread 15 state: waiting for i/o request (write thread) I/O thread 16 state: waiting for i/o request (write thread) I/O thread 17 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 1508906 OS file reads, 511535898 OS file writes, 9136562 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 3002, seg size 3004, 94846 inserts, 94852 merged recs, 60272 merges Hash table size 80749181, node heap has 182887 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 11280747623265 Log flushed up to 11280747436187 Last checkpoint at 11280747436187 Max checkpoint age 3396675134 Checkpoint age target 3290529037 Modified age 187078 Checkpoint age 187078 0 pending log writes, 0 pending chkp writes 265920189 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 41838182400; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 3642418264 (645993448 + 2996424816) Page hash 40375592 Dictionary cache 161716616 (161499824 + 216792) File system 98656 (82672 + 15984) Lock system 101812008 (100937192 + 874816) Recovery system 0 (0 + 0) Threads 409496 (406936 + 2560) Dictionary memory allocated 216792 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1258379600 has waited at row/row0purge.c line 541 for 272.00 seconds the semaphore: S-lock on RW-latch at 0xe03f60 '&dict_operation_lock' a writer (thread id 1449539920) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1258379600 has waited at row/row0purge.c line 541 for 303.00 seconds the semaphore: S-lock on RW-latch at 0xe03f60 '&dict_operation_lock' a writer (thread id 1449539920) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ###### Diagnostic info printed to the standard error stream Buffer pool size 2490367 Buffer pool size, bytes 40802172928 Free buffers 598255 Database pages 1709225 Old database pages 630964 Modified db pages 5 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 485694, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 1508935, created 1478551, written 318828513 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: 1709225, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 30217, id 1233201488, state: waiting for server activity Number of rows inserted 125909885, updated 10287121477, deleted 388555, read 33054928109 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------ TRANSACTIONS ------------ Trx id counter 27D17250F Purge done for trx's n:o < 27D1724D9 undo n:o < 1 History list length 7 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started, process no 30217, OS thread id 1449539920 MySQL thread id 4769050, query id 887220270 10.10.102.6 dbm checking permissions drop table if exists checkin2.posts2_old ---TRANSACTION 27D17171C, not started, process no 30217, OS thread id 1656674640 MySQL thread id 4768921, query id 887209438 10.10.101.12 booyah_reader Opening tables SELECT * FROM users2 WHERE id = 11856718 ................. ............. .................. END OF INNODB MONITOR OUTPUT ============================ InnoDB: Warning: a long semaphore wait: --Thread 1258379600 has waited at row/row0purge.c line 541 for 341.00 seconds the semaphore: S-lock on RW-latch at 0xe03f60 '&dict_operation_lock' a writer (thread id 1449539920) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 1 ===================================== 100802 16:42:53 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 3313529 1_second, 3313529 sleeps, 331348 10_second, 203 background, 203 flush srv_master_thread log flush and writes: 3360698 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8248236, signal count 51831322 --Thread 1469774160 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1903212880 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1841445200 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1780210000 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1784469840 has waited at btr/btr0cur.c line 480 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1769560400 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1877653840 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1872861520 has waited at btr/btr0cur.c line 480 for 0.0000 seconds the semaphore: X-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1888303440 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x7fc652deea50 '&new_index->lock' a writer (thread id 1784469840) has reserved it in mode wait exclusive number of readers 2, waiters flag 1, lock_word: fffffffffffffffe Last time read locked in file btr/btr0cur.c line 487 Last time write locked in file btr/btr0cur.c line 480 --Thread 1937824080 has waited at btr/btr0cur.c line 553 for 1.0000 seconds the semaphore: X-lock on RW-latch at 0x7fc6cd4820c8 '&block->lock' number of readers 0, waiters flag 0, lock_word: 100000 Last time read locked in file buf/buf0flu.c line 879 Last time write locked in file btr/btr0cur.c line 553 wait has ended --Thread 1560029520 has waited at btr/btr0cur.c line 553 for 1.0000 seconds the semaphore: X-lock on RW-latch at 0x7fc6cd4820c8 '&block->lock' number of readers 0, waiters flag 0, lock_word: 100000 Last time read locked in file buf/buf0flu.c line 879 Last time write locked in file btr/btr0cur.c line 553 wait has ended Mutex spin waits 79431107, rounds 328882286, OS waits 7406395 RW-shared spins 5544963, OS waits 403559; RW-excl spins 1928009, OS waits 351754 Spin rounds per wait: 4.14 mutex, 2.94 RW-shared, 36.38 RW-excl -------- 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 (read thread) I/O thread 7 state: waiting for i/o request (read thread) I/O thread 8 state: waiting for i/o request (read thread) I/O thread 9 state: waiting for i/o request (read thread) I/O thread 10 state: waiting for i/o request (write thread) I/O thread 11 state: waiting for i/o request (write thread) I/O thread 12 state: waiting for i/o request (write thread) I/O thread 13 state: waiting for i/o request (write thread) I/O thread 14 state: waiting for i/o request (write thread) I/O thread 15 state: waiting for i/o request (write thread) I/O thread 16 state: waiting for i/o request (write thread) I/O thread 17 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: 1; buffer pool: 0 1508906 OS file reads, 511536493 OS file writes, 9136563 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 594.41 writes/s, 1.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 3002, seg size 3004, 94846 inserts, 94852 merged recs, 60272 merges Hash table size 80749181, node heap has 182887 buffer(s) 916.08 hash searches/s, 2966.03 non-hash searches/s --- LOG --- Log sequence number 11280747845000 Log flushed up to 11280747623375 Last checkpoint at 11280747436187 Max checkpoint age 3396675134 Checkpoint age target 3290529037 Modified age 224076 Checkpoint age 408813 1 pending log writes, 0 pending chkp writes 265920784 log i/o's done, 594.41 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 41838182400; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 3642418264 (645993448 + 2996424816) Page hash 40375592 Dictionary cache 161716616 (161499824 + 216792) File system 98224 (82672 + 15552) Lock system 101968328 (100937192 + 1031136) Recovery system 0 (0 + 0) Threads 409496 (406936 + 2560) Dictionary memory allocated 216792 Buffer pool size 2490367 Buffer pool size, bytes 40802172928 Free buffers 598250 Database pages 1709230 Old database pages 630926 Modified db pages 847 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 486692, not young 0 997.00 youngs/s, 0.00 non-youngs/s Pages read 1508935, created 1478556, written 318828513 0.00 reads/s, 5.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 66 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 1709230, 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 process no. 30217, id 1233201488, state: sleeping Number of rows inserted 125910686, updated 10287121496, deleted 388559, read 33054930229 800.20 inserts/s, 18.98 updates/s, 4.00 deletes/s, 2117.88 reads/s ------------ TRANSACTIONS ------------ Trx id counter 27D172C98 Purge done for trx's n:o < 27D172561 undo n:o < 0 History list length 24 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 27D172C13, not started, process no 30217, OS thread id 2056833360 MySQL thread id 4769747, query id 887215064 10.10.101.16 booyah_writer Sending data SELECT * FROM users2 WHERE id = 288316 ---TRANSACTION 27D172BF2, not started, process no 30217, OS thread id 2011040080 mysql tables in use 1, locked 1 MySQL thread id 4769686, query id 887215031 10.10.101.12 booyah_writer freeing items INSERT INTO user_achievement1 ( id, achievement_id, app, user_id, unlocked_at ) VALUES ( 22828847, 285212697, 'TIT', 12075380, 1280791907 ) ---TRANSACTION 27D172C16, not started, process no 30217, OS thread id 2057365840 MySQL thread id 4769748, query id 887215017 10.10.101.105 booyah_writer ---TRANSACTION 27D172BFE, not started, process no 30217, OS thread id 1931434320 MySQL thread id 4769585, query id 887214984 10.10.101.18 booyah_writer Sending data SELECT * FROM users2 WHERE id = 1244107 ---TRANSACTION 27D172BFA, not started, process no 30217, OS thread id 1933031760 MySQL thread id 4769589, query id 887214916 10.10.101.18 booyah_writer closing tables SELECT * FROM users1 WHERE id = 5394425 ---TRANSACTION 27D172C09, not started, process no 30217, OS thread id 2048579920 MySQL thread id 4769730, query id 887214907 10.10.101.102 booyah_writer Sending data SELECT * FROM users2 WHERE id = 11916736 ---TRANSACTION 27D172BD3, not started, process no 30217, OS thread id 1985481040 mysql tables in use 1, locked 1 MySQL thread id 4769657, query id 887214903 10.10.101.16 booyah_writer freeing items INSERT INTO user_achievement2 ( id, achievement_id, app, user_id, unlocked_at ) VALUES ( 22831684, 285212692, 'TIT', 10392826, 1280791905 ) ............... .............. (The second innodb status was after the drop table had finished.)