Stop using /owners/<owner>/info for sub compliance dashboard widget
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';
Updated by Justin Sherrill over 6 years ago
- Subject changed from /owners/<owner>/info can be slow (1-2 seconds) when large number of systems are registered to Stop using /owners/<owner>/info for sub compliance dashboard widget
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.