Case Study: How to Fix Out of Memory Error on Daily Scheduled Maintenance Jobs at 5 AM

The Problem

Users have reported that they found tasks are locking up with the out of memory error causing TIBCO JasperReports^® Server to lock up at 5 AM each day. They found 4,.3 million rows in jiauditevent table and 450K rows in jiauditeventarchive table.

The Analysis

JasperReports Server runs an audit archive job at 5 AM each day to move audit records older than 30 days from audit table to archive table. This information is in TIBCO JasperReports^® Server ADMINISTRATOR GUIDE RELEASE 7.1 document, section 9.4.2 "Auditing Archive Options".

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-administrator-guide/v71/configuring-auditing-and-monitoring

During this operation, it needs to have exclusive control to audit event related tables to move records from jiauditevent table to jiauditeventarchive table. If this process ever failed in one of the daily runs, it could have a ripple effect on all the subsequent runs on the following days. The volume of the audit table becomes so large that it can cause out of the memory error and not be able to complete the everyday archive process to reduce table size in a timely and orderly manner.

The Suggestion

If users do not care about those audit records, the simplest way is to truncate the audit table to get rid of all its rows.

JasperReports Server does not have a utility to move those rows from audit to audit archive. The daily archive process is an internal class method triggered by the scheduler. Due to the current size of the user's audit table with 4 million plus and growing records caused by the failing daily run of archive attempts, the normal archive run will take forever to finish. To use this existing internal archive process to clean up the audit archive backlogs, what users can do is to pick up a system downtime date (over a weekend, for example), reset the scheduler (through cronExpression) and change the maxAuditEventAgeToArchive number to a larger number to avoid archive huge amount of rows all at once. Then restart the server to kick off the archive process. Users will need to repeat the above process multiple times to decrement the maxAuditEventAgeToArchive number, say from 90, to 75, 60, 45, and 30, to eventually complete the archive process to retain only 30 days worth of audit data in this table.

The third option is to use a SQL query to copy rows from audit table to audit archive table, then delete those rows from audit table. Since this is a direct execution against the DB, users should proceed with caution. This may also require DBA's involvement to carry out the task. Users can run the following SQL query to get audit statistical information to help planing this task:

select event_date::date as audit_date
, count(id) audit_count
, min(id) as id_lower
, max(id) as id_upper
from jiauditevent
group by audit_date
order by audit_date asc

The above query is for PostgreSQL repository database. For other DBs, users need to find a similar function to convert the timestamp to a date field in order to gather daily audit information.

TTC-20201129-1880875