Hi Guys,
Today i ran into a weird issue on Percona 5.6 after successfully running for past 6months. All my Select, Commit, Insert was in Hung State and it keept on pilling up till the server crashed. I fortunately got the SHOW ENGINE INNODB STATUS and saw some mutex errors.
OS WAIT ARRAY INFO: reservation count 4487167
--Thread 47584657541440 has waited at dict0boot.ic line 36 for 48.000 seconds the semaphore:
Mutex at 0x2b41067fbae8 '&dict_sys->mutex', lock var 1
waiters flag 1
--Thread 47585826445632 has waited at buf0buf.cc line 2560 for 34.000 seconds the semaphore:
S-lock on RW-latch at 0x2b44b2f23e40 '&block->lock'
a writer (thread id 47584652749120) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.cc line 3051
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos5-64/rpmbuild/BUILD/percona-server-5.6.21-70.1/storage/innobase/buf/buf0buf.cc line 3728
OS WAIT ARRAY INFO: signal count 5808535
Mutex spin waits 17056534, rounds 52170104, OS waits 839521
RW-shared spins 4714980, rounds 109885979, OS waits 3459657
RW-excl spins 1394826, rounds 17190451, OS waits 162118
Spin rounds per wait: 3.06 mutex, 23.31 RW-shared, 12.32 RW-excl
Please help me to understand what went wrong. Since neither mysql/system error log showed any clue.
Thanks
Also from FILE IO i See there was Pending 1 Fsync request which was blocked:
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 5 [0, 1, 2, 0, 0, 1, 0, 1] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
102327307 OS file reads, 112622662 OS file writes, 15459988 OS fsyncs
33 pending preads, 0 pending pwrites
0.33 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
Any help will be appreciated.
I’m not convinced there are any “errors” in that listing.
Where to you see “Hung”? In the PROCESSLIST?
Let’s see some of the queries that piled up, together with SHOW CREATE TABLE.