Hide Forgot
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. <pre> 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 </pre> These are the last entries in the production.log. <pre> 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. </pre>
Created from redmine issue http://projects.theforeman.org/issues/10053
Upstream bug assigned to mhulan
Moving to POST since upstream bug http://projects.theforeman.org/issues/10053 has been closed ------------- Marek Hulán Applied in changeset commit:2f1cc0f4b8e8d320e8eef309b1ab7c834b201dce.
VERIFIED on sat6.2 snap19.1 The 2 fields are now excluded from search until explicitly included: # hammer report list --search='log~"Triggered"' [Foreman] Password for admin: ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- ID | HOST | LAST REPORT | APPLIED | RESTARTED | FAILED | RESTART FAILURES | SKIPPED | PENDING ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- 11 | my-host-1 | 2016/07/11 12:56:33 | 24 | 26 | 0 | 0 | 0 | 0 ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- [root var]# hammer report list --search='"Triggered"' [Foreman] Password for admin: ---|------|-------------|---------|-----------|--------|------------------|---------|-------- ID | HOST | LAST REPORT | APPLIED | RESTARTED | FAILED | RESTART FAILURES | SKIPPED | PENDING ---|------|-------------|---------|-----------|--------|------------------|---------|-------- [root var]# hammer report list --search='resource~"Puppet"' [Foreman] Password for admin: ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- ID | HOST | LAST REPORT | APPLIED | RESTARTED | FAILED | RESTART FAILURES | SKIPPED | PENDING ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- 11 | my-host-1 | 2016/07/11 12:56:33 | 24 | 26 | 0 | 0 | 0 | 0 1 | my-host-1 | 2016/07/11 10:54:41 | 0 | 0 | 0 | 0 | 0 | 0 ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- [root var]# hammer report list --search='"Puppet"' [Foreman] Password for admin: ---|------|-------------|---------|-----------|--------|------------------|---------|-------- ID | HOST | LAST REPORT | APPLIED | RESTARTED | FAILED | RESTART FAILURES | SKIPPED | PENDING ---|------|-------------|---------|-----------|--------|------------------|---------|-------- [root var]# [root var]# hammer report list --search='pending=0' [Foreman] Password for admin: ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- ID | HOST | LAST REPORT | APPLIED | RESTARTED | FAILED | RESTART FAILURES | SKIPPED | PENDING ---|--------------------------------------------|---------------------|---------|-----------|--------|------------------|---------|-------- 62 | my-host-1 | 2016/07/12 13:54:41 | 0 | 0 | 0 | 0 | 0 | 0 61 | my-host-1 | 2016/07/12 13:54:24 | 0 | 0 | 0 | 0 | 0 | 0 60 | my-host-1 | 2016/07/12 13:24:41 | 0 | 0 | 0 | 0 | 0 | 0
Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory, and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. https://access.redhat.com/errata/RHBA-2016:1501