GitLab Plan Stage, 30 Mar 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Using performance bar statistics for identifying SQL query issues

Description

Overall statistics gathered from performance bar data can be used for identifying SQL query issues.

A

Show a new performance bar feature performance bar is a handy tool for identifying performance issues. On a specific page, for example, you can see a list of sql queries done by request and you can investigate its backtraces and identify methods which executes these slow queries or so many sql queries.

A

What is new is that recently we started gathering also overall statistics about about sql queries done by all requests which have performance bar enabled you can access this statistics by clicking the stats link in the performance bar.

A

And on the statistics page, you can see a couple of charts. First of all, what we do is that for each request, which has performance bar enabled we we parse backtraces of all sql queries and we group we group sql queries by its location. So we store methods which call.

A

We, which call any sql query and we log how many sql queries they execute and what was their duration.

A

So in the first chart we can see the overall number of processed requests, number of sql queries and duration of all sql queries. This chart is handy for identifying time frames when there was some.

A

Spike in duration or n plus one issues, for example, if you see a spike in duration graph, it means that during this time frame there was probably one or more sql queries executed and you can drill down in the chart and identify identify the method.

A

In from from the charts below, I will get to this later,.

A

So next chart is maximum number of sql queries by single request. So here we show a list of methods which execute.

A

Most sql queries per single request so, for example, perform method in pipeline chain create executes more than 1300 sql queries per single request, which might indicate uh some and pass one issue. So this this uh this chart in general is handy for identifying n plus one issue. Candidates.

A

Next next one is overall number of sql queries.

A

This is handy for identifying a methods which which execute sql query most frequently. So we can see that the full part is full part method executes an sql query and it's called really often so. This can be handy for identifying queries which might be optimized further because they are called so often total time is handy for identifying methods with biggest impact, because these methods execute this chart shows overall time, it's duration, multiplied by the frequency.

A

So any optimization done in these methods will have a biggest impact on the overall performance and the average duration shows the list of slowest sql queries or methods which execute the slowest sql queries.

A

So in these charts we show just metal locations, but it may not be obvious how to what is really going on or how to reproduce a request which executed it. So let me show you how to find a specific request.

A

Based on this method location, you can select some metal location, which you want to investigate further, and you need to search for the specific request id based on this location, and you can find this request id in sidekick blocks, because these statistics are processed in sidekick marker and, you can add a filter and filter by method.

A

Parts so now you get.

A

All all these requests, where processed by by by this performance, stats worker and you can take one of these request, ids and search for this specific request in rails.

A

A

Okay, like me, it's my request and we can see that in this case it's it's a method in pipelines, controller which does this excessive amount of sql queries so from here you can just try to.

A

Try to access the same page and see if you can reproduce the same issue and continue with debugging as you are used to. Okay. That's all! Thank you very much. You.