Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Many database locks make UI go slowly #15327

Open
5 of 11 tasks
lamm opened this issue Jul 3, 2024 · 3 comments
Open
5 of 11 tasks

Many database locks make UI go slowly #15327

lamm opened this issue Jul 3, 2024 · 3 comments
Labels
community needs_triage type:bug

Comments

@lamm
Copy link

lamm commented Jul 3, 2024

Please confirm the following

  • I agree to follow this project's code of conduct.
  • I have checked the current issues for duplicates.
  • I understand that AWX is open source software provided for free and that I might not receive a timely response.
  • I am NOT reporting a (potential) security vulnerability. (These should be emailed to security@ansible.com instead.)

Bug Summary

We are experiencing lock issues on the Postgres database.
This happens randomly but always during the day.
When the problem appears, there are not many playbooks running in parallel (less than 10).
When the locks appear we notice significant slowdowns in the interface.

AWX version

24.2.0

Select the relevant components

  • UI
  • UI (tech preview)
  • API
  • Docs
  • Collection
  • CLI
  • Other

Installation method

openshift

Modifications

no

Ansible version

No response

Operating system

No response

Web browser

No response

Steps to reproduce

Currently we don't know how to reproduce the issue.

Expected results

No locks on prostgres database

Actual results

Locks on prostgres database making UI slowdown.
image
image

Additional information

Our database administrator identifies the query that could be problematic.
The request does not seem to filter sufficiently and returns several gigabytes of IO block.

SELECT 
	DISTINCT icep1."main_activitystream"."id", 
	icep1."main_activitystream"."actor_id", 
	icep1."main_activitystream"."operation", 
	icep1."main_activitystream"."timestamp", 
	icep1."main_activitystream"."changes", 
	icep1."main_activitystream"."deleted_actor", 
	icep1."main_activitystream"."action_node", 
	icep1."main_activitystream"."object_relationship_type", 
	icep1."main_activitystream"."object1", 
	icep1."main_activitystream"."object2", 
	icep1."main_activitystream"."setting" 
FROM icep1."main_activitystream" 
	LEFT OUTER JOIN icep1."main_activitystream_user" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_user"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_ad_hoc_command" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_ad_hoc_command"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_adhoccommand" ON (icep1."main_activitystream_ad_hoc_command"."adhoccommand_id" = icep1."main_adhoccommand"."unifiedjob_ptr_id") 
	LEFT OUTER JOIN icep1."main_activitystream_inventory" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_inventory"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_host" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_host"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_host" ON (icep1."main_activitystream_host"."host_id" = icep1."main_host"."id") 
	LEFT OUTER JOIN icep1."main_activitystream_group" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_group"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_group" ON (icep1."main_activitystream_group"."group_id" = icep1."main_group"."id") 
	LEFT OUTER JOIN icep1."main_activitystream_inventory_source" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_inventory_source"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_inventorysource" ON (icep1."main_activitystream_inventory_source"."inventorysource_id" = icep1."main_inventorysource"."unifiedjobtemplate_ptr_id") 
	LEFT OUTER JOIN icep1."main_activitystream_inventory_update" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_inventory_update"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_inventoryupdate" ON (icep1."main_activitystream_inventory_update"."inventoryupdate_id" = icep1."main_inventoryupdate"."unifiedjob_ptr_id") 
	LEFT OUTER JOIN icep1."main_inventorysource" T20 ON (icep1."main_inventoryupdate"."inventory_source_id" = T20."unifiedjobtemplate_ptr_id") 
	LEFT OUTER JOIN icep1."main_activitystream_credential" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_credential"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_organization" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_organization"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_notification_template" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_notification_template"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_notificationtemplate" ON (icep1."main_activitystream_notification_template"."notificationtemplate_id" = icep1."main_notificationtemplate"."id") 
	LEFT OUTER JOIN icep1."main_activitystream_notification" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_notification"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_notification" ON (icep1."main_activitystream_notification"."notification_id" = icep1."main_notification"."id") 
	LEFT OUTER JOIN icep1."main_notificationtemplate" T31 ON (icep1."main_notification"."notification_template_id" = T31."id") 
	LEFT OUTER JOIN icep1."main_activitystream_label" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_label"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_label" ON (icep1."main_activitystream_label"."label_id" = icep1."main_label"."id") 
	LEFT OUTER JOIN icep1."main_activitystream_role" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_role"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_project" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_project"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_project_update" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_project_update"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_projectupdate" ON (icep1."main_activitystream_project_update"."projectupdate_id" = icep1."main_projectupdate"."unifiedjob_ptr_id") 
	LEFT OUTER JOIN icep1."main_activitystream_job_template" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_job_template"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_job" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_job"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_job" ON (icep1."main_activitystream_job"."job_id" = icep1."main_job"."unifiedjob_ptr_id") 
	LEFT OUTER JOIN icep1."main_activitystream_team" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_team"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_o_auth2_application" ON (icep1."main_activitystream"."id" = icep1."main_activitystream_o_auth2_application"."activitystream_id") 
	LEFT OUTER JOIN icep1."main_activitystream_job" T52 ON (icep1."main_activitystream"."id" = T52."activitystream_id") 
