This is an automated email from the ASF dual-hosted git repository.
leonbao pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/dev by this push:
new ed9fca6 [Bug] [dolphinscheduler-api] The pages of project management,
workflow instance and task instance are accessed slowly #7061 (#7139)
ed9fca6 is described below
commit ed9fca6c976761abcf4cef2656b61516fd976ad0
Author: GaoTianDuo <[email protected]>
AuthorDate: Sat Dec 4 13:13:30 2021 +0800
[Bug] [dolphinscheduler-api] The pages of project management, workflow
instance and task instance are accessed slowly #7061 (#7139)
1、I found that the SQL behind this interface is very slow. The main table
is large, but there is no index to use. By add the index, the all mode in the
query plan can be changed into a more efficient ref mode. If this optimization
still fails to meet the requirements, please contact me and I will continue to
optimize。
2、modify index start_time_index in init script and upgrade scripts.
---
dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql | 2 +-
.../src/main/resources/sql/dolphinscheduler_postgre.sql | 2 +-
.../resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql | 2 ++
.../sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql | 3 ++-
4 files changed, 6 insertions(+), 3 deletions(-)
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
index 949959b..aac2429 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
@@ -600,7 +600,7 @@ CREATE TABLE `t_ds_process_instance` (
`next_process_instance_id` int(11) DEFAULT '0' COMMENT 'serial queue next
processInstanceId',
PRIMARY KEY (`id`),
KEY `process_instance_index` (`process_definition_code`,`id`) USING BTREE,
- KEY `start_time_index` (`start_time`) USING BTREE
+ KEY `start_time_index` (`start_time`,`end_time`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgre.sql
b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgre.sql
index de74540..0c489cf 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgre.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgre.sql
@@ -507,7 +507,7 @@ CREATE TABLE t_ds_process_instance (
) ;
create index process_instance_index on t_ds_process_instance
(process_definition_code,id);
-create index start_time_index on t_ds_process_instance (start_time);
+create index start_time_index on t_ds_process_instance (start_time,end_time);
--
-- Table structure for table t_ds_project
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
index d53a9c1..e57e13c 100644
---
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
+++
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
@@ -387,6 +387,8 @@ alter table t_ds_process_instance add var_pool longtext
COMMENT 'var_pool' AFTER
alter table t_ds_process_instance add dry_run tinyint(4) DEFAULT '0' COMMENT
'dry run flag：0 normal, 1 dry run' AFTER var_pool;
alter table t_ds_process_instance drop KEY `process_instance_index`;
alter table t_ds_process_instance add KEY `process_instance_index`
(`process_definition_code`,`id`) USING BTREE;
+alter table t_ds_process_instance drop KEY `start_time_index`;
+alter table t_ds_process_instance add KEY `start_time_index`
(`start_time`,`end_time`) USING BTREE;
alter table t_ds_process_instance drop process_instance_json;
alter table t_ds_process_instance drop locations;
alter table t_ds_process_instance drop connects;
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
index 3c9e4a0..b398684 100644
---
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
+++
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
@@ -98,11 +98,12 @@ BEGIN
--- drop index
EXECUTE 'DROP INDEX IF EXISTS "process_instance_index"';
EXECUTE 'DROP INDEX IF EXISTS "task_instance_index"';
+ EXECUTE 'DROP INDEX IF EXISTS "start_time_index"';
--- create index
EXECUTE 'CREATE INDEX IF NOT EXISTS priority_id_index ON ' ||
quote_ident(v_schema) ||'.t_ds_command USING
Btree("process_instance_priority","id")';
EXECUTE 'CREATE INDEX IF NOT EXISTS process_instance_index ON ' ||
quote_ident(v_schema) ||'.t_ds_process_instance USING
Btree("process_definition_code","id")';
-
+ EXECUTE 'CREATE INDEX IF NOT EXISTS start_time_index ON ' ||
quote_ident(v_schema) ||'.t_ds_process_instance USING
Btree("start_time","end_time")';
---add comment
EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_user.state is ''state 0:disable 1:enable''';
