A Related Matter: Optimizing your webapp by using django-debug-toolbar, ... with Christopher Adams

Speaker 1: Hello everyone. Hello Django Khan. Um this is this talk is about Django Debug Toolbar. And a few optimizations that may be helpful for you. To introduce myself, my name is Christopher Adams. I'm currently at GitHub, previously at Venmo. I'm at Adam C64. Interestingly, there's uh quite a few people named Chris Adams involved in the Python space. In fact, even in the Django space. I am not A C D H A, who works at the Library of Convent of Congress. I don't know if he's here. I gave a talk years ago where uh a friend of his was here and thought that he had come to hear him, but he didn't he didn't. Uh so uh I'm uh I'm also not the gentleman Chris Adams, a 19

Speaker 1: Aries uh nine nineties era professional wrestler. Some may remember him, uh but I guess um when you have a common name, this is the situation you're in. Django is great, but Django, as we know, is really a set of tools that compose the framework. Tools are great, we all love tools, or we wouldn't be software engineers, but tools can be used in good or bad ways The Django RRM is itself a set of tools or um APIs interfaces that you use to interact with the database and represent data. But the key point that you often have to remember is to manage your own expectations for tools.

Speaker 1: Many people approach a new tool with a broad set of expectations as to what they think it will do for them. But this may have little correlation with what the project act actually has implemented. As amazing as it would be if they did, unicorns don't exist. You have to look at the thing you're working with and not just assume that uh it can solve all your problems and that it's completely going to work exactly as you expect it does and never never have any uh never be any mismatch there. Uh the Django RM is an abstraction layer over the database. And abstraction layers are great because they take us away from messy details. You don't have to write SQL anymore. But they're also risky because they take us away from messy details.

Speaker 1: So don't forget when working with the ORM, you're far from the ground. You're writing queries using uh ORM, uh you're defining models, you're you're making query sets, you're executing the query sets, and that's going to be making the queries. So you're a bit you're a bit far away sometimes from the from the things that are actually going on under the hood, so to speak. So so what is the query set interface? Um well uh uh or uh before we talk Query sets are objects, okay, and they have certain properties, one of which they are lazy. Another property that query sets have is that they are immutable.

Speaker 1: So what does this mean? A lazy object doesn't evaluate until it needs to. And an immutable object never itself changes. At least it's designed never itself to change. It's supposed to represent a query as it will happen, not as it has happened. Uh many people think of query sets as if they're queries that already happened, but they're actually future instructions in a sense. So um in each of these, none of these actually makes a database query. Model objects all doesn't hit doesn't hit the database. If I have a query set and I filter on that, that doesn't hit the database in most cases. And values doesn't hit the database either. It just simply defines a new query set.

Speaker 1: And so far no queries have been made. However, these uh statements do hit the database. List query set. Query set slice. If you iterate over the query set, or if you bool the query set, if query set is an implicit bool operation. These all perform SQL queries. So you see, even just here, many people don't realize this. They're they're confused what's going on. Why isn't this query happening? Or why is this query happening? uh you're you're far away from kind of uh what's going on underneath uh in in many cases. So let's take a deep dive here into an example app. A hosting site for blogs. Okay. Take a look at a few models here. Sorry about that blue text that may be a little hard to hard to read actually.

Speaker 1: Uh we have uh we're very simplified. We have a blog class, we have posts on the blog and comments um on the posts. The blog class, uh the blog model um has uh uh a submitter, which is just an auth-user object who submitted the blog. Uh each post uh it is references back to the blog that it's it's it's a post on, it's a foreign key. And uh also is a repres has a representation of a many to many uh many to many representation with users who like that post. And then there's comments. And you have people who submitted the comments and also the post that the comment is on. Okay.

Speaker 1: So the first quick quick view we might write is something like this. Let's say this is a management page where we're seeing all the blogs and not just the ones owned by you. or or maybe our application allows users to see uh other people's blogs here. And um but this is you know very simplified. So we're we're we're getting all the blogs. We're iterating over them all. We're printing the name and we're saying it's submitted by this username. Okay. Seems pretty straightforward. Uh and it seems like this is the right way to do things without really thinking very much about it. Reading the Django documentation. Okay, just this this seems like a straightforward way to do it. Um here's some just sample data I used uh with the Ipsum lorem

