Description of problem:
With candlepin 0.9.54, the following query pops up as taking over 1000msec:
select count(this_.id) as y0_ from cp_consumer this_ inner join cp_consumer_facts f1_ on this_.id=f1_.cp_consumer_id where this_.owner_id=$1 and f1_.mapkey ilike $2 and f1_.element ilike $3
parameters: $1 = '8a98d3fd568e316701568e31cd1d0001', $2 = 'virt.is_guest', $3 = 'true'
This was on a DB with 20K systems in a single org.
It looks like this is happening around https://github.com/candlepin/candlepin/blob/master/server/src/main/java/org/candlepin/model/OwnerInfoCurator.java#L114-L119.
this is called by the katello dashboard which is updated every 5 seconds, so if the query can be improved, it would reduce postgres load and make the dashboard faster.
I don't have a specific hard target for this, but 100msec or less would be great.
Version-Release number of selected component (if applicable): 0.9.54
I added the following index which reduced the time to 33% of the previous time, but it's still > 500 msec:
create index fact_idx_1 on cp_consumer_facts(mapkey) where mapkey ilike 'virt.is_guest';
Is this a general RFE that the call to get ownerinfo take less than 100 msec?
If the above statement is true, can you provide the sample data & configuration under which the call should take 100 msec so we can work that onto the backlog?
100-200msec for the /info call would be great.
I can supply a test DB to use.
My suggestion would be to see what information the dashboard is relying on from the call to candlepin. If this is info that we can perhaps store in our database (and keep up to date w/ candlepin events, if needed).
Based on a quick discussion with Justin, this is only using the Consumer Count and the breakdown of consumer counts by compliance status. As I understand it that information could already be queried from the Katello database instead of asking Candlepin.
Chris, would that be feasible with the other work you are doing?
Ya, we can swap it over. I'll leave this BZ as needinfo on me until I create a katello redmine.
I'm moving this to Dashboard as the work to remove the candlepin dependency will be done there.
Created redmine issue http://projects.theforeman.org/issues/19505 from this bug
Thank you for your interest in Satellite 6. We have evaluated this request, and we do not expect this to be implemented in the product in the foreseeable future. We are therefore closing this out as WONTFIX. If you have any concerns about this, please feel free to contact Rich Jerrido or Bryan Kearney. Thank you.