Unable to get list of task executions with a large amount of task execution rows

[gyugyu90]

Description:
We are using 2.11.2 version of SCDF. After migration, we cannot get list from Task Executions tab. It is GET /tasks/executions API that causes this issue, as it takes longer than our server's timeout, which is 60 seconds. It seems that time took for getting list takes longer as the number of rows of table AGGREGATE_TASK_EXECUTION and related tables grows.

Why don't you provide with lighter version of API just like GET /jobs/thinexecutions from Job Executions tab?
I think it takes much time while fetching task arguments, which is not shown in the grid UI. We have over a million AGGREGATE_TASK_EXECUTION_PARAMS rows, and the application tries to search from it n times.

Release versions:
2.11.x

Custom apps:
N/A

Steps to reproduce:
Make a large number of Task Executions and try to GET /task/executions

Screenshots:

Additional context:
We don't see this issue in our development environment which has around 1k rows of AGGREGATE_TASK_EXECUTION, but our production server has around 100k rows and can't get the list in time.

[corneil]

@gyugyu90

There is already a change in snapshot for fixing this.
It will be in the form of an automatic migration.
If you are in a hurry you can apply the following, but remember to drop the indexes before deploying the next update.

create index BATCH_STEP_EXECUTION_JOB_EXECUTION_ID_IX on BATCH_STEP_EXECUTION(JOB_EXECUTION_ID);
create index BOOT3_BATCH_STEP_EXECUTION_JOB_EXECUTION_ID_IX on BOOT3_BATCH_STEP_EXECUTION(JOB_EXECUTION_ID);
create index BOOT3_TASK_TASK_BATCH_JOB_EXECUTION_ID_IX on BOOT3_TASK_TASK_BATCH(JOB_EXECUTION_ID);
create index TASK_TASK_BATCH_JOB_EXECUTION_ID_IX on TASK_TASK_BATCH(JOB_EXECUTION_ID);
create index BATCH_JOB_EXECUTION_START_TIME_IX on BATCH_JOB_EXECUTION(START_TIME);
create index BOOT3_BATCH_JOB_EXECUTION_START_TIME_IX on BOOT3_BATCH_JOB_EXECUTION(START_TIME);

[gyugyu90]

Hello, @corneil

I've created indices you suggested, but it seems they do not improve performance dramatically for now.

The GET /tasks/executions API internally tries to look for arguments as many times as the page size, and it took around three seconds to get each from a million AGGREGATE_TASK_EXECUTION_PARAMS rows.
I think changing the internal N+1 querying logic to using IN clause would make it faster. Or introducing new 'thinexecutions' API for task executions would do.
Please let me know if you are considering one of these.

I think I should wait for the next version and delete the old rows from the database for now.

[corneil]

@gyugyu90 Which database are you using?

[gyugyu90]

@corneil
I am using Aurora MySQL 8.0, and it is running on db.r6g.large AWS RDS instance