Database is slow
I have two identical servers running mysql 5.6.32-78.0. Daily on both servers, few tables are truncated and loaded with data and primary keys added. one of the server is taking more time than the other for this data load operations.
When i ran show engine innodb status i'm seeing few semaphore errors like below, then i ran SHOW ENGINE INNODB MUTEX; and seeing huge difference for combined &block->lock variable when compared between both the servers. on problematic server this counter is increasing at a rapid rate. I want to understand what this variable is about and how can i use performance schema to troubleshoot this kind of issue. I'm guessing there are some OS disk I/O waits or locks which is causing the delay, but how can i prove that.
InnoDB | combined &block->lock | os_waits=1217753
show engine innodb status semaphore output
OS WAIT ARRAY INFO: reservation count 105641200
--Thread 140084339894016 has waited at fsp0fsp.cc line 200 for 0.0000 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f6b5550ede0 '&block->lock'
a writer (thread id 140084339894016) 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 1074
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
OS WAIT ARRAY INFO: signal count 363770929
Mutex spin waits 1211196930, rounds 2515491899, OS waits 33837726
RW-shared spins 101273440, rounds 851587378, OS waits 26666899
RW-excl spins 95271109, rounds 1736866660, OS waits 44407688
Spin rounds per wait: 2.08 mutex, 8.41 RW-shared, 18.23 RW-excl
Database is slow
August 11, 2017 08:31AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.