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

pooja.guptapooja.gupta EntrantCurrent User Role Participant
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'
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'
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

Comments

  • pooja.guptapooja.gupta Entrant Current User Role Participant
    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.
  • bowenzhuangbowenzhuang Current User Role Participant
    i also encountered the same problem in mysql 5.6.40 . 
    Looking for expert opinion, Any help will be appreciated. +1 

  • vaibhav_upadhyay40vaibhav_upadhyay40 Contributor Current User Role Mentor
    edited July 1
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.