MySQL locks and Redmine collapsing
Our heavily used Redmine often dies during update or create a new issue:
ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction: SELECT `issues`.* FROM `issues` WHERE `issues`.`root_id` IS NULL ORDER BY `rgt` desc LIMIT 1 FOR UPDATE
Ruby 1.8.7, MySql 5.5.4, Redmine 2.0.3, OS FreeBSD, WebServer is Passenger. Every 5 minutes is running rake redmine:email:receive_pop3.
No errors in production.log.
It is imposible to simulate this behaviour, but it occurs if more than 5-10 peoples are working. We had no problems on older Redmine < 2.0.
#1 Updated by Etienne Massip over 9 years ago
- Category set to Database
Is there a way you could give more information about the existing blocking transaction when the problem occurs?
I'm not a MySQL expert, maybe http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html#innodb-standard-monitor could be of some help (for InnoDB).
#4 Updated by Etienne Massip over 9 years ago
Petr Pospisil wrote:
I should you provide more information, but i dont know which...
Told you, blocking transactions. Because the SQL you've been writing down here is the blocked one, not the blocking one.
Try to execute a
SHOW ENGINE INNODB STATUS\G as soon as the issue occurs again?
#5 Updated by Toshi MARUYAMA over 9 years ago
Try increase max pool size.
#8 Updated by Etienne Massip over 9 years ago
You can identify long running requests:
- enable request logging and use request-log-analyzer
- set a value to
config.active_record.auto_explain_threshold_in_secondsparameter in your
config/environment/production.rbso that they automatically get an explain plan in log
BTW, by default there should not be such "FOR UPDATE" part at the end of statement unless specified in code and there's no such thing.
Could that be a tuned parameter or a plugin behavior?
#14 Updated by @ go2null over 6 years ago
related to Defect #8143 Problem to update subtask.
I am also having the same issue - it is caused with large parent-child hierarchies and the consequent cascading updates of status, priority and other checks.
Environment: Redmine version 2.6.0.stable Ruby version 1.9.3-p484 (2013-11-22) [x86_64-linux] Rails version 3.2.19 Environment production Database adapter Mysql2