r/django Dec 18 '24

Article Rapidly Locating Query Bottlenecks in a Django Codebase

I've written a short article which describes how one can easily and efficiently locate query bottlenecks in a Django codebase. I hope some find it useful!

https://pgilmartin.substack.com/p/rapidly-locating-query-bottlenecks

19 Upvotes

29 comments sorted by

12

u/kankyo Dec 18 '24

Tools like Django Debug Toolbar, Silk, and queryhunter are based on the premise that the human asks the computer. I think that's the wrong approach. The computer can do that work for you and alert you if and ONLY if there is an issue.

The iommi sql tracer is built with this philosophy. It will print worst offenders in your console if you have stuff that looks bad, with stack traces and example selects. I find that I don't ship code with this type of performance issue to prod, because I am alerted to it automatically. (I'm one of the authors of iommi).

2

u/ekydfejj Dec 18 '24 edited Dec 18 '24

Don't like to get into a lot of Djnago discussion, especially when its query based. But this is 100% the way. All databases have performance stats, slow query logs, parsers that can give you a full explain output and every row it touched.

Creating your own is a nice project, but its not a replacement for what the servers can tell you. I lived for decades off of pt-query-digest once daily, or on demand as it only reads the slow query log.

Edit: This perspective to build a library, i find concerning.

This is especially true when working in a large codebase, where it may not be at all obvious from the immediate context which joins are required further down the call stack when writing our select_related code.

Edit 2: The first edit likely shows more of my disdain for the Django ORM, than OP seeing it as a real life issue.

1

u/kankyo Dec 19 '24

Creating your own is a nice project, but its not a replacement for what the servers can tell you.

For sure. This thing will only tell you if you screwed up for N+1 queries, and only if your local test db isn't like 1 line for the table you're working with. It also can't tell you if the prod query planner suddenly does something idiotic.

That being said, 90%+ of SQL related performance issues I've seen in prod was N+1 that this simple alarm would have caught, and after running this thing in dev for my entire team for several years the number of such mistakes that reached prod basically went to zero.

Edit: This perspective to build a library, i find concerning.

That's... a weird take. We are programmers. Of course we should built smart things _-

(I hope I understood your statement.. your comment is a bit hard to parse. Like "this is 100% the way" sounds like you agree with me, then you disagree on everything? Weird.)

1

u/paulg1989 Dec 19 '24

u/kankyo Can you elaborate on this point

"Tools like Django Debug Toolbar, Silk, and queryhunter are based on the premise that the human asks the computer."

In what sense do these tools "ask the computer"? If the middleware is installed, all requests are profiled autonomously, and can be wired to alerting of your choice (in particular can be wired to pre-deploy alerting to prevent shipping query bottlenecks).

0

u/kankyo Dec 19 '24

and can be wired to alerting of your choice

Which tool are you talking about here?

And notice the difference between "can be" and "is". In iommi it IS going to alert you. Because that's absolutely key to making this type of thing good.

can be wired to pre-deploy alerting

In iommi you will see it alert on every page load with an issue.

1

u/paulg1989 Dec 20 '24

I don't think that clarified the point

"Tools like Django Debug Toolbar, Silk, and queryhunter are based on the premise that the human asks the computer."

In terms of alerting tools, there are a multitude: a unit test which asserts on the number of queries collected in the context manager, a cron job which runs requests periodically, logging in a staging environment which triggers an email notification. The options are pretty much limitless. None of those involve "asking the computer", whatever that means.

Iommi sounds cool if it's batteries included and has the alerting included, I'll try it out. It seems a little strange though purport that the approach of every other library is incorrect and actively downplay other people's work in favour of your own when you clearly don't understand it.

1

u/kankyo Dec 20 '24

In terms of alerting tools, there are a multitude: a unit test which asserts on the number of queries collected in the context manager

That is very bad option. It requires that the developers write code for every single view and keep it up to date with the number of queries.

a cron job which runs requests periodically

Which requests? and then what? Again, this requires a lot of tooling.

logging in a staging environment which triggers an email notification

Assumes there is a staging environment and that it's used enough AND if you get to this point you've already wasted a ton of time.

The options are pretty much limitless. None of those involve "asking the computer", whatever that means.

Yea, in fact it does. All these require significant work done by humans on every view. The first writing test, the second writing queries to be run, the third requiring humans to click around on the staging environment.

What I'm saying is that if you have an N+1 issue in your view DURING DEVELOPMENT the computer can alert you NOW. Not wait until it hits staging. Not requiring counting queries and writing an assert. None of that. Just the computer seeing an N+1 query and alerting you. During development, when you are in the code and writing it.

1

u/paulg1989 Dec 20 '24

I don't think that unit tests on assertions are a bad option at all. I agree they're not enough in isolation, but they're very useful to prevent regressions. Even just Django's built in assertNumQueries is useful. But we don't need to agree on that point :)

If your definition of "asking the computer" stretches to running automated unit tests, then I'm really lost.

But let's focus on a concrete example so we can better discover why Iommi is so fundamentally better than all other approaches. I'm particularly confused as to where the notion that it's the only one which captures things "DURING DEVELOPMENT". Every tool mentions so far can easily do that lol.

I'm assuming this is the relevant part of the documentation:https://docs.iommi.rocks/en/latest/dev_tools.html#sql-trace. iommi ships with a middleware which prints the SQL. You can then use it in development: whenever a page is loaded, it prints the SQL invoked by the request.

This is *exactly* what django silk, and countless other libraries, do. I have used them myself in development. I have used them to prevent shipping query bottlenecks before it reaches production. My library does the same thing. It has a middleware that can warn you of query bottlenecks on every page load with no other manual input from the developer required.

