Skip to content
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

Slow query performance while stopping job [BATCH-2422] #1180

Closed
spring-projects-issues opened this issue Aug 26, 2015 · 3 comments
Closed

Slow query performance while stopping job [BATCH-2422] #1180

spring-projects-issues opened this issue Aug 26, 2015 · 3 comments

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented Aug 26, 2015

Milan Plancik opened BATCH-2422 and commented

Dear developers,

I have roughly 10 000 stored executions in batch job tables (JOB_EXECUTION) and I am starting to have a performance issue while stopping the job. I use locally started H2 database on running on some TCP port (not embedded).

The problematic SQL is:

private static final String GET_JOB_FROM_EXECUTION_ID = "SELECT ji.JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, ji.VERSION from %PREFIX%JOB_INSTANCE ji, %PREFIX%JOB_EXECUTION je where JOB_EXECUTION_ID = ? and ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID";

and takes more than 10 seconds to execute while I try to stop the tasklet using jobOperator. The question is whether such query can not be implemented better (I am not DB expert), for example:

String string = "SELECT " +
 "ji.JOB_INSTANCE_ID, " +
 "JOB_NAME, JOB_KEY, " +
 "ji.VERSION " +
 "FROM BATCH_JOB_EXECUTION je " +
 "LEFT JOIN BATCH_JOB_INSTANCE ji ON ji.JOB_INSTANCE_ID = je.JOB_INSTANCE_ID " +
 "WHERE JOB_EXECUTION_ID = :executionId";

Affects: 3.0.5

Referenced from: pull request #720, and commits 872845c

Backported to: 4.2.0.M3

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 23, 2018

Mahmoud Ben Hassine commented

Hi Milan Plancik

Thank you for raising this. Since you are using H2 in server mode, have you added the recommended indexes as described in the Recommendations for Indexing Meta Data Tables section? The default DDL that Spring Batch provides for H2 does not include those indexes by default.

Please add those indexes and let us know if you still have this performance issue.

Kr,
Mahmoud

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented May 27, 2019

Mahmoud Ben Hassine commented

Hi Milan Plancik,

I could not reproduce the issue. Here is the repo I used for my tests: https://github.com/benas/spring-batch-sandbox/tree/master/batch2422 (See detailed steps to reproduce the case in the README.md file).

Even with 10000 job instances/executions, stopping a job takes a few milliseconds and not 10 seconds. I even tried to time the query GET_JOB_FROM_EXECUTION_ID by isolating the call to jdbcJobInstanceDao.getJobInstance(jobExecution) and this still runs in 1ms or so. I run my tests on a Macbook Pro 16Go RAM, 2.9 GHz Intel Core i7 CPU, MacOS Mojave 10.14.5, Oracle JDK 1.8.0_201.

Since my job is a simple "hello world" job, I probably don't have enough data in my h2 instance as in your case. Can you share your h2 database file (.mv.db file) against which the query takes 10s. Otherwise, please share the detailed steps to reproduce the issue.

Thank you upfront.

Kind regards,
Mahmoud

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Jun 21, 2019

Mahmoud Ben Hassine commented

While I was not able to reproduce the issue with JobOperator#stop(JobExecutionId), I noticed a big performance issue when trying to stop a Job using the CommandLineJobRunner (with -stop option).

Stopping a job with the command line job runner loads all job executions and does the filtering on the client side, while it could have done that on the database side using JobExplorer#findRunningJobExecutions. I tested this change here and the improvement is very significant: For 100.000 job executions, stopping a job takes in average 1m45s with v4.1.2 while it takes 1s with the aforementioned changes. That's 100x faster!

takes more than 10 seconds to execute while I try to stop the tasklet using jobOperator.

Milan Plancik Can you confirm the issue is still happening with v4.1.2 using the JobOperator and if you encountered the same performance issue with the CommandLineJobRunner ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants