Article Original Creation Date: 2011-03-02
Overview
Configure purging Job to run as effective as possible.
- Setting up the Max Records to expire at one Time
- purging job frequency
- And check what really happens via the Encore.log entries (removed).
History is set to 86400 seconds (1 day)
Environment
- Solaris 10
- Oracle 10
- JBoss 4.0.4 GA
- Tomcat 5.5.25
- SG 4.0.13
Root Cause
Up to now, the history tables were able to grow very big because the history was set to 40 days.
This causes after some time performance problems of the DB and total application.
Now the customer agreed to change the history to 1 day and they have to use another method to retrieve historical data out of the involved tables.
The involved history tables are:
sprt_sg_policy_device_history
sprt_sg_policy_action_history
sprt_sg_policy_action_history
Resolution
In this particular situation, this is DB hardware (performance) and informs/second related, we set the job up like this:
The purging job runs now on 1 APP server, the other 5 jobs run on the second server.
Configuration for: SG4.1.3
-I deleted 2 x delete query out of the sprt_ec_record_purging table:
DELETE FROM sprt_sg_policy_device_history WHERE guid IN (SELECT dh.guid FROM sprt_sg_policy_device_history dh LEFT JOIN sprt_sg_policy_exec_history eh ON dh.sg_policy_exec_history_guid = eh.guid WHERE eh.guid IS NULL AND rownum <= {max_rows_to_purge})
DELETE FROM sprt_sg_policy_action_history WHERE guid IN (SELECT ah.guid FROM sprt_sg_policy_action_history ah LEFT JOIN sprt_sg_policy_exec_history eh ON ah.sg_policy_exec_history_guid = eh.guid WHERE eh.guid IS NULL AND rownum <= {max_rows_to_purge})
* I noticed these queries take a lot of time and delete nothing under normal circumstances.
Configuration for: SG4.1.3
-I deleted 2 x delete query out of the sprt_ec_record_purging table:
DELETE FROM sprt_sg_policy_device_history WHERE guid IN (SELECT dh.guid FROM sprt_sg_policy_device_history dh LEFT JOIN sprt_sg_policy_exec_history eh ON dh.sg_policy_exec_history_guid = eh.guid WHERE eh.guid IS NULL AND rownum <= {max_rows_to_purge})
DELETE FROM sprt_sg_policy_action_history WHERE guid IN (SELECT ah.guid FROM sprt_sg_policy_action_history ah LEFT JOIN sprt_sg_policy_exec_history eh ON ah.sg_policy_exec_history_guid = eh.guid WHERE eh.guid IS NULL AND rownum <= {max_rows_to_purge})
* I noticed these queries take a lot of time and delete nothing under normal circumstances.
Setup:
-Max deleted rows= 5000 per run
-Pause=240 seconds by that the job runs every ~ 5 minutes
-Max deleted rows= 5000 per run
-Pause=240 seconds by that the job runs every ~ 5 minutes
-History=86400 seconds (1 day)
Take care!, in case of a cluster the job logs to all cluster members, not only the server the job is started on.
Check the Encore.logs for 'removed' or 'action_exec_start_time' or 'execution_end_time', the last 2 strings belong to the history tables.
-In theory this setup can delete max 12*5000= 60.000 entries per hour out of both history tables.
-This customer has at this moment max ~ 40.000 entries per hour.
For this setup the tables stay around below shown amount of entries:
Take care!, in case of a cluster the job logs to all cluster members, not only the server the job is started on.
Check the Encore.logs for 'removed' or 'action_exec_start_time' or 'execution_end_time', the last 2 strings belong to the history tables.
-In theory this setup can delete max 12*5000= 60.000 entries per hour out of both history tables.
-This customer has at this moment max ~ 40.000 entries per hour.
For this setup the tables stay around below shown amount of entries:
sprt_sg_policy_device_history= 482K
sprt_sg_policy_action_history=1.216K
sprt_sg_policy_action_history=1.216K