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.
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:
Last 7 days:
7 days prior to the change (for perspective):