Job Queue Management

This section talks about the Holistics Job Queue system, or how jobs are being distributed and managed from within Holistics.

A job is an operation that is

  • Involving interacting with customer database
  • Executed in the background to avoid blocking other user actions
  • Executed based on schedules

Job Queues

Our application needs to handle many process long-running operations to perform some business logic (constructing queries from models, copy and load data from source to destination tables, etc.), in which these operations might take from a few seconds to minutes processing time. An operation will be categorized as a job, which will be enqueued into a queue and process asynchronously.

To ensure the performance and stability for both Holistics and the tenant's system, each tenant will have their own queue, and the number of jobs running at the same time will be limited for each tenant.

Furthermore, depending on the nature of the job, it'll be classified into different queues (or pools). For example, a report job runs in a different queue than a data transform job.

Job status

Each job will have the following status:

  • created: When a job is first triggered (whenever you click submit or create button), it will have created status.
  • queued: Depend on the default slot limit of a specific queue in your tenant/company, if the slot is currently not fully occupied, the created job will be queued.
  • running: Depend on the available slots of our concurrent running workers, Holistics will then execute/run jobs which are currently being queued.
  • success: If the job runs successfully, it will have success status.
  • failure: If the job runs unsuccessfully, it will have failure status.
  • cancelling: While a job is running, if you manually cancel the job, it will have cancelling status.
  • cancelled: If the job is cancelled successfully, it will have cancelled status.
  • already_existed: When a job have this status, it means that this job currently coincides with the previous jobs created / queued / being run.

The issue you might encounter when there are too many jobs stuck at Created

Your reports are loading so slow.

Data Imports, Data Transforms, and Schedules cannot run at the exact time that you have set.

Reasons that cause slow jobs

In general, the issue might happen because:

  • Each workspace only has a limited number of concurrent workers/jobs for a specific queue. If you have too many jobs stuck at Created, it might never be queued thus causing the slowness when viewing reports or scheduling an email.
  • Slow connection between Holistics and your database
  • Too large amount of data needs to be transmitted from customer database to Holistics
  • The query itself is slow, which leads to major part of the execution time being occupied by waiting for the customer database to finish executing the query.
  • For Data Transform, Data Import and Data Schedule queues, you probably have set the scheduled too close to each other (Run every 10 minutes or 1 hour).

Default slots for specific job queues

Below is the current list of queues (and their default slots, i.e. number of jobs running concurrently) for each account. Do note that the Embedded Analytic features utilize a special type of worker called Embed Worker. They are separate and can be manually adjusted from the Embed Analytics Manager:

QueueDefault SlotAction included
Default51. Create/Update Custom Field
2. Refresh Models and Dependant Models
3. Run Adhoc Query
Filter31. Filter suggestion
2. Process filter in Dashboard
Report10Execute report/widget
Prefetch21. Synchronize Schema
2. Prefetch Filter Cache
Preview31. Validate Data Import
2. Preview Report/Query
Export101. Export Dashboard
2. Export Dashboard Widget/Report
Email Schedule2Executing schedule (Email, Slack, SFTP, Google Scheet)
Data Import2Executing Data Import
Data Transform2Executing Data Transform (or Storage Settings)
Validate51. Validate Table Structure in Data Transform
2. Validate Query in Data Transform
3. Preview Data Transform
Embed Analytics QueueDefault SlotAction included
Embed01. Execute Embedded Dashboard Widget/Report
2. Export Embedded Dashboard
3. Export Embedded Dashboard Widget/Report

Please note that these limitations of your tenant might be slightly different from the list above if we have already increased the concurrent worker for you. If you want to know your default slots for specific job queues, please contact us via [email protected].

If you want to enabled our Embedded Analytics feature, please refer to our doc about Embedded Analytics for more information.

What to do when encountering slow jobs

Limit Number of Report Jobs Run Per User

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.

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.

Avoid Running Duplicate Report Query Jobs

If 2 users (A and B) open the same report within 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 happens, Holistics have a built-in de-duplication mechanism, 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 to user B.

Cancel Running Queries

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 queus

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.