DolphinScheduler 调度变慢？试试这些数据库性能优化策略

2025年6月26日
| 没有评论

问题背景

DolphinScheduler 1.3.9版本

查询任务实例列表等接口时，有时会出现响应慢的情况，超过20秒才返回结果。

问题诊断

（1）mysql开启慢日志

/etc/mysql.cnf添加如下配置参数

slow_query_log = ON 
slow_query_log_file = /data/log/mysql/mysql-slow.log 
long_query_time = 2

（2）慢日志分析

日志中以下几类sql超时频率较高：

select * from t_ds_process_instance 
where 1=1 and state in ( 0 , 1 , 2 , 4 ) and process_definition_id=71 
and (schedule_time >= '2022-07-20 00:00:00' and schedule_time <= '2022-07-20 23:59:59.999' 
or start_time >= '2022-07-20 00:00:00' and start_time <= '2022-07-20 23:59:59.999') 
order by start_time desc limit 1;

select instance.*,process.name as process_instance_name from t_ds_task_instance instance 
left join t_ds_process_definition define on instance.process_definition_id = define.id 
left join t_ds_process_instance process on process.id=instance.process_instance_id 
where define.project_id = 6 order by instance.start_time desc LIMIT 0,10;

主要涉及t_ds_process_instance和t_ds_task_instance两张表。

通过explain analyze命令分析，可以发现第一类sql使用start_time_index这个索引查询的过程中耗时较多，start_time_index区分度较差，需要有更好区分度的索引来提升查询速度；

-> Limit: 1 row(s) (cost=0.20 rows=0) (actual time=103.391..103.391 rows=1 loops=1) 
    -> Filter: ((t_ds_process_instance.process_definition_id = 18) and (((t_ds_process_instance.schedule_time >= TIMESTAMP'2022-07-18 00:00:00') and (t_ds_process_instance.schedule_time <= TIMESTAMP'2022-07-18 23:59:59.999')) or ((t_ds_process_instance.start_time >= TIMESTAMP'2022-07-18 00:00:00') and (t_ds_process_instance.start_time <= TIMESTAMP'2022-07-18 23:59:59.999')))) (cost=0.20 rows=0) (actual time=103.391..103.391 rows=1 loops=1) 
        -> Index scan on t_ds_process_instance using start_time_index (reverse) (cost=0.20 rows=2) (actual time=0.030..103.165 rows=1239 loops=1)

第二类sql主要耗时在left join阶段，需要更新索引字段

-> Limit: 10 row(s) (actual time=3601.141..3601.147 rows=10 loops=1) 
    -> Sort row IDs: `instance`.start_time DESC, limit input to 10 row(s) per chunk (actual time=3601.140..3601.145 rows=10 loops=1) 
        -> Table scan on <temporary> (cost=0.01..3774.21 rows=301738) (actual time=0.002..190.179 rows=722743 loops=1) 
            -> Temporary table (cost=469419.96..473194.16 rows=301738) (actual time=3332.896..3551.716 rows=722743 loops=1) 
                -> Nested loop left join (cost=439246.15 rows=301738) (actual time=0.051..1431.254 rows=722743 loops=1) 
                    -> Nested loop inner join (cost=107334.40 rows=301738) (actual time=0.045..1239.699 rows=722743 loops=1) 
                        -> Filter: (define.id is not null) (cost=12.40 rows=121) (actual time=0.025..0.162 rows=121 loops=1) 
                            -> Index lookup on define using process_definition_index (project_id=6) (cost=12.40 rows=121) (actual time=0.025..0.110 rows=121 loops=1) 
                        -> Index lookup on instance using task_instance_index (process_definition_id=define.id) (cost=639.65 rows=2494) (actual time=0.006..9.972 rows=5973 loops=121) 
                    -> Single-row index lookup on process using PRIMARY (id=`instance`.process_instance_id) (cost=1.00 rows=1) (actual time=0.000..0.000 rows=1 loops=722743)

（3）问题定位

  • mysql配置未优化
    当前使用的mysql配置均为默认配置，对查询影响较大的配置项如：

innodb_buffer_pool_size（InnoDB使用一个缓冲池来保存索引和原始数据）。

innodb_thread_concurrency（设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量）

innodb_write_io_threads & innodb_read_io_threads （innodb使用后台线程处理数据页上的读写 I/O请求）
需要调整以上参数，提高mysql性能。

  • DolphinScheduler索引未优化
    当前慢查询报出的SQL的索引需要优化。

  • DolphinScheduler单表数据较大
    当前慢查询涉及的SQL表（t_ds_task_instance）较大，数据大小超过4G，数据条数超过200w，需要切分归档。

优化内容

（1）大表归档

  • 创建备份表

根据t_ds_process_instance DDL建表语句复制表t_ds_process_instance_bak。

根据t_ds_task_instance DDL建表语句复制表 t_ds_task_instance_bak。

注：外键行删除

CONSTRAINT foreign_key_instance_id FOREIGN KEY (process_instance_id) REFERENCES t_ds_process_instance (id) ON DELETE CASCADE ON UPDATE RESTRICT

  • 将归档日期以前的数据从两张表中挪至备份表，如2022-01-01 00:00:00
INSERT into t_ds_process_instance_copy select * from t_ds_process_instance WHERE start_time < '2022-01-01 00:00:00';

INSERT into t_ds_task_instance_copy select * from t_ds_task_instance WHERE start_time < '2022-01-01 00:00:00';
  • 比对是否备份成功
select count(*) from t_ds_process_instance_copy;
 
select count(*) from t_ds_process_instance where start_time < '2022-01-01 00:00:00';
 
select count(*) from t_ds_task_instance_copy;
 
select count(*) from t_ds_task_instance where start_time < '2022-01-01 00:00:00';
  • 删除历史数据
DELETE FROM t_ds_process_instance WHERE start_time < '2022-01-01 00:00:00';
 
DELETE FROM t_ds_task_instance WHERE start_time < '2022-01-01 00:00:00';

（2）参数优化

/etc/my.cnf mysql配置文件中调整如下参数并重启mysql

innodb_log_buffer_size= 64M
 
innodb_buffer_pool_size= 20G //（根据服务器内存适当调整）如果数据库独占一台机器则设置物理内存的70%
 
innodb_log_file_size= 1G
 
innodb_thread_concurrency = 0
 
join_buffer_size = 64M
 
sort_buffer_size = 64M
 
innodb_read_io_threads = 16 //（根据服务器CPU适当调整）
 
innodb_write_io_threads = 16 //（根据服务器CPU适当调整）

（3）索引优化

create index state_index on t_ds_process_instance(state, process_definition_id);
 
create index start_time_process_definition_index on t_ds_task_instance(start_time, process_definition_id);
 
alter table t_ds_task_instance drop index task_instance_index;

转载自daozi126
原文链接：https://blog.csdn.net/u012938208/article/details/147899920

本文由 白鲸开源 提供发布支持！

数据库