Semaphore locks causing stalls

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?