Some time later, mysql has now hung on a simple insert statement from replication. I'm assuming there's some sort of internal data file corruption here that's gone undetected, let me know please if it'd be useful to see it happening. 2012-08-28 16:29:25.005361500 InnoDB: Warning: a long semaphore wait: 2012-08-28 16:29:25.005459500 --Thread 47404182935312 has waited at buf0buf.c line 3667 for 241.00 seconds the semaphore: 2012-08-28 16:29:25.005485500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:29:25.005486500 waiters flag 1 2012-08-28 16:29:25.005740500 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: 2012-08-28 16:29:25.005768500 InnoDB: Pending preads 0, pwrites 0 2012-08-28 16:29:33.537933500 2012-08-28 16:29:33.537935500 ===================================== 2012-08-28 16:29:33.537936500 120828 16:29:33 INNODB MONITOR OUTPUT 2012-08-28 16:29:33.537937500 ===================================== 2012-08-28 16:29:33.537938500 Per second averages calculated from the last 60 seconds 2012-08-28 16:29:33.537939500 ----------------- 2012-08-28 16:29:33.537940500 BACKGROUND THREAD 2012-08-28 16:29:33.537941500 ----------------- 2012-08-28 16:29:33.537942500 srv_master_thread loops: 7319 1_second, 7304 sleeps, 705 10_second, 378 background, 378 flush 2012-08-28 16:29:33.537976500 srv_master_thread log flush and writes: 8093 2012-08-28 16:29:33.537977500 ---------- 2012-08-28 16:29:33.537978500 SEMAPHORES 2012-08-28 16:29:33.537979500 ---------- 2012-08-28 16:29:33.537980500 OS WAIT ARRAY INFO: reservation count 38142, signal count 38077 2012-08-28 16:29:33.537981500 --Thread 47404182935312 has waited at buf0buf.c line 3667 for 249.00 seconds the semaphore: 2012-08-28 16:29:33.537982500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:29:33.537988500 waiters flag 1 2012-08-28 16:29:33.537989500 --Thread 47404177839888 has waited at buf0flu.c line 1887 for 242.00 seconds the semaphore: 2012-08-28 16:29:33.537990500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:29:33.537991500 waiters flag 1 2012-08-28 16:29:33.537992500 Mutex spin waits 41706, rounds 1214545, OS waits 36676 2012-08-28 16:29:33.537993500 RW-shared spins 1167, rounds 33805, OS waits 1028 2012-08-28 16:29:33.537994500 RW-excl spins 38, rounds 4457, OS waits 125 2012-08-28 16:29:33.537999500 Spin rounds per wait: 29.12 mutex, 28.97 RW-shared, 117.29 RW-excl 2012-08-28 16:29:33.538000500 -------- 2012-08-28 16:29:33.538001500 FILE I/O 2012-08-28 16:29:33.538001500 -------- 2012-08-28 16:29:33.538002500 I/O thread 0 state: waiting for completed aio requests (insert buffer thread) 2012-08-28 16:29:33.538011500 I/O thread 1 state: waiting for completed aio requests (log thread) 2012-08-28 16:29:33.538013500 I/O thread 2 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538014500 I/O thread 3 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538015500 I/O thread 4 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538016500 I/O thread 5 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538021500 I/O thread 6 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538022500 I/O thread 7 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538023500 I/O thread 8 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538024500 I/O thread 9 state: waiting for completed aio requests (read thread) 2012-08-28 16:29:33.538026500 I/O thread 10 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538030500 I/O thread 11 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538031500 I/O thread 12 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538033500 I/O thread 13 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538034500 I/O thread 14 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538042500 I/O thread 15 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538043500 I/O thread 16 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538044500 I/O thread 17 state: waiting for completed aio requests (write thread) 2012-08-28 16:29:33.538046500 Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] , 2012-08-28 16:29:33.538047500 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 2012-08-28 16:29:33.538101500 Pending flushes (fsync) log: 0; buffer pool: 0 2012-08-28 16:29:33.538103500 4397 OS file reads, 103773 OS file writes, 14107 OS fsyncs 2012-08-28 16:29:33.538104500 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s 2012-08-28 16:29:33.538105500 ------------------------------------- 2012-08-28 16:29:33.538106500 INSERT BUFFER AND ADAPTIVE HASH INDEX 2012-08-28 16:29:33.538107500 ------------------------------------- 2012-08-28 16:29:33.538108500 Ibuf: size 1, free list len 14, seg size 16, 760 merges 2012-08-28 16:29:33.538113500 merged operations: 2012-08-28 16:29:33.538114500 insert 830, delete mark 135, delete 13 2012-08-28 16:29:33.538115500 discarded operations: 2012-08-28 16:29:33.538116500 insert 0, delete mark 0, delete 0 2012-08-28 16:29:33.538117500 Hash table size 6375023, node heap has 11 buffer(s) 2012-08-28 16:29:33.538118500 0.00 hash searches/s, 0.00 non-hash searches/s 2012-08-28 16:29:33.538119500 --- 2012-08-28 16:29:33.538120500 LOG 2012-08-28 16:29:33.538124500 --- 2012-08-28 16:29:33.538125500 Log sequence number 833668872575 2012-08-28 16:29:33.538126500 Log flushed up to 833668872575 2012-08-28 16:29:33.538127500 Last checkpoint at 833636596629 2012-08-28 16:29:33.538136500 Max checkpoint age 434154333 2012-08-28 16:29:33.538138500 Checkpoint age target 420587011 2012-08-28 16:29:33.538138500 Modified age 32275946 2012-08-28 16:29:33.538139500 Checkpoint age 32275946 2012-08-28 16:29:33.538140500 0 pending log writes, 0 pending chkp writes 2012-08-28 16:29:33.538141500 26775 log i/o's done, 0.00 log i/o's/second 2012-08-28 16:29:33.538142500 ---------------------- 2012-08-28 16:29:33.538143500 BUFFER POOL AND MEMORY 2012-08-28 16:29:33.538148500 ---------------------- 2012-08-28 16:29:33.538149500 Total memory allocated 3309305856; in additional pool allocated 0 2012-08-28 16:29:33.538267500 Internal hash tables (constant factor + variable factor) 2012-08-28 16:29:33.538268500 Adaptive hash index 51184624 (51000184 + 184440) 2012-08-28 16:29:33.538269500 Page hash 3188312 (buffer pool 0 only) 2012-08-28 16:29:33.538271500 Dictionary cache 12925928 (12751472 + 174456) 2012-08-28 16:29:33.538272500 File system 111272 (82672 + 28600) 2012-08-28 16:29:33.538273500 Lock system 7969872 (7969496 + 376) 2012-08-28 16:29:33.538274500 Recovery system 0 (0 + 0) 2012-08-28 16:29:33.538281500 Dictionary memory allocated 174456 2012-08-28 16:29:55.035056500 InnoDB: ###### Diagnostic info printed to the standard error stream 2012-08-28 16:29:55.035811500 InnoDB: Warning: a long semaphore wait: 2012-08-28 16:29:55.035812500 --Thread 47404182935312 has waited at buf0buf.c line 3667 for 271.00 seconds the semaphore: 2012-08-28 16:29:55.035814500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:29:55.035815500 waiters flag 1 2012-08-28 16:29:55.035816500 InnoDB: Warning: a long semaphore wait: 2012-08-28 16:29:55.035817500 --Thread 47404177839888 has waited at buf0flu.c line 1887 for 264.00 seconds the semaphore: 2012-08-28 16:29:55.035818500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:29:55.035828500 waiters flag 1 2012-08-28 16:29:55.035829500 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: 2012-08-28 16:29:55.035830500 InnoDB: Pending preads 0, pwrites 0 2012-08-28 16:30:25.065907500 InnoDB: ###### Diagnostic info printed to the standard error stream 2012-08-28 16:30:25.066807500 InnoDB: Warning: a long semaphore wait: 2012-08-28 16:30:25.066808500 --Thread 47404182935312 has waited at buf0buf.c line 3667 for 301.00 seconds the semaphore: 2012-08-28 16:30:25.066809500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:30:25.066810500 waiters flag 1 2012-08-28 16:30:25.066811500 InnoDB: Warning: a long semaphore wait: 2012-08-28 16:30:25.066812500 --Thread 47404177839888 has waited at buf0flu.c line 1887 for 294.00 seconds the semaphore: 2012-08-28 16:30:25.066814500 Mutex at 0x2577c78 '&buf_pool->mutex', lock var 1 2012-08-28 16:30:25.066871500 waiters flag 1 2012-08-28 16:30:25.066872500 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: 2012-08-28 16:30:25.066873500 InnoDB: Pending preads 0, pwrites 0 2012-08-28 16:30:55.096914500 InnoDB: ###### Diagnostic info printed to the standard error stream