appsignal

A quick post about reducing MongoDB queries

Robert Beekman

Robert Beekman on

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:

Query results

Share this article

RSS
Robert Beekman

Robert Beekman

As a co-founder, Robert wrote our very first commit. He's also our support role-model and knows all about the tiny details in code. Travels and photographs (at the same time).

-> All articles by Robert Beekman-> Become an AppSignal author

AppSignal monitors your apps

AppSignal provides insights for Ruby, Rails, Elixir, Phoenix, Node.js, Express and many other frameworks and libraries. We are located in beautiful Amsterdam. We love stroopwafels. If you do too, let us know. We might send you some!

Discover AppSignal
AppSignal monitors your apps