Related to OA Billing 7.1 and higher
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:
|Name||EventsCleanup||The task name|
|Execution interval (sec)||86400||Interval between task executions|
|Start time||00:30||Time when the task starts running (the provider's local time)|
|DaysAffected (days)||90||Only events older than this number of days are processed|
|RowsLimit||200000||Maximum number of events to be processed in one iteration|
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:
|StartTime||Time when the task was executed|
|TaskFinishTime||Time when processing of the |
|AEventFinishTime||Time when processing of the |
|AEventsDeleted||The actual number of deleted events from the |
|TasksDeleted||The actual number of deleted tasks from the |
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:
pbadatabase, 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;
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_deletedis 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).
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.
To understand what is your case, you should look on the remaining 25 executions. If you see that all executions have
events_deletedequals or near (accuracy about 10%) to
rows_limit, you have the case A and should consider tuning the
RowsLimitparameter, otherwise you have the case B and do not need to change anything as the system will be stabilized after several tasks executions.
- If the
AEventtable is still big and it affects the system performance, you can decrease the
DaysAffectedparameter 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
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.