Bulk Process Data to Decrease Database Hits and Boost Performance
Tags: rails, ruby, performance
Published: 2019-01-10 15:09:53

---
title: Bulk Process Data to Decrease Database Hits and Boost Performance
published: true
description: How processing data in bulk can decrease database hits and dramatically improve your performance.
tags: rails, ruby, performance 
cover_image: https://thepracticaldev.s3.amazonaws.com/i/abn8hn3tj8ws1w5xldta.png
---

When asked to speed up data processing, often the first thing we do as Site Reliability Engineers, is dive into slow query optimization. However, sometimes that is not enough. This was the case for us at Kenna. Despite optimizing all our slow queries, we still couldn't process data fast enough. In addition, our MySQL database was constantly maxed out on CPU. It was so bad, that even simple requests were a struggle. Before I dive into the details, I first want to give you a little background on Kenna so you have some context for this story.

### Kenna Security
Kenna helps Fortune 500 companies manage their cybersecurity risk. The average company has 60 thousand assets. An asset is basically anything with an IP address. The average company also has 24 million vulnerabilities. A vulnerability is anyway you can hack an asset. With all this data it can be extremely difficult for companies to know what they need to focus on and fix first. That’s where Kenna comes in. At Kenna, we take all of that data and we run it through our proprietary algorithms. Those algorithms then tell our clients which vulnerabilities pose the biggest risk to their infrastructure so they know what they need to fix first. 

We initially store all of this data in MySQL, which is our source of truth. From there, we index the asset and vulnerability data into [Elasticsearch](https://www.elastic.co/products/elasticsearch).

![](https://thepracticaldev.s3.amazonaws.com/i/9xmvs6beft38mb4z6pik.png)

In order to index all these assets and vulnerabilities into Elasticsearch we have to serialize them and that is exactly what I want to cover in this post, serialization! Particularly, I want to focus on the serialization of vulnerabilities since that is what we do the most of at Kenna.

### Serializing Vulnerabilities
When we first started serializing vulnerabilities for Elasticsearch we were using [`ActiveModelSerializers`](https://github.com/rails-api/active_model_serializers) to do it. `ActiveModelSerializers` hook right into your ActiveRecord models so all you have to do is define what methods you want serialize and it takes care of the rest. 

```ruby
module Serializers
  class Vulnerability < ActiveModel::Serializer
    attributes :id, :client_id, :created_at, :updated_at,
                    :priority, :details, :notes, :asset_id,
                    :solution_id, :owner_id, :ticket_id
  end
end
```

It is super easy, which is why it was our first solution. However, it became a less great solution **when we started serializing over 200 million vulnerabilities a day.** Our vulnerability data is constantly changing and we have to make sure to keep it insync between MySQL and Elasticsearch which is why we have to do so much serialization. As the number of vulnerabilities we were serializing increased, the speed at which we could serialize them dropped dramatically. Soon, we couldn't keep up with the rate at which our data was changing!

In addition, our database began to max out on CPU. 

![](https://thepracticaldev.s3.amazonaws.com/i/5ir2wh0wubrzwlk6rb5n.png)

The caption for this screenshot in Slack was “11 hours and counting”.  Our database was literally on fire ALL THE TIME! Some people’s first reaction when they see this graph might be to say, why not just beef up your hardware? Unfortunately, at this point, we were already running on the largest RDS instance AWS had to offer so that was not an option. When my team and I saw this graph we thought there has to be a horrible MySQL query in there. So off we went went, hunting for that elusive horrible query. 

![](https://thepracticaldev.s3.amazonaws.com/i/wanphqfdf74ivoa4ko8z.gif)

But we never found any horrible, long running queries because they didn't exist. Instead, we found a lot of fast, milli second queries executing over and over again. All these queries were lightening fast, but we were making so many of them at a time that the database was overloaded. We immediately started trying to figure out how we could serialize all of these vulnerabilities and make less database calls. What if, instead of making individual calls to get data for each vulnerability, we group all of the vulnerabilities together and make a single call to get all the data at once.  

![](https://thepracticaldev.s3.amazonaws.com/i/kvtbrms156mzm6wgfdwx.png)

From this idea came the concept of bulk serialization. 

### Bulk Serialization
In order to implement this we started with a cache class that was responsible for taking a set of vulnerabilities and a client and looking up all of the related data in MySQL at once. 

```ruby
class BulkVulnerabilityCache
  attr_accessor :vulnerabilities, :client, :vulnerability_ids

  def initialize(vulns, client)
    self.vulnerabilities = vulns
    self.vulnerability_ids = vulns.map(&:id)
    self.client = client
  end	

  # MySQL Lookups
end
```

We then took this cache class and passed it to our vulnerability serializer, which still had all the formatting logic needed to serialize each field. 

```ruby
module Serializers
  class Vulnerability
    attr_accessor :vulnerability, :cache

    def initialize(vuln, bulk_cache)
      self.cache = bulk_cache
      self.vulnerability = vuln
    end
  end
end
```
Except now, the serializer would get the data **from the cache** instead of the database. Let’s look at an example. In our application, vulnerabilities have a related model called Custom Fields. 
```ruby
class Vulnerability
  has_many :custom_fields
end
```
Before, when we would serialize custom fields, we would have to talk to the database. Now, we could simply read from the cache. 

![](https://thepracticaldev.s3.amazonaws.com/i/5wenwyt0r3a54s1on96s.png)

### The Result 
The payoff of this change was big! For starters, the time to serialize vulnerabilities dropped dramatically. To serialize 300 vulnerabilities individually takes just over 6 seconds.

```ruby
(pry)> vulns = Vulnerability.limit(300);                                                           
(pry)> Benchmark.realtime { vulns.each(&:serialize) }                                                                                                                          
=> 6.022452222998254
```
Often, it would take even longer when the database was maxed out. If we serialize those exact same 300 vulnerabilities in bulk...

```ruby
(pry)> Benchmark.realtime do                                                           
>    BulkVulnerability.new(vulns, [], client).serialize                                              
> end                                                                                          
=> 0.7267019419959979
```
it takes **less than a second!** These speed ups are **a direct result of the decrease in database hits** we have to make to serialize the vulnerabilities. 

When we serialize those 300 vulnerabilities individually we have to make **2100** calls to the database. When we serialize them in bulk, we only have to make **7** calls to the database. You can probably glean from the math that it's 7 calls per individual vulnerability OR 7 calls for however many vulnerabilities are grouped together at once. In our case, we index vulnerabilities in batches of 1000. This means we took the number of database requests we were making from 7k down to 7 for every batch.  

This drop in database requests is easy to see in this MySQL queries graph. The graph shows the number of queries being issued BEFORE and AFTER we deployed the bulk serialization change. 

![](https://thepracticaldev.s3.amazonaws.com/i/d8axqmq9df06j9mnrvc9.png)

With this large decrease in queries also came a large drop in database load which you can see in this RDS CPU utilization graph. 

![](https://thepracticaldev.s3.amazonaws.com/i/21etbrhxf8s9oajohxgj.png)

### Lesson Learned
The moral of this story is when you are processing large amounts of data try to find ways to process that data in bulk. We did this for serialization, but the same concept can apply anytime you find yourself processing data in a one-by-one manner. Take a step back, and ask yourself, could I process all of this data together? Because one database call for 1000 IDs is always going to be faster than 1000 individual database calls.

 _If you are interested in other ways to prevent database hits using Ruby checkout my [Cache Is King](http://confreaks.tv/videos/rubyconf2018-cache-is-king-get-the-most-bang-for-your-buck-from-ruby) speech from RubyConf which is what inspired this post._