Note: This bug is displayed in read-only format because
the product is no longer active in Red Hat Bugzilla.
Red Hat Satellite engineering is moving the tracking of its product development work on Satellite to Red Hat Jira (issues.redhat.com). If you're a Red Hat customer, please continue to file support cases via the Red Hat customer portal. If you're not, please head to the "Satellite project" in Red Hat Jira and file new tickets here. Individual Bugzilla bugs will be migrated starting at the end of May. If you cannot log in to RH Jira, please consult article #7032570. That failing, please send an e-mail to the RH Jira admins at rh-issues@redhat.com to troubleshoot your issue as a user management inquiry. The email creates a ServiceNow ticket with Red Hat. Individual Bugzilla bugs that are migrated will be moved to status "CLOSED", resolution "MIGRATED", and set with "MigratedToJIRA" in "Keywords". The link to the successor Jira issue will be found under "Links", have a little "two-footprint" icon next to it, and direct you to the "Satellite project" in Red Hat Jira (issue links are of type "https://issues.redhat.com/browse/SAT-XXXX", where "X" is a digit). This same link will be available in a blue banner at the top of the page informing you that that bug has been migrated.
Description of problem:
There is slow SQL query: UPDATE "architectures" SET "hosts_count" = COALESCE("hosts_count", 0) + 1 WHERE "architectures"."id" = 1;
Version-Release number of selected component (if applicable):
satellite-6.2.0-13.0.el7sat.noarch
How reproducible:
sometimes
Steps to Reproduce:
1. Observed on Sat with ~1100 hosts registered with katello-agent/gofferd running (each have 3 tzdata erratas applicable&installable)
Actual results:
This query pops up into the logs from time to time
Expected results:
Should be quick
Additional info:
Extreme case from logs (not this bad every time):
==> /var/lib/pgsql/data/pg_log/postgresql-Tue.log <==
2016-05-31 16:36:01 EDT LOG: duration: 334153.512 ms execute <unnamed>: UPDATE "architectures" SET "hosts_count" = COALESCE("hosts_count", 0) + 1 WHERE "architectures"."id" = 1
This is an issue caused by rails updating the counter_cache inside the same transaction as the host creation/update. When multiple hosts are updated at the same time, this may cause locks when two hosts attempt to update the same column at the same time. There may be some tuning on the database level that can be done to reduce these locks, but I'm not sure as to precisely which.
Another possibility is to remove these cached counters altogether which will cause slowness on pages that use the counts as each page view will cause several count queries, but may be still within reasonable.
WORKAROUND:
cat >>/etc/httpd/conf.d/passenger.conf <<EOPS
# Allow only one Foreman and one Puppet Master process
<IfModule mod_passenger.c>
PassengerMaxPoolSize 2
</IfModule>
EOPS
This limits number of processes one each, this will slow down processing of requests tho.
I've currently got a Satellite with xxx content host records, 1100 of which are active with approximately 150 applicable errata each.
The content host page loads slightly slower than usual, but not to the point where it is an issue. New content host registration takes roughly the same time. I will attach a screenshot of the content host page with network timeline as well as the log activity during new content host registration.
Verified in Satellite 6.2.5 Snap 3
Based on the system in comment #25. Enabled debugging mode for postgres, then registered a new host to the system. The captured output does not contain the query outlined in comment #27. Also, the exact query did not show up when editing the architecture of a host. Finally, there was no deviation in performance from what I outlined in #25.
captured logs during host registration
http://pastebin.test.redhat.com/438201
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:2940
Description of problem: There is slow SQL query: UPDATE "architectures" SET "hosts_count" = COALESCE("hosts_count", 0) + 1 WHERE "architectures"."id" = 1; Version-Release number of selected component (if applicable): satellite-6.2.0-13.0.el7sat.noarch How reproducible: sometimes Steps to Reproduce: 1. Observed on Sat with ~1100 hosts registered with katello-agent/gofferd running (each have 3 tzdata erratas applicable&installable) Actual results: This query pops up into the logs from time to time Expected results: Should be quick Additional info: Extreme case from logs (not this bad every time): ==> /var/lib/pgsql/data/pg_log/postgresql-Tue.log <== 2016-05-31 16:36:01 EDT LOG: duration: 334153.512 ms execute <unnamed>: UPDATE "architectures" SET "hosts_count" = COALESCE("hosts_count", 0) + 1 WHERE "architectures"."id" = 1