Question

PushLogusagedata causing performance issue

We have observed pushlogusagedata causing lot of pega0005 alerts in alert log during campaign and inbound requests span which is causing performance issue in DB.

Our application is using pega version 7.3.1 and AES 7.30.

Pushlogusagedata is running every 600sec .

1.what would happen if run this agent once per day during non-business office hour?

2.Do i have to customize the PusgLogUsagetoAES in this case?

3. will it improve the query performance if i create one more index on column 'pxRequestorType'?

Query : SELECT TO_CHAR(TRUNC("PC0"."PXSNAPSHOTTIME",'DD'),'YYYYMMDD') AS "pyDateValue(1)" , "PC0"."PXREQUESTORTYPE" AS "pxRequestorType" , COUNT(DISTINCT "PC0"."PXREQUESTORID") AS "pySummaryCount(1)" , COUNT(DISTINCT "PC0"."PYUSERIDENTIFIER") AS "pySummaryCount(2)" FROM PEGADATA.pr_perf_stats "PC0" WHERE ( "PC0"."PXSYSTEMNODEID" = ? AND "PC0"."PXSNAPSHOTTIME" >= ? AND "PC0"."PXSNAPSHOTTIME" < ? ) AND "PC0"."PXOBJCLASS" = ? GROUP BY TO_CHAR(TRUNC("PC0"."PXSNAPSHOTTIME",'DD'),'YYYYMMDD') , "PC0"."PXREQUESTORTYPE" ORDER BY 1 DESC, 2 ASC

Please suggest

***Moderator Edit-Vidyaranjan: Updated Platform Capability***

Comments

Keep up to date on this post and subscribe to comments

Pega
August 18, 2019 - 11:51am

Hi,

It's a good idea, You can try creating an index for the fields used in the where clause. It requires server restart.

If still you are facing the same issue you can think about disabling Push notification service.

August 18, 2019 - 1:53pm
Response to ramau

If you meant DB index then that should not require any server restart.

August 18, 2019 - 2:32pm

Is it possible for you to complete the query with the parameter values that you would find in the same Alert entry, and generate the explain plan of this query from database (seek help from your DBA if you don't know how to generate it)?

I have a quick look at the DB index of this table and found it already got a composite index on PXSNAPSHOTTIME and PXSYSTEMNODEID, so no reason to believe this query is not optimized.

My best guess is the longer response time is mainly caused by two factor - 1) use of function on PXSNAPSHOTTIME (and its subsequent use in GROUP BY clause),  2) Your application is inserting good amount of records on this table (expected for high volume system).

In that case a function based index would help, but first let us have a look the current explain plan.