@Hubzilla Support Forum If you are under reliatively fresh MySQL 8 then try

Submitted ⁨⁨1⁩ ⁨day⁩ ago⁩ by ⁨kostikov@zotum.net@zotum.net⁩ to ⁨adminsforum@hubzilla.org⁩

@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.

FYI @Mario Vavti

original

Comments

Sort:hotnew top

mario@hub.somaton.com@hub.somaton.com ⁨1⁩ ⁨day⁩ ago

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

original

kostikov@zotum.net@zotum.net ⁨1⁩ ⁨day⁩ ago

@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)

original

kostikov@zotum.net@zotum.net ⁨1⁩ ⁨day⁩ ago
@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
original
- mario@hub.somaton.com@hub.somaton.com ⁨1⁩ ⁨day⁩ ago
  @Max Kostikov have you tried index with workerq_priority and workerq_id only? All rows (except max worker count) will have workerq_reservationid NULL :thinking_face:
  original
  - -> View More Comments
- mario@hub.somaton.com@hub.somaton.com ⁨1⁩ ⁨day⁩ ago
  @Max Kostikov 6M is a lot :astonished_face:
  Mostly convo right?
  original
  - -> View More Comments