Bacularis 5.0 on Debian 12 – /api/v3/jobs very slow without age

empbilly

hello Marcin,

I don't know if you remember, but a while ago, still on the Bacularis email list, I started a thread saying that after an update, the jobs on the dashboard were taking a long time to appear.

I installed 5.0 this afternoon and it's also taking a while. Not as long as it took back then, but it's still taking a little time.

Since then I’m seeing a big performance gap specifically on the /api/v3/jobs endpoint when no age is provided.

Environment

OS: Debian 12 (bookworm), Apache 2.4.65
Bacularis: 5.0.0 (panel on port 9197), legacy 2.2.1 still running on 9097
Bacula: Community (Director/SD/FD on the same VM)
DB: MariaDB 10.11.14, catalog size ~2.3 GB
Clients/Jobs: large history; JobMedia ~1.2M rows (cardinality from indexes)

What works (fast)

/api/v3/clients, /api/v3/jobs/totals, /api/v3/dbsize, etc. are quick.
Adding a time window is fast. Example (age = 30 days):

time curl -u admin:'***' -s \
'http://127.0.0.1:9197/api/v3/jobs?director=bacula-dir&age=30&limit=500' | jq '. | length'
# -> ~0.2s total

What’s slow

Without age (even with small limit) it’s still ~35s:

time curl -u admin:'***' -s \
'http://127.0.0.1:9197/api/v3/jobs?director=bacula-dir&limit=5' | jq '. | length'
# -> ~35s total

Access log example:

GET /api/v3/jobs?director=bacula-dir&limit=5 ... 200 ... 34932ms

DB tuning already applied

/etc/mysql/mariadb.conf.d/50-server.cnf:

innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=4
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
tmp_table_size=512M
max_heap_table_size=512M
innodb_io_capacity=2000
innodb_read_io_threads=8
innodb_write_io_threads=8
slow_query_log=1
long_query_time=1

Indexes already created

-- JobMedia “first volume” + joins
CREATE INDEX idx_jobmedia_jobid_jobmediaid ON JobMedia (JobId, JobMediaId);
CREATE INDEX idx_jobmedia_job ON JobMedia (JobId);
CREATE INDEX idx_jobmedia_med ON JobMedia (MediaId);

-- Job foreign keys + time
CREATE INDEX idx_job_client  ON Job (ClientId);
CREATE INDEX idx_job_pool    ON Job (PoolId);
CREATE INDEX idx_job_fileset ON Job (FileSetId);
CREATE INDEX idx_job_start   ON Job (StartTime);

-- For prev/next by name + time
CREATE INDEX idx_job_name_start_jobid ON Job (Name, StartTime, JobId);

ANALYZE TABLE Job, JobMedia, Client, Pool, FileSet;
OPTIMIZE TABLE Job, JobMedia;

Slow query log (representative snippet)

Count: 6  Time=35.5s  Rows_examined~3.7M  Rows_sent~170
SELECT Job.*,
       pn.prev_jobid AS prev_jobid,
       pn.next_jobid AS next_jobid,
       Client.Name as client,
       Pool.Name as pool,
       FileSet.FileSet as fileset,
       jm.volumename AS firstvol,
       COALESCE(mi.volcount, N) AS volcount,
       ...
FROM Job
LEFT JOIN (
  SELECT JobMedia.JobId AS jobid,
         Media.VolumeName AS volumename,
         ROW_NUMBER() OVER (PARTITION BY JobMedia.JobId ORDER BY JobMedia.JobMediaId) AS jmi
  FROM ...
) jm ON jm.jobid = Job.JobId AND jm.jmi = 1
...
-- (window functions / joins across Job, JobMedia, Media, etc.)

What I tried on the web tier

UI default “Job age on dashboard graphs” is set to 14 days (works great).
I briefly tried forcing age=30 via Apache rewrite, but removed it to avoid interfering with Bacularis routing (I can add it back if there’s a recommended pattern).

