CloudBlue Technical Support

Related to OA Billing 7.1 and higher

Solution Description

Starting from OA Billing 7.1, outdated events stored in the AEvent table are cleaned up automatically. The solution is implemented through the EventsCleanup scheduled task. Only events in statuses Processed, Failed and Cancelled are removed during this task execution. If there are related tasks in statuses Cancelled, Finished, Failed, they are removed too. But if there are related tasks in other statuses, both the tasks and the events are skipped. The default scheduled task has the following attributes:

You can change the parameters of this task on the Scheduler tab at System > Settings > Events.

Monitoring the event activity

The fact whether the task was launched can be found at Operations > Tasks filtered by the Event Type set to EventsCleanup. For more details, the auxiliary table AEventCleanupLog is used. The table has the following structure:

Fine-tuning the event parameters

In some cases, the cleanup tuning may be required. To check whether you have that case, use the following algorithm:

1. On the pba database, run the following query:

   select to_timestamp("StartTime") start_time, (("AEventFinishTime"-"StartTime")/60)::int runtime_min, "DaysAffected" days_affected, "RowsLimit" rows_limit, "AEventsDeleted" events_deleted, "TasksDeleted" tasks_deleted from "AEventCleanupLog" order by "StartTime" desc limit 30;
   

2. The query shows the task’s statistics for the last 30 executions, which corresponds to one month, assuming that the execution frequency is once per day. There are two main values that are worth paying your attention to:

   • events_deleted – If for last 5 executions the value of events_deleted is equal or near (accuracy about 10%) to rows_limit, that means:
   • Case A - the number of events generated by the system on daily basis is more than can be processed by the cleanup task;
   • Case B - not long before, there was a spike that generated a lot of events (for example, monthly billing).
   
   To understand what is your case, you should look on the remaining 25 executions. If you see that all executions have events_deleted equals or near (accuracy about 10%) to rows_limit, you have the case A and should consider tuning the RowsLimit parameter, otherwise you have the case B and do not need to change anything as the system will be stabilized after several tasks executions.
   
   
   • runtime_min – If duration of the task runtime is significant (10 minutes or more), probably there is a conflict in the database that blocks the task execution (see the OA Upgrade Performance Troubleshooting section of the Upgrade Workflow guide on how to determine whether there are blocking sessions in the database), or you should consider upgrading the hardware.
3. If the AEvent table is still big and it affects the system performance, you can decrease the DaysAffected parameter that allows the task to process more events. For example, if the system generates 100,000 events daily, in 90 days 9 million events are generated, and all they will be skipped by the cleanup algorithm.

Tuning RowsLimit parameter

To tune the RowsLimit parameter firstly you need to gather events growth rate statistic. Within a few days (3 or more) at the same time (let it be 08:00) run the following query and write somewhere the results of:

select count(*) from "AEvent";

After that, calculate the daily growth rate. Round it up to thousands, and that will be the value you should add to the RowsLimit parameter.

For example, the first day you had 33,044, the second day – 37,093, the third – 39,694. Hence, the average growth rate is (39,694 – 33,044)/2 = 3,325; round up to thousands gives you 4,000. Assuming that RowsLimit has the default value, you should set the RowsLimit to 204,000. For details on how to set event parameters, refer to the Billing Provider’s guide.

Internal content