Laravel Queue Slow Query

[ekaterina4952]

Hi, I am using laravel queue. but I am struggling with so slowing on laravel application. (CPU 8, RAM 32GB, IOPS 100MB/s)
Most slow query log are related with queue. over 2k slow query log per day. Please advise. Thanks advance.

select * from jobs where queue = 'default' and ((reserved_at is null and available_at <= 1707701703) or (reserved_at <= 1707701613)) order by id asc limit 1 FOR UPDATE SKIP LOCKED;
Time: 2024-02-12T01:35:42.568834Z
User@Host: api[api] @ localhost [127.0.0.1] Id: 71261
Query_time: 38.893383 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 11

[ekaterina4952]

Laravel Pulse

[ekaterina4952]

[bulletproof-coding]

DB queue is not recommended in prod environment because its inability to offer ATOMIC locks. SQS or other messaging system is recomended.

[ekaterina4952]

Hi @marius-mcp, this query slowing happens too many per day. any thoughts? Thanks in advance.

[ekaterina4952]

delete_at index isnt helpful for me

[bulletproof-coding]

Update
@ekaterina4952 Please run explain and then your query and show the results (print screen).

[bulletproof-coding]

@ekaterina4952 see this issue #54068

[ekaterina4952]

explain select * from tablewhereid= 123445678 andtable.deleted_at is null limit 1

[bulletproof-coding]

@ekaterina4952 123 mil rows is something I don't have experienced (my exp is limited to few millions only) so I can only guess that 5 seconds to fetch a row by its primary key must be because of server business or row/table lock. Anyway thanks for this as I saw that a composed index on deleted_at and another column is not used when deleted_at is nullable.