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
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.
Each job will have the following status:
- created: When a job is first triggered (whenever you click
createbutton), it will have
- queued: Depend on the default slot limit of a specific queue in your tenant/company, if the slot is currently not fully occupied, the
createdjob will be
- running: Depend on the available slots of our concurrent running workers, Holistics will then execute/run jobs which are currently being
- success: If the job runs successfully, it will have
- failure: If the job runs unsuccessfully, it will have
- cancelling: While a job is running, if you manually cancel the job, it will have
- cancelled: If the job is cancelled successfully, it will have
- 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:
|Queue||Default Slot||Action included|
|Default||5||1. Create/Update Custom Field|
2. Refresh Models and Dependant Models
3. Run Adhoc Query
|Filter||3||1. Filter suggestion|
2. Process filter in Dashboard
|Prefetch||2||1. Synchronize Schema |
2. Prefetch Filter Cache
|Preview||3||1. Validate Data Import |
2. Preview Report/Query
|Export||10||1. Export Dashboard |
2. Export Dashboard Widget/Report
|Email Schedule||2||Executing schedule (Email, Slack, SFTP, Google Scheet)|
|Data Import||2||Executing Data Import|
|Data Transform||2||Executing Data Transform (or Storage Settings)|
|Validate||5||1. Validate Table Structure in Data Transform|
2. Validate Query in Data Transform
3. Preview Data Transform
|Embed Analytics Queue||Default Slot||Action included|
|Embed||0||1. 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
Currently, Holistics supports canceling these types of running job:
- DataTransform: execute
- DataImport: execute
- EmailSchedule: execute
- QueryReport: execute, preview
- DashboardWidget: execute
QueryReport jobs (
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.