[ETL] Issues with monitoring/testing

[mahalakshme]

Issue:

ETL for rwbngos2023 completed in a minute, but in database it looks like it took 15 mins giving a wrong picture

If you see in the below image as well, the start time of some jobs are earlier than the end time of other jobs. So this looks like either the start time of next job or end time of previous job is recorded incorrectly. This is posing issues for monitoring the ETL jobs.

AC:

• started_at time needs to be the time, when the ETL process started for that org and ended_at needs to be the time ETL job finished running.
• Extend this report to send notification mail(to Maha alone) for the below:
  - When ETL of an organisation of 'Organisation Category' - production or UAT and 'Organisation Status' - Live fails.
  - When time taken to complete one round of ETL takes more than 1.5 hours
• Add a way to trigger the ETL of an org immediately overriding the other orgs in the queue - Currently testing of ETL stories are very difficult and time-consuming since even after disabling->enabling the ETL, most of the time it takes around 20-30 mins to trigger for that organisation. This should work when a button 'Trigger' is clicked on the org page of super admin. For usual enable, disable it need not trigger immediately. This is to prevent accidental disabling and to ve the audit unchanged.

Technical analysis/suggestions:

• Finding the times at appropriate callback methods(job listener events) should help to fix start and end time
• To trigger immediately: Currently we are triggering with a scheduler, and hence it might not take effect even when we mention a start time. Try triggering it once without scheduler like below, and then trigger it with a scheduler.
  Trigger trigger = TriggerBuilder.newTrigger()
  .withIdentity("triggerName", "triggerGroup")
  .startNow()
  .build();
• One way to identify if all the jobs of ETL are triggered in one-and-half an hour is to cross-check entries of scheduled_job_run table with qrtz_job_details

Ignore:

What:

• ETL failures
• time taken for an ETL if it exceeds 15 mins
• one run completes in 1:30 hours
• disabled for unneeded orgs

Who:

• can setup report - get alert - Maha to look into it
• generate bundle from UAT - store ETL status in the bundle
• check with implementation team

[himeshr]

Joy's Comment

the ACs look like they will be costly to implement since we are leveraging spring batch here and queuing and the tables are managed by it.

For AC1 (monitoring), we can rely on logs as source of truth and ignore the DB.
For AC2, we don't really have a concept of 'round' of ETL so again might be costly/complicated to determine this. Per org should be easier to do
For AC3, would it be sufficient to have an endpoint that disables ETL for all orgs so we can focus on the org we want to test as implementing priority within the queue is again going to be costly

[himeshr]

Himesh's Comment

In general, i agree with the issues that we aim to resolve here.. but have difference in the approach to resolve them though

• For AC1, Would recommend introducing an additional ETL-JOB-AUDIT table with info like ORGANISATION_UUID, ETL_TRIGGER_TYPE(org/orgGroup), ETL_START_TIME, ETL_END_TIME, ETL_JOB_STATUS, ETL_JOB_RUNTIME
• For AC2, Create a Metabase alert on ETL-JOB-AUDIT table as per requirement
• For AC3, Introduce an Adhoc ETL Job, that runs in Parallel to the Quartz scheduled ETL jobs, this would be run only once, scheduled immediately / within a day based on queue of Adhoc triggers and does not make any change to the Quartz based periodic execution of ETL (precedent exists in Avni-Integration-service for doing this)

[mahalakshme]

Viveks comment:

We are using Quartz btw, not spring batch for ETL.
I think this line may be the problem:

avni-etl/src/main/java/org/avniproject/etl/domain/quartz/ScheduledJobRun.java

Line 38 in 19591ac

scheduledJobRun.startedAt = trigger.getNextFireTime();

We perhaps should use new Date here
We can try to figure out why we are getting this issue, because the database entries are managed by us using the JobListener
If job listener is not the best way, we can hook into the actual execution callback that we get to record the times

[1t5j0y]

AC1 fixed as per Vivek's input above and seems to work well.
AC3 fixed by adding an additional higher priority trigger for the first run after enabling ETL for an org.

AC2 (metabase report) pending. Moving to code review ready so AC1 and AC3 can be tested.

[1t5j0y]

