This content is dedicated to our next version. It is work in progress: its content will evolve until the new version is released. Before that time, it cannot be considered as official.

Database deletion volume testing

The following tests have been run in the following databases.

Testing environment

Tests were run on Intel Core i7 10th Gen, 8 CPU cores, 16 Gb of RAM.
Database running inside a docker container.
Purge tool run from the same machine.

PostgreSQL 11.x

Remove 1 160 800 archived cases (corresponds to 6 964 800 lines in the database)
Total Time: 71m30s

Execution

╰─$ bin/bonita-purge-tool 6547377706517145159 1537600000000
  ____              _ _                                         _              _
 |  _ \            (_) |                                       | |            | |
 | |_) | ___  _ __  _| |_ __ _    _ __  _   _ _ __ __ _  ___   | |_ ___   ___ | |
 |  _ < / _ \| '_ \| | __/ _` |  | '_ \| | | | '__/ _` |/ _ \  | __/ _ \ / _ \| |
 | |_) | (_) | | | | | || (_| |  | |_) | |_| | | | (_| |  __/  | || (_) | (_) | |
 |____/ \___/|_| |_|_|\__\__,_|  | .__/ \__,_|_|  \__, |\___|   \__\___/ \___/|_|
                                 | |               __/ |
                                 |_|              |___/
2020-02-21 18:19:40.072  INFO 8946 -[main] o.bonitasoft.engine.purge.ApplicationKt  : Starting ApplicationKt on manu-DellXPS with PID 8946 (/home/manu/workspace/bonita-purge-tool/build/bonita-purge-tool/lib/bonita-purge-tool.jar started by manu in /home/manu/workspace/bonita-purge-tool/build/bonita-purge-tool)
2020-02-21 18:19:40.076  INFO 8946 -[main] o.bonitasoft.engine.purge.ApplicationKt  : No active profile set, falling back to default profiles: default
2020-02-21 18:19:41.551  INFO 8946 -[main] org.bonitasoft.engine.purge.Application  : Using datasource with HikariDataSource (null)
2020-02-21 18:19:41.711  INFO 8946 -[main] o.s.s.c.ThreadPoolTaskScheduler          : Initializing ExecutorService 'taskScheduler'
2020-02-21 18:19:41.798  INFO 8946 -[main] o.bonitasoft.engine.purge.ApplicationKt  : Started ApplicationKt in 2.611 seconds (JVM running for 3.325)
2020-02-21 18:19:41.828  INFO 8946 -[main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-02-21 18:19:42.149  INFO 8946 -[main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-02-21 18:19:42.283  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Database URL is jdbc:postgresql://localhost:5432/bonita
2020-02-21 18:19:42.284  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Tenant id used is 1
2020-02-21 18:19:42.284  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : All settings can be changed in application.properties file
2020-02-21 18:19:43.218  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Will purge all archived process instances and their elements for process 'RFS_n_ACT_MOCKED' in version '1.1' that are finished since at least 2018-09-22T09:06:40
Start the purge using the above parameters? [y/N]
y
2020-02-21 18:19:52.609  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Starting archive process instance purge....
2020-02-21 18:22:00.446  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 6964800 rows from table ARCH_PROCESS_INSTANCE...
2020-02-21 18:23:43.453  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 3299016 rows from table ARCH_CONTRACT_DATA in 103000 ms
2020-02-21 19:00:04.318  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 24189472 rows from table ARCH_DATA_INSTANCE in 2180864 ms
2020-02-21 19:00:04.336  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 0 rows from table ARCH_DOCUMENT_MAPPING in 17 ms
2020-02-21 19:09:05.510  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 23216120 rows from table ARCH_FLOWNODE_INSTANCE in 541173 ms
2020-02-21 19:09:27.984  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 2321612 rows from table ARCH_PROCESS_COMMENT in 22474 ms
2020-02-21 19:09:27.989  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 0 rows from table ARCH_REF_BIZ_DATA_INST in 5 ms
2020-02-21 19:09:30.396  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 0 rows from table ARCH_CONNECTOR_INSTANCE in 2407 ms
2020-02-21 19:09:30.404  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 0 rows from table ARCH_CONTRACT_DATA in 7 ms
2020-02-21 19:29:02.660  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 4643224 rows from table ARCH_DATA_INSTANCE in 1172256 ms
2020-02-21 19:31:17.330  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Deleted 8125642 rows from table ARCH_CONNECTOR_INSTANCE in 134669 ms
2020-02-21 19:31:17.352  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Archive process instance purge completed.
2020-02-21 19:31:17.353  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : Some of the deleted elements may still appear in Bonita applications for a short while.
2020-02-21 19:31:17.353  INFO 8946 -[main] o.b.e.purge.DeleteOldProcessInstances    : If you try to access them you will get a not found error. This is the expected behaviour.
2020-02-21 19:31:17.353  INFO 8946 -[main] org.bonitasoft.engine.purge.Application  : Execution completed in 4295553 ms
2020-02-21 19:31:17.357  INFO 8946 -[main] o.s.s.c.ThreadPoolTaskScheduler          : Shutting down ExecutorService 'taskScheduler'
2020-02-21 19:31:17.358  INFO 8946 -[main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2020-02-21 19:31:17.366  INFO 8946 -[main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

MYSQL (5.5.X)

Remove 137 603 archived cases (corresponds to 825 618 lines in the database)
Total Time: 25 minutes

Configuration ( mysql.cnf )

max_connections = 200
key_buffer_size = 512M
innodb_buffer_pool_size = 5500M
innodb_buffer_pool_instances=8
125 Gb in the database

chunk size : 500 k

Initial data volume

125 Gb in the database

SELECT
	table_name,
	table_rows,
	round(((data_length + index_length) / 1024 / 1024), 2) 'size MB'
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = 'bonita'
order by
	table_name ASC ;
segment_name            Size in MB     	NUM_ROWS
-------------------------------------------------------
ARCH_FLOWNODE_INSTANCE	       9730254.09      9730254
ARCH_PROCESS_INSTANCE	       2938758         2938758
ARCH_DATA_INSTANCE             28696           16179052
QUERIABLE_LOG                  3044085         3044085
ARCH_CONTRACT_DATA             0               0
ARCH_CONTRACT_DATA_BACKUP      6757.02         1075697
ARCH_CONNECTOR_INSTANCE	       3010846         3010846
DOCUMENT                       0               0
ARCH_PROCESS_COMMENT	       976635          976635
ARCH_REF_BIZ_DATA_INST	       0               0
ARCH_DOCUMENT_MAPPING	       0               0
JOB_PARAM                      0               0
ARCH_MULTI_BIZ_DATA            0               0
PROCESS_COMMENT                0               0
MESSAGE_INSTANCE               0               0
DOCUMENT_MAPPING               0               0
DATA_INSTANCE                  0               0

Execution

╰─$./bin/bonita-purge-tool 7431518296865410294 1537689211864

  ____              _ _                                         _              _
 |  _ \            (_) |                                       | |            | |
 | |_) | ___  _ __  _| |_ __ _    _ __  _   _ _ __ __ _  ___   | |_ ___   ___ | |
 |  _ < / _ \| '_ \| | __/ _` |  | '_ \| | | | '__/ _` |/ _ \  | __/ _ \ / _ \| |
 | |_) | (_) | | | | | || (_| |  | |_) | |_| | | | (_| |  __/  | || (_) | (_) | |
 |____/ \___/|_| |_|_|\__\__,_|  | .__/ \__,_|_|  \__, |\___|   \__\___/ \___/|_|
                                 | |               __/ |
                                 |_|              |___/
2020-03-23 18:49:42,436 INFO  Starting ApplicationKt on pascal-XPS-15-9570 with PID 31673 (/home/pascal/development/bonita-purge-tool/build/distributions/bonita-purge-tool/lib/bonita-purge-tool.jar started by pascal in /home/pascal/development/bonita-purge-tool/build/distributions/bonita-purge-tool)
2020-03-23 18:49:42,438 DEBUG Running with Spring Boot v2.1.6.RELEASE, Spring v5.1.8.RELEASE
2020-03-23 18:49:42,439 INFO  No active profile set, falling back to default profiles: default
2020-03-23 18:49:43,449 INFO  Using datasource with HikariDataSource
2020-03-23 18:49:43,553 INFO  Started ApplicationKt in 1.344 seconds (JVM running for 1.757)
2020-03-23 18:49:43,759 INFO  Database URL is jdbc:mysql://localhost:3307/bonita?allowMultiQueries=true
2020-03-23 18:49:43,759 INFO  Tenant id used is 1
2020-03-23 18:49:43,759 INFO  All settings can be changed in application.properties file
2020-03-23 18:49:45,376 INFO  Will purge all archived process instances and their elements for process 'RFS_n_ACT_MOCKED' in version '1.3' that are finished since at least 2018-09-23T09:53:31.864
2020-03-23 18:49:45,376 INFO  Starting archive process instance purge...
2020-03-23 18:49:45,380 DEBUG Executing SQL: CREATE INDEX idx_rootprocid_archprocinst_tmp ON arch_process_instance(rootprocessinstanceid)
2020-03-23 18:49:52,285 DEBUG SQL command executed in 6905 ms
2020-03-23 18:49:52,286 DEBUG Executing SQL: DELETE A FROM arch_process_instance A INNER JOIN arch_process_instance B ON A.rootprocessinstanceid = B.rootprocessinstanceid WHERE B.rootprocessinstanceid = B.sourceobjectid AND B.processdefinitionid = ? AND (B.stateid = 6 OR B.stateid = 3 OR B.stateid = 4) AND B.enddate <= ? AND B.tenantId = ?
2020-03-23 18:50:55,427 INFO  Deleted 825618 rows from table ARCH_PROCESS_INSTANCE in 63141 ms
2020-03-23 18:50:55,427 DEBUG Executing SQL: DELETE FROM arch_contract_data WHERE kind = 'PROCESS' AND tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE arch_contract_data.scopeid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 18:50:55,430 INFO  Deleted 0 rows from table arch_contract_data in 3 ms
2020-03-23 18:50:56,430 DEBUG Executing SQL: DELETE FROM arch_data_instance WHERE arch_data_instance.containertype = 'PROCESS_INSTANCE' AND arch_data_instance.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE arch_data_instance.containerid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 18:52:02,567 INFO  Deleted 500000 rows from table arch_data_instance in 66137 ms
2020-03-23 18:53:13,722 INFO  Deleted 500000 rows from table arch_data_instance in 70154 ms
2020-03-23 18:54:34,795 INFO  Deleted 500000 rows from table arch_data_instance in 80071 ms
2020-03-23 18:56:00,910 INFO  Deleted 500000 rows from table arch_data_instance in 85115 ms
2020-03-23 18:57:25,547 INFO  Deleted 500000 rows from table arch_data_instance in 83636 ms
2020-03-23 18:58:47,634 INFO  Deleted 500000 rows from table arch_data_instance in 81086 ms
2020-03-23 19:00:08,156 INFO  Deleted 500000 rows from table arch_data_instance in 79522 ms
2020-03-23 19:01:29,054 INFO  Deleted 500000 rows from table arch_data_instance in 79897 ms
2020-03-23 19:03:22,682 INFO  Deleted 265693 rows from table arch_data_instance in 112627 ms
2020-03-23 19:03:23,685 DEBUG Executing SQL: DELETE FROM arch_document_mapping WHERE tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE arch_document_mapping.processinstanceid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:03:23,691 INFO  Deleted 0 rows from table arch_document_mapping in 5 ms
2020-03-23 19:03:24,691 DEBUG Executing SQL: DELETE FROM arch_flownode_instance WHERE tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE arch_flownode_instance.rootcontainerid = b.rootprocessinstanceid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:04:22,641 INFO  Deleted 500000 rows from table arch_flownode_instance in 57950 ms
2020-03-23 19:05:45,568 INFO  Deleted 500000 rows from table arch_flownode_instance in 81927 ms
2020-03-23 19:07:10,313 INFO  Deleted 500000 rows from table arch_flownode_instance in 83745 ms
2020-03-23 19:08:35,304 INFO  Deleted 500000 rows from table arch_flownode_instance in 83990 ms
2020-03-23 19:09:55,926 INFO  Deleted 500000 rows from table arch_flownode_instance in 79622 ms
2020-03-23 19:11:15,852 INFO  Deleted 252060 rows from table arch_flownode_instance in 78925 ms
2020-03-23 19:11:16,853 DEBUG Executing SQL: DELETE FROM arch_process_comment WHERE tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE arch_process_comment.processinstanceid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:11:37,423 INFO  Deleted 275206 rows from table arch_process_comment in 20569 ms
2020-03-23 19:11:38,424 DEBUG Executing SQL: DELETE FROM arch_ref_biz_data_inst WHERE tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE arch_ref_biz_data_inst.orig_proc_inst_id = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:11:38,429 INFO  Deleted 0 rows from table arch_ref_biz_data_inst in 4 ms
2020-03-23 19:11:39,430 DEBUG Executing SQL: DELETE FROM arch_connector_instance WHERE containertype = 'process' AND tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE arch_connector_instance.containerid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:11:42,190 INFO  Deleted 0 rows from table arch_connector_instance in 2760 ms
2020-03-23 19:11:43,191 DEBUG Executing SQL: DELETE FROM arch_contract_data WHERE KIND = 'TASK' AND tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_flownode_instance b WHERE arch_contract_data.scopeid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:11:43,193 INFO  Deleted 0 rows from table arch_contract_data in 2 ms
2020-03-23 19:11:44,194 DEBUG Executing SQL: DELETE FROM arch_data_instance WHERE containertype = 'ACTIVITY_INSTANCE' AND tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_flownode_instance b WHERE arch_data_instance.containerid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:12:09,429 INFO  Deleted 500000 rows from table arch_data_instance in 25235 ms
2020-03-23 19:13:21,716 INFO  Deleted 50412 rows from table arch_data_instance in 71287 ms
2020-03-23 19:13:22,718 DEBUG Executing SQL: DELETE FROM arch_connector_instance WHERE containertype = 'flowNode' AND tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_flownode_instance b where arch_connector_instance.containerid = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:13:36,432 INFO  Deleted 500000 rows from table arch_connector_instance in 13714 ms
2020-03-23 19:14:04,755 INFO  Deleted 463221 rows from table arch_connector_instance in 27322 ms
2020-03-23 19:14:05,822 INFO  Detected presence of table ARCH_CONTRACT_DATA_BACKUP. Purging it as well.
2020-03-23 19:14:05,823 DEBUG Executing SQL: DELETE FROM arch_contract_data_backup WHERE KIND = 'PROCESS' AND tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE arch_contract_data_backup.scopeId = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:14:58,476 INFO  Deleted 275206 rows from table arch_contract_data_backup in 52653 ms
2020-03-23 19:14:59,478 DEBUG Executing SQL: DELETE FROM arch_contract_data_backup WHERE KIND = 'TASK' AND tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_flownode_instance b WHERE arch_contract_data_backup.scopeId = b.sourceobjectid AND b.tenantId = ?) LIMIT ?
2020-03-23 19:15:09,906 INFO  Deleted 0 rows from table arch_contract_data_backup in 10428 ms
2020-03-23 19:15:10,909 DEBUG Executing SQL: DROP INDEX idx_rootprocid_archprocinst_tmp ON arch_process_instance
2020-03-23 19:15:11,035 DEBUG SQL command executed in 126 ms
2020-03-23 19:15:11,036 INFO  Archive process instance purge completed.
2020-03-23 19:15:11,036 INFO  Some of the deleted elements may still appear in Bonita applications for a short while.
2020-03-23 19:15:11,036 INFO  If you try to access them you will get a not found error. This is the expected behaviour.
2020-03-23 19:15:11,036 INFO  Execution completed in 1527482 ms
2020-03-23 19:15:11,036 INFO  According to the database type you use, you may need to execute certain maintenance commands
2020-03-23 19:15:11,036 INFO  to reclaim space or optimize the newly purged tables.
2020-03-23 19:15:11,036 INFO  Eg. VACUUM REINDEX on PostgreSQL

ORACLE

Remove 59 025 archived cases (corresponds to 354 150 lines in the database)
Total Time: 66 minutes

SELECT
	table_name,
	table_rows,
	round(((data_length + index_length) / 1024 / 1024), 2) 'size MB'
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = 'bonita'
order by
	table_name ASC ;
segment_name            Size in MB     	NUM_ROWS
------------------------------------------------
ARCH_FLOWNODE_INSTANCE	408             2104224
ARCH_PROCESS_INSTANCE	72              578238
ARCH_DATA_INSTANCE      72              470169
QUERIABLE_LOG           128             389695
ARCH_CONTRACT_DATA      96              182639
ARCH_CONNECTOR_INSTANCE	18              168844
DOCUMENT                56              164716
ARCH_PROCESS_COMMENT	22              163844
ARCH_REF_BIZ_DATA_INST	15              148158
ARCH_DOCUMENT_MAPPING	9               133459
JOB_PARAM               13              87620
ARCH_MULTI_BIZ_DATA     2               74079
PROCESS_COMMENT         8               43764
MESSAGE_INSTANCE        4               28956
DOCUMENT_MAPPING        2               28542
DATA_INSTANCE           5               28408

Initial data volume

select segment_name, bytes/1024/1024 AS MB, NUM_ROWS
from dba_segments
JOIN all_tables ON dba_segments.segment_name = all_tables.table_name
where segment_type='TABLE' and dba_segments.OWNER ='BONITA'
ORDER BY NUM_ROWS DESC;
segment_name            Size in MB     	NUM_ROWS
------------------------------------------------
ARCH_FLOWNODE_INSTANCE	408             2104224
ARCH_PROCESS_INSTANCE	72              578238
ARCH_DATA_INSTANCE      72              470169
QUERIABLE_LOG           128             389695
ARCH_CONTRACT_DATA      96              182639
ARCH_CONNECTOR_INSTANCE	18              168844
DOCUMENT                56              164716
ARCH_PROCESS_COMMENT	22              163844
ARCH_REF_BIZ_DATA_INST	15              148158
ARCH_DOCUMENT_MAPPING	9               133459
JOB_PARAM               13              87620
ARCH_MULTI_BIZ_DATA     2               74079
PROCESS_COMMENT         8               43764
MESSAGE_INSTANCE        4               28956
DOCUMENT_MAPPING        2               28542
DATA_INSTANCE           5               28408

Execution

╰─$ bin/bonita-purge-tool 5488089572307653177 1584631356000                                                                                                                                            2 ↵
  ____              _ _                                         _              _
 |  _ \            (_) |                                       | |            | |
 | |_) | ___  _ __  _| |_ __ _    _ __  _   _ _ __ __ _  ___   | |_ ___   ___ | |
 |  _ < / _ \| '_ \| | __/ _` |  | '_ \| | | | '__/ _` |/ _ \  | __/ _ \ / _ \| |
 | |_) | (_) | | | | | || (_| |  | |_) | |_| | | | (_| |  __/  | || (_) | (_) | |
 |____/ \___/|_| |_|_|\__\__,_|  | .__/ \__,_|_|  \__, |\___|   \__\___/ \___/|_|
                                 | |               __/ |
                                 |_|              |___/
2020-03-19 16:23:01,725 INFO  Starting ApplicationKt on manu-DellXPS with PID 13151 (/home/manu/workspace/bonita-purge-tool/build/bonita-purge-tool/lib/bonita-purge-tool.jar started by manu in /home/manu/workspace/bonita-purge-tool/build/bonita-purge-tool)
2020-03-19 16:23:01,727 DEBUG Running with Spring Boot v2.1.6.RELEASE, Spring v5.1.8.RELEASE
2020-03-19 16:23:01,728 INFO  No active profile set, falling back to default profiles: default
2020-03-19 16:23:02,809 INFO  Using datasource with HikariDataSource
2020-03-19 16:23:02,933 INFO  Started ApplicationKt in 1.46 seconds (JVM running for 1.919)
2020-03-19 16:23:03,425 INFO  Database URL is jdbc:oracle:thin:@//localhost:1521/ORCLPDB1.localdomain
2020-03-19 16:23:03,425 INFO  Tenant id used is 1
2020-03-19 16:23:03,425 INFO  All settings can be changed in application.properties file
2020-03-19 16:23:03,481 INFO  Will purge all archived process instances and their elements for process 'All Kind Of Elements Auto' in version '1.1' that are finished since at least 2020-03-19T16:22:36
Start the purge using the above parameters? [y/N]
y
2020-03-19 16:23:09,354 INFO  Starting archive process instance purge...
2020-03-19 16:23:09,366 DEBUG Executing SQL: CREATE INDEX idx_rootprocid_archprocinst_tmp ON arch_process_instance(rootprocessinstanceid)
2020-03-19 16:23:11,223 DEBUG SQL command executed in 1856 ms
2020-03-19 16:23:11,224 DEBUG Executing SQL: DELETE FROM ARCH_PROCESS_INSTANCE A WHERE exists ( SELECT rootprocessinstanceid FROM ARCH_PROCESS_INSTANCE B WHERE B.ROOTPROCESSINSTANCEID = B.SOURCEOBJECTID AND A.ROOTPROCESSINSTANCEID = B.ROOTPROCESSINSTANCEID AND PROCESSDEFINITIONID = ? and (STATEID = 6 OR STATEID = 3 OR STATEID = 4) AND ENDDATE <= ?) AND tenantId = ?
2020-03-19 16:29:38,125 INFO  Deleted 354150 rows from table ARCH_PROCESS_INSTANCE in 386901 ms
2020-03-19 16:29:38,126 DEBUG Executing SQL: DELETE FROM ARCH_CONTRACT_DATA a WHERE a.KIND = 'PROCESS' AND a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_PROCESS_INSTANCE b WHERE a.SCOPEID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 16:31:03,463 INFO  Deleted 39350 rows from table ARCH_CONTRACT_DATA in 85337 ms
2020-03-19 16:31:03,463 DEBUG Executing SQL: DELETE FROM ARCH_DATA_INSTANCE a WHERE a.CONTAINERTYPE = 'PROCESS_INSTANCE' AND a.tenantId = ? AND NOT EXISTS ( SELECT id FROM ARCH_PROCESS_INSTANCE b WHERE a.CONTAINERID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 16:35:25,170 INFO  Deleted 154056 rows from table ARCH_DATA_INSTANCE in 261706 ms
2020-03-19 16:35:25,171 DEBUG Executing SQL: DELETE FROM ARCH_DOCUMENT_MAPPING a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_PROCESS_INSTANCE b WHERE a.PROCESSINSTANCEID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 16:36:54,060 INFO  Deleted 78700 rows from table ARCH_DOCUMENT_MAPPING in 88889 ms
2020-03-19 16:36:54,060 DEBUG Executing SQL: DELETE FROM ARCH_FLOWNODE_INSTANCE a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT id FROM ARCH_PROCESS_INSTANCE b WHERE a.ROOTCONTAINERID = b.ROOTPROCESSINSTANCEID AND b.tenantId = ?)
2020-03-19 17:25:07,584 INFO  Deleted 1167526 rows from table ARCH_FLOWNODE_INSTANCE in 2893524 ms
2020-03-19 17:25:07,585 DEBUG Executing SQL: DELETE FROM ARCH_PROCESS_COMMENT a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_PROCESS_INSTANCE b WHERE a.PROCESSINSTANCEID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 17:25:07,822 INFO  Deleted 0 rows from table ARCH_PROCESS_COMMENT in 237 ms
2020-03-19 17:25:07,823 DEBUG Executing SQL: DELETE FROM ARCH_REF_BIZ_DATA_INST a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_PROCESS_INSTANCE b WHERE a.ORIG_PROC_INST_ID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 17:27:39,818 INFO  Deleted 78700 rows from table ARCH_REF_BIZ_DATA_INST in 151995 ms
2020-03-19 17:27:39,819 DEBUG Executing SQL: DELETE FROM ARCH_CONNECTOR_INSTANCE a WHERE a.CONTAINERTYPE = 'process' AND a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_PROCESS_INSTANCE b WHERE a.CONTAINERID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 17:27:39,885 INFO  Deleted 0 rows from table ARCH_CONNECTOR_INSTANCE in 66 ms
2020-03-19 17:27:39,885 DEBUG Executing SQL: DELETE FROM ARCH_CONTRACT_DATA a WHERE a.KIND = 'TASK' AND a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_FLOWNODE_INSTANCE b WHERE a.SCOPEID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 17:27:40,388 INFO  Deleted 0 rows from table ARCH_CONTRACT_DATA in 503 ms
2020-03-19 17:27:40,388 DEBUG Executing SQL: DELETE FROM ARCH_DATA_INSTANCE a WHERE a.CONTAINERTYPE = 'ACTIVITY_INSTANCE' AND a.tenantId = ? AND NOT EXISTS ( SELECT id FROM ARCH_FLOWNODE_INSTANCE b WHERE a.CONTAINERID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 17:27:41,044 INFO  Deleted 0 rows from table ARCH_DATA_INSTANCE in 656 ms
2020-03-19 17:27:41,045 DEBUG Executing SQL: DELETE FROM ARCH_CONNECTOR_INSTANCE a WHERE a.CONTAINERTYPE = 'flowNode' AND a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM ARCH_FLOWNODE_INSTANCE b WHERE a.CONTAINERID = b.SOURCEOBJECTID AND b.tenantId = ?)
2020-03-19 17:29:12,637 INFO  Deleted 75564 rows from table ARCH_CONNECTOR_INSTANCE in 91592 ms
2020-03-19 17:29:12,821 DEBUG Executing SQL: DROP INDEX idx_rootprocid_archprocinst_tmp
2020-03-19 17:29:13,054 DEBUG SQL command executed in 233 ms
2020-03-19 17:29:13,054 INFO  Archive process instance purge completed.
2020-03-19 17:29:13,055 INFO  Some of the deleted elements may still appear in Bonita applications for a short while.
2020-03-19 17:29:13,055 INFO  If you try to access them you will get a not found error. This is the expected behaviour.
2020-03-19 17:29:13,055 INFO  Execution completed in 3970120 ms

MS SQL Server

Remove 112 396 archived process instances (corresponds to 674 379 lines in the database)
Total Time: 5m30s

Initial data volume

SELECT t.Name AS TableName, p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
Where used_pages <> 0
GROUP BY t.Name, s.Name, p.Rows
ORDER BY Used_MB DESC
TableName
|RowCounts|Used_MB|
------------------------|---------|-------|
arch_flownode_instance  |2 948 801|2227.38|
document                |   227454| 988.68|
arch_data_instance      |   682875| 385.91|
arch_process_instance   |  679 506| 321.69|
queriable_log           |   569821| 293.39|
arch_process_comment    |   449944| 188.71|
arch_contract_data      |   341431| 185.84|
arch_ref_biz_data_inst  |   224972|  71.98|
arch_connector_instance |   227612|  70.97|
flownode_instance       |     1498|  46.52|
arch_document_mapping   |   226026|  42.99|
process_comment         |     5286|  13.87|
data_instance           |     1334|   8.64|
process_instance        |     1428|   8.45|
ref_biz_data_inst       |     2666|   7.89|
page                    |       24|   7.88|
arch_multi_biz_data     |   112486|   6.70|

Execution

╰─$ bin/bonita-purge-tool 6186406801545861394 1584703344000                                                                                                                                                  130 ↵
  ____              _ _                                         _              _
 |  _ \            (_) |                                       | |            | |
 | |_) | ___  _ __  _| |_ __ _    _ __  _   _ _ __ __ _  ___   | |_ ___   ___ | |
 |  _ < / _ \| '_ \| | __/ _` |  | '_ \| | | | '__/ _` |/ _ \  | __/ _ \ / _ \| |
 | |_) | (_) | | | | | || (_| |  | |_) | |_| | | | (_| |  __/  | || (_) | (_) | |
 |____/ \___/|_| |_|_|\__\__,_|  | .__/ \__,_|_|  \__, |\___|   \__\___/ \___/|_|
                                 | |               __/ |
                                 |_|              |___/
2020-03-20 12:23:40,671 INFO  Starting ApplicationKt on manu-DellXPS with PID 2760 (/home/manu/workspace/bonita-purge-tool/build/bonita-purge-tool/lib/bonita-purge-tool.jar started by manu in /home/manu/workspace/bonita-purge-tool/build/bonita-purge-tool)
2020-03-20 12:23:40,673 DEBUG Running with Spring Boot v2.1.6.RELEASE, Spring v5.1.8.RELEASE
2020-03-20 12:23:40,673 INFO  No active profile set, falling back to default profiles: default
2020-03-20 12:23:41,705 INFO  Using datasource with HikariDataSource
2020-03-20 12:23:41,830 INFO  Started ApplicationKt in 1.407 seconds (JVM running for 1.872)
2020-03-20 12:23:42,078 INFO  Database URL is jdbc:sqlserver://localhost:1433;database=bonita
2020-03-20 12:23:42,078 INFO  Tenant id used is 1
2020-03-20 12:23:42,078 INFO  All settings can be changed in application.properties file
2020-03-20 12:23:42,186 INFO  Will purge all archived process instances and their elements for process 'All Kind Of Elements' in version '1.2' that are finished since at least 2020-03-20T12:22:24
Start the purge using the above parameters? [y/N]
y
2020-03-20 12:24:24,225 INFO  Starting archive process instance purge...
2020-03-20 12:24:24,245 DEBUG Executing SQL: CREATE INDEX idx_rootprocid_archprocinst_tmp ON arch_process_instance(rootprocessinstanceid)
2020-03-20 12:24:26,432 DEBUG SQL command executed in 2187 ms
2020-03-20 12:24:26,433 DEBUG Executing SQL: DELETE A FROM arch_process_instance A INNER JOIN arch_process_instance B ON A.rootprocessinstanceid = B.rootprocessinstanceid WHERE B.rootprocessinstanceid = B.sourceobjectid AND B.processdefinitionid = ? AND (B.stateid = 6 OR B.stateid = 3 OR B.stateid = 4) AND B.enddate <= ? AND B.tenantId = ?
2020-03-20 12:24:48,774 INFO  Deleted 674379 rows from table ARCH_PROCESS_INSTANCE in 22341 ms
2020-03-20 12:24:48,775 DEBUG Executing SQL: DELETE a FROM arch_contract_data as a WHERE a.kind = 'PROCESS' AND a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE a.scopeid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:24:52,769 INFO  Deleted 112486 rows from table arch_contract_data in 3994 ms
2020-03-20 12:24:52,769 DEBUG Executing SQL: DELETE a FROM arch_data_instance as a WHERE a.containertype = 'PROCESS_INSTANCE' AND a.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE a.containerid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:25:14,468 INFO  Deleted 449942 rows from table arch_data_instance in 21699 ms
2020-03-20 12:25:14,468 DEBUG Executing SQL: DELETE a FROM arch_document_mapping as a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE a.processinstanceid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:25:17,323 INFO  Deleted 224793 rows from table arch_document_mapping in 2854 ms
2020-03-20 12:25:17,324 DEBUG Executing SQL: DELETE a FROM arch_flownode_instance as a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE a.rootcontainerid = b.rootprocessinstanceid AND b.tenantId = ?)
2020-03-20 12:28:14,781 INFO  Deleted 2914573 rows from table arch_flownode_instance in 177457 ms
2020-03-20 12:28:14,781 DEBUG Executing SQL: DELETE a FROM arch_process_comment as a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_process_instance b WHERE a.processinstanceid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:28:23,173 INFO  Deleted 449944 rows from table arch_process_comment in 8392 ms
2020-03-20 12:28:23,174 DEBUG Executing SQL: DELETE a FROM arch_ref_biz_data_inst as a WHERE a.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE a.orig_proc_inst_id = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:28:34,153 INFO  Deleted 224972 rows from table arch_ref_biz_data_inst in 10979 ms
2020-03-20 12:28:34,153 DEBUG Executing SQL: DELETE a FROM arch_connector_instance as a WHERE a.containertype = 'process' AND a.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_process_instance b WHERE a.containerid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:28:34,701 INFO  Deleted 0 rows from table arch_connector_instance in 548 ms
2020-03-20 12:28:34,701 DEBUG Executing SQL: DELETE a FROM arch_contract_data as a WHERE a.KIND = 'TASK' AND a.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_flownode_instance b WHERE a.scopeid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:28:45,360 INFO  Deleted 224972 rows from table arch_contract_data in 10659 ms
2020-03-20 12:28:45,360 DEBUG Executing SQL: DELETE a FROM arch_data_instance as a WHERE a.containertype = 'ACTIVITY_INSTANCE' AND a.tenantId = ? AND NOT EXISTS ( SELECT id FROM arch_flownode_instance b WHERE a.containerid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:29:06,026 INFO  Deleted 224972 rows from table arch_data_instance in 20666 ms
2020-03-20 12:29:06,027 DEBUG Executing SQL: DELETE a FROM arch_connector_instance a WHERE a.containertype = 'flowNode' AND a.tenantId = ? AND NOT EXISTS ( SELECT ID FROM arch_flownode_instance b where a.containerid = b.sourceobjectid AND b.tenantId = ?)
2020-03-20 12:29:12,747 INFO  Deleted 224972 rows from table arch_connector_instance in 6719 ms
2020-03-20 12:29:12,817 DEBUG Executing SQL: DROP INDEX IF EXISTS idx_rootprocid_archprocinst_tmp ON arch_process_instance
2020-03-20 12:29:12,822 DEBUG SQL command executed in 5 ms
2020-03-20 12:29:12,822 INFO  Archive process instance purge completed.
2020-03-20 12:29:12,823 INFO  Some of the deleted elements may still appear in Bonita applications for a short while.
2020-03-20 12:29:12,823 INFO  If you try to access them you will get a not found error. This is the expected behaviour.
2020-03-20 12:29:12,823 INFO  Execution completed in 330991 ms
2020-03-20 12:29:12,823 INFO  According to the database type you use, you may need to execute certain maintenance commands
2020-03-20 12:29:12,823 INFO  to reclaim space or optimize the newly purged tables.
2020-03-20 12:29:12,823 INFO  Eg. VACUUM REINDEX on PostgreSQL