Speaker 1: generator text. And some names. And here's our page. Okay, no CSS. Great website here. Okay, so so that's our blog list. Now uh let's look at blogs, the detail page, get the object or 404, filter for the blog we're looking at, so it's like slash one. Okay, the blog ID is gonna be one, and it's gonna get blog one. And then it's going to return the posts for blog one. It's going to render with those posts and the blog. So it's just going to render this page here. Okay, this is the page that it renders. But uh just take a look. Uh this is the our Django template, okay? We're we're going through all the posts, we're printing the post name, we're saying that if people like this post, it's gonna say liked

Speaker 1: by and the name of all the users that like it And then it's going to print all the comments out in in bullets. And these are the comments on that on that. Okay, so so again, pretty straightforward, reading the Django uh docs, and it seems like this is the way to do it. Um and um so this is this is what this generates. Okay, we could see this is kind of structured data. Um uh each post has comments, and then there's there's people who like uh who who who who like the uh the po the the post. Okay. Now um Stepping back for a second, uh, a lot of developers when they're in the beginner intermediate phase will often uh start wondering what is happening when I do something

Speaker 1: Um and but not really know how to figure that out. Okay. Uh what 's happening with the with with the database layer. Um so what SQL queries are happening when I do X? Whatever that thing is, whether it's making HTTP request res uh um uh on on a web page, or it's just in the say the the Django shell. You just Like what what is going on? I'm defining a query set. Did that hit the database or didn't it? I don't even know. Uh, because the shell doesn't doesn't tell us by default. Um there's a kind of a few workaround solutions to this. One is um this is one I use often. I just import logging, get the logger for the jungle uh for the database backend. uh set it to debug level and just stream the results.

Speaker 1: And uh sorry, that text is a little small, but basically uh now whenever I'm the j when I'm on the Django shell, Um I t I I I I perform an operation. It the logger will just spew out to standard out if there was a database query. There it is. That's the database query that was used. And if there wasn't a database query, it won't spew anything out. So you can kind of get information dynamically using the Django shell as to whether or not an event happened hitting the database. Another solution, this is something that can be implemented on uh in production systems with caution because you're gonna be filling up the logs. um with new kinds of information, which can kind of over overwhelm the logs sometimes.

Speaker 1: So think think about it. You might want to set it up on a staging environment so that you know you don't you're not polluting your production logs. Uh or uh so uh but basically if you use Postgres or my or MySQL for example, there's something called uh uh statement logging or my MySQL calls it the general log, where you could have your database back and just print all the queries that are happening. Um and I I've been in uh situations where we we just put it on for say uh a minute, collect the all the queries that are happening in production, and we can do an analysis on all those queries. So that's kind of um helpful. You can also set that up in your development environment by default just to have the general log or the query log on. So you know you can be developing in one window and your other window you have you're tailing

Speaker 1: tailing this log and you're just seeing as you're as you're performing actions, you're seeing the queries that are happening. So you can get immediate feedback there. There's other statements you also need besides these are just the the main ones. You also want to set up your log destination and things. So you could look at the The documentation for your database backend and uh put some more put put some more options in there uh for um to configure these things. But uh a third third solution, which is one one thing I'm going to talk about today, and uh thank you to the last speaker who who uh gave a call-out to this. To help introduce my talk is the Django debug toolbar. This is really a great, great tool, as the previous speaker also said, available with Pip

Speaker 1: Install. If you're going to set it up Um I would in your settings install conditionally so you're not you're not installing it in production. Do not install this on production. So you say if debug equals true, then you you you kind of will install your the the toolbar. Um and it it it operates through middleware and um Let's see, uh our our first page again, the blog list page that we talked about. Notice on the right here there's um this new toolbar that appears, this kind of JavaScript widget that pops out and gives you all sorts of like really interesting information. Um on a basic level and what is going on when when this HTTP request just happened. So the HTTP re HTTP request happened and you're getting information on the right about the thing that just happened.

