Bug #10053: Table lock hangs all processes - Foreman

Actions

Copy link

Bug #10053

closed

Table lock hangs all processes

Added by Jason Hane about 10 years ago. Updated over 6 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Marek Hulán

Category:

Database

Target version:

1.12.0

Difficulty:

Triaged:

Bugzilla link:

1324450

Pull request:

https://github.com/theforeman/foreman/pull/3392

Fixed in Releases:

Found in Releases:

1.7.3

Red Hat JIRA:

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.

Related issues 2 (0 open — 2 closed)

Updated by Jason Hane about 10 years ago

Here is the explain plan.

+------+-------------+----------------------+--------+-----------------------------+--------------------------+---------+-------------------------------------------+------+----------+-----------------------------------------------------------+
| id   | select_type | table                | type   | possible_keys               | key                      | key_len | ref                                       | rows | filtered | Extra                                                     |
+------+-------------+----------------------+--------+-----------------------------+--------------------------+---------+-------------------------------------------+------+----------+-----------------------------------------------------------+
|    1 | PRIMARY     | hosts                | ref    | PRIMARY,index_hosts_on_type | index_hosts_on_type      | 258     | const                                     |  371 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|    1 | PRIMARY     | reports              | ref    | index_reports_on_host_id    | index_reports_on_host_id | 4       | foreman.hosts.id                          | 1075 |   100.00 | Using where                                               |
|    1 | PRIMARY     | hosts                | eq_ref | PRIMARY,index_hosts_on_type | PRIMARY                  | 4       | foreman.hosts.id                          |    1 |   100.00 | Using where; Distinct                                     |
|    1 | PRIMARY     | hosts_reports_join   | eq_ref | PRIMARY,index_hosts_on_type | PRIMARY                  | 4       | foreman.hosts.id                          |    1 |   100.00 | Using where; Distinct                                     |
|    1 | PRIMARY     | environments         | eq_ref | PRIMARY                     | PRIMARY                  | 4       | foreman.hosts_reports_join.environment_id |    1 |   100.00 | Using where; Distinct                                     |
|    1 | PRIMARY     | logs                 | ref    | index_logs_on_report_id     | index_logs_on_report_id  | 5       | foreman.reports.id                        |   19 |   100.00 | Distinct                                                  |
|    1 | PRIMARY     | messages             | eq_ref | PRIMARY                     | PRIMARY                  | 4       | foreman.logs.message_id                   |    1 |   100.00 | Using where; Distinct                                     |
|    1 | PRIMARY     | logs_reports_join    | ref    | index_logs_on_report_id     | index_logs_on_report_id  | 5       | foreman.reports.id                        |   19 |   100.00 | Distinct                                                  |
|    1 | PRIMARY     | sources              | eq_ref | PRIMARY                     | PRIMARY                  | 4       | foreman.logs_reports_join.source_id       |    1 |   100.00 | Using where; Distinct                                     |
|    1 | PRIMARY     | hosts_reports_join_2 | eq_ref | PRIMARY,index_hosts_on_type | PRIMARY                  | 4       | foreman.hosts.id                          |    1 |   100.00 | Using where; Distinct                                     |
|    1 | PRIMARY     | hostgroups           | eq_ref | PRIMARY                     | PRIMARY                  | 4       | foreman.hosts_reports_join_2.hostgroup_id |    1 |   100.00 | Using where; Distinct                                     |
+------+-------------+----------------------+--------+-----------------------------+--------------------------+---------+-------------------------------------------+------+----------+-----------------------------------------------------------+

Updated by Marek Hulán about 9 years ago

Status changed from New to Assigned
Assignee set to Marek Hulán

Updated by The Foreman Bot about 9 years ago

Status changed from Assigned to Ready For Testing
Pull request https://github.com/theforeman/foreman/pull/3392 added

Updated by Marek Hulán about 9 years ago

Bugzilla link set to 1324450

Updated by Dominic Cleal about 9 years ago

Has duplicate Bug #2778: Search in /reports without keyword make request never end on mysql added

Updated by Ivan Necas about 9 years ago

Related to Bug #14503: Listing reports for a host via API/hammer consumes large amount of memory added

Updated by Marek Hulán about 9 years ago

Status changed from Ready For Testing to Closed
% Done changed from 0 to 100

#10

Updated by Dominic Cleal about 9 years ago

Translation missing: en.field_release set to 136

Actions

Copy link

Also available in: Atom PDF

	Related to Foreman - Bug #14503: Listing reports for a host via API/hammer consumes large amount of memory	Closed	Ivan Necas	04/06/2016			Actions
	Has duplicate Foreman - Bug #2778: Search in /reports without keyword make request never end on mysql	Duplicate		07/12/2013			Actions

Project

General

Custom queries

Profile

Bug #10053

Table lock hangs all processes

Updated by Jason Hane about 10 years ago

Updated by Aaron Stone almost 10 years ago

Updated by Dominic Cleal almost 10 years ago

Updated by Marek Hulán about 9 years ago

Updated by The Foreman Bot about 9 years ago

Updated by Marek Hulán about 9 years ago

Updated by Dominic Cleal about 9 years ago

Updated by Ivan Necas about 9 years ago

Updated by Marek Hulán about 9 years ago

Updated by Dominic Cleal about 9 years ago