DatabaseQueue::claimItem() is inefficient with huge data

TLDR

BatchQueue::claimItem() has a query which can cause memory issues in the database engine, if the queue table contains huge chunks of data for a single batch.

This can lead to the query getting stuck.

The visible effect is that the batch operation gets stuck / does not start.

Background / use case

On a project I am working on, I encountered a case where a query in BatchQueue::claimItem() got stuck, because another module (search_api_et) added unreasonably large chunks of data to the 'queue' table. See #3124298: Huge data size in batch queue items.

The first lesson of course is to fix that contrib module.

But I also found a possible (memory) optimization to the query in core, so will scale much better to huge batches. See #985184-6: Poor performance of batch API with large queues ff.

Technical details

The query looks like this:

public function claimItem($lease_time = 30) {
    while (TRUE) {
      try {
        $item = $this->connection->queryRange('SELECT [data], [created], [item_id] FROM {' . static::TABLE_NAME . '} q WHERE [expire] = 0 AND [name] = :name ORDER BY [created], [item_id] ASC', 0, 1, [':name' => $this->name])->fetchObject();
      }
      catch (\Exception $e) {
        $this->catchException($e);
      }
     [..]

In our case, the 'queue' table contained around 500 entries for a single batch name, and the 'data' column was quite big for each one.

Internally, it seems MySQL does the following:

• Build the full list of 500 results with data + item_id. This can get very big if the 'data' column has long values.
• Sort the full list of items with data + item_id.
• Limit the list to the first item.
• Return the first item.

It is possible to run this query directly in sqlc or in devel/php, without running a batch, given that:

• the data is still present in the table.
• you specify the correct 'name' parameter which actually has this huge result set.
• the query contains an ORDER BY. Without that, it will not get stuck.

Symptoms:

• If the data is sufficiently large, the query gets stuck.
• If the data is not too large, the query is quite fast.
• I have not found a case where the query is simply just slow but eventually finishes. This suggests that this is a memory issue inside the database engine, NOT a performance issue.

Solution

The solution is quite simple:

• First fetch only the item_id, so that the temporary list inside MySQL will be smaller.
• Use a second query to fetch the data for the given item_id.