Not the answer you need?
Register and ask your own question!

Semaphore locks causing stalls

debian.dbadebian.dba EntrantCurrent User Role Beginner
Hi all,

I use 5.7.16-10-log Percona Server on Ubuntu 16.04.1 LTS with a m4.2xlarge ec2 instance type on AWS.

For the past few months, I've been facing an issue with semaphore waits which cause stalls for around a minute. This happens only in the 2nd minute of every hour between 10 AM to 3 PM.

The
mysql> show processlist
during this time reveals a huge list of queries, roughly between 200-300, waiting during this entire minute.

Pastebin link for the complete output of
mysql> SHOW ENGINE INNODB STATUS\G
is https://pastebin.com/ivehDxeT.
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 1779463 --Thread 140348635367168 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 0, waiters flag 0, lock_word: 20000000 Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 ..... --Thread 140349785003776 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348636964608 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 0, waiters flag 0, lock_word: 20000000 Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 wait has ended --Thread 140348634302208 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348625782528 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348996093696 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 3, waiters flag 0, lock_word: 1ffffffd Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348626315008 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348630841088 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 2, waiters flag 0, lock_word: 1ffffffe Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348624185088 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140348621788928 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 2, waiters flag 0, lock_word: 1ffffffe Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140349784205056 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 4, waiters flag 0, lock_word: 1ffffffc Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140349793257216 has waited at btr0cur.cc line 3944 for 0.00 seconds the semaphore: X-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140349798848256 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 0, waiters flag 0, lock_word: 20000000 Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140349007808256 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 0, waiters flag 0, lock_word: 20000000 Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140349017659136 has waited at btr0sea.ic line 128 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 1, waiters flag 0, lock_word: 1fffffff Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 --Thread 140349004613376 has waited at btr0sea.ic line 90 for 0.00 seconds the semaphore: X-lock on RW-latch at 0x36b2ca8 created in file btr0sea.cc line 230 number of readers 2, waiters flag 0, lock_word: 1ffffffe Last time read locked in file btr0sea.ic line 128 Last time write locked in file /mnt/workspace/percona-server-5.7-debian-binary/label_exp/ubuntu-xenial-64bit/percona-server-5.7-5.7.16-10/storage/innobase/include/btr0sea.ic line 90 ..... OS WAIT ARRAY INFO: signal count 80055845 RW-shared spins 0, rounds 19163955, OS waits 975752 RW-excl spins 0, rounds 47087597, OS waits 559240 RW-sx spins 600569, rounds 10520166, OS waits 115854 Spin rounds per wait: 19163955.00 RW-shared, 47087597.00 RW-excl, 17.52 RW-sx .....
I've changed the table names in the latest deadlock section and snipped the actual query.

Is there any way to identify the actual query which is causing these waits?
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.