Red Hat Bugzilla – Bug 1341338
slow SQL query: UPDATE "architectures" SET "hosts_count" = COALESCE("hosts_count", 0) + 1 WHERE "architectures"."id" = 1;
Last modified: 2017-06-01 07:40:25 EDT
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
Connecting redmine issue http://projects.theforeman.org/issues/12193 from this bug
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.
Upstream bug component is Provisioning
*** Bug 1344709 has been marked as a duplicate of this bug. ***
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.
Moving to POST since upstream bug http://projects.theforeman.org/issues/12193 has been closed
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.
Created attachment 1230456 [details] content host page load content host registration logs http://pastebin.test.redhat.com/438183
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
and now with the right pastebin link! http://pastebin.test.redhat.com/438203
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
*** Bug 1408299 has been marked as a duplicate of this bug. ***
*** Bug 1455464 has been marked as a duplicate of this bug. ***