The power of indexes

Brandon Johnson

tl;dr: If you think there’s slowness or performance issues in your application database, ask your local friendly DBAs. Often times we can help (and we like to!) — (graphs below for evidence) :)
Or if you don’t have a DB team, check out this great guide on how to check for slow queries.

Longer version:

Last week, one of the release engineering staff approached me in regards to some slow performance on our buildbot database systems.

Upon investigation, we realized there was a significant buildup of slow queries during weekdays. In this system, slow queries were defined as any query taking longer than 2 seconds.

After a short investigation, it was pretty easily determined that there was a set of queries repeating about 1-2 times every second, that took 2 seconds to run. This query was able to gain some substantial benefits from the addition of only a few single indexes. Since the impact was low, we decided to execute this change into dev/stage and then into production on Tuesday.

The graphs below show the impact of the change over the course of 3 days (Sunday, Monday, Tuesday) where the change was implemented on the morning of Tuesday the 28th, and again for the last 7 days.

3 days around the change:

Screen Shot 2014-02-03 at 12.43.10 PM

 

Last 7 days:

Screen Shot 2014-02-03 at 12.48.58 PM

 

7 days prior to the change (for perspective):

Screen Shot 2014-02-03 at 12.53.15 PM