How to delete data from act_ge_bytearray and act_hi_actiinst tables

[ujjwalchoudhari]

My application is on production and data in the above 2 table is huge. I want to clean up data. Although my ACT_HI_DETAIL tables is empty.

My concerns are :-

1. Does any body got the way to clear ACT_GE_BYTEARRAY table? how to find the records from bytearray table to be deleted? what is the relation of bytearray table and ACT_HI_ACTINST table in DB? How can I fetch records from ACT_GE_BYTEARRAY table starting from ACT_HI_ACTINST table?


2. How to find what all history data i can delete? can anybody post any query ? what all checks i need to keep in mind regarding date or anything else?

3. Data in my ACT_HI_DETAIL table is empty. Does this mean that all the History data is not of any use for me?

[hari]

Hi Ujjwal,

I believe deleting is not a good option to consider that too for production environment. Try to index the tables and see if it helps.
btw whats the amount of data in those tables ?

[ujjwalchoudhari]

Hi Thanks for the reply.

act_hi_varinst –> 9.085.976 records
ACT_GE_BYTEARRAY –> 2.095.225 rows
and estimated zipped(!) size of 7-8GB!

I think i am not using any history data in my application. I guess it can be accessed only through history service api's of activiti.
And i am not using any history service api.

If i delete all history data and all the byte array table data for those history records will it work? Or i will be in a bigger mess?

something like this below :-

DELETE from act_ge_bytearray where id_ in
(select bytearray_id_ FROM ACT_HI_VARINST
WHERE proc_inst_id_=processId
AND var_type_ = 'serializable');

[jbarrez]

If you delete history information, do realize you're throwing away all your 'audit data' about past process instances.

You can safely delete historical data from process instance that are not in the act_ru_execution table anymore.

[maudrid]

I've deleted records from this table in the past with activiti 6.
The side effect is that I had to just redeploy the processes, because the bpm data is also kept there.
In this case I was removing all run-time and history data but keeping the models, because we wanted to start with a clean database but keep the process definitions.

[ujjwalchoudhari]

Does the below query make sense?

SELECT p.ID_ AS PROCESS_ID
    FROM ACT_HI_PROCINST p
    INNER JOIN ACT_HI_TASKINST t on p.ID_ = t.PROC_INST_ID_
    WHERE p.END_TIME_< = rocess_end_timestamp
    AND  t.delete_reason_ = 'completed';

Here I am selecting all the process instance id's whose task are completed and who have end time less then some time period in past.
By doing this i will delete alll completed task prcoess instance history data.

Will this query give a different and wrong result If I am not using ACT_RU_EXECUTION table.
Or should i select the Process instance id like this :-
SELECT p.ID_ AS PROCESS_ID
    FROM ACT_HI_PROCINST p
where p.ID_ not in ( Select PROC_INST_ID_ from ACT_RU_EXECUTION );

[jbarrez]

No, this query is ok. END_TIME_ will be null if the process instance is still in the ACT_RU_EXECUTION table

[ujjwalchoudhari]

Thank you for your reply