Many database locks make UI go slowly

[lamm]

Please confirm the following

• [X] I agree to follow this project's code of conduct.
• [X] I have checked the current issues for duplicates.
• [X] I understand that AWX is open source software provided for free and that I might not receive a timely response.
• [X] 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
• [X] 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" = $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]

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]

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]

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