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

问题背景

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

（1）mysql开启慢日志

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

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

复制代码

（2）慢日志分析

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

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

复制代码

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

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

复制代码

（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
  

1. -> Limit: 1 row(s) (cost=0.20 rows=0) (actual time=103.391..103.391 rows=1 loops=1)
2.     -> 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)
3.         -> 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)

复制代码

1. -> Limit: 10 row(s) (actual time=3601.141..3601.147 rows=10 loops=1)
2.     -> 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)
3.         -> Table scan on <temporary> (cost=0.01..3774.21 rows=301738) (actual time=0.002..190.179 rows=722743 loops=1)
4.             -> Temporary table (cost=469419.96..473194.16 rows=301738) (actual time=3332.896..3551.716 rows=722743 loops=1)
5.                 -> Nested loop left join (cost=439246.15 rows=301738) (actual time=0.051..1431.254 rows=722743 loops=1)
6.                     -> Nested loop inner join (cost=107334.40 rows=301738) (actual time=0.045..1239.699 rows=722743 loops=1)
7.                         -> Filter: (define.id is not null) (cost=12.40 rows=121) (actual time=0.025..0.162 rows=121 loops=1)
8.                             -> 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)
9.                         -> 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)
10.                     -> 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)

复制代码

• 比对是否备份成功
  

1. INSERT into t_ds_process_instance_copy select * from t_ds_process_instance WHERE start_time < '2022-01-01 00:00:00';

复制代码

• 删除历史数据
  

1. INSERT into t_ds_task_instance_copy select * from t_ds_task_instance WHERE start_time < '2022-01-01 00:00:00';

复制代码

（2）参数优化

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

1. select count(*) from t_ds_process_instance_copy;
3. select count(*) from t_ds_process_instance where start_time < '2022-01-01 00:00:00';
5. select count(*) from t_ds_task_instance_copy;
7. select count(*) from t_ds_task_instance where start_time < '2022-01-01 00:00:00';

复制代码

（3）索引优化

1. DELETE FROM t_ds_process_instance WHERE start_time < '2022-01-01 00:00:00';
3. DELETE FROM t_ds_task_instance WHERE start_time < '2022-01-01 00:00:00';

复制代码

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

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

来源：豆瓜网用户自行投稿发布，如果侵权，请联系站长删除