XIJIU123 commented on issue #15921:
URL:
https://github.com/apache/dolphinscheduler/issues/15921#issuecomment-2232667686
## sql performance test
| type | interfaces
| Approximate average length | sql
| remark |
| ------------- |
------------------------------------------------------------ |
-------------------------- |
:----------------------------------------------------------- |
------------------------------------------------------------ |
| numeric value | Number of projects
| 100ms | SELECT COUNT(*) from (
select distinct project_id from t_ds_project
p,t_ds_relation_project_user rel where p.id = rel.project_id and
rel.user_id= 2 UNION ALL select distinct id from
t_ds_project where user_id= 2 ) result; |
|
| numeric value | Total workflows, number of online workflows
| 100ms | select release_state as proc_status,count(*)
as proc_count from t_ds_process_definition group by
release_state; | |
| numeric value | The number of online tasks
| 100ms | select count(distinct b.post_task_code) from
(select user_id,project_id from t_ds_relation_project_user where user_id=2
group by user_id,project_id)a join (select id,code from t_ds_project)c
on a.project_id=c.id join (select project_code,post_task_code from
t_ds_process_task_relation)b on c.code = b.project_code; |
|
| numeric value | The number of scheduled tasks, the number of successfully
scheduled tasks, and the number of tasks that were successfully scheduled
yesterday | 3.3s | select count(*) from
t_ds_process_instance instance, t_ds_process_definition define,
t_ds_task_instance tins, t_ds_project project where
instance.schedule_time is not null and instance.process_definition_code
= define.code and tins.process_instance_id = instance.id and
project.code = define.project_code and instance.schedule_time >
'2024-07-15 00:00:00' and instance.schedule_time < '2024-07-16
00:00:00'; | It takes about 300ms to query the number of scheduled tasks only,
and about 3s to calculate the number of tasks that should be scheduled. |
| manifest | Top 5 Tasks in Running Duration
| 200ms | select name, duration from (
select a.process_definition_code,
AVG(timestampdiff(MINUTE,a.start_time,a.end_time)) duration from
t_ds_process_instance a,t_ds_process_definition b, t_ds_project c
where a.schedule_time is not null and a.process_definition_code =
b.code and c.code = b.project_code and a.start_time
>='2024-07-15 00:00:00' and start_time <='2024-07-16 00:00:00'
group by a.process_definition_code order by duration desc,
process_definition_code asc limit 5 ) tmp left join
t_ds_process_definition c on c.code = tmp.process_definition_code |
|
| manifest | Top 5 Failed Tasks
| 200ms | select c.name, tmp.count from
( select a.process_definition_code,
count(*) count from t_ds_process_instance a,
t_ds_process_definition b, t_ds_project c where a.schedule_time is
not null and a.process_definition_code = b.code and
c.code = b.project_code and a.start_time >='2024-07-15 00:00:00'
and start_time <='2024-07-16 00:00:00' group by
a.process_definition_code order by count desc,
process_definition_code asc limit 5 ) tmp left join
t_ds_process_definition c on tmp.process_definition_code = c.code; |
|
| Trends | Task status trends
| 200ms | select name, hh,
sum(`sum`) as `value` from ( select n.id,
case state when 1 then '正在运行' when 5 then '停止' when 6 then
'失败' when 7 then '成功' else '其他' end as name, m.*
from ( select j.project_code, j.hh, j.state,
sum(j.cnt) as sum from ( select
b.project_code, a.state, a.hh, a.cnt from (
select task_code, state, hh, count(*) cnt
from( select task_code, state,
hour(DATE_ADD(start_time,INTERVAL 14 HOUR)) hh from
t_ds_task_instance where start_time >='2024-07-15 00:00:00'
and start_time <='2024-07-16 00:00:00' ) k group by
k.task_code, k.state, k.hh ) a left join
t_ds_task_definition b on a.task_code = b.code ) j where
j.project_code is not null group by j.project_code,
j.state, j.hh ) m left join t_ds_project n on
m.project_code = n.code ) p group by p.hh, p.name order by
p.hh; | A day's worth of statistics by hourly dimension. |
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