Ask

Is there any additional index or recommended hint/config for MariaDB 10.11 to speed up /api/v3/jobs without age?
Does Bacularis 5.x intentionally compute prev/next and “first volume” for the entire history before applying LIMIT? If so, is there a setting to disable or defer those computations when no age is given?
Any best-practices for large catalogs (indexes, views, or API parameters) to keep “no-age” queries under a few seconds?
If there’s an official way to set a global default age at the API level (rather than UI), I’d be happy to use it.

Thanks a lot for any pointers. I’m happy to provide full EXPLAIN output of the slow query if that helps.

gani

Hello @empbilly,

empbilly I don't know if you remember, but a while ago, still on the Bacularis email list, I started a thread saying that after an update, the jobs on the dashboard were taking a long time to appear.

Yes, I remember this thread on that old user Bacularis mail list.

Thanks for providing all details about environment. I think you researched this issue very well and you did good job to optimize this part.

empbilly What I tried on the web tier

OK. Great.

This age parameter is used only for dashboard page, so it can be safely rewritten in web server, as you did, or hard-coded in Bacularis code without worrying that it will break other parts of Bacularis. This is state for today of course, because in the future theoretically can come functions that will use it as well. But for now they are not planned and we don't have any this type of tasks in roadmap that could use it.

empbilly Is there any additional index or recommended hint/config for MariaDB 10.11 to speed up /api/v3/jobs without age?

I don't know any. With milion(s) number of records in my opinion it can be hard to operate with dashboard without using the age limit. Using default value for this age parameter was added exactly for that - for limiting records for the job status summary graph and the job size and files per day graphs. It was added in version 2.7.0. Before that version the results were unlimited, so it was this situation that you have now.

empbilly Does Bacularis 5.x intentionally compute prev/next and “first volume” for the entire history before applying LIMIT? If so, is there a setting to disable or defer those computations when no age is given?

This is a good question. This would be great if we could limit values in those sub-queries. When I prepared this query I couldn't find any way to add this limit. This is because for example to know what volumes are used by jobs and what is volume count for each job we need to examine all volumes. With setting limit there, the result will be incomplete because we don't know where exactly in the volume table are volumes that we need. If you know how we could use limits in those sub-queries, please share your ideas. Maybe there exist a way to do it with limits inside sub-queries.

empbilly Any best-practices for large catalogs (indexes, views, or API parameters) to keep “no-age” queries under a few seconds?

I understand what you want to achieve however the reason why you need it is not clear for me. Why you don't want to use the age input field to set the job age? And why you want to configure it in the API and not in the web interface where we have dedicated field for that? I am writing about it because if we know reasons, maybe we could find other ways to solve this problem than focusing on that age parameter.

empbilly If there’s an official way to set a global default age at the API level (rather than UI), I’d be happy to use it.

There isn't official way but you can use this simple patch below to manipulate the job age on the API side. Here is an example for 30 days history:

diff --git a/API/Pages/API/Jobs.php b/API/Pages/API/Jobs.php
index fe07c18..02c31a0 100644
--- a/API/Pages/API/Jobs.php
+++ b/API/Pages/API/Jobs.php
@@ -48,6 +48,9 @@ class Jobs extends BaculumAPIServer
                $type = $this->Request->contains('type') && $misc->isValidJobType($this->Request['type']) ? $this->Request['type'] : '';
                $jobname = $this->Request->contains('name') && $misc->isValidName($this->Request['name']) ? $this->Request['name'] : '';
                $clientid = $this->Request->contains('clientid') ? $this->Request['clientid'] : '';
