Ensure Page Smoothness With This DolphinScheduler Task Data Cleanup and Backup Strategy

As Apache DolphinScheduler runs for an extended period, the number of tasks continues to increase. The task data is primarily stored in the t_ds_task_instance and t_ds_process_instance tables in the database. The continuous growth of data in these two tables leads to system page lag. Solution To address the above issue, the measure taken is to regularly clean up data in the t_ds_process_instance and t_ds_task_instance tables that is more than one month old. Data Backup Before cleaning up the data, first back up the original table data to ensure data safety. use dolphinscheduler; -- Create backup tables t_ds_process_instance_backup20241120 and t_ds_task_instance_backup20241120 CREATE TABLE t_ds_process_instance_backup20241120 LIKE t_ds_process_instance; CREATE TABLE t_ds_task_instance_backup20241120 LIKE t_ds_task_instance; -- Backup the original table data into the corresponding backup tables INSERT INTO t_ds_process_instance_backup20241120 SELECT * FROM t_ds_process_instance; INSERT INTO t_ds_task_instance_backup20241120 SELECT * FROM t_ds_task_instance; Check Backup Status To ensure the backup operation is successful, you can check the data row count of the backup tables and the original tables. -- Check the data row count of the backup tables SELECT COUNT(*) FROM t_ds_process_instance_backup20241120; SELECT COUNT(*) FROM t_ds_task_instance_backup20241120; -- Check the data row count of the original tables SELECT COUNT(*) FROM t_ds_process_instance; SELECT COUNT(*) FROM t_ds_task_instance; Data Cleanup After the backup is completed and confirmed to be error-free, perform the cleanup operation to delete data before October 19, 2024, 23:59:59. -- Delete data before October 19, 2024, 23:59:59 in the t_ds_task_instance table DELETE FROM t_ds_task_instance WHERE submit_time < '2024-10-19 23:59:59'; -- Delete data before October 19, 2024, 23:59:59 in the t_ds_process_instance table DELETE FROM t_ds_process_instance WHERE end_time < '2024-10-19 23:59:59'; As Apache DolphinScheduler runs for an extended period, the number of tasks continues to increase. The task data is primarily stored in the t_ds_task_instance and t_ds_process_instance tables in the database. The continuous growth of data in these two tables leads to system page lag. t_ds_task_instance t_ds_process_instance Solution To address the above issue, the measure taken is to regularly clean up data in the t_ds_process_instance and t_ds_task_instance tables that is more than one month old. t_ds_process_instance t_ds_task_instance Data Backup Before cleaning up the data, first back up the original table data to ensure data safety. use dolphinscheduler; -- Create backup tables t_ds_process_instance_backup20241120 and t_ds_task_instance_backup20241120 CREATE TABLE t_ds_process_instance_backup20241120 LIKE t_ds_process_instance; CREATE TABLE t_ds_task_instance_backup20241120 LIKE t_ds_task_instance; -- Backup the original table data into the corresponding backup tables INSERT INTO t_ds_process_instance_backup20241120 SELECT * FROM t_ds_process_instance; INSERT INTO t_ds_task_instance_backup20241120 SELECT * FROM t_ds_task_instance; use dolphinscheduler; -- Create backup tables t_ds_process_instance_backup20241120 and t_ds_task_instance_backup20241120 CREATE TABLE t_ds_process_instance_backup20241120 LIKE t_ds_process_instance; CREATE TABLE t_ds_task_instance_backup20241120 LIKE t_ds_task_instance; -- Backup the original table data into the corresponding backup tables INSERT INTO t_ds_process_instance_backup20241120 SELECT * FROM t_ds_process_instance; INSERT INTO t_ds_task_instance_backup20241120 SELECT * FROM t_ds_task_instance; Check Backup Status To ensure the backup operation is successful, you can check the data row count of the backup tables and the original tables. -- Check the data row count of the backup tables SELECT COUNT(*) FROM t_ds_process_instance_backup20241120; SELECT COUNT(*) FROM t_ds_task_instance_backup20241120; -- Check the data row count of the original tables SELECT COUNT(*) FROM t_ds_process_instance; SELECT COUNT(*) FROM t_ds_task_instance; -- Check the data row count of the backup tables SELECT COUNT(*) FROM t_ds_process_instance_backup20241120; SELECT COUNT(*) FROM t_ds_task_instance_backup20241120; -- Check the data row count of the original tables SELECT COUNT(*) FROM t_ds_process_instance; SELECT COUNT(*) FROM t_ds_task_instance; Data Cleanup After the backup is completed and confirmed to be error-free, perform the cleanup operation to delete data before October 19, 2024, 23:59:59. -- Delete data before October 19, 2024, 23:59:59 in the t_ds_task_instance table DELETE FROM t_ds_task_instance WHERE submit_time < '2024-10-19 23:59:59'; -- Delete data before October 19, 2024, 23:59:59 in the t_ds_process_instance table DELETE FROM t_ds_process_instance WHERE end_time < '2024-10-19 23:59:59'; -- Delete data before October 19, 2024, 23:59:59 in the t_ds_task_instance table DELETE FROM t_ds_task_instance WHERE submit_time < '2024-10-19 23:59:59'; -- Delete data before October 19, 2024, 23:59:59 in the t_ds_process_instance table DELETE FROM t_ds_process_instance WHERE end_time < '2024-10-19 23:59:59';