Comment - FBXL Lotide

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

mario@hub.somaton.com@hub.somaton.com ⁨2⁩ ⁨days⁩ ago

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

Sort:hotnew top

kostikov@zotum.net@zotum.net ⁨2⁩ ⁨days⁩ 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 ⁨2⁩ ⁨days⁩ 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 ⁨2⁩ ⁨days⁩ 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
  - kostikov@zotum.net@zotum.net ⁨2⁩ ⁨days⁩ ago
    @Mario Vavti No, but we can give a try.
    original
    mario@hub.somaton.com@hub.somaton.com ⁨2⁩ ⁨days⁩ ago
    @Max Kostikov can't compete with your numbers :rolling_on_the_floor_laughing:
    original
    -> View More Comments
    mario@hub.somaton.com@hub.somaton.com ⁨2⁩ ⁨days⁩ ago
    @Max Kostikov that would be great!
    original
    -> View More Comments
- mario@hub.somaton.com@hub.somaton.com ⁨2⁩ ⁨days⁩ ago
  @Max Kostikov 6M is a lot :astonished_face:
  Mostly convo right?
  original
  - kostikov@zotum.net@zotum.net ⁨2⁩ ⁨days⁩ ago
    @Mario Vavti That's right. But the queue moves along normally after the index is added.
    original