I'm a bit new to all this D7 stuff, but I was generating content using devel generate, and tried to create 500,000 items. It was taking a long time to complete, and so I investigated, and the line that was taking a long time to execute was:

class BatchQueue extends SystemQueue {

  public function claimItem($lease_time = 0) {
    $item = db_query('SELECT data, item_id FROM {queue} q WHERE name = :name ORDER BY item_id ASC', array(':name' => $this->name))->fetchObject();

Which was returning 500,000 rows from the db to just get the first one :( Sad times.

Patch on its way.

CommentFileSizeAuthor
#1 drupal-batch-performance-985184.patch830 bytessteven jones

Comments

steven jones’s picture

steven jones commented
Status: Active » Needs review
Issue tags: +Performance
StatusFileSize
new drupal-batch-performance-985184.patch830 bytes

Patch attached. Let's see what the testbot thinks.

moshe weitzman’s picture

moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

Ouch. Nice catch.

webchick’s picture

webchick
she/they
Primary language English
Location Vancouver 🇨🇦
commented
Status: Reviewed & tested by the community » Fixed

D'oh! :)

Committed to HEAD. Thanks!

Status: Fixed » Closed (fixed)
Issue tags: -Performance

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags: +Performance

Restoring issue tags, see #2125755: System messages removed all issue tags during D7 upgrade.

donquixote’s picture

donquixote commented
Issue summary: View changes

This can still take longer than needed, if the "data" column contains large chunks.

Internally, it seems MySQL works like this:

  • Build the full list of 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.
  • Return the first item.

We have a case here with search_api_et, but it is possible that there is a bug in our code or in search_api_et that causes the data to be so big.

Still, it would be useful to optimize this one step further.

donquixote’s picture

donquixote commented

The case I ran into was indeed due to a bug in search_api_et:
#3124298: Huge data size in batch queue items

Still it would be useful to optimize.

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.

Btw, some observations:

  • The query only gets stuck if it has an ORDER BY. I think otherwise MySQL will not build the huge list internally.
  • Depending on the size of data and number of items, the query either gets completely stuck, or is super fast. There is no in-between.
    This suggests that we are dealing with a memory issue, not a performance issue.

I am going to create a new issue for this.

donquixote’s picture

donquixote commented
Related issues: +#3124304: DatabaseQueue::claimItem() is inefficient with huge data

New issue: #3124304: DatabaseQueue::claimItem() is inefficient with huge data