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 databasechunk 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 0Execution
╰─$./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 PostgreSQLORACLE
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 28408Initial 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 28408Execution
╰─$ 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 msMS 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 DESCTableName
|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