Bug 1554421

Summary:	candlepin takes >2m on /candlepin/consumers/UUID/guests query
Product:	Red Hat Satellite	Reporter:	Pavel Moravec <pmoravec>
Component:	Candlepin	Assignee:	satellite6-bugs <satellite6-bugs>
Status:	CLOSED ERRATA	QA Contact:	jcallaha
Severity:	high	Docs Contact:
Priority:	high
Version:	6.2.12	CC:	aperotti, bcourt, bkearney, daniele, jhutar, khowell, ktordeur
Target Milestone:	6.5.0	Keywords:	Triaged
Target Release:	Unused
Hardware:	x86_64
OS:	Linux
Whiteboard:
Fixed In Version:	candlepin-2.5.11-1	Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:
Clones:	1556930 1556932 (view as bug list)		Environment:
Last Closed:	2019-05-14 12:37:00 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:	1556930, 1556932
Bug Blocks:	1353215

Description Pavel Moravec 2018-03-12 16:07:06 UTC

Description of problem:
GET requests like

/candlepin/consumers/7ff6bec6-2e52-4d99-9d40-7223d27f6a5e/guests

takes above 2minutes at a customer, flooding passenger queue of Satellite. Running the queries on an idle candlepin, it takes 1m13s and postgres is full of  long-running statements like:

2018-03-12 11:15:15 EDT LOG:  duration: 369.689 ms  execute <unnamed>: select cp_consumer.id from cp_consumer inner join cp_consumer_facts on cp_consumer.id = cp_consumer_facts.cp_consumer_id where cp_consumer_facts.mapkey = 'virt.uuid' and lower(cp_consumer_facts.element) in ($1, $2) and cp_consumer.owner_id = $3 order by cp_consumer.updated desc
2018-03-12 11:15:15 EDT DETAIL:  parameters: $1 = '42023592-dceb-9e50-3642-479ef1597a63', $2 = '92350242-ebdc-509e-3642-479ef1597a63', $3 = '8abba470582f8d8a01582f8e14010001'

(cp_consumer having 8k records, cp_consumer_facts having 1M records)


Version-Release number of selected component (if applicable):
Sat 6.2.12
candlepin-0.9.54.23-1.el7.noarch


How reproducible:
100%


Steps to Reproduce:
1. (no idea of generic reproducer, just on the customer data we have)
2. time curl -u admin:admin -ks https://localhost:8443/candlepin/consumers/145b68c1-8d5a-480b-9e06-d1bd042188da/guests > out.txt


Actual results:
takes over 1 minute


Expected results:
takes several seconds ideally


Additional info:
candlepin postgres dump will be provided

Comment 4 Pavel Moravec 2018-03-14 11:00:54 UTC

workaround is in adding an index, per KCS 3379731

Comment 8 Pavel Moravec 2018-05-29 11:22:31 UTC

Different reproducer:

run more requests in parallel such that some such requests are against the same type of URI ( consumer/*/guests ).

Running one such request on idle candlepin, it took 8-10s to complete. Running it concurrently with some other / random requests (max 10 concurrently run), it took up to even 160s to complete it.

So another use case is to run the guest request with some other requests in parallel.

Comment 14 jcallaha 2019-04-11 20:50:10 UTC

With no way to reproduce, I am marking this bug verified under no-break conditions.

Comment 17 errata-xmlrpc 2019-05-14 12:37:00 UTC

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/RHSA-2019:1222