WHERE 
(
	(icep1."main_activitystream_user"."user_id" = $1 
	OR icep1."main_adhoccommand"."inventory_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $2) AND V0."content_type_id" = $3 AND V0."role_field" = $4)) 
	OR 	icep1."main_activitystream_inventory"."inventory_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $5) AND V0."content_type_id" = $6 AND V0."role_field" = $7)) 
	OR icep1."main_host"."inventory_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $8) AND V0."content_type_id" = $9 AND V0."role_field" = $10))
	OR icep1."main_group"."inventory_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $11) AND V0."content_type_id" = $12 AND V0."role_field" = $13)) 
	OR icep1."main_inventorysource"."inventory_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $14) AND V0."content_type_id" = $15 AND V0."role_field" = $16)) 
	OR T20."inventory_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $17) AND V0."content_type_id" = $18 AND V0."role_field" = $19)) 
	OR icep1."main_activitystream_credential"."credential_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $20) AND V0."content_type_id" = $21 AND V0."role_field" = $22)) 
	OR icep1."main_activitystream_user"."user_id" IN 
		(SELECT DISTINCT X2."user_id" FROM icep1."main_organization" X0 LEFT OUTER JOIN icep1."main_rbac_roles" X1 ON (X0."member_role_id" = X1."id") LEFT OUTER JOIN icep1."main_rbac_roles_members" X2 ON (X1."id" = X2."role_id") WHERE (X0."id" IN 
			(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $23) AND V0."content_type_id" = $24 AND V0."role_field" = $25)) 
		OR X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $26) AND V0."content_type_id" = $27 AND V0."role_field" = $28))))
	OR icep1."main_activitystream_organization"."organization_id" IN (SELECT DISTINCT X0."id" FROM icep1."main_organization" X0 WHERE (X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $29) AND V0."content_type_id" = $30 AND V0."role_field" = $31)) 
	OR X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $32) AND V0."content_type_id" = $33 AND V0."role_field" = $34)))) 
	OR icep1."main_notificationtemplate"."organization_id" IN (SELECT DISTINCT X0."id" FROM icep1."main_organization" X0 WHERE (X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $35) AND V0."content_type_id" = $36 AND V0."role_field" = $37)) 
	OR X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $38) AND V0."content_type_id" = $39 AND V0."role_field" = $40)))) 
	OR T31."organization_id" IN (SELECT DISTINCT X0."id" FROM icep1."main_organization" X0 WHERE (X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $41) AND V0."content_type_id" = $42 AND V0."role_field" = $43)) 
		OR X0."id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $44) AND V0."content_type_id" = $45 AND V0."role_field" = $46)))) 
	OR icep1."main_label"."organization_id" IN 
		(SELECT DISTINCT X0."id" FROM icep1."main_organization" X0 WHERE (X0."id" IN 
			(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $47) AND V0."content_type_id" = $48 AND V0."role_field" = $49)) 
			OR X0."id" IN 
			(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $50) AND V0."content_type_id" = $51 AND V0."role_field" = $52))))
	OR icep1."main_activitystream_role"."role_id" IN (SELECT V0."id" FROM icep1."main_rbac_roles" V0 INNER JOIN icep1."main_rbac_role_ancestors" V1 ON (V0."id" = V1."descendent_id") WHERE V1."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $53)) 
	OR icep1."main_activitystream_project"."project_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $54) AND V0."content_type_id" = $55 AND V0."role_field" = $56)) 
	OR icep1."main_projectupdate"."project_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $57) AND V0."content_type_id" = $58 AND V0."role_field" = $59)) 
	OR icep1."main_activitystream_job_template"."jobtemplate_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $60) AND V0."content_type_id" = $61 AND V0."role_field" = $62)) 
	OR icep1."main_job"."job_template_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $63) AND V0."content_type_id" = $64 AND V0."role_field" = $65)) 
	OR icep1."main_activitystream_team"."team_id" IN 
		(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $66) AND V0."content_type_id" = $67 AND V0."role_field" = $68)) 
	OR icep1."main_activitystream_o_auth2_application"."oauth2application_id" IN 
		(SELECT X0."id" FROM icep1."main_oauth2application" X0 WHERE X0."organization_id" IN 
			(SELECT W0."id" FROM icep1."main_organization" W0 WHERE W0."id" IN 
				(SELECT DISTINCT V0."object_id" FROM icep1."main_rbac_role_ancestors" V0 WHERE (V0."ancestor_id" IN (SELECT U0."id" FROM icep1."main_rbac_roles" U0 INNER JOIN icep1."main_rbac_roles_members" U1 ON (U0."id" = U1."role_id") WHERE U1."user_id" = $69) AND V0."content_type_id" = $70 AND V0."role_field" = $71))
			)
		)
	) AND 
	T52."job_id" = $72) 
ORDER BY 
	icep1."main_activitystream"."id" ASC LIMIT $73
@djyasin
Copy link
Member

djyasin commented Jul 3, 2024

Hello,
We would like to gather a bit more information from you. The UI's performance depends heavily on the APIs performance. Can you please find slow API requests using the browser network debugger and reply with the results?

Is there any particular endpoint that is problematic?

@lamm
Copy link
Author

lamm commented Jul 8, 2024

Hello,

When the problem occurs, several parts of the user interface are slowed down such as:

  • Jobs/Template views
  • Job details

Some users also reported the fact that Jobs remained in pending status for a long time.
Not knowing how to reproduce the problem, we will set up a probe to be able to give you more information when the problem occurs.

@thedoubl3j
Copy link
Member

if you suspect that activity streams is causing the issue, you can disable them (for debugging purposes) in the settings under miscellaneous system.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
community needs_triage type:bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@lamm @thedoubl3j @djyasin