New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Rundeck] Slow DB request : index needed on workflow_workflow_step table ? #2023

Open
katanafleet opened this Issue Aug 23, 2016 · 5 comments

Comments

Projects
None yet
6 participants
@katanafleet

katanafleet commented Aug 23, 2016

We have many projects (> 100) and we're using rundeck with Mysql Database.
We found out that some requests could be very slow (> 10s)

Example :
Count: 11579 Time=12.81s (148271s) Lock=0.00s (1s) Rows=7.6 (87880), mysuslu1[mysuslu1]@2hosts select commands0_.workflow_commands_id as workflow1_16_0_, commands0_.workflow_step_id as workflow2_18_0_, commands0_.commands_idx as commands3_0_, workflowst1_.id as id1_17_1_, workflowst1_.version as version2_17_1_, workflowst1_.description as descript3_17_1_, workflowst1_.error_handler_id as error_ha4_17_1_, workflowst1_.keepgoing_on_success as keepgoin5_17_1_, workflowst1_.adhoc_execution as adhoc_ex7_17_1_, workflowst1_.adhoc_filepath as adhoc_fi8_17_1_, workflowst1_.adhoc_local_string as adhoc_lo9_17_1_, workflowst1_.adhoc_remote_string as adhoc_r10_17_1_, workflowst1_.arg_string as arg_str11_17_1_, workflowst1_.file_extension as file_ex12_17_1_, workflowst1_.interpreter_args_quoted as interpr13_17_1_, workflowst1_.script_interpreter as script_14_17_1_, workflowst1_.job_group as job_gro15_17_1_, workflowst1_.job_name as job_nam16_17_1_, workflowst1_.node_filter as node_fi17_17_1_, workflowst1_.node_keepgoing as node_ke18_17_1_, workflowst1_.node_rank_attribute as node_ra19_17_1_, workflowst1_.node_rank_order_ascending as node_ra20_17_1_, workflowst1_.node_step as node_st21_17_1_, workflowst1_.node_threadcount as node_th22_17_1_, workflowst1_.json_data as json_da23_17_1_, workflowst1_.type as type24_17_1_, workflowst1_.class as class6_17_1_ from workflow_workflow_step commands0_ inner join workflow_step workflowst1_ on commands0_.workflow_step_id=workflowst1_.id where commands0_.workflow_commands_id=N ;
=> To fix this issue we've created an index on "workflow_workflow_step" table
CREATE INDEX workflow_commands_id_index on workflow_workflow_step(workflow_commands_id);

@jhenry82

This comment has been minimized.

Show comment
Hide comment
@jhenry82

jhenry82 Jan 4, 2017

We've experienced the same issue. Adding two indices improved GUI performance by orders of magnitude. Literally from minutes to 1 second.

ALTER TABLE workflow_workflow_step ADD INDEX commands_idx ( commands_idx );
ALTER TABLE workflow_workflow_step ADD INDEX workflow_commands_id ( workflow_commands_id );

Rundeck 2.6.9-1
CentOS 6.8 64-bit
Percona Server 5.5.52 (mysql compatible)

Please consider making these part of the default schema, and including a migration to add them if missing. It's unbelievable what a difference they make on usability once you have lots of jobs and a long execution history.

Some other issues (#1564, #1547, #573) describe a similar problem

jhenry82 commented Jan 4, 2017

We've experienced the same issue. Adding two indices improved GUI performance by orders of magnitude. Literally from minutes to 1 second.

ALTER TABLE workflow_workflow_step ADD INDEX commands_idx ( commands_idx );
ALTER TABLE workflow_workflow_step ADD INDEX workflow_commands_id ( workflow_commands_id );

Rundeck 2.6.9-1
CentOS 6.8 64-bit
Percona Server 5.5.52 (mysql compatible)

Please consider making these part of the default schema, and including a migration to add them if missing. It's unbelievable what a difference they make on usability once you have lots of jobs and a long execution history.

Some other issues (#1564, #1547, #573) describe a similar problem

@calebcall

This comment has been minimized.

Show comment
Hide comment
@calebcall

calebcall Apr 3, 2017

Holy smokes!!! I almost gave up on rundeck because despite running the UI on it's own machine (physical server, SSD drives, 64G memory, 10GB Nic, etc) and the mysql db on it's own three node cluster (similar specs as the UI node, but +HBAs for SAN disk for DB) it was still slower than I could handle. I also have been pruning my history down to a single day just so that I can still use the UI. Yet it was still 5+ minutes to load the activity tab, about the same amount of time to manually execute a job, etc. Applied those two indexes and now it's still a little slow but I'm talking a few seconds instead of many minutes. I'll take it.

calebcall commented Apr 3, 2017

Holy smokes!!! I almost gave up on rundeck because despite running the UI on it's own machine (physical server, SSD drives, 64G memory, 10GB Nic, etc) and the mysql db on it's own three node cluster (similar specs as the UI node, but +HBAs for SAN disk for DB) it was still slower than I could handle. I also have been pruning my history down to a single day just so that I can still use the UI. Yet it was still 5+ minutes to load the activity tab, about the same amount of time to manually execute a job, etc. Applied those two indexes and now it's still a little slow but I'm talking a few seconds instead of many minutes. I'll take it.

@shamil

This comment has been minimized.

Show comment
Hide comment
@shamil

shamil Apr 3, 2017

@gschueler are you going to fix this? Or we should run those queries manually after fresh install?

shamil commented Apr 3, 2017

@gschueler are you going to fix this? Or we should run those queries manually after fresh install?

@gschueler

This comment has been minimized.

Show comment
Hide comment
@gschueler

gschueler Apr 4, 2017

Member

There has been some work on adding DB migration feature #2260 but it is not complete and is not yet part of the release... the index definitions can be added in that feature. Currently I believe it is not possible to have Grails automatically create the extra indexes, but perhaps we can add some code to "manually" add the indexes for Mysql and possible other dbs. For now i'd recommend adding those indexes manually

Member

gschueler commented Apr 4, 2017

There has been some work on adding DB migration feature #2260 but it is not complete and is not yet part of the release... the index definitions can be added in that feature. Currently I believe it is not possible to have Grails automatically create the extra indexes, but perhaps we can add some code to "manually" add the indexes for Mysql and possible other dbs. For now i'd recommend adding those indexes manually

gschueler added a commit to gschueler/rundeck that referenced this issue Apr 24, 2017

@kwaio

This comment has been minimized.

Show comment
Hide comment
@kwaio

kwaio Aug 28, 2017

Same here, we had CPU saturation during jobs import, and adding a simple index on workflow_commands_id ( workflow_commands_id ) changed everything.

Globaly, the tuning/optimization part of the documentation would need more work.

kwaio commented Aug 28, 2017

Same here, we had CPU saturation during jobs import, and adding a simple index on workflow_commands_id ( workflow_commands_id ) changed everything.

Globaly, the tuning/optimization part of the documentation would need more work.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment