DolphinScheduler Project Management Page Loads Slowly? Here's How to Optimize It
Problem Description Sometimes, the Apache DolphinScheduler project management page fails to load. When inspecting in the browser, the request [http://ip:12345/dolphinscheduler/projects?pageSize=10&pageNo=1&searchVal=] times out. Troubleshooting Steps 1. Check DolphinScheduler Logs (No Exceptions Found) tail /home/dolphinscheduler/api-server/logs/dolphinscheduler-api.log 2. Restart the DolphinScheduler api-server Module sh /home/dolphinscheduler/bin/dolphinscheduler-daemon.sh stop api-server sh /home/dolphinscheduler/bin/dolphinscheduler-daemon.sh start api-server Result: The page still fails to load. 3. Test the Timeout Request and Observe the Response Testing reveals that data can be returned, but it takes over ten seconds. 4. Check the Source Code for Potential Slow Queries Code location: 5. Use Arthas to Analyze Runtime Performance curl -O https://arthas.aliyun.com/arthas-boot.jar java -jar arthas-boot.jar # Identify the DolphinScheduler API process; in my case, it was the first one. Trace the runtime to find where the delay occurs. It was identified in the queryProjectListPaging method. trace org.apache.dolphinscheduler.api.service.impl.ProjectServiceImpl queryProjectListPaging -n 1 Inspecting the queryProjectListPaging method: 6. Run the SQL Query and Analyze Testing the SQL query reveals that it is not inherently slow. SELECT * FROM t_ds_project p WHERE 1=1 AND p.id IN (1, 2, 3, 5, 6, 8, 9, 12, 13, 14, 16, 17, 19, 21, 22, 23, 25, 26) GROUP BY p.id ORDER BY p.id DESC; Testing the SQL being executed by DolphinScheduler, using Arthas and the CTBots MyBatis SQL Parser: 7. Identify the Slow Query The slow SQL query was identified as follows: SELECT p.id, p.name, p.code, p.description, p.user_id, p.flag, p.create_time, p.update_time, u.user_name AS user_name, (SELECT COUNT(*) FROM t_ds_process_definition AS def WHERE def.project_code = p.code) AS def_count, (SELECT COUNT(*) FROM t_ds_process_definition_log def, t_ds_process_instance inst WHERE def.code = inst.process_definition_code AND def.version = inst.process_definition_version AND def.project_code = p.code AND inst.state = 1) AS inst_running_count FROM t_ds_project p LEFT JOIN t_ds_user u ON u.id = p.user_id WHERE 1=1 AND p.id IN (1, 2, 3, 5, 6, 8, 9, 12, 13, 14, 16, 17, 19, 21, 22, 23, 25, 26) GROUP BY p.id, u.user_name ORDER BY p.id DESC LIMIT 100; Analysis using EXPLAIN revealed that the bottleneck was in the inst_running_count subquery. The t_ds_process_definition table contains approximately 280,000 rows, while t_ds_process_definition_log has 1,000 rows. 8. Optimize the Query with Indexes Adding an index to the t_ds_process_instance table significantly improved performance: CREATE INDEX qwl_diy_index ON t_ds_process_instance (process_definition_code, process_definition_version, state); 9. Verify the Results After adding the index, the query execution time improved, and the page loaded correctly. By addressing the slow query with an appropriate index, the issue was resolved. If this article was helpful, feel free to like, bookmark, or share it!
Problem Description
Sometimes, the Apache DolphinScheduler project management page fails to load. When inspecting in the browser, the request [http://ip:12345/dolphinscheduler/projects?pageSize=10&pageNo=1&searchVal=] times out.
Troubleshooting Steps
1. Check DolphinScheduler Logs (No Exceptions Found)
tail /home/dolphinscheduler/api-server/logs/dolphinscheduler-api.log
2. Restart the DolphinScheduler api-server Module
sh /home/dolphinscheduler/bin/dolphinscheduler-daemon.sh stop api-server
sh /home/dolphinscheduler/bin/dolphinscheduler-daemon.sh start api-server
Result: The page still fails to load.
3. Test the Timeout Request and Observe the Response
Testing reveals that data can be returned, but it takes over ten seconds.
4. Check the Source Code for Potential Slow Queries
Code location:
5. Use Arthas to Analyze Runtime Performance
curl -O https://arthas.aliyun.com/arthas-boot.jar
java -jar arthas-boot.jar
# Identify the DolphinScheduler API process; in my case, it was the first one.
Trace the runtime to find where the delay occurs. It was identified in the queryProjectListPaging method.
trace org.apache.dolphinscheduler.api.service.impl.ProjectServiceImpl queryProjectListPaging -n 1
Inspecting the queryProjectListPaging method:
6. Run the SQL Query and Analyze
Testing the SQL query reveals that it is not inherently slow.
SELECT * FROM t_ds_project p
WHERE 1=1 AND p.id IN (1, 2, 3, 5, 6, 8, 9, 12, 13, 14, 16, 17, 19, 21, 22, 23, 25, 26)
GROUP BY p.id
ORDER BY p.id DESC;
Testing the SQL being executed by DolphinScheduler, using Arthas and the CTBots MyBatis SQL Parser:
7. Identify the Slow Query
The slow SQL query was identified as follows:
SELECT
p.id, p.name, p.code, p.description, p.user_id, p.flag, p.create_time, p.update_time,
u.user_name AS user_name,
(SELECT COUNT(*) FROM t_ds_process_definition AS def WHERE def.project_code = p.code) AS def_count,
(SELECT COUNT(*) FROM t_ds_process_definition_log def, t_ds_process_instance inst
WHERE def.code = inst.process_definition_code
AND def.version = inst.process_definition_version
AND def.project_code = p.code
AND inst.state = 1) AS inst_running_count
FROM t_ds_project p
LEFT JOIN t_ds_user u ON u.id = p.user_id
WHERE 1=1
AND p.id IN (1, 2, 3, 5, 6, 8, 9, 12, 13, 14, 16, 17, 19, 21, 22, 23, 25, 26)
GROUP BY p.id, u.user_name
ORDER BY p.id DESC LIMIT 100;
Analysis using EXPLAIN revealed that the bottleneck was in the inst_running_count subquery.
The t_ds_process_definition table contains approximately 280,000 rows, while t_ds_process_definition_log has 1,000 rows.
8. Optimize the Query with Indexes
Adding an index to the t_ds_process_instance table significantly improved performance:
CREATE INDEX qwl_diy_index
ON t_ds_process_instance (process_definition_code, process_definition_version, state);
9. Verify the Results
After adding the index, the query execution time improved, and the page loaded correctly.
By addressing the slow query with an appropriate index, the issue was resolved. If this article was helpful, feel free to like, bookmark, or share it!
