Development

How We Achieved a 1,000X Boost in Performance By Adding One Database Table

By November 1, 2016 No Comments

We all want things to be fast. There are numerous studies that show users get frustrated when a website takes too long to load. Services like Google have set the bar high in terms of speed and response time. Google returns highly complex search results in less than a second.

Depending on the complexity of your website or web application, it can take a LOT of engineering to make it run fast. As a digital agency, we have to keep in mind the additional constraints of our client’s budgets, and sometimes the effort required to squeeze out those few extra milliseconds isn’t worth the cost.

Sometimes, though, we can gain massive performance improvements with a modicum of effort. Here’s how we recently achieved a 1,000X (thousand times) boost in speed for one of our Ruby on Rails web applications just by adding a single database table!

Problem

We have a large Ruby on Rails web application for one of our clients that handles a very large amount of data. The application collects sales and diagnostic data from 1,300 locations all across the USA every minute. The application provides numerous reports for local, regional, and national managers to compare sales data across different product lines and geographic regions broken down by hour, day, week, month, and year.

All sales, information, and diagnostic data is stored in a single database table that currently has over 170,000,000 rows. The majority of all reporting logic for the application reads and aggregates data from this table. So far, this has actually performed very well and has been fast enough to satisfy our needs.

Recently, however, we were asked to create a new report in the application that would show sales numbers aggregated by each product line, for all locations, spanning 2 years of time. The report would need to look something like this:

Location Today Yesterday This Week Last Week This Month Last Month YTD Last YTD TTM Last TTM
A $150 $271 $8,123 $9,876 $45,678 $43,634 $445,672 $513,112 $699,321 $770,863
B $75 $123 $4,139 $5,863 $23,781 $21,341 $231,721 $250,232 $387,317 $489,634
C $50 $71 $1,234 $2,764 $11,784 $13,344 $125,624 $133,124 $299,214 $370,235

Without going into all the details, it was essentially taking the system 4 hours each day to crunch the data needed to generate this report. That means for a period of 4 hours each day the data in this report is not 100% accurate, which is not great. We would run this task at 1:00 AM in the morning, so by the time a real human would need this report the data would be there, but still not an ideal situation.

Possible Solutions

So, we thought to ourselves, how could we speed up this process and make it as close to real-time as possible?

Idea 1: Faster Hardware

A common tactic is to just “throw more hardware at the problem” which means move to a faster server (more memory, better CPU, etc). This is usually a fairly economical choice since servers are relatively cheap these days, when compared to paying a team of developers. However, in this case we decided against this for two reasons:

  1. Site is already running on some beefy hardware
  2. This problem is only going to get worse as we collect and store more data every minute (and old data is never destroyed)

We didn’t want to get in the habit of constantly “up-sizing” hardware just to keep up with demand. Servers are relatively cheap, but at some point the costs start to get out of control.

Idea 2: Optimize Database Queries

Next, we thought there might be some optimization we could do on our SQL queries to the database. However, after further review, we had already added the appropriate database indexes to our MySQL table, and there didn’t seem to be much else we could do there. The table size was so large that adding more indexes could also potentially make the queries worse!

Idea 3: Summary Tables

Next, we decided to do more research on the concept of summary tables which are commonly used in larger database systems or “data warehouses”. A summary table is a new table you add to your database that summarizes the data contained in another table. In our case, we had a single events table that contained ALL the data ever received from all locations, down to the second. However, almost all of the reporting screens in our web application really only cared about “day” time periods, not seconds. Most people just cared about sales data for the current day, current week, month, etc. There wasn’t a need to show a real-time stream of sales data coming in each second. In fact, since there’s so much activity, a real-time stream would be unusable since your eyeballs couldn’t keep up with the screen!

Final Solution – One Thousand Times Faster!

We decided to implement a single summary table that we named daily_summary that had a schema like this:

date location_id total_count product_1_count product_2_count total_revenue product_1_revenue product_2_revenue
2016-10-01 1 51 35 16 510.00 35.00 16.00
2016-10-01 2 100 40 60 125.00 50.00 75.00
2016-10-02 1 70 55 15 70.00 55.00 15.00
2016-10-02 2 99 65 34 123.75 81.25 42.50

This table contains a single row per day, per location, with the total (sum) of sales for that day. Now our reports can query this daily_summary table and get results MUCH faster than our events table. We also added a few database indexes on this table to speed things up even more.

How much faster, you ask?

Previously, it was taking 4 hours to crunch the data for a 1 day period. After implementing the daily_summary table, we can now crunch the data for 1 day in just 14 seconds! That’s over 1,000 times faster (14,400 seconds down to 14 seconds).

We can now compute 2 years of data in the same time it used to take to compute a single day.

Summary tables do add some complexity to your code, since you have to keep them up-to-date any time new data comes in, but in this case the tradeoff was worth it for the massive performance gains.

Web Application Startup Guide

A 30-page ebook that covers positioning, marketing, pricing, and building your startup product, plus more.

Leave a Reply