 I'm originally from Mexico. I'm a platform engineer at GitHub and today I want to share with you some thoughts about database performance. So why do we think it's important about talking about performance? Active record, normal frameworks make it so easy to talk to a database so we can concentrate to build an amazing product. However, as we grow and we have more users on load and our code target is more complex, we find ourselves finding queries that are duplicate in different parts of the code or optimizing for reads, pagination, counts, big tables, et cetera. Performance issues will affect your development cycle, your team morale, and ultimately your customers. But the real reason I care about this is because in my years of experience building back in applications, I've seen many fires and this is me when I have to fix one of those. Most of the times, if I have some information upfront, it could be prevented. So that is why it's important for me and I want to show you a little bit of how we do it at GitHub. So there are three aspects that build performance culture in any organization or team. So if you want to build that in your team, you need to take care of these three points. The first one is that it has to be the entire team's responsibility. It's not just the engineers, the DVAs, the QA team, or it's from management, everybody's on the hook. It requires process and tooling and you require infrastructure to support that. Automation tests, monitoring of development and other environments. And you need metrics and not metrics are one thing like page load time or things like that. And you need to ensure everything. IO, cache, individual queries, writes, reads, as much as you can. So enough talking, let me show you some numbers. GitHub is in the rails and my SQL application, I should say, my SQL, it's 16 million users. We have 19 million repositories. This is at least the public repositories and about 15 million issues. We do about 250,000 issues per week. So all this plus all the data that these actions generate will make up for a very large database. And this is just public information or public repositories. So I'm going to share with you how we do process and tooling to be on top of application performance, database performance problems in our application. My favorite coworker, HuBot, no offense to anybody, but he's super cool, is deployable everywhere, it's open source, it's built in Node.js and you can use it too. So one of the things that we use with HuBot, for instance, is graphing directly in Slack. This is the Graphite command. We can actually go to Graphite, copy the command that can run in HuBot. And this is great for onboarding people and actually teaching or showing when you build in a feature, how it's going to be, you know, you have metrics right there. Anybody can see them and because we're a remote company, so it's just available for everybody to use. One of my favorite aspects of HuBot is that you can customize anything and we have my SQL commands running HuBot. So for instance, if I'm building a feature and I want to know, you know, if I should add a new column, I can see if the size of the tables, I don't need to ask a database administrator or anybody in the infrastructure team, I can do it myself and I can make a decision whether it's a good idea or not to keep adding columns to the table, probably not. So I'm going to be using two examples of the most common problems that are, it doesn't matter what size of database you have or application, but these are the most common database performance problems that we probably all have experienced them. The first one is the famous Mplus1 queries. Everybody knows what Mplus1 queries. Well, an example of Mplus1 is when you have an association and a parent-child relationship and you issue one query to get the parent and then end being the number of children in the association. So the fix for this is very easy. We just need to eager load the association upfront. We can do that with includes and we have multiple, I'm not going to talk about the solution because we can figure that out, but I'm going to talk about how we actually find them at scale. One thing that we use when you are developing a feature as an application engineer, we use PIC. There are other tooling available, but we use PIC in particular. It's also open source. It was originally built at GitHub. And what it does, it puts a little top, a bar at the top of your page so you're developing a feature, you're working on this page. You can see helpful information. We have, in this case, elastic search, how much time it takes to call cache or features, but I'm interested in the SQL section because that's what I do. So we can see how many objects are being loaded. So this will probably give you an idea that there may be some smell here. And it also shows me the SQL that's being executed. So this just requires a little bit of instrumentation. It's super easy to do. And as we can see here, we have issued many queries against the label. So we have an N plus 1, so we just need to find where, because we're in the page that executes the code, we can just go and fix it. There's also other things we can do, like instrumentation on the API stabilization. You can count how many queries are executed at the beginning and then how many queries are executed after. You can graph that out so you can be creative. The point is that there's tools available to do that. And the second most common problem is poor indexing. And this is my favorite one. It comes in different flavors. First, lack of indexes. So if you can guess where the index was added there. This is another tool that we use. It's not open source. It's Vivid Cortex. It is like, I don't know, maybe you use New Relic or any other tool. Also, unused indexes. So when we change how a feature works, how a page behaves, it may be that we no longer need an index. And they will hurt our performance when writing. Sometimes we tend to optimize for reads, but also we need to take care of writing. And having too many indexes in the database, it's not good. So we have this another command running Hubert. My SQL index stats. You pass the name of the table. And this is wholly underneath the performance schema for MySQL. But I'm sure there is another thing for any other database. So you can just customize it. So we have here that this index issues and the table issues on x column is no longer used. So we can go ahead, whatever is the process, to remove it. And too many indexes. And I'm looking at you, Postgres guy. Sorry. But we can add this index merge on Postgres available. And it works. And it's great. But it's not a best practice to add an index on every column just because we can do it. As I said, eventually you will see the penalty on writes. So what is a good index strategy? Build indexes for performance for your critical queries. Don't try to fix everything. It's going to be impossible to do it upfront. Either way, see what are the most critical issues that you have. That's why you need the tooling so you can identify which tables are the most critical in terms of reads and writes so you can fix one by one. Also try to fix or add indexes that will help more than one query. So in this example, I have, because I'm suggesting a multi-column index, I'm choosing repository and user ID because I have an equality condition in the repository. But I don't have, in the second query, an equality condition for user. So it will stop working as soon as it heads the non-equality condition. And this index will help me use for other variations. If I only query on repository, it will still work. Prefer to extend an index instead of adding a new one. As I said, too many indexes is a problem. So try to take a step back. There is another command that we have that you can see as I show it, all the indexes so you can see it right there and try to do extend a new one. It's our extended system one instead of adding a new one. And favorite multi-column indexes, impartial indexes if you can. So how do we identify when we need a new index or when we have slow queries? Usually that's something database administrators do or the tooling lives in the infrastructure side. We use Haystack, it's our exception tracking tool. There is an engineering blog post that I have linked at the end so you can read more about it. And we use it for everything. And this is application side, which means that we can customize what a slow query means. So we can say half a second is a slow for us or two seconds or 10 seconds. So we grab the query, we can see where it comes from, and then we can execute the explain in Cuba. So that's why Cuba is my favorite. So we have the execution plan right there. We can see in this case that I'm querying on close at, but I'm having an index on user ID and created at. So I do need an index. So it seems like I do need an index or I need to write my query. So once I have the exception plan and I have a plan to add an index, it's really hard to actually test it if you don't have the data that is in production. You cannot never pretend that you will have the same execution plan at your local. Many things will depend for the execution plan. So what we do is we have tools available. Again, Cuba comes to rescue. We can clone a table to staging. So we can do testing there. And maybe this is not available for you, but that is a point about having an infrastructure to support the tooling. Once we clone the issue to, in this case, the issue is stable to the staging environment. We can test it. We can experiment and validate our plan. So in this case, it's successful. And well, finally, if you don't have this available to you, you can use science. And what I mean by that is scientists, which is a Ruby library that is for the factor in critical paths. And in this case, I have a, it's used in the index hands because my SQL supports them. But I mean, you can be creative and just have a different query in the experiment control. So it's going to use the first one, and it's going to try the second one only for comparing the performance of the index because that is my use case. And scientists will allow you to put this code in production if it's available to you and enable it for a percentage of your users. So you can test only in a percentage of your use base, and you have a graph like this, what it shows. Well, indeed, the new index that I propose is much better, or slightly better, but better after all. The key to database performance is to have limits to be on top of what you're doing. So limit round trips to the database, limit pages on your pagination, and do as much limited of your users but that you can do. And also be mindful without any columns. Now what, we have this information. I hope some of this is useful for you. Try to incorporate something in your team. Talk to your team about performance. It's not just engineering work. It's everybody's. It's for your good. And set a typical goals, start small and simple. You don't have to do everything, and sometimes you need to slow down to speed up at the end. The most important thing, oh, everything's fine. Thank you. Awesome. Thank you so much for seeing.