Bug #87302 Data load and index creation taking time
Submitted: 3 Aug 2017 12:23 Modified: 4 Aug 2017 10:29
Reporter: Jaya Karthik Reddy Karri Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.32 OS:CentOS (2.6.32-279.el6.x86_64)
Assigned to: CPU Architecture:Any
Tags: Semaphore

[3 Aug 2017 12:23] Jaya Karthik Reddy Karri
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

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.
[4 Aug 2017 10:29] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.