SEMAPHORES - S-lock & X-lock on RW-latch

MySQL & MariaDB Percona Server for MySQL 5.6
1

I can see many types of contention in the show engine innodb status\G

Its increasing load on the server , CPU utilization was High and High I/O noticed.

Can see below contentions in error logs.

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 197682819
–Thread 139909836908288 has waited at btr0cur.cc line 591 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f3eb0029fa0 ‘&new_index->lock’
a writer (thread id 139911101409024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.cc line 591
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.39-83.1/storage/innobase/btr/btr0cur.cc line 582

OS WAIT ARRAY INFO: reservation count 197784171
–Thread 139909319145216 has waited at btr0sea.cc line 1494 for 0.0000 seconds the semaphore:
X-lock on RW-latch at 0x23d9658 ‘&btr_search_latch_arr[i]’
a writer (thread id 139909794551552) has reserved it in mode exclusive
number of readers 0, waiters flag 0, lock_word: 0
Last time read locked in file btr0sea.cc line 954
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.39-83.1/storage/innobase/btr/btr0sea.cc line 1494

OS WAIT ARRAY INFO: reservation count 202249793
–Thread 139909786564352 has waited at btr0pcur.cc line 467 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f41132df320 ‘&block->lock’
a writer (thread id 139909862520576) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file row0sel.cc line 3346
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.39-83.1/storage/innobase/row/row0row.cc line 823

–Thread 139909849413376 has waited at row0sel.cc line 3346 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f41132df320 ‘&block->lock’
a writer (thread id 139909862520576) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file row0sel.cc line 3346
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.39-83.1/storage/innobase/row/row0row.cc line 823

–Thread 139909836089088 has waited at row0sel.cc line 3346 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f406d31ed20 ‘&block->lock’
number of readers 1, waiters flag 0, lock_word: fffff
Last time read locked in file row0sel.cc line 3346
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.39-83.1/storage/innobase/row/row0ins.cc line 2718

–Thread 139909854123776 has waited at buf0buf.cc line 3303 for 0.0000 seconds the semaphore:
Mutex at 0x7f40a4ad8840 ‘&block->mutex’, lock var 0
waiters flag 0
–Thread 139909777962752 has waited at btr0sea.cc line 1380 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x23d9658 ‘&btr_search_latch_arr[i]’
number of readers 4, waiters flag 0, lock_word: ffffc
Last time read locked in file btr0sea.cc line 954

–Thread 139909845509888 has waited at btr0pcur.cc line 467 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f41132df320 ‘&block->lock’
a writer (thread id 139909862520576) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file row0sel.cc line 3346
Last time write locked in file /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.39-83.1/storage/innobase/row/row0row.cc line 823

Mysql version is 5.6.39-83.1

Do i need to set innodb_thread_concurrency to some value,
currently its innodb_thread_concurrency =0, innodb_buffer_pool_instances =1, innodb_read_io_threads=64, innodb_write_io_threads=64, innodb_adaptive_hash_index=ON.

All tables are innodb .

Buffer pool size is 80GB.

Total memory on the servers is 125GB and 8 core’s CPU

2

Hi,

Do I need to adjust below parameters ?

±--------------------------±------+
| Variable_name | Value |
±--------------------------±------+
| innodb_thread_concurrency | 0 |
±--------------------------±------+

±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| innodb_read_io_threads | 64 |
±-----------------------±------+

±------------------------±------+
| Variable_name | Value |
±------------------------±------+
| innodb_write_io_threads | 64 |
±------------------------±------+

±--------------------------------------±------+
| Variable_name | Value |
±--------------------------------------±------+
| innodb_adaptive_hash_index | ON |
±--------------------------------------±------+

±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| innodb_buffer_pool_instances | 1 |
±-----------------------------±------+

Looking for expert opinion, Any help will be appreciated.

Thanks,
Pooja.

3

i also encountered the same problem in mysql 5.6.40 . 
Looking for expert opinion, Any help will be appreciated. +1 

4

Hi @bowenzhuang and @“pooja.gupta”

Check hash and non-hash search in innodb engine output. If its ok to test in your environment to disable innodb_adaptive_hash_index, please do so and monitor for the performance and for the above issue.

@“pooja.gupta”

apart from this, i would recommend to split the buffer pool into multiple instances. 
Hope this helps.