Speaker 1: There's all sorts of uh things you can dig into, but we're just for this talk, we're gonna look at this the the SQL. Um notice there's 52 queries here for a single page load. So that that should be the first thing that seems curious. Um and if if we click in, we can take a look. Wow, that's a lot of things going on just for one page load. Okay. Um and uh this concept uh known as the n plus one query where um Uh a system runs an operation to fetch a li a list of items, but runs an additional query for each item. So Basically, you know, the in this case we only really want one um so I we we one list of blogs and that we we should be able to

Speaker 1: Do that in one query, but we're actually getting other things as well here. Um and this leads to inefficient performance and can result in a unnecessarily large number of queries. And it's unfortunately an easy bug to introduce using RM frameworks like Django and Rails because of that levels of of uh separation. And here's the culprit, actually, we're um iterating all over all the blogs. Now the all the blogs come in one query, but then as we go through each blog, we're dot submitter Is a is a foreign key to auth user. So what that's doing is Django is making another query every iteration for every blog. Oh, get user one, oh get user five, oh get user eleven, oh get user fourteen.

Speaker 1: So it's it's making all these unnecessary queries. Um And uh so this is where SelectRelated comes in. SelectRelated uses SQL joins that include fields from related objects, okay, the auth user in this case, in a single select statement. This allows Django to fetch related objects in the same database query. And this is only effective for single-value relationships such as foreign key or one-to-one relationships. So in our case, we have the blog, the submitter. Yeah, there's a a user, okay, that that has submitted the thing and uh select related, we see the implementation here, select related submitter. Uh select related will um just now turn our many queries into two.

Speaker 1: One that selects um the users. And when it selects the blogs. So that's it. We went from 60 queries or 50 queries to two just in uh very quickly. Each query it i if you're wondering why this is even a problem, has latency. It goes back and forth from your application to the database. So even if the queries themselves are are really quick That latency is a killer, especially if your database is in another data center. Okay. Okay, so and it uses an inner join. And as we see there's there's two queries here. Okay. Now this is uh second uh problem here. It's a little bit more tricky. We have 44 queries that are used to serve this page.

Speaker 1: Again, looks like a big mess. Looks like a time bomb if this were to go in production on a high-volume website. So um I I just want to look at a quick thing here. Each post has many likers associated with it, people who liked it. So it's when it's rendering those people's names or the yeah, the the the likers usernames Um it it what is what what Django is doing is it is um making many queries. for each post, it's grabbing all the likes, the likers, every single time, every time it goes over the posts. Now how can we make this more optimized? This is what prof prefetch related where prefetch related comes in. This is useful when dealing with many-to-many

Speaker 1: relationships. So select related is useful when dealing with foreign keys or one-to-one relationships. Prefetch related is useful when dealing with many-to-many relationships. Uh it's also useful when dealing with reverse foreign keys, because if you think about it, um a user has many blogs that they may have, but a blog only has one submitter. So uh the reverse of that, the m the the uh the the user can have many blogs. That's similar to a many-to-many relationship in that there's a lot of things from the from the other side of the relationship. So prefretch related is an optimization where you're dealing with getting many things at once in two uh from two tables, from two sources. And without this function, Django does a query for each um user who likes the comment.

Speaker 1: Sorry, uh I think this is wrong. Django does basically uh It finds all the likers for each blog. And this causes uh uh additionally causes this n plus one problem. Okay. Um so uh basically This is going to I'm I'm going to show you. So I I if I use prefetch related here, and um I should just say this select related and prefetch related are operations on a query set. So in both cases we have the posts query set. I'm using prefetch related and I'm saying what I want to pre-select. And it's it's it's returning another query set. That represent that that that again is I'm calling it posts because it's just uh add is composing this operation and saying

Speaker 1: do this efficiently and and do it and pay uh by um fetching the users Who like the posts at the same time. And um let's see, hold on a second. So there's another application here. Yeah, there's another optimization. I'm using this to demonstrate that you can um do this i i it with multiple um uh uh operations at once or or multiple entities at once and that is that a comment uh has a post And implicitly, a postmodel object has that related name pointing back to it. So a postmodel object has dot comments, post dot comments.

