@Max Kostikov ah... that is somehow suprising...Anyway, i think i will go with ALTER TABLE workerq ADD INDEX idx

2026-10-21 04:22:58

adminsforum@hubzilla.org

Markus wrote the following post Wed, 21 Oct 2026 04:22:48 -0700

Hubzilla isn't running well right now. The database and, at times, the queue are running significantly slower than usual. Have you noticed this, too?

2026-10-21 04:44:08

Saiwal

sk@utsukta.org

on my hub enabling pubstream adds a lot to cpu load which contributes to this, irrespective of what theme i use. So disabling pubstream or keeping it local only helps.

2026-10-21 05:37:15

Alfred Bühler

abu@hub.alfredbuehler.ch

@Hubzilla Support Forum The speed is as usual here. But last night my DB size increased by 54 MB, which is significantly higher as usual.

2026-10-21 06:51:29

*jayrope

jrp@hub.kliklak.net

@Hubzilla Support Forum I am on 11.4.2 and all systems are running well. No database or pubstream related troubles either. The only queues I see regularly piling up and then go down again are mostly from other Hubzilla servers. The rest of the Fediverse seems to not do this.

@Markus @Alfred Bühler @Saiwal

2026-10-22 02:29:58

Max Kostikov

kostikov@zotum.net

@Hubzilla Support Forum
If you are under reliatively fresh MySQL 8 then try to add this index
ALTER TABLE workerq ADD INDEX idx_claim (workerq_reservationid, workerq_priority DESC, workerq_id);
In my case, on the `dev` branch of Hubzilla, this significantly improved the performance of the queueworker from tens of seconds to a couple hundred milliseconds.
UPD: CPU usage has also dropped sharply.

FYI @Mario Vavti

2026-10-22 05:48:12

Mario Vavti

mario@hub.somaton.com

@Max Kostikov which query is it that took so long?

2026-10-22 06:09:59

Max Kostikov

kostikov@zotum.net

@Mario Vavti
It was this one on the queue with 6M+ tasks

SELECT workerq_id, workerq_cmd FROM workerq WHERE workerq_reservationid IS NULL ORDER BY workerq_priority DESC, workerq_id ASC LIMIT 1 FOR UPDATE SKIP LOCKED;

The MySQL version is 8.0.46

2026-10-22 06:31:32

Mario Vavti

mario@hub.somaton.com

@Max Kostikov 6M is a lot

Mostly convo right?

2026-10-22 06:48:53

Mario Vavti

mario@hub.somaton.com

@Max Kostikov have you tried index with workerq_priority and workerq_id only? All rows (except max worker count) will have workerq_reservationid NULL

2026-10-22 06:51:24

Max Kostikov

kostikov@zotum.net

@Mario Vavti No, but we can give a try.

2026-10-22 07:00:01

Mario Vavti

mario@hub.somaton.com

@Max Kostikov that would be great!

2026-10-22 07:05:24

Max Kostikov

kostikov@zotum.net

@Mario Vavti
Yes, it seems everything works fine even without `worker_id`. Perhaps, to ensure compatibility with older versions of MySQL, you could also omit `DESC`.

2026-10-22 07:20:54

Mario Vavti

mario@hub.somaton.com

@Max Kostikov do you mean without workerq_id or without workerq_reservationid?

2026-10-22 07:26:38

Max Kostikov

kostikov@zotum.net

@Mario Vavti
Without `worker_id`, since omitting `worker_reservationid` did not have a positive effect.
ALTER TABLE workerq ADD INDEX idx_claim (workerq_reservationid, workerq_priority DESC);

2026-10-22 07:50:38

Mario Vavti

mario@hub.somaton.com

@Max Kostikov ah... that is somehow suprising...

Anyway, i think i will go with

ALTER TABLE workerq
    ADD INDEX idx_claim (
        workerq_reservationid,
        workerq_priority DESC,
        workerq_id ASC
    );

as you suggested first.

2026-10-22 07:01:33

Mario Vavti

mario@hub.somaton.com

@Max Kostikov can't compete with your numbers

2026-10-22 06:14:12

Max Kostikov

kostikov@zotum.net

@Mario Vavti
Here is how EXPLAIN looked like before the index have been added

hz@localhost [hz]> EXPLAIN ANALYZE
    -> SELECT workerq_id, workerq_cmd FROM workerq
    -> WHERE workerq_reservationid IS NULL
    -> ORDER BY workerq_priority DESC, workerq_id ASC
    -> LIMIT 1 FOR UPDATE SKIP LOCKED;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 1 row(s)  (cost=401951 rows=1) (actual time=37560..37560 rows=1 loops=1)
    -> Sort: workerq.workerq_priority DESC, workerq.workerq_id, limit input to 1 row(s) per chunk  (cost=401951 rows=3.12e+6) (actual time=37560..37560 rows=1 loops=1)
        -> Index lookup on workerq using workerq_reservationid (workerq_reservationid=NULL), with index condition: (workerq.workerq_reservationid is null)  (cost=401951 rows=3.12e+6) (actual time=0.245..34819 rows=6.37e+6 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (37.63 sec)