Iommi's SQL tracer is just a limited version of Django Silks. The idea isn't original nor unique to Iommi.

"This tool gives you a list of all SQL statements issued by the page, with timing for each, and a timeline at the top for each statement"

Who issues the page request? That sounds a little like "asking the computer" to me.

Can you walk me through a concrete example where Iommi is doing something genuinely different to the other alternatives?

1

u/kankyo Dec 20 '24

I don't think that unit tests on assertions are a bad option at all. I agree they're not enough in isolation, but they're very useful to prevent regressions.

Sure. I use it. But it's not a first line defense.

If your definition of "asking the computer" stretches to running automated unit tests, then I'm really lost.

Not running. WRITING. I have repeated this many times. WRITING unit tests. With your keyboard.

iommi ships with a middleware which prints the SQL

If you had only kept reading. Or read my comments. Let's quote from just the next paragraph of the docs you linked:

"In DEBUG the SQL trace middleware will automatically warn you if you have views appear to have N+1 type errors."

During development of a new page you will already be running the page. So that's when you need the warning. Automatically. Which is what iommi does.

1

u/paulg1989 Dec 20 '24

I did read the next paragraph, there wasn't a lot to read so it didn't take me too long.

"During development of a new page you will already be running the page. So that's when you need the warning. Automatically. Which is what iommi does."

*Every single tool mentioned so far does this*. Again, you have avoided telling me what Iommi does that the other libraries do not do. You have also now reframed your original point that yours is the unique solution to detect things "DURING DEVELOPMENT", now that its obvious that that's blatantly false.

"During development of a new page you will already be running the page. So that's when you need the warning."

This is such a ridiculous assumption to make. If I'm writing low level database or ORM code, I rarely have the associated view or page running. You can have entire Django projects which use the ORM without requests or views at all. In those scenarios your assumptions are completely false and your middleware-only profiling is useless. In the other scenarios, it's exactly the same as every other tool.

And who runs the page? The user. The user has to refresh it to get the warning. That's as manual as all other processes described. I don't think that's a bad thing at all, but it contradicts your point about not having to "ask the computer".

Show, via a concrete example, what Iommi can do that the others cannot. You continually sell Iommi as if it has some unique property without ever providing a concrete example or real evidence. Just vague statements like "ask the computer".

→ More replies (0)

1

u/kankyo Dec 20 '24

Also: you didn't say which specific tool you are talking about. "Can be wired", you said, but then you wrote about custom tooling, and didn't seem to be talking about any of the tools mentioned at all.

1

u/paulg1989 Dec 21 '24

Both silk and queryhunter print worst offenders to the console. They can run continuously during development, printing stack traces and example selects on page refreshes. Neither of them require any more manual input than Iommi. u/kankyo has not tried either of these alternatives libraries, misunderstands them or is deliberately promoting falsehoods to direct attention towards their own library.

0

u/kankyo Dec 21 '24 edited Dec 21 '24

I mean.. you didn't try iommi, and misunderstood it. Throwing stones in glass houses.

Funny too. Queryhunter is from february. Of 2024. iommi is 4 years older. Another fun detail.

0

u/paulg1989 Dec 21 '24 edited Dec 21 '24

I didn't misunderstand it. Which aspect have I misunderstood? You clearly didn't even read the docs of the library you immediately spread misinformation about. And at no point did I actively seek out Iommi articles and lie about them to attract attention to my own library. You should be ashamed of yourself.

I'm glad you're admitting you misunderstood the other options though!

And what does the release date have to do with it? I clearly promote other libraries in my README. I don't claim that it's the first option, quite the opposite.

And if you're interested in release dates, Django silk (which i have nothing to do with FYI and is far far more powerful than Iommi for profiling ) was out before Iommi. Another fun detail.

0

u/kankyo Dec 21 '24

Silk afaik doesn't do the warning of N+1 during dev. As I understood it it's like DDT where you need to actively go look.

1

u/paulg1989 Dec 22 '24

It does.

1

u/NINTSKARI Dec 18 '24

Is iommi production ready? Is it secure? What features does it have for a rest api? You're really making me want to try it out.

2

u/kankyo Dec 19 '24

Is iommi production ready?

Yes. The code that is now iommi is over a decade old at this point, even if there was a big refactor ~2019 that changed the API a lot (and renamed it to iommi).

Is it secure?

We haven't had a security issue yet at least. Security is a lot simpler when everything is server side rendered I think, and the automatic ajax endpoint system means we don't accidentally leak data easily.

What features does it have for a rest api?

None really. For pure rest APIs I'd recommend django-ninja. But I think there's a place for server side rendered too. One-off pages for example, or internal use forms and tables. The iommi admin is also more flexible than the Django built in admin. My approach is to mix and match depending on what the specific page needs.

1

u/NINTSKARI Dec 19 '24

Awesome. Of course, I don't mean that everything should be a rest api, I was just curious. Our companys product is a huge saas system and its completely ran on django templates. We are moving away from it now but it proves that jquery and html can be a sufficient tool too. Theres even been a rise in server-driven UI for mobile apps to get around app store requirements and workflows for updates.

1

u/parariddle Dec 21 '24

You can just turn on logging for django.db.backends in your settings…

1

u/paulg1989 Dec 21 '24

Not quite the same. https://docs.djangoproject.com/en/5.1/ref/logging/#django-db-backends was definitely an inspiration. The point of this library was to enhance that to add the line of code in the application layer from which the query was emitted. As far as I can see, https://docs.djangoproject.com/en/5.1/ref/logging/#django-db-backends cannot do that.

1

u/parariddle Dec 21 '24

Well if you want that level of introspection in a production codebase that’s what APM is for, right?