“I absolutely love AppSignal.”

Recently we noticed that our database was doing a lot of queries while processing our queue. Lets see if we can fix that.

Our background worker was looping through a given list of items and for each item it performed several MongoDB queries.

It generated:

• Three finds
• Two upserts
• One set
• One insert

This is six queries per item, where a batch of items consists of 10 to 100 items. This quickly results in a lot of queries!

The class looked something like this:

class EntryWorker
  def perform(id, payload)
    site = Site.find(id)

    payload.entries.each do |entry|
      ItemProcessor.new(site, entry).process!
    end
  end
end

Where the ItemProcessor does the processing and querying.

To reduce the number of queries we’ve wrapped the ItemProcessor with a class that tries to cache as much data for queries as possible. Here’s a simplified version:

class EntryWorker
  def perform(id, payload)
    site = Site.find(id)
    @inserts = []
    @increments = {}

    payload.entries.each do |entry|
      ItemProcessor.new(site, entry).process!
    end
    flush_inserts!
    flush_increments!
  end
end

The ItemProcessor populates the hashes and arrays set by the perform method and when the loop is complete, the data is flushed to the database.

Lets take the increments for example, the data format for this hash is:

{
  [bson_id] => {
        'hits' => 10,
        'exceptions' => 1,
        'slow' => 2
  }
}

This way we can loop the increments at the end and push them to the database.

# Pushes the increments to MongoDB
def flush_incs!
  @increments do |id, incs|
    Model.
      where(:id => id).
      find_and_modify('$inc' => incs)
  end
end

So instead of dozens of upserts that increment a number by 1, we now have a few with the total counts.

Mongoid doesn’t support bulk inserts, but the driver it depends on (Moped) does. If we drop down to the collection class we can use that to insert everything at once.

# Pushes all inserts to MongoDb at once
def flush_inserts!
  Model.collection.insert(@inserts)
end

Instead of having hundreds of inserts that take ~ 1ms. We now have one insert that takes only a couple of ms.

We did the same with all the finds, all the data is fetched before the loop and stored in an array. We use array.find to select the correct object and use that in the ItemProcessor.

You can see the results below: