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.