Our application logs are showing an excessive amount of lock wait times, and deadlocks for queries that have not had issues previously after upgrading from 5.6 to 5.7 MySQL in AWS Aurora. Are there any settings that I can adjust to reduce the deadlocks and lock wait timeouts?
Variables and settings here: https://pastebin.com/cmu11Dwh
Running on db.r5.12xlarge
We had the same issue after we upgraded to Aurora 2.11.1. The issue is mainly due to a bug fix implemented on 2.11.0. You have to contact the AWS support and raise this concern and they will apply the fix internally by disabling a particular parameter from hardware level.
Please refer below for more information :
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.2110.html
A new dynamic configuration option, innodb_deadlock_detect, may be used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. (Bug #23477773) More information on Innodb deadlock detection can be found in the MySQL documentation.