AC2 Metabase Reports:
https://reporting.avniproject.org/question/4840-latest-etl-run-failures-for-live-uat-and-prod-orgs-production-environment

https://reporting.avniproject.org/question/4841-etl-round-completed-in-90-minutes

Alerts can be enabled after this change is promoted due to inaccurate start/end times in scheduled_job_run

[himeshr]

AC2 Metabase Reports: https://reporting.avniproject.org/question/4840-latest-etl-run-failures-for-live-uat-and-prod-orgs

https://reporting.avniproject.org/question/4841-etl-round-completed-in-90-minutes

Alerts can be enabled after this change is promoted due to inaccurate start/end times in scheduled_job_run

Made slight additions to the first report to filter by "SyncJobs" job_group and show OrgCategory and OrgStatus values in readable format.

Code review didn't result in any other issues of concern.

[himeshr]

Additionally create following reports for QA and others to determin ETL job status:

• https://reporting.avniproject.org/question/4842-latest-etl-run-details-for-all-orgs-prod-environment

• https://reporting.avniproject.org/question/4843-latest-etl-run-details-for-all-orgs-staging-environment

[himeshr]

On debugging issue reported by Achala, observed the following in Prod environment:

• We are successfully creating a one time Higher Priority trigger for T+5 seconds
• We are also creating a repeating Normal Priority trigger for T+RepeatIntervalMinutes(120)
• Quartz has a lot of triggers backed up which need to have run more than 1 hour ago, these get recycled for later as part of Misfire time limit config(1 hour)
• Our Higher Priority trigger is still stuck in queue behind earlier trigger time jobs, and its priority matters only if trigger time is same or later

Discussion thoughts:

• There is no point in optimizing our HigherPriority triggerTime through some logic, as the backed-up triggers in combination with Misfire threshold will most likely result in our trigger being discarded
• We should rather focus on a way to get priority to be given higher precedence than time in-order to get it to be picked up next

[himeshr]

This is a good article to understand Quartz misfire scenarios and choice implications..
https://nurkiewicz.com/2012/04/quartz-scheduler-misfire-instructions.html

[1t5j0y]

"Fire Now" should be ideally used for One time Triggers, which is the case with FirstRun trigger.

The scenario I am worried about is:

• ETL is executing for org A
• ETL enabled for org B - triggers created
• org B 'First Run' trigger executes and misfires as org A ETL is still running and is 'fired now' again.
• Above step keeps happening

[himeshr]

Issues raised regarding ETL runs

• ETL is not run immediately after doing org Disable/Enable analytics
• ETL in general takes long time to reflect changes (days rather than hours)
• Org Group ETL runs take hours to complete
• Any other?

Discussion notes with recommendation on how to resolve issues raised for ETL run management

• Improve ETL processing performance (Optimize for invocation of queries atleast, if not for optimizing the queries themselves)
• Reduce frequency of ETL org runs (increase interval)
• Configure Misfire tolerance to be small value(10 mins) rather than the large one now (8 hours)
• Make RepeatJobs scheduling misfire logic to be "withMisfireHandlingInstructionNowWithRemainingCount" instead of default of "withMisfireHandlingInstructionNextWithRemainingCount"
• Make it multi-threaded (2 threads) To get ETL adhoc requests served immediately

Note: We would need to retrigger ETL jobs after repeat frequency config change using Postman Run Collection capability.

[1t5j0y]

Job chaining might also be viable for ETL to guarantee execution for all orgs and avoid misfires. Will require us to 'splice' newly scheduled jobs into the chain.

• https://blog.harveydelaney.com/implementing-job-chaining-in-quartz-net/

[himeshr]

As per standup discussion the 2 action points for now are as follows:

• Increase the interval between job runs to 4 hours for all organisations and OrgGroups
• Focus on improving ETL performance

[mahalakshme]

@himeshr unarchived the card and updated the release to 11.0

[himeshr]

Info:

• Performance impact of the change fccaeed is that, it reduces the media deleteSql execution time from around 10 seconds to less than 100 milliseconds for Utthan Org.
• Overall the ETL run time for Utthan OrgGroup after these code changes reduced from "more than 1 hour" to "less than 10 minutes"