Mulitple active objects remain in status 'Waiting for SYNC' and the RWP queue count increases rapidly.

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

With PostgreSQL as an Automation Engine database the following scenario can occur:

• Activities remain in status 'Waiting for SYNC'
• The count of the MQRWP rises quickly

• The RWP usage remains constantly at > 90%

• The RWP log file shows the following messages continuously

U00003434 Server routine  'JPEXEC_R/CHKSYNC' required '0' minutes and '2,064' seconds for processing.
U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '2:066.987.000'
U00003525 UCUDB: ===> 'select lock_eh('select ey_ah_idnr as eh_ah_idnr from ey  where EY_SyncName = ''SYNC_OBJECT'' 

Environment

Automic Automation Engine 21.X, 24.X

PostgreSQL only

Cause

Defect.

Resolution

Workaround:

Replace the following stored procedure (lock_eh).

• Current definition

CREATE OR REPLACE FUNCTION lock_eh (sql varchar) RETURNS void as $$
           DECLARE
           rec record;
           sql varchar;
           BEGIN
           sql := $1;
           FOR rec IN EXECUTE SQL LOOP
           UPDATE eh SET eh_usr_idnr = eh_usr_idnr WHERE eh_ah_idnr = rec.eh_ah_idnr;
           END LOOP;
           END;
           $$ LANGUAGE plpgsql;

• New definition

CREATE OR REPLACE FUNCTION lock_eh(sql varchar) RETURNS void AS $$
           DECLARE
           rec record;
           sql varchar;
           BEGIN
           sql := $1;
           FOR rec IN EXECUTE SQL LOOP
           perform eh_ah_idnr from eh WHERE eh_ah_idnr = rec.eh_ah_idnr for update;
           END LOOP;
           END;
           $$ LANGUAGE plpgsql;

 

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Automation Engine
Automic Automation Engiine 21.0.12 - Available
Automic Automation Engine 24.3.0 - Planned to be released January 2025

Additional Information

Update to a fixed version listed below or a newer version if available.

Article title: How to register to Broadcom Software Product updates and Critical Alerts

https://knowledge.broadcom.com/external/article?articleId=133819

DefectID: DE153003

Defect Title: Performance problem with syncs on PostgreSQL

Defect Public Description: A problem has been solved where the performance of the system decreased on PostgreSQL when using SYNC objects with many active tasks (> 30.000).