To improve performance, we recently upgraded the server from 2 core-8GB to 4 core-16GB. we increased the gunicorn workers from 5 to 9. Our App Server and DB Server are in the same EC2 Instance.
Post Upgrade, we are getting to see frequent locks in database and users are not able to complete the transaction.
Any guidance on how to approach this issues?
Any temporary fix or method to clear the deadlocks in DB so that user can continue. ?
We see that in mariadb innodb_autoinc_lock_mode parameter is set to Consecutive Lock Mode. In this mode for bulk insert/update table level lock will occur.
We are suspecting that table level lock might be happening resulting in Lock Timeout.
Can we change the innodb_autoinc_lock_mode to Interleaved Lock Mode?
Will it improve the db performance? Will it have any implications?
I have not tried to modify database parameters and cannot say if it will solve the problem. Before trying that approach , I might reduce the gunicorn workers to 5 to see if that was the cause of the problem. I will defer to someone with knowledge of database settings for advice on those.