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';

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

A 10-Minute Deep Dive Into the Core Architecture of Apache SeaTunnel and DataX

How to Seamlessly Transfer From Airflow to Apache DolphinScheduler With Air2phin

A Comprehensive Guide to Building DolphinScheduler 3.2.0 Production-Grade Cluster Deployment

How to Regularly Delete Log Instances in DolphinScheduler

How to Enable Auto-Start for Apache DolphinScheduler

A 10-Minute Deep Dive Into the Core Architecture of Apache SeaTunnel and DataX

How to Seamlessly Transfer From Airflow to Apache DolphinScheduler With Air2phin

A Comprehensive Guide to Building DolphinScheduler 3.2.0 Production-Grade Cluster Deployment

How to Regularly Delete Log Instances in DolphinScheduler

How to Enable Auto-Start for Apache DolphinScheduler

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps