Detect and report slow database queries

When monitoring applications, an important and key aspect is often how the database is performing. Typically this can be one of the most common causes of latency, and being able to determine a poorly performing query can save significant time in the troubleshooting process.

Further if we can detect these prior to production, perhaps through the use of load testing (https://gitlab.com/gitlab-org/gitlab-ee/issues/3016) we can help companies avoid potential issues before they occur.

We should look at ways of detecting these queries for MySQL and PG databases.

For PG, it looks like there are a few possible options each with trade offs.

• Logging of queries that run over Xms with log_min_duration_statement. This does seem to need to be tuned, in the event you have a lot of long running queries. This also requires access to the PG log file, which would be difficult for customer hosted databases.
• Another option is the extension pg_stat_statements, but I am not clear on performance impact of this. It also seems to require superuser access, or at least the same user account as the account performing the queries to see the actual query text (which you need to make sense of the data)

@yorickpeterse do you have any guidance here perhaps, or other suggestions? Ideally we'd like to provide this to our customers to be able to capture and display this data in GitLab.

@joshlambert We already ship/support all of this in omnibus and use this for GitLab.com. The data is useful, but ultimately you are left only with the SQL queries (or parts thereof depending on how they're stored in pg_stat_statements). This means you still have to figure out where these originated from in the application yourself, as no such information is provided by PostgreSQL.

@yorickpeterse we export the actual SQL queries that are problematic / consuming the most DB resources?

I see what looks like stats on queries coming from different sections, but not the actual queries themselves. I assume some of these items make multiple underlying queries?

For example would be great to get something like this: (Click to see the Youtube Video)

@joshlambert

we export the actual SQL queries that are problematic / consuming the most DB resources?

pg_stat_statements is backed up every hour to a table called pg_stat_statements_history which is done using some custom script living somewhere in one of our Chef repos. Slow SQL queries in turn are logged to the PostgreSQL log file, but we don't extract those and store them elsewhere. Basically all of it requires some manual digging around.

added Product Vision 2018 label

added APM devopsmonitor labels

added GitLab Ultimate and removed GitLab Premium labels

This should be covered by Tracing: &89

This is because with either manual instrumentation, or automated instrumentation if available, database queries are commonly traced for performance. (They are a common source of latency.)

Closing as right now we do not plan to specifically work on this outside of the larger Tracing support.

closed

removed Product Vision 2018 label

added Enterprise Edition label