Skip to main content

Job Queue Optimization

This page describes the techniques that Holistics employs to optimize its Job Queue and what you can do to optimize your Job Queue.

Holistics's internal mechanisms to optimize Job Queues

Caching

When the cache data is available, Holistics will fetch the result directly from cache and will not execute a new Job.

Holistics has many caching mechanisms to optimize its performance.
You can learn more about the main caching mechanism, which is the Reporting cache, via this link.

Job de-duplication

If 2 users (A and B) open the same report within a short amount of time, there's a high chance a duplicate query will be sent to the system while the first query is still running. This unnecessarily overloads the system, and increases the waiting time of both users.

To avoid this from happening, Holistics has a built-in de-duplication mechanism, which works as follow:

  • Every time a query job is submitted, the query hash is used to look up concurrent running jobs within the last 10 minutes to find the same query currently being executed at customer database
  • If found, the job status is set to "already existed" and the job result is routed to the previous running job with the same query.

If the first job already exists and the result is stored in cache, the caching mechanism will kick in. The second query will not be sent at all, and we will use the cached result to serve user B.

Note

The exact de-duplication behavior may vary according to your version of Holistics and your Report type.

What to do when encountering slow jobs

Limit Number of Report Jobs Run Per User

Maintenance Notice

This feature is currently not supported in Holistics 3.0 and above, but we are planning to develop it soon.

Since the max concurrent jobs are being capped per queue, when a user A purposely or accidentally farms multiple jobs, these jobs quickly take up all the available slots in the queue, causing other users unable to use the system. To prevent this from happening, admins can set a limit on how many report jobs each user can run concurrently. This is done in the Admin, Settings page.

Limit the Query Timeout of your Database Connection

Having a limit on the Query Timeout would help prevent slow queries from occupying the Job Queue for too long and block the other Jobs.

Please refer to this documentation to configure the Query Timeout of your Database Connection.

Example Scenario
Let's say you have a Dashboard of 25 Reports, each Report takes 1 second to run.
By default, the Report Job Queue has 10 slots.

Therefore, when you open the Dashboard (and the cache is not available):

  1. At t = 0, 10 Reports will be executed first in parallel.
  2. At t = 1 second, the first 10 Reports will have finished, so the next 10 Reports will be executed in parallel.
  3. At t = 2 seconds, the second 10 Reports will have finished, so the last 5 Reports will be executed next in parallel.
  4. At t = 3 seconds, the last 5 Report will have finished.

-> It takes 3 seconds in total to run your whole Dashboard.

Then, an Analyst makes some modifications to the first 10 Reports, causing them to take 5 seconds to run.
Without any Query Timeout, those first 10 Reports will occupy the whole Job Queue for 10 second and delay the other Reports.

As the result:

  • It now takes 7 seconds in total to run your whole Dashboard
  • It takes 6 seconds to see the result of Report 11, even when Report 11 takes only 1 second to run

With a Query Timeout of 1 second, the first 10 Reports will be terminated after 1 second, freeing the Job Workers in the Job Queue for the other Reports:

Thus, the Query Timeout is a very helpful configuration for you to control the Job Queue.

Cancel Running Jobs

Some type of jobs are able to be cancelled while running. This is incredibly helpful when a user accidentally farms a long-running job as an available slot in the queue will be wasted. A running job's ability to be canceled is determined based on the job's Source type and Action. Currently, Holistics supports canceling these types of running job:

  • DataTransform: execute
  • DataImport: execute
  • EmailSchedule: execute
  • QueryReport: execute, preview
  • DashboardWidget: execute For QueryReport jobs (execute, preview), there are also Cancel buttons in Report view and Report editor, which share the same effect of cancelling the job from Jobs monitoring screen.

While cancelling a running job, Holistics will also try to cancel all job's running queries in order to save your database server's resources.

Holistics uses a simple, yet effective mechanism to cancel job's running queries. We includes the job's id as a comment in every query sent to your database server, and uses this information to identify the specific process running the query. Afterwards, Holistics will send a specific query depending on DataSource type (e.g, pg_cancel_backend for PostgreSQL) to kill the identified process.

Increase your default slots for specific Job Queues

This approach cannot be done from your side since this action will require our support engineer to adjust the queue size.

If you think that your current default slot (2 concurrent jobs for data transforms for example) is not enough for your operation, please contact us via [email protected] and we will process your request.

Contact Holistics Support

Please see the below section.

Reporting slow-running Jobs to Holistics Support

Holistics is here to help! Please send us a ticket via [email protected] should you encounter slow-running jobs while using our application. To help us serve you better, consider including these suggested information in your request:

info

Important: When comparing jobs’ execution time between Holistics and your database, please make sure to run those jobs using the same database user credentials.

✅ Attach your support request with the ID of the job

Holisitics offers Job Monitoring dashboard which records every job executed.

Please access this dashboard to find the ID of the slow-running job and send it to us.

✅ Share with us your thoughts on what you've expected of the job's performance

We would love to gain insights on your expectations of the job's execution speed. If possible, we are keen to know why you have adopted these expectations (i.e that is the normal performance of the applications that you have used).

✅(Optional) Include a before/ after log that demonstrates a performance degradation

If your normal jobs become noticeably slower, you can add a before/ after log showing the differences in these jobs’ duration (using the Job Monitoring dashboard like above).

✅ (Optional) Check the job logs to see which steps take long time to finish

You can see the detailed logs of your job by selecting the Logs button in Jobs Monitoring dashboard. This log is helpful to pinpoint what step(s) is slow.


Let us know what you think about this document :)