Description:
we have two servers with same mysql configuration and both are used for loading same set of data daily, while loading data and creating indexes on table it is taking more time on one server when compared to other.
When i run show engine innodb status it is showing SEMAPHORES section is showing some '&block->lock' messages with different file names. Below is the one of those message and also show engine innodb mutex is showing high count for below parameter and this is increasing rapidly. when i compared per day increase between the servers one server it is just 2000 where as on the other it is almost 96000.
InnoDB | combined &block->lock | os_waits=49103838
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 106755039
--Thread 140091819861760 has waited at fsp0fsp.cc line 200 for 0.0000 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f6aff98a060 '&block->lock'
a writer (thread id 140091819861760) has reserved it in mode wait exclusive
number of readers 1, waiters flag 0, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.cc line 1080
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos6-64/rpmbuild/BUILD/percona-server-5.6.32-78.0/storage/innobase/fsp/fsp0fsp.cc line 200
--Thread 140093430429440 has waited at buf0flu.cc line 1080 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f6b78ff45e0 '&block->lock'
a writer (thread id 140091819861760) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: fffffffffff00000
Last time read locked in file buf0flu.cc line 1080
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos6-64/rpmbuild/BUILD/percona-server-5.6.32-78.0/storage/innobase/btr/btr0btr.cc line 741
OS WAIT ARRAY INFO: signal count 367100945
Mutex spin waits 1219118276, rounds 2534943376, OS waits 34170583
RW-shared spins 102111526, rounds 860712250, OS waits 26958075
RW-excl spins 96063321, rounds 1754951129, OS waits 44891862
Spin rounds per wait: 2.08 mutex, 8.43 RW-shared, 18.27 RW-excl
------------
How to repeat:
we are seeing this messages daily whenever we load data into the tables.
