Identify reporting mechanism for query times

[jeff2d2]

Why: Currently, there isn’t an easy way for us to see database query times and therefore it is difficult for us to know what queries are the slowest and need to be optimized.

What: This work needs to be timeboxed just to 1 sprint. If we don’t have the tools to built this dashboard quickly, we should identify what tool could solve our problem and talk with Dror + Fleet about acquiring what we need.

AC

• [x] Look into options for visualizing database query times, focusing first on what we tools we have, but not restricted to only those
• [x] Draft a quick-ish write-up of suggested tool and approach
• [x] Follow-up with Dror + Fleet if the tool is something we need to acquire

[LindseySaari]

PgHero (actively working on this)

I was able to successfully get the performance dashboard up and running by adding in a middleware and doing some css overriding of the default pg_query gem behavior.

PgHero doesn't currently have support for Rails in API only mode, BUT We can get around this by overriding some of the middleware and adding in views needed for the performance dashboard.

PgHero has console functionality and a nice UI that visualizes a performance dashboard for Postgres. It has the ability to point out slow running/non-performant queries.

For the time being, we may want to put this behind basic auth until we can implement the Github teams/Oauth based authentication strategy.

PgHero was different from sidekiq due to pg_query being implemented as an engine whereas sidekiq is a rack app.

Performance Insights for Amazon RDS

We may want to additionally use this insights service.

Myself and possibly the rest of the team don't have RDS permissions in AWS currently to test this out.

By default, Performance Insights is enabled in the console create wizard for Amazon RDS engines. If you have more than one database on a DB instance, Performance Insights aggregates performance data

Performance Insights has an available dashboard with information containing active sessions grouped by waits, SQL, users, or hosts. There's information containing DB load items such as a SQL statements and which queries are contributing the most to DB load. Additional Information here

[jeff2d2]

Thank you @LindseySaari! I know you're still actively working on this, but would you be able to guesstimate the implementation effort for getting PgHero fully up and running and behind basic auth? We could potentially start with that as an MVP, and then look into the insights service in a later effort.

[jeff2d2]

cc'ing @drorva and @f1337 even though this isn't a new tool to acquire, but to follow along and ensure we're staying on the right path for the desired outcome.

[LindseySaari]

@jeff2d2 I think the effort of getting PgHero up and running is feasible this sprint! Standing up basic auth requires some new credstash variables and a devops PR, which are both relatively straightforward. I agree that the insights service can be integrated into an MVP and revisited during a future effort.