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. ?
The server upgrade by itself should not cause the problem, though the increase in gunicorn workers may be a contributing factor. If you are using a stock MariaDB setup this link came up when Googling the error message:
@smino for the response.
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?
Any suggestion on this?
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.
@smino. Also have you done any additional indexes on the fields to improve the performance?
Have raised an specific query on the same. Any inputs on these will be really helpful.
The System begins to slow down when the DB has huge number of transactions records(Stock ledger, stock entry,Delivery Note etc). We see from the Slow Query Log that many of the searches are done based on the unindexed fields.
Below are few examples
against_sales_order, against_sales_invoice in delivery note item table
quality_inspection in stock entry detail table
reference_doctype, reference_docname in workflow action table
Indexing these fields will improve the select query performa…