You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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"= $1OR 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"= $3AND 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"= $6AND 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"= $9AND 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"= $12AND 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"= $15AND 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"= $18AND 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"= $21AND 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"= $24AND 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"= $27AND 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"= $30AND 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"= $33AND 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"= $36AND 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"= $39AND 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"= $42AND 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"= $45AND 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"= $48AND 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"= $51AND 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"= $55AND 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"= $58AND 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"= $61AND 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"= $64AND 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"= $67AND 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"= $70AND V0."role_field"= $71))
)
)
) AND
T52."job_id"= $72)
ORDER BY
icep1."main_activitystream"."id"ASCLIMIT $73
The text was updated successfully, but these errors were encountered:
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?
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.
Please confirm the following
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
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](https://private-user-images.githubusercontent.com/4119982/345342169-1bc8d4ce-1971-48d3-9958-aec48a9340f5.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjU1NzkzMDQsIm5iZiI6MTcyNTU3OTAwNCwicGF0aCI6Ii80MTE5OTgyLzM0NTM0MjE2OS0xYmM4ZDRjZS0xOTcxLTQ4ZDMtOTk1OC1hZWM0OGE5MzQwZjUucG5nP1gtQW16LUFsZ29yaXRobT1BV1M0LUhNQUMtU0hBMjU2JlgtQW16LUNyZWRlbnRpYWw9QUtJQVZDT0RZTFNBNTNQUUs0WkElMkYyMDI0MDkwNSUyRnVzLWVhc3QtMSUyRnMzJTJGYXdzNF9yZXF1ZXN0JlgtQW16LURhdGU9MjAyNDA5MDVUMjMzMDA0WiZYLUFtei1FeHBpcmVzPTMwMCZYLUFtei1TaWduYXR1cmU9YWZmMGIwNjlhYTg0MzQ3MmMzNzA4MzMzZGZhOWRlNDlkNWJmMzU4MTg2OGY0NjU3ZjJjNDc5YzBmNjRjM2FmZCZYLUFtei1TaWduZWRIZWFkZXJzPWhvc3QmYWN0b3JfaWQ9MCZrZXlfaWQ9MCZyZXBvX2lkPTAifQ.7LWSC7RgBdRDOCNLyHqljGnASFU0XSwia_go5IPk1HM)
![image](https://private-user-images.githubusercontent.com/4119982/345343174-35c8c594-9608-4799-960c-51ae47eae796.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjU1NzkzMDQsIm5iZiI6MTcyNTU3OTAwNCwicGF0aCI6Ii80MTE5OTgyLzM0NTM0MzE3NC0zNWM4YzU5NC05NjA4LTQ3OTktOTYwYy01MWFlNDdlYWU3OTYucG5nP1gtQW16LUFsZ29yaXRobT1BV1M0LUhNQUMtU0hBMjU2JlgtQW16LUNyZWRlbnRpYWw9QUtJQVZDT0RZTFNBNTNQUUs0WkElMkYyMDI0MDkwNSUyRnVzLWVhc3QtMSUyRnMzJTJGYXdzNF9yZXF1ZXN0JlgtQW16LURhdGU9MjAyNDA5MDVUMjMzMDA0WiZYLUFtei1FeHBpcmVzPTMwMCZYLUFtei1TaWduYXR1cmU9NTU0ODgyMjliMTE4NzhmYTNmZjRmNmQ5MGFmMDdiNWJmNDg5NTRiZWFhMDc5NzRmY2ZhNGJlYzBmNTg2NjBmYyZYLUFtei1TaWduZWRIZWFkZXJzPWhvc3QmYWN0b3JfaWQ9MCZrZXlfaWQ9MCZyZXBvX2lkPTAifQ.d8OiYng_uboqpgOwjlAWkwIa7M-Wj8VYm60WdsBs2Is)
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.
The text was updated successfully, but these errors were encountered: