[5.x]: Queue with lots of large jobs degrades performance

[charliegrinsted]

What happened?

Description

When the queue contains a large number of jobs and the job column is large, the queue performance slows.

I believe this is due to the reserve method in the queue service sorting by priority, but the queue table not having an index for the priority column.

To test this, I created a dummy job class with an empty execute method and a large (200kb+) string parameter.

Obviously this is an extreme example, but we initially noticed this with Commerce jobs for order emails & catalog pricing.

When I add a composite index for channel, priority and id, performance is restored.

Steps to reproduce

1. Push a large number of jobs into the queue, where the job column blob is also relatively large
2. Observe query to reserve the job taking a longer amount of time

Expected behavior

Queue runs fast

Actual behavior

Queue runs slower than it could do

Craft CMS version

5.10.5

PHP version

8.5

Operating system and version

No response

Database type and version

MySQL 8.4.7

Image driver and version

No response

Installed plugins and versions

No response

[linear-code]

CMS-2208

[brandonkelly]

Do you know which database query(s) are seeing performance issues?

I created a queue locally with 150K rows, and not seeing any performance impact. The following query only takes 0.1 seconds:

SELECT `id`, `description`, `timePushed`, `delay`, `progress`, `progressLabel`, `timeUpdated`, `fail`, `error`
FROM `queue`
WHERE `channel`='queue'
ORDER BY CASE WHEN `fail`=FALSE THEN 1 ELSE 0 END DESC, CASE WHEN NOT (`timeUpdated` IS NULL) THEN 1 ELSE 0 END DESC, GREATEST(1781625101, `timePushed` + `delay`) ASC, `priority`, `id`
LIMIT 50

When I add a composite index for channel, priority and id, performance is restored.

Only those columns? At least with the above query, that wouldn’t even get factored in, as the existing indexes are better suited.

[charliegrinsted]

In my experience it's the reserve method, so:

SELECT *
FROM `queue`
WHERE (`channel`='queue') AND ((`fail`=0) AND (`timeUpdated` IS NULL)) AND (`timePushed` + `delay` <= 1781679223)
ORDER BY `priority`, `id`
LIMIT 1

It will take a couple of seconds for me, which obviously adds up.

Crucially, the value in the job column has to be quite large to replicate this. I've since rewritten some of our ephemeral jobs to store data elsewhere in the database rather in the job column itself.

Fair enough if you don't see it as a problem, since it doesn't happen if the job is efficiently written, but the "fix" I mentioned did resolve the initial slowness.