Add per day counter of views for analysis

I'd like to be able to make a tabular overview of which view type is built, how often, how long it takes, etc. similar to the tabular overview of git timings and cache timings.

I would subsequently add a table to performance.gitlab.net with the following query

SELECT "action", "count" as "Amount", "duration_mean" AS "Mean", "duration_50th" AS "50th Percentile", "duration_95th" AS "95th Percentile", "duration_99th" as "99th Percentile" FROM downsampled."[[process_type]]_view_timings_per_action_and_view_per_day" WHERE $timeFilter AND action =~ /[[action]]/ ORDER BY time DESC

Obviously, this may reveal a limited understanding on my part on how many "different" kinds of views are called... I am assuming they are not unique per user, i.e. it is a manageable set, just like the git timings (169 elements) and the cache timings (550 timings).

I'm assuming we can not do this for the postgresql timings, because those are unique to the user who calls them. (?)

changed the description

Views may be conditional, but usually they are not I think. Views work much like functions: you start with 1 view, this view in turn may call other views. This means that the first view is usually not the most interesting since it also includes timings of all sub views.

I'm assuming we can not do this for the postgresql timings, because those are unique to the user who calls them.

I'm not sure if I fully understand this. Do you want to show timings per SQL query? If so, we can't do that in Grafana since we don't measure that data in InfluxDB.

merged

@yorickpeterse

Do you want to show timings per SQL query? If so, we can't do that in Grafana since we don't measure that data in InfluxDB.

Yes, pretty much. Where do we store that data then? Just in postgresql on an aggregate basis per query?

@yorickpeterse I added the table here https://performance.gitlab.net/dashboard/db/daily-overview?panelId=16&fullscreen&edit&orgId=1&tab=general, but it is not populating yet. I'll check tomorrow after it has had enough time to aggregate data... but wondering if you agree that this is the only issue with it.

@ernstvn

Yes, pretty much. Where do we store that data then? Just in postgresql on an aggregate basis per query?

PostgreSQL keeps track of executed queries (with some of their values stripped out) in pg_stat_statements, which IIRC is reset every hour (and copied to pg_stat_statements_history). Besides that we track slow queries (queries taking more than 1 second) in our log files, but that's all we store query wise.

but it is not populating yet.

Woops, I completely forgot about this MR and didn't add the query to Influx after it got merged. The query has been added now.

Thanks @yorickpeterse. I’m clearly still missing the mental step to ask you (or others) to update the query in InfluxDB.

@ernstvn It involves running rake queries in this repository essentially, which ensures all queries are in place. It's a bit annoying but there's no way of automating this otherwise that I know of (e.g. there are no "database migrations" for influx).

@yorickpeterse I can use your advice on the actual query in Grafana, I must have made some mistake since https://performance.gitlab.net/dashboard/db/daily-overview?panelId=16&fullscreen&orgId=1 is borked. Can you please take a look?

@ernstvn The query was OK, but the table columns weren't configured properly. It's working now (I just copied the JSON from another table to save time, then adjusted it).

Thanks @yorickpeterse . I'll see if I can find that JSON and make a remark about it on handbook/infrastructure/monitoring.

assigned to @ernstvn

@ernstvn To get the JSON you'll need to:

1. Click the title of a graph
2. Click the hamburger icon that pops up
3. Click on "Panel JSON"
4. Conveniently the JSON will now pop up at the top of the page, meaning you may have to scroll up to see it