Speaker 1: Okay. And what this related name does is it creates an attribute on each post object. So if I say post dot comments, it's gonna trigger a query where it it gets those comments, okay? However, the magic here is that once I use SELECT related or prefetch related, the query set that's returned from select related or preset or prefetch related. It's gonna kind of magically know what to do. Because I've told it, I've told it, I want you to pre-fetch the users here, or the likers, in which case they're called. Or I want I want you to pre-fetch I'm going back here. The submitter in the case of the blog. So it's telling Django, get all these things at once.

Speaker 1: And Django just makes a complex query, but one query. It it runs that query, hits the database in a single query or two queries instead of 50, and it's going to compose those um results. Uh in an efficient uh in uh in in uh in a more efficient way than an N plus one situation. Uh sorry, sorry. Uh yeah, and here's the point here. Using pre-fetch related, Django fetches all the users for the comments in a single query and then links them in Python. And this can ironically be more efficient than having the database do it for you because uh there's in in situations like this.

Speaker 1: Um where you had these complicated kinds of relationships. This way, instead of running a new query for each comment, it just runs two queries: one for the comment and one for the related users, avoiding the N plus one problem. Okay. Um so let's see here. I want to point this out just because it's a little confusing. Sorry. Okay. So um Our ultimate solution for this list page is going to be this: likers and comments underscore submitter. So likers represents the users that like my posts. Comment submitter represents the users who submitted the comments

Speaker 1: for the posts. And what I'm telling, this is the most complicated of all, and if you don't understand this, you can dive into it in your own time. What I'm doing here though. is I'm telling prefetch related to get all the possible users that are involved in in uh in in my uh that I'm going to need to to generate my template information. And um so I have some users who are the likers of these posts and some users that are submitters of the comments of the post. And as strange as this seems, this is telling Django, these are all the things, so you only have to make a limited set of queries to the users table, not a query for every user. Django is gonna is going to um

Speaker 1: now render this page in seven queries, whereas before it was 50 or 60. Okay, so um again we have uh an optimization where uh Django is uh selecting all these users in batches and not just uh individually. So um select related and prefletch prefetch uh related are very um helpful in these cases where you're doing these implicit queries that seem to come out of nowhere that you didn't understand and that they have to do with um just doing uh simple things where you're where you're accessing attributes And causing these queries by mistake. So Django debug toolbar

Speaker 1: can be a tool where you can discover these problems. You see those N plus one queries just stacking up, and you say, This, I think I know how to solve this. You you should always think is when you see M plus one queries, would select related solve this situation? Um So besides SQL queries, Django Debug Toolbar gives a bunch of other information uh that that may be useful to you in your development, but um just for the sake of uh of Time being out and just maybe getting a few questions. Just a summary, um the query set APIs here implement best practices to reduce unnecessary queries. Select related used for one-to-many or one-to-one relationships, and prefetch related for many-to-many or reverse foreign key relations.

Speaker 1: Thank you very much.

Speaker 2: Are there any questions for Chris?

Speaker 3: Thank you for the talk. For the pre -fetch related where it's doing the join in Python or in the application. Is could there be a situation where there's too much data and is that something that the developer should be aware of when they're writing? Uh

Speaker 1: yes. Um and i i especially so there there's many cases especially in large applications with um Uh I mean at Venmo we call these certain kinds of users pathological users because they had just thousands of um of uh of um uh payments in short periods of time which we had to list and query. Um or um uh you know they they they were very active on they were very active on the site. Uh and it especially in users like this, you there's certain assumptions you might make. You say, well, you know, um you know a a a blog post in most cases may only get a certain number of comments before it becomes kind of irrelevant and the the comments stop happening.

Speaker 1: But there's cases where um especially when something goes viral or there's a user on your system that's especially um active. you have to kind of monitor or uh for these things. And so you always have to be looking at the top 99 percentile, 95th percentile in your in timings, for example, when you're looking at uh HTTP request timings. You shouldn't be just looking at the average. You should be looking on a high volume site, also at how long the 95% and 99% take to happen. Um in in the cases where the like the data topology is unique for a subset of users or subset of entities. And that can be kind of a hard situation. Yeah. But you you have to kind of look at those uh those top tier, top percentile situations um

Speaker 1: um and and monitor it kind of not only the average response times and things like that.

Speaker 2: Thank you. That's all the time we have. If you have any more questions, he's going to also take questions on at the Hallworth lobby. So round of applause for Christopher, please.