Actions
Bug #10053
closed
Table lock hangs all processes
Difficulty:
Triaged:
Bugzilla link:
Pull request:
Description
We have an issue that occurs frequently that causes all available Passenger threads to wait on the database. In the database there is one query that locks several tables causing a backlog of connections to hang rendering Foreman unresponsive. The database is MySQL.
This is the query causing the locks. It's been running for almost 2 hours.
SELECT DISTINCT `reports`.id FROM `reports`
LEFT OUTER JOIN `hosts` ON `hosts`.`id` = `reports`.`host_id` AND
`hosts`.`type` IN ('Host::Managed')
LEFT OUTER JOIN `hosts` `hosts_reports_join` ON `hosts_reports_join`.`id` = `reports`.`host_id` AND
`hosts_reports_join`.`type` IN ('Host::Managed')
LEFT OUTER JOIN `environments` ON `environments`.`id` = `hosts_reports_join`.`environment_id`
LEFT OUTER JOIN `logs` ON `logs`.`report_id` = `reports`.`id`
LEFT OUTER JOIN `messages` ON `messages`.`id` = `logs`.`message_id`
LEFT OUTER JOIN `logs` `logs_reports_join` ON `logs_reports_join`.`report_id` = `reports`.`id`
LEFT OUTER JOIN `sources` ON `sources`.`id` = `logs_reports_join`.`source_id`
LEFT OUTER JOIN `hosts` `hosts_reports_join_2` ON `hosts_reports_join_2`.`id` = `reports`.`host_id` AND
`hosts_reports_join_2`.`type` IN ('Host::Managed')
LEFT OUTER JOIN `hostgroups` ON `hostgroups`.`id` = `hosts_reports_join_2`.`hostgroup_id`
WHERE `reports`.`host_id` IN (
SELECT `hosts`.`id` FROM `hosts`
WHERE `hosts`.`type` IN ('Host::Managed')) AND
(((((`reports`.`status` >> 0 & 16777215) > 0)) AND
(`hosts`.`name` LIKE '%applied%' OR
`environments`.`name` LIKE '%applied%' OR
`messages`.`value` LIKE '%applied%' OR
`sources`.`value` LIKE '%applied%' OR
`hostgroups`.`name` LIKE '%applied%' OR
`hostgroups`.`title` LIKE '%applied%' OR
`hostgroups`.`title` LIKE '%applied%'))
)
ORDER BY `reports`.`reported_at` DESC LIMIT 150 OFFSET 0
These are the last entries in the production.log.
Started POST "/api/reports" for 10.48.184.55 at 2015-04-07 15:35:28 +0000
Processing by Api::V2::ReportsController#create as JSON
Parameters: {"report"=>"[FILTERED]", "apiv"=>"v2"}
processing report for lvsdevwsv02-01.us.gspt.net
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
Updated by 
Updated by 
Updated by 
Updated by 
Updated by 
Updated by
Actions