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 Portal 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 Portal 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 Portal 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 Portal 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