+               if ($this->Request->contains('age')) {
+                       $age = 2592000; // 30 days
+               }
 
                if (!empty($clientid) && !$misc->isValidId($clientid)) {
                        $this->output = JobError::MSG_ERROR_CLIENT_DOES_NOT_EXISTS;

If you use Bacularis installed from the binary packages, this patch needs to be added for file in this path:

/usr/share/bacularis/protected/API/Pages/API/Jobs.php

With this patch to display the dashboard graphs correctly, you need to set the age parameter in the web interface to 0 (zero) to make it unlimited. In the web interface this text field is in the Application settings page.

Best regards,
Marcin Haba (gani)

empbilly

hello @gani,

Following your guidance, we made two changes that dropped /api/v3/jobs from ~35s to ~0.46s for a 7-day window:

Reasonable default age (when none provided) in API/Pages/API/Jobs.php, to keep the UI snappy by default.
SQL change in JobManager (both copies):
Files:
- /usr/share/bacularis/protected/API/Modules/JobManager.php
- /usr/share/bacularis/protected/vendor/bacularis/bacularis-api/API/Modules/JobManager.php
We replaced the “first volume” subquery that used a window function with a cheaper join using MIN(JobMediaId):

-- BEFORE (simplified)
LEFT JOIN (
  SELECT
    JobMedia.JobId AS jobid,
    Media.VolumeName AS volumename,
    ROW_NUMBER() OVER (PARTITION BY JobMedia.JobId ORDER BY JobMedia.JobMediaId) AS jmi
  FROM Media
  LEFT JOIN JobMedia USING (MediaId)
) AS jm ON jm.JobId = Job.JobId AND jm.jmi = 1

-- AFTER (fast on MariaDB 10.11)
LEFT JOIN (
  SELECT jm.JobId AS jobid, m.VolumeName AS volumename
  FROM JobMedia jm
  JOIN (
    SELECT JobId, MIN(JobMediaId) AS min_jmid
    FROM JobMedia
    GROUP BY JobId
  ) first ON first.JobId = jm.JobId AND first.min_jmid = jm.JobMediaId
  JOIN Media m ON m.MediaId = jm.MediaId
) AS jm ON jm.jobid = Job.JobId

Extra indexes + ANALYZE (helped the planner):

CREATE INDEX idx_job_start_jobid  ON Job (StartTime, JobId);
CREATE INDEX idx_job_start_name   ON Job (StartTime, Name);
CREATE INDEX idx_jobmedia_jobid_jobmediaid_mediaid ON JobMedia (JobId, JobMediaId, MediaId);
ANALYZE TABLE Job, JobMedia;

Results (curl):

age=604800&limit=500 (7 days): ~0.46s (was ~35s)
age=30&limit=500: ~0.28s
With no age, the new default keeps pages fast.

After editing, we cleared protected/runtime/ and reloaded Apache.

Thank you, Marcin, for the pointers — happy to contribute a small patch/PR with this SQL change if it helps others.

gani

Hello @empbilly,

Thank you very much for your feedback and for the improvement for this SQL query.

I am glad that Bacularis started working well and quickly on your side.

empbilly Reasonable default age (when none provided) in API/Pages/API/Jobs.php, to keep the UI snappy by default.

OK, nice.

empbilly We replaced the “first volume” subquery that used a window function with a cheaper join using MIN(JobMediaId)

Great idea. Thank you. Right, this join with MIN() is cheaper. I did quick test for this patch with PostgreSQL catalog. I don't have large environment (6k jobs and 2k volumes) but it is fine here as well.

Yes, please prepare the PR in GitHub:

https://github.com/bacularis/bacularis-api

The developer guide can help for that:

https://bacularis.app/doc/contributions/patches.html

In case any question, please write here.

empbilly Extra indexes + ANALYZE (helped the planner):

Thanks for sharing it. It may be helpful for other Community users.

empbilly Thank you, Marcin, for the pointers — happy to contribute a small patch/PR with this SQL change if it helps others.

Thank you, @empbilly . Sure, this will make Bacularis better 🙂

Best regards,
Marcin Haba (gani)

gani

Hello @empbilly

Thank you for preparing PR.

This has been successfully added to the Bacularis API repository.

Thank you very much for your valuable contribution.

Best regards,
Marcin Haba (gani)