Efficient MySQL Performance
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Nichter, Daniel
This presentation introduces the major topics and most important points of the new book Efficient MySQL Performance published by O’Reilly (Dec. 2021). And like the book, this presentation is intended for software engineers using MySQL, not managing it (but DBAs are gladly welcomed). In 40 minutes, we’ll quickly cover topics including query metrics, indexes and indexing, data access patterns, server metrics, and more. At the end, you’ll have a “lay of the land” for understanding the aspects of MySQL that affect performance, and you’ll understand where to go and what to learn next to increase performance.
 Welcome to Efficient MySQL Performance in 40 Minutes by me, Daniel Nester, for FOSDOM online February 2022. Let's begin with a quick introduction. Again, my name is Daniel Nester. I have 17 years experience with MySQL. My personal website is hackmysql.com, where I've been posting blog posts and tools and other stuff since about 2005. But I'm usually known for my eight-year tenure at Percona, where I worked on Percona Toolkit. I'm currently a DBA at Block, formerly known as Square. And right now I'm working with the Cash App, helping to manage their databases. I'm also the author of Efficient MySQL Performance, recently published by O'Reilly. You can see the book cover there on the right. This is actually my first virtual conference and recorded presentation. So it's a little strange for me because I'm used to presenting in front of a live audience. So to help you put a face to the voice that you're hearing, this is me. Up top is a crazy beard that I grew recently, but it was a little too crazy. And so I trimmed it finally. And I usually look more like the photo at bottom, is how people would probably recognize me from the previous conferences that were live and in person. Likewise, I need an audience too. So I chose this picture to have an audience to talk to, because this is actually Faust in 2019. So I will pretend like I am talking to this audience. So objectives for this talk. This is actually the second time I've recorded this talk, because the first time I recorded it, the runtime came out to one hour, 25 minutes. And this is supposed to be a 50 minute slot with some time for Q&A. So that didn't work. That is because there is a lot of material to cover. This talk is based off my book, which is why it has the same title, Efficient My Secret Performance. The book has 10 chapters, but I knew that that wouldn't fit in the time slot. So I first whittled it down to chapters one through four and chapter 10. But even after recording that, that was an hour and 25 minutes. So I've had to whittle it down even more to just these three chapters. This presentation, like the book, is intended for engineers using MySQL, not database administrators or MySQL experts or engineers aspiring to be DBAs or MySQL experts. It provides a path through the complexity of MySQL as depicted here in this diagram. When you're new to MySQL, it can be very difficult to know where to start and where to go after that to achieve better performance, as shown there. Performance is like a shining star somewhat out of reach. Well, that's what this presentation does in super condensed form given the limited time. It begins to show you a path through that complexity, and that path begins with query response time. The path begins with this understanding that performance is query response time. Pretty much everything comes back to this point, especially for engineers using MySQL. Performance is query response time. But let's step back for a moment and ask a broader, kind of more general question, which is, what do we want from better performance? When engineers say that they want better performance with MySQL or MySQL is running slowly, or when they ask a DBA to do something to increase the performance of MySQL, what are we really getting at here? What do we want as a result? Oftentimes, there's a perception that we want more QPS, but QPS is not performance. It is one measurement of performance, specifically just throughput. But whether you have relatively high or relatively low QPS doesn't actually mean that MySQL is performing well. And I'll show in a later slide how much QPS can vary based off different applications. So just having high QPS doesn't mean that you have great performance. Sometimes it's another metric like CPU usage. Maybe the engineer wants MySQL to utilize more CPU cores. And I've actually had this case before, an application that was never really utilizing more than 50% CPU. And the engineer wanted more performance out of MySQL and they thought, well, if MySQL would just use that other 50% of the CPU, then it would be performing better. But that's not actually the case either. Because the contrary sometimes happens too. Sometimes we want less CPU. I've had this happen too. An application was causing MySQL to consistently use 90% or greater of CPU. It was really burning up the CPU. And in that case, the engineer wanted MySQL, wanted better performance, meaning that MySQL would use less CPU. So you can pick any metric and it's completely arbitrary. And that's because these individual metrics are not what we really want from better performance. At the end, they're just measurements out of performance. They aren't what performance actually is. So what do we really want? It's not those things. What we really want is to do more work in the same amount of time with the same hardware. You can think of MySQL like a machine in a factory that outputs something. That's its work. We want to have that machine stay what it is. But we want that machine to do more work, output more of whatever it makes, for example. Do more of that in the same amount of time each day, for example. So that leads to the question, well, if MySQL is the machine, then what does it do? MySQL really just executes queries. At the end of the day, you can just say that this is its one and only job. Of course, under the hood, it's actually doing a lot of other things like replication, for example. This is its purpose. The application puts in some data, stores it, and then maybe later it executes a query to fetch back that data. And that's really all MySQL does is execute queries. So what we're really saying here is this. When we want better performance, we really want MySQL to execute more queries in the same amount of time with the same hardware. That would be real nice. And if it could do that, then we would say, oh yeah, MySQL is performing a lot better. Okay, great. So how do we achieve that? Well, the typical approach is one, scaling up the hardware. So basically a bigger, faster machine than tuning MySQL, tuning the machine, making it more efficient. And then finally, optimizing queries, optimizing what we're putting into the machine. That is the common sequence that I see all the time. And it's understandable, perhaps, because from the application engineer's point of view of the person using MySQL, scaling up is relatively easy, especially if you're in the cloud, just click some buttons and now you have more CPU cores and more RAM. Tuning MySQL is also somewhat easy from their point of view because they ask the DBA to do that. Hey, DBA, please fix the MySQL configuration or do something to make it more efficient. And then finally, the most difficult is optimizing queries. And I understand that too because query optimization is not a trivial task. There's lots of things you have to learn and consider to do this. So by comparison, it's a lot easier. But honestly, that is the wrong approach. The correct approach is first optimizing queries, then scaling up hardware as a distant second, and then even more distant tuning MySQL. Why? Going from the bottom up here. Why is tuning MySQL last? Because MySQL is not a new database. It has been around for 20 years or more, and it has been tuned and optimized over all that time by world-class database experts. So internally, MySQL is already very tuned and highly efficient. Tuning MySQL these days is akin to the proverbial squeezing blood from a turnip or squeezing blood from a stone or spinning gold from straw, whatever you want to use. Some experts can do it, and there are cases where it needs to be done, but by and large, it is a distant, distant third. Likewise, scaling up is a distant second because hardware is usually pretty plentiful. It's pretty easy to get a lot of CPU cores, a lot of RAM, a lot of IOPS on the storage. I mean, there are cases, especially in the cloud where it can be drastically under-provisioned. But again, hardware is so cheap and plentiful these days that it's usually not the first thing you should jump to. It's a distant second. But number one, optimizing queries. As we just said, the primary work that MySQL does is optimizing queries. That is the input. That is what drives its work. So that has the most bang for the buck, so to speak, for getting better performance by optimizing the work that the application is asking MySQL to do. So let me move that up top. So really, the first thing you need to do for better performance is optimizing queries. And then I grade out two and three because they are important, but they're a distant second and a distant third. So with respect to optimizing queries, again, we come back to query response time as the primary metric, as the north star that guides all your efforts. Because for two reasons, well, query response time is the only metric we actually experience. And if you think about that for a moment, you realize that that is true and uniquely so because if a query takes seven seconds to execute, we actually experience those seven seconds of maybe seven seconds of impatience. And not just us as the engineers, but customers as well. So they experience query response time in the form of slow queries, or hopefully they experience it in the positive as, you know, very quick queries in a very snappy responsive application. So that's the first reason why query metric or query response time is the most important metric. And the second reason why query response time is that it is actionable. We can directly change and optimize queries to make query response time less to make the queries execute faster. And that is the clear goal here. You can do this with some other metrics, but it doesn't necessarily mean that response time is going to be better, or that it will have some sort of impact on us as engineers or customers. For example, you could make a query access less rows, but unless that affects response time, no one really experiences accessing a certain number of rows. And sometimes you have to the query if the query means to select 10,000 rows, then it has to select 10,000 rows so you can't really change that. But there's always things you can do to affect changes to query response time to reduce it. So that's why again, it is the focus here. So when we want better performance, we are going to focus on optimizing queries because that is the work that my SQL does. The north star of all this is query response time because it's what we experience and it's what we can change. So again, it comes back to this, this all important statement that performance is query response time. And again, I'm going really fast here because we don't have much time in this presentation, but I wanted to at least provide three more specific steps about what you can do to get started along this path. And I blogged about this recently. If you go to my website hackmysql.com, there is a blog post from January about how to enable query metrics and use a query metric tool. But briefly, the steps are first you have to enable query metrics. This depends on several factors. Again, I documented in my blog post. Then you need to use a tool to analyze those query metrics to find out which queries are the slowest. And then three, you begin to optimize those slow queries. If you do this, the result will be that my SQL performance increases. So this is the first step along the path. This is the most important thing to know. In fact, if you do nothing else but this, you might achieve much greater performance with my SQL for quite a long time and you might not need to make any other changes. That's how powerful query response time, query metrics, query analysis is. That's why again, it is the first step along this path. Next step, access patterns. Let me begin with this idea. My SQL performance is limited by the application, not the other way around. This tends to garner some debate and it's understandable for reasons that we'll examine in slide after next. But this is the main idea that I want to press upon with respect to access patterns and we'll build more on this idea here in a few slides. But first, I wanted to present these real numbers from real servers, all of which are roughly the same hardware, all of which have roughly, you know, the same performance with respect to response time. Like all these, these are real production applications that have acceptable response time. And so, in so far as we can, you know, control all the variables we have. And what this shows here is how much different access patterns or workloads from the application can affect my SQL. So on the left column, we have threads running, which is a general indication of how hard my SQL is working because one thread executes one query. And on the right column, we have the number, the QPS for those number of threads running. So at the top, we had an application that generally has four threads running and does about 8000 QPS. But then we have an application that doubles the amount of threads running, 8, but has slightly less QPS, 6000. Third row, same number of threads running, different application, eight threads running, but now a huge jump to 30,000 QPS. And then fourth row, a different application has typically 12 threads running and it's doing slightly less QPS, 23,000. And on the last row, another example of a different application typically running 15 threads, but has 33,000 QPS. Again, I'm not saying that QPS here is an indication of performance because that's not what performance is. Performance is query response time and all these applications have good response time. But what it shows is the huge difference in work and workload and other metrics that the applications access patterns can cause in my SQL, especially those highlighted two rows there with eight threads running really makes the picture clear. Whereas one application typically causing my SQL to keep eight threads active, that turns out to be only 6000 QPS. Whereas another application completely different, obviously, must have completely different access patterns, has the exact same number of threads, so basically the same load on my SQL, at least in terms of threads running, but significantly more QPS. So these are real world numbers. So it gives you an idea, a sense of just how much access patterns from the application affect my SQL. So let's now get into cause and effect and two perceptions and a reality. The first perception here is that my SQL is slow and that is causing the application to be slow. That arrow is a little thin there, but the arrow is pointing from my SQL to the application. This is a very common perception, especially when engineers start to want more performance from my SQL or my SQL is suddenly slow. They think that my SQL itself is being slow and that is therefore causing the application to be slow. But I can assure you that this is rarely the case. There are rare cases where my SQL might have a bug, for example, that causes some sort of slowness. There are cases where a gross misconfiguration of my SQL might cause my SQL to be slow, but in a professional environment, presuming that my SQL was configured by experts, that shouldn't be the case. This perception is reality very, very rarely. And when it is the case, it is usually some of the most interesting and difficult cases to figure out, because as I mentioned earlier, my SQL is more than 20 years old, so it has been fine tuned to a very high degree. This is usually not the case. Second perception is somewhat related and somewhat more common, but again, in the grand scheme of things, it is still very rare. The perception here is that something is causing my SQL to be slow, and therefore that causes the application to be slow. So in the diagram, the turtle is the thing external to my SQL, causing my SQL to be slow, which then therefore causes the application to be slow. Now, this definitely can't happen in my career. As a DBA, I have seen this many times, and it could be anything from DNS to the network to a degraded rate array to, I mean, heck, I've seen faulty CPUs, faulty memory, all sorts of external things cause my SQL to be slow and then cause the application to be slow. But again, we presume that you're running my SQL in a stable, professionally managed and built out environment such that these things shouldn't happen. There are the exceptions, not the norm, and especially kind of on a day to day basis, if there's no obvious fires, if there's no obvious turtles, so to speak, then this is not the reality, at least not the one that you as an application engineer using my SQL should jump to. And if it ever is the case, DBA should be on top of it and should be aware of this and should notify you ahead of time that, hey, there's this thing affecting our databases. But again, this is usually not the reality. The reality is this, almost always, it is the application that is accessing and using my SQL in some way, and that workload, the combination of queries, data and access patterns from the application to my SQL causes my SQL to be slow, and then slow response time back to the application. But the root cause here is the application, how the application uses my SQL. And really, it's as simple as that. I would say that this is probably the case in the reality, at least 80% of the time, if not more. So the point is, when you're using my SQL, and everything's fine, and then suddenly everything's not fine, and my SQL is slow, start with this reality. Start by asking, hey, what is the application doing, or perhaps not doing, what has changed about how the application is using my SQL, because that is the vast majority of the times, that is why the application is getting slow response from my SQL, because the application is doing something to my SQL that is causing it to have slow response time. And only rarely, the other perception one and two are the case, and when they are, hopefully your DBA will let you know about it ahead of time. So let's keep that reality there at the bottom. And now focus on access patterns. And the interesting thing about access patterns, to me at least, is that everyone seems to know them and talk about them. Pretty much every engineer I've ever dealt with is aware of access patterns and even talks about them. But interestingly, I have never seen a, an enumerated list of specific access patterns for my SQL, and I couldn't really find any for any other relational transactional database. I did find one for Amazon DynamoDB. They publish a list of access patterns specific to DynamoDB. But apart from that, I couldn't find it from my SQL. And so in the course of writing my book, I enumerated this list of nine access patterns. And so I'll briefly run through each of the nine to give you an idea of what they signify with respect to how the application is accessing data in my SQL. And a lot of them will be very familiar to you, I'm sure, but now they have a specific name and are specifically enumerated. So the first access pattern is probably the most basic and probably the most important, which is read write. Does the application primarily read data, primarily write data, or is it a combination of both? And that's important because scaling reads and writes are much different considerations. So write scaling writes is much more difficult. Likewise, throughput is closely related to there's a big difference between an application doing 100 reads per second versus 100,000 reads per second. Likewise with writes, if you're doing, you know, one write per second, you can almost forget everything. If you're going to try and do 100,000 writes per second. Okay, now that's a that's a significant challenge and entails all sorts of other considerations. Data age is difficult to explain in short terms. But basically it is a reflection of, or it's an indication of the how my SQL is going to handle the working set size. Data is accessed in my SQL. The lingo is that data is made young. So when it's access, it's new, it's young. And when data ceases to be accessed, it gets older and older until it is eventually evicted out of memory. And because a certain percentage of the data, generally the rule of thumb is 10 to 20% of your data is considered the working set the frequently accessed data. That is the data that my SQL tries to keep in memory. And data age as an access pattern has us look at and consider whether that is actually true. The application sticks to that working set, or does the application tend to dredge up old data so frequently that it's difficult for my SQL to keep the working set in memory. Data model means, does the access reflect a relational database, a transactional database, you know, row or row oriented database, or does it reflect something else like a document store or Q. As it turns out, applications use my SQL for a lot of purposes, not all of which actually are good fits for a relational data model. So that's why we need to look at it. Transaction isolation is also important because my SQL is a transactional storage engine, at least in ODB, in other storage engines. And so does the application actually need transaction isolation, and if so, which levels. If it doesn't, then maybe my SQL or any transactional data store is not the right choice. Read consistency means that does the application need read after write semantics, or is eventually consistent read good enough or acceptable. If you can do eventually consistent reads, then perhaps this data can be read from a cache. So another important consideration, especially if you have a high rate of reads, you know, the three put through reads is very high. And if those can be eventually consistent, then great, there might be an avenue for performance optimization by using a cache. Concurrency means is the same data accessed at the same time. Does the application typically do this for applications with high concurrency, meaning they're accessing the same rows at the same time. Then the other access pattern traits become really important, like is it a read, concurrent reads or concurrent writes concurrent reads are relatively easy to scale but concurrent writes. And that is the that's the tricky performance problem to scale, especially if you're talking a high throughput concurrent writes. That's one of the most challenging things. So we need to consider the concurrency of the data access. Row access means does it access single rows, ranges of rows, or random rows all over the place. So if you have a high throughput and high concurrency that also writes data all over the place, that is, that's a real tough access pattern. But other access patterns are a little bit different. So the most challenging thing to consider is the high throughput and high concurrency. And again, that means either point ranges of rows or random rows. And then the result set means kind of three things. Does the query group sort or limit the result set. And that's a consideration because grouping and sorting and limiting the results is a very important thing. And that means kind of three things. Does the query group sort or limit the result set. And that's a consideration because grouping and sorting and limiting have different performance optimizations and might even work differently on different data stores. So if you begin to think, oh, we can move this access or store this data somewhere else, but it groups or orders or limits the results that that might affect your considerations. So again, all these nine access patterns, when you begin to sit down and think about them in terms of your application, really begin to shed light on how the application uses my SQL and why or why not it is getting the performance out of my SQL that it does. It's related to these access patterns. As shown there at the bottom, the application accesses my SQL in some way, and those access patterns are really what determine what kind of performance you get out of my SQL in terms of query response time. So, in the book, I make this analogy by comparing by comparing a Toyota to a Ferrari, and here we have a Ferrari. It's an interesting analogy, I think, because both brands of cars have roughly the same parts. They have engines, transmissions, wheels, roughly all the same parts. But even looking at a car like this, the Ferrari, you know that it is so much more performant or high performance and efficient and faster and everything. But if you begin to think about, well, why is that if they have roughly the same parts? Why is this Ferrari such a faster machine? And the answer is, is because it is designed and engineered in every aspect to be faster and more efficient. The point is, is that if you want maximum performance out of my SQL, if you want to improve performance out of my SQL, then you have to engineer your application in terms of its access patterns specifically to be like a Ferrari. Last up in this short presentation is two ideas actually about my SQL in the cloud that I think are very important to know because running my SQL in the cloud is becoming much more popular and common. The first important idea, especially if you're an engineer just using my SQL in the cloud and trusting the cloud provider to operate it, is that cloud providers are not DBAs. They're, they often describe themselves as my SQL being fully managed, but there is a lot more toward that story. And so to give you just a brief idea, this is the condensed version of a database, list of database operations that I use to help teach new database engineers. If you want the full, very long list, it's on my website hackmysql.com under the engineer menu at the top database operations manual. But this is the short list. So we can just go down through it real quick here to show and to make you aware of the things that cloud providers are not going to do. So from top to bottom, of course, the cloud provisions and configures my SQL for you. That's part of the basic cost of everything. But then the next several things, my SQL user, server metrics, query metrics and OSC, which is online schema change, that's all up to you to do. And, you know, if you have just a few my SQL, maybe that's easy, but as you begin to have many, then that becomes a serious consideration. Failover is usually within the same region, but there are caveats here. The cloud providers do do this if one my SQL instance in the same region fails, they failover to another. And this is what they call high availability, but we'll get to that here in a moment. So they do provide that, but there are caveats. So read the fine print and test it on a reality. What they typically don't do, depending on the product, is disaster recovery, DR. So if you need to run my SQL in a completely different region, for example, if you're running on the US West Coast, you need to run on the US East Coast. That's up to you. And that is not a trivial process to do. These are closely related to high availability. I put a question mark in the cloud because they say they definitely claim it's high availability. But there's caveats there and read more about it, read the fine print. I would argue that it's not really high availability. So that's why I have a check under the right column view. The cloud does provide upgrading my SQL, but they don't really automate it unless you just let them run it at during their maintenance window. They provide backup recovery, which is nice. But then the remaining four items CDC's change data capture, which is streaming out data changes, usually to a data lake. They don't do that. They don't do security. They essentially don't help you with my SQL related things. It depends on your level of support, but you're probably also not going to get a my SQL DBA or export if you contact their support. And of course cost. That's up to you to keep costs under control. Speaking of cost, the last idea and point is that in the cloud performance is money. You pay for everything. You even have to, for example, provision IOPS and then you pay for those IOPS and also be aware that yes, it can be easy to scale up the database instance. But costs tend to double for each level. So if you go up the next level, your cost double and if you go up another level, then your cost have got tripled. So in the cloud performance, again, going back to the fact that performance is query response time is imperative because you pay for that performance. So really, if you remember this crowd at slide from the beginning, we say when we when we want better performance, we want my SQL to execute more queries in the same amount of time with the same hardware. To do that, we have to optimize those queries focusing on query response time because that's the metric that we experience and it's something we can do. And in the cloud, we can also add that we want to do this without it costing a fortune. So very brief presentation. I wish I had hours to cover everything. But that is why I recently wrote the book because all of this and a lot more is covered in the book. But let's summarize and talk about some next steps for you. So in summary, first and main idea, first step along the path is that performance is query response time. We optimize queries to reduce response time. And that is what truly increases my SQL performance. The application is what limits my SQL performance as a result of its access patterns. So if you ever think, oh, is my SQL being slow, that's not likely the case. It's more likely that the act that the application is causing my SQL to run slowly. Start with that premise and then, you know, hopefully trust that the DBAs will tell you if that is not actually the case that in the rare, rare cases that maybe something is affecting my SQL and it's not the application affecting my SQL. And the cloud, please be aware that cloud providers are not substitute for DBAs. So hire or contract help if needed. So to avoid outages, because if my SQL is down, well, that's kind of the worst performance of all, right? No response at all. Performance in the cloud requires even stricter attention to these details and every detail because you pay for every bit of that performance. And finally, again, the path to my SQL performance begins with query response time. So next steps. I know I breezed through, well, it seems like a lot of information, but it also seems like not a lot of information. This is why I recently wrote a book on the subject. And it's no surprise that for the next steps, these are the first eight chapters in my book. And they are also what I would suggest to you to learn more about as you go along the path from, you know, trying to reach that shining star, that north star of performance. So start with query response time and query metrics, then learn about indexes and indexing, then learn about data, especially with respect to keeping data size under control and efficient data access, then learn about access patterns. You know, make your application, engineer your application as a Ferrari for high performance in every detail. Then you'll probably have to learn about sharding scaling out my SQL. Then you need to learn about server metrics. Then you should learn about replication lag. Why? Because the risk there is data loss. And then you should learn about transactions and row locking because that is a fundamental part of a transactional data store. So that is the path that I have laid out when I'm teaching engineers how to achieve better performance with my SQL. I would highly encourage you to read the latest books. Start with what O'Reilly has published. For example, just recently they published three updated books about my SQL. Again, there's my book, Efficient My SQL Performance. There is also the fourth edition of High Performance My SQL, and there is also the second edition of Learning My SQL, all published by O'Reilly. Highly suggest that you read them and they will help you learn these aspects along the path. Then also read the relevant sections of the MySQL manual. That is the sections of the MySQL manual related to the things that you read in these books. You can also go to my website hackmysql.com slash help where I keep a list of these things, publishers, blogs, etc, etc. And finally, the best thing to do is get in there and try it. Start learning and doing these things. Begin with query metrics. Find the slowest queries. Explain those queries. Start going along these path, learning these things. Optimize those queries. Reduce query response time, and MySQL performance will improve. So thank you for attending this session. Thank you for attending FOSM 2022, and I hope you have a great rest of your day.
 Welcome to Efficient MySQL Performance in 40 Minutes by me, Daniel Nester, for FOSDOM  online February 2022.  Let's begin with a quick introduction.  Again, my name is Daniel Nester.  I have 17 years experience with MySQL. My personal website is hackmysql.com, where I've been  posting blog posts and tools and other stuff since about 2005.  But I'm usually known for my eight-year tenure at Percona, where I worked on Percona Toolkit.  I'm currently a DBA at Block, formerly known as Square.  And right now I'm working with the Cash App, helping to manage their databases.  I'm also the author of Efficient MySQL Performance, recently published by O'Reilly.  You can see the book cover there on the right.  This is actually my first virtual conference and recorded presentation.  So it's a little strange for me because I'm used to presenting in front of a live audience.  So to help you put a face to the voice that you're hearing, this is me.  Up top is a crazy beard that I grew recently, but it was a little too crazy.  And so I trimmed it finally.  And I usually look more like the photo at bottom,  is how people would probably recognize me from the previous conferences that were live and in person.  Likewise, I need an audience too.  So I chose this picture to have an audience to talk to,  because this is actually Faust in 2019.  So I will pretend like I am talking to this audience.  So objectives for this talk.  This is actually the second time I've recorded this talk,  because the first time I recorded it, the runtime came out to one hour, 25 minutes.  And this is supposed to be a 50 minute slot with some time for Q&A.  So that didn't work.  That is because there is a lot of material to cover.  This talk is based off my book, which is why it has the same title,  Efficient My Secret Performance.  The book has 10 chapters, but I knew that that wouldn't fit in the time slot.  So I first whittled it down to chapters one through four and chapter 10.  But even after recording that, that was an hour and 25 minutes.  So I've had to whittle it down even more to just these three chapters.  This presentation, like the book, is intended for engineers using MySQL,  not database administrators or MySQL experts or engineers aspiring to be DBAs or MySQL experts.  It provides a path through the complexity of MySQL as depicted here in this diagram.  When you're new to MySQL, it can be very difficult to know where to start and where to go after that  to achieve better performance, as shown there.  Performance is like a shining star somewhat out of reach.  Well, that's what this presentation does in super condensed form given the limited time.  It begins to show you a path through that complexity,  and that path begins with query response time.  The path begins with this understanding that performance is query response time.  Pretty much everything comes back to this point, especially for engineers using MySQL.  Performance is query response time.  But let's step back for a moment and ask a broader, kind of more general question,  which is, what do we want from better performance? When engineers say that they want better performance  with MySQL or MySQL is running slowly, or when they ask a DBA to do something to increase the performance of MySQL,  what are we really getting at here? What do we want as a result?  Oftentimes, there's a perception that we want more QPS,  but QPS is not performance. It is one measurement of performance, specifically just throughput.  But whether you have relatively high or relatively low QPS doesn't actually mean that MySQL is performing well.  And I'll show in a later slide how much QPS can vary based off different applications.  So just having high QPS doesn't mean that you have great performance.  Sometimes it's another metric like CPU usage. Maybe the engineer wants MySQL to utilize more CPU cores.  And I've actually had this case before, an application that was never really utilizing more than 50% CPU.  And the engineer wanted more performance out of MySQL and they thought, well, if MySQL would just use that other 50% of the CPU,  then it would be performing better. But that's not actually the case either.  Because the contrary sometimes happens too. Sometimes we want less CPU. I've had this happen too.  An application was causing MySQL to consistently use 90% or greater of CPU. It was really burning up the CPU.  And in that case, the engineer wanted MySQL, wanted better performance, meaning that MySQL would use less CPU.  So you can pick any metric and it's completely arbitrary.  And that's because these individual metrics are not what we really want from better performance.  At the end, they're just measurements out of performance. They aren't what performance actually is.  So what do we really want? It's not those things. What we really want is to do more work in the same amount of time with the same hardware.  You can think of MySQL like a machine in a factory that outputs something. That's its work.  We want to have that machine stay what it is. But we want that machine to do more work, output more of whatever it makes, for example.  Do more of that in the same amount of time each day, for example.  So that leads to the question, well, if MySQL is the machine, then what does it do?  MySQL really just executes queries. At the end of the day, you can just say that this is its one and only job.  Of course, under the hood, it's actually doing a lot of other things like replication, for example.  This is its purpose. The application puts in some data, stores it, and then maybe later it executes a query to fetch back that data.  And that's really all MySQL does is execute queries.  So what we're really saying here is this.  When we want better performance, we really want MySQL to execute more queries in the same amount of time with the same hardware.  That would be real nice. And if it could do that, then we would say, oh yeah, MySQL is performing a lot better.  Okay, great. So how do we achieve that?  Well, the typical approach is one, scaling up the hardware.  So basically a bigger, faster machine than tuning MySQL, tuning the machine, making it more efficient.  And then finally, optimizing queries, optimizing what we're putting into the machine.  That is the common sequence that I see all the time.  And it's understandable, perhaps, because from the application engineer's point of view of the person using MySQL, scaling up is relatively easy,  especially if you're in the cloud, just click some buttons and now you have more CPU cores and more RAM.  Tuning MySQL is also somewhat easy from their point of view because they ask the DBA to do that.  Hey, DBA, please fix the MySQL configuration or do something to make it more efficient.  And then finally, the most difficult is optimizing queries.  And I understand that too because query optimization is not a trivial task.  There's lots of things you have to learn and consider to do this.  So by comparison, it's a lot easier. But honestly, that is the wrong approach.  The correct approach is first optimizing queries, then scaling up hardware as a distant second, and then even more distant tuning MySQL.  Why? Going from the bottom up here. Why is tuning MySQL last?  Because MySQL is not a new database.  It has been around for 20 years or more, and it has been tuned and optimized over all that time by world-class database experts.  So internally, MySQL is already very tuned and highly efficient.  Tuning MySQL these days is akin to the proverbial squeezing blood from a turnip or squeezing blood from a stone or spinning gold from straw, whatever you want to use.  Some experts can do it, and there are cases where it needs to be done, but by and large, it is a distant, distant third.  Likewise, scaling up is a distant second because hardware is usually pretty plentiful.  It's pretty easy to get a lot of CPU cores, a lot of RAM, a lot of IOPS on the storage.  I mean, there are cases, especially in the cloud where it can be drastically under-provisioned.  But again, hardware is so cheap and plentiful these days that it's usually not the first thing you should jump to.  It's a distant second.  But number one, optimizing queries.  As we just said, the primary work that MySQL does is optimizing queries.  That is the input. That is what drives its work.  So that has the most bang for the buck, so to speak, for getting better performance by optimizing the work that the application is asking MySQL to do.  So let me move that up top.  So really, the first thing you need to do for better performance is optimizing queries.  And then I grade out two and three because they are important, but they're a distant second and a distant third.  So with respect to optimizing queries, again, we come back to query response time as the primary metric, as the north star that guides all your efforts.  Because for two reasons, well, query response time is the only metric we actually experience.  And if you think about that for a moment, you realize that that is true and uniquely so because if a query takes seven seconds to execute, we actually experience those seven seconds of maybe seven seconds of impatience.  And not just us as the engineers, but customers as well.  So they experience query response time in the form of slow queries, or hopefully they experience it in the positive as, you know, very quick queries in a very snappy responsive application.  So that's the first reason why query metric or query response time is the most important metric.  And the second reason why query response time is that it is actionable. We can directly change and optimize queries to make query response time less to make the queries execute faster.  And that is the clear goal here.  You can do this with some other metrics, but it doesn't necessarily mean that response time is going to be better, or that it will have some sort of impact on us as engineers or customers.  For example, you could make a query access less rows, but unless that affects response time, no one really experiences accessing a certain number of rows.  And sometimes you have to the query if the query means to select 10,000 rows, then it has to select 10,000 rows so you can't really change that.  But there's always things you can do to affect changes to query response time to reduce it.  So that's why again, it is the focus here.  So when we want better performance, we are going to focus on optimizing queries because that is the work that my SQL does.  The north star of all this is query response time because it's what we experience and it's what we can change.  So again, it comes back to this, this all important statement that performance is query response time.  And again, I'm going really fast here because we don't have much time in this presentation, but I wanted to at least provide three more specific steps about what you can do to get started along this path.  And I blogged about this recently. If you go to my website hackmysql.com, there is a blog post from January about how to enable query metrics and use a query metric tool.  But briefly, the steps are first you have to enable query metrics. This depends on several factors. Again, I documented in my blog post.  Then you need to use a tool to analyze those query metrics to find out which queries are the slowest. And then three, you begin to optimize those slow queries.  If you do this, the result will be that my SQL performance increases. So this is the first step along the path. This is the most important thing to know.  In fact, if you do nothing else but this, you might achieve much greater performance with my SQL for quite a long time and you might not need to make any other changes.  That's how powerful query response time, query metrics, query analysis is. That's why again, it is the first step along this path.  Next step, access patterns. Let me begin with this idea. My SQL performance is limited by the application, not the other way around.  This tends to garner some debate and it's understandable for reasons that we'll examine in slide after next.  But this is the main idea that I want to press upon with respect to access patterns and we'll build more on this idea here in a few slides.  But first, I wanted to present these real numbers from real servers, all of which are roughly the same hardware, all of which have roughly, you know, the same performance with respect to response time.  Like all these, these are real production applications that have acceptable response time.  And so, in so far as we can, you know, control all the variables we have.  And what this shows here is how much different access patterns or workloads from the application can affect my SQL.  So on the left column, we have threads running, which is a general indication of how hard my SQL is working because one thread executes one query.  And on the right column, we have the number, the QPS for those number of threads running.  So at the top, we had an application that generally has four threads running and does about 8000 QPS.  But then we have an application that doubles the amount of threads running, 8, but has slightly less QPS, 6000.  Third row, same number of threads running, different application, eight threads running, but now a huge jump to 30,000 QPS.  And then fourth row, a different application has typically 12 threads running and it's doing slightly less QPS, 23,000.  And on the last row, another example of a different application typically running 15 threads, but has 33,000 QPS.  Again, I'm not saying that QPS here is an indication of performance because that's not what performance is.  Performance is query response time and all these applications have good response time.  But what it shows is the huge difference in work and workload and other metrics that the applications access patterns can cause in my SQL,  especially those highlighted two rows there with eight threads running really makes the picture clear.  Whereas one application typically causing my SQL to keep eight threads active,  that turns out to be only 6000 QPS.  Whereas another application completely different, obviously, must have completely different access patterns,  has the exact same number of threads, so basically the same load on my SQL, at least in terms of threads running, but significantly more QPS.  So these are real world numbers.  So it gives you an idea, a sense of just how much access patterns from the application affect my SQL.  So let's now get into cause and effect and two perceptions and a reality.  The first perception here is that my SQL is slow and that is causing the application to be slow.  That arrow is a little thin there, but the arrow is pointing from my SQL to the application.  This is a very common perception, especially when engineers start to want more performance from my SQL or my SQL is suddenly slow.  They think that my SQL itself is being slow and that is therefore causing the application to be slow.  But I can assure you that this is rarely the case.  There are rare cases where my SQL might have a bug, for example, that causes some sort of slowness.  There are cases where a gross misconfiguration of my SQL might cause my SQL to be slow,  but in a professional environment, presuming that my SQL was configured by experts, that shouldn't be the case.  This perception is reality very, very rarely.  And when it is the case, it is usually some of the most interesting and difficult cases to figure out,  because as I mentioned earlier, my SQL is more than 20 years old, so it has been fine tuned to a very high degree.  This is usually not the case.  Second perception is somewhat related and somewhat more common, but again, in the grand scheme of things, it is still very rare.  The perception here is that something is causing my SQL to be slow, and therefore that causes the application to be slow.  So in the diagram, the turtle is the thing external to my SQL, causing my SQL to be slow, which then therefore causes the application to be slow.  Now, this definitely can't happen in my career.  As a DBA, I have seen this many times, and it could be anything from DNS to the network to a degraded rate array to, I mean, heck, I've seen faulty CPUs, faulty memory,  all sorts of external things cause my SQL to be slow and then cause the application to be slow.  But again, we presume that you're running my SQL in a stable, professionally managed and built out environment such that these things shouldn't happen.  There are the exceptions, not the norm, and especially kind of on a day to day basis, if there's no obvious fires, if there's no obvious turtles, so to speak,  then this is not the reality, at least not the one that you as an application engineer using my SQL should jump to.  And if it ever is the case, DBA should be on top of it and should be aware of this and should notify you ahead of time that, hey, there's this thing affecting our databases.  But again, this is usually not the reality.  The reality is this, almost always, it is the application that is accessing and using my SQL in some way, and that workload, the combination of queries, data and access patterns from the application to my SQL causes my SQL to be slow,  and then slow response time back to the application. But the root cause here is the application, how the application uses my SQL.  And really, it's as simple as that. I would say that this is probably the case in the reality, at least 80% of the time, if not more.  So the point is, when you're using my SQL, and everything's fine, and then suddenly everything's not fine, and my SQL is slow, start with this reality.  Start by asking, hey, what is the application doing, or perhaps not doing, what has changed about how the application is using my SQL,  because that is the vast majority of the times, that is why the application is getting slow response from my SQL, because the application is doing something to my SQL that is causing it to have slow response time.  And only rarely, the other perception one and two are the case, and when they are, hopefully your DBA will let you know about it ahead of time.  So let's keep that reality there at the bottom.  And now focus on access patterns. And the interesting thing about access patterns, to me at least, is that everyone seems to know them and talk about them.  Pretty much every engineer I've ever dealt with is aware of access patterns and even talks about them. But interestingly, I have never seen a, an enumerated list of specific access patterns for my SQL, and I couldn't really find any for any other  relational transactional database.  I did find one for Amazon DynamoDB. They publish a list of access patterns specific to DynamoDB.  But apart from that, I couldn't find it from my SQL. And so in the course of writing my book, I enumerated this list of nine access patterns.  And so I'll briefly run through each of the nine to give you an idea of what they signify with respect to how the application is accessing data in my SQL.  And a lot of them will be very familiar to you, I'm sure, but now they have a specific name and are specifically enumerated.  So the first access pattern is probably the most basic and probably the most important, which is read write. Does the application primarily read data, primarily write data, or is it a combination of both?  And that's important because scaling reads and writes are much different considerations.  So write scaling writes is much more difficult. Likewise, throughput is closely related to there's a big difference between an application doing 100 reads per second versus 100,000 reads per second.  Likewise with writes, if you're doing, you know, one write per second, you can almost forget everything. If you're going to try and do 100,000 writes per second.  Okay, now that's a that's a significant challenge and entails all sorts of other considerations.  Data age is difficult to explain in short terms. But basically it is a reflection of, or it's an indication of the how my SQL is going to handle the working set size.  Data is accessed in my SQL. The lingo is that data is made young.  So when it's access, it's new, it's young. And when data ceases to be accessed, it gets older and older until it is eventually evicted out of memory.  And because a certain percentage of the data, generally the rule of thumb is 10 to 20% of your data is considered the working set the frequently accessed data.  That is the data that my SQL tries to keep in memory. And data age as an access pattern has us look at and consider whether that is actually true.  The application sticks to that working set, or does the application tend to dredge up old data so frequently that it's difficult for my SQL to keep the working set in memory.  Data model means, does the access reflect a relational database, a transactional database, you know, row or row oriented database, or does it reflect something else like a document store or Q.  As it turns out, applications use my SQL for a lot of purposes, not all of which actually are good fits for a relational data model.  So that's why we need to look at it. Transaction isolation is also important because my SQL is a transactional storage engine, at least in ODB, in other storage engines.  And so does the application actually need transaction isolation, and if so, which levels. If it doesn't, then maybe my SQL or any transactional data store is not the right choice.  Read consistency means that does the application need read after write semantics, or is eventually consistent read good enough or acceptable.  If you can do eventually consistent reads, then perhaps this data can be read from a cache. So another important consideration, especially if you have a high rate of reads, you know, the three put through reads is very high.  And if those can be eventually consistent, then great, there might be an avenue for performance optimization by using a cache.  Concurrency means is the same data accessed at the same time. Does the application typically do this for applications with high concurrency, meaning they're accessing the same rows at the same time.  Then the other access pattern traits become really important, like is it a read, concurrent reads or concurrent writes concurrent reads are relatively easy to scale but concurrent writes.  And that is the that's the tricky performance problem to scale, especially if you're talking a high throughput concurrent writes. That's one of the most challenging things.  So we need to consider the concurrency of the data access.  Row access means does it access single rows, ranges of rows, or random rows all over the place.  So if you have a high throughput and high concurrency that also writes data all over the place, that is, that's a real tough access pattern.  But other access patterns are a little bit different.  So the most challenging thing to consider is the high throughput and high concurrency.  And again, that means either point ranges of rows or random rows. And then the result set means kind of three things.  Does the query group sort or limit the result set. And that's a consideration because grouping and sorting and limiting the results is a very important thing.  And that means kind of three things. Does the query group sort or limit the result set. And that's a consideration because grouping and sorting and limiting have different performance optimizations and might even work differently on different data stores.  So if you begin to think, oh, we can move this access or store this data somewhere else, but it groups or orders or limits the results that that might affect your considerations.  So again, all these nine access patterns, when you begin to sit down and think about them in terms of your application, really begin to shed light on how the application uses my SQL and why or why not it is getting the performance out of my SQL that it does.  It's related to these access patterns.  As shown there at the bottom, the application accesses my SQL in some way, and those access patterns are really what determine what kind of performance you get out of my SQL in terms of query response time.  So, in the book, I make this analogy by comparing by comparing a Toyota to a Ferrari, and here we have a Ferrari.  It's an interesting analogy, I think, because both brands of cars have roughly the same parts. They have engines, transmissions, wheels, roughly all the same parts.  But even looking at a car like this, the Ferrari, you know that it is so much more performant or high performance and efficient and faster and everything.  But if you begin to think about, well, why is that if they have roughly the same parts? Why is this Ferrari such a faster machine?  And the answer is, is because it is designed and engineered in every aspect to be faster and more efficient.  The point is, is that if you want maximum performance out of my SQL, if you want to improve performance out of my SQL, then you have to engineer your application in terms of its access patterns specifically to be like a Ferrari.  Last up in this short presentation is two ideas actually about my SQL in the cloud that I think are very important to know because running my SQL in the cloud is becoming much more popular and common.  The first important idea, especially if you're an engineer just using my SQL in the cloud and trusting the cloud provider to operate it, is that cloud providers are not DBAs.  They're, they often describe themselves as my SQL being fully managed, but there is a lot more toward that story.  And so to give you just a brief idea, this is the condensed version of a database, list of database operations that I use to help teach new database engineers.  If you want the full, very long list, it's on my website hackmysql.com under the engineer menu at the top database operations manual.  But this is the short list.  So we can just go down through it real quick here to show and to make you aware of the things that cloud providers are not going to do.  So from top to bottom, of course, the cloud provisions and configures my SQL for you. That's part of the basic cost of everything.  But then the next several things, my SQL user, server metrics, query metrics and OSC, which is online schema change, that's all up to you to do.  And, you know, if you have just a few my SQL, maybe that's easy, but as you begin to have many, then that becomes a serious consideration.  Failover is usually within the same region, but there are caveats here.  The cloud providers do do this if one my SQL instance in the same region fails, they failover to another.  And this is what they call high availability, but we'll get to that here in a moment.  So they do provide that, but there are caveats.  So read the fine print and test it on a reality.  What they typically don't do, depending on the product, is disaster recovery, DR.  So if you need to run my SQL in a completely different region, for example, if you're running on the US West Coast, you need to run on the US East Coast.  That's up to you.  And that is not a trivial process to do.  These are closely related to high availability.  I put a question mark in the cloud because they say they definitely claim it's high availability.  But there's caveats there and read more about it, read the fine print.  I would argue that it's not really high availability. So that's why I have a check under the right column view.  The cloud does provide upgrading my SQL, but they don't really automate it unless you just let them run it at during their maintenance window.  They provide backup recovery, which is nice.  But then the remaining four items CDC's change data capture, which is streaming out data changes, usually to a data lake.  They don't do that.  They don't do security.  They essentially don't help you with my SQL related things.  It depends on your level of support, but you're probably also not going to get a my SQL DBA or export if you contact their support.  And of course cost.  That's up to you to keep costs under control.  Speaking of cost, the last idea and point is that in the cloud performance is money.  You pay for everything.  You even have to, for example, provision IOPS and then you pay for those IOPS and also be aware that yes, it can be easy to scale up the database instance.  But costs tend to double for each level.  So if you go up the next level, your cost double and if you go up another level, then your cost have got tripled.  So in the cloud performance, again, going back to the fact that performance is query response time is imperative because you pay for that performance.  So really, if you remember this crowd at slide from the beginning, we say when we when we want better performance, we want my SQL to execute more queries in the same amount of time with the same hardware.  To do that, we have to optimize those queries focusing on query response time because that's the metric that we experience and it's something we can do.  And in the cloud, we can also add that we want to do this without it costing a fortune.  So very brief presentation.  I wish I had hours to cover everything.  But that is why I recently wrote the book because all of this and a lot more is covered in the book.  But let's summarize and talk about some next steps for you.  So in summary, first and main idea, first step along the path is that performance is query response time.  We optimize queries to reduce response time.  And that is what truly increases my SQL performance.  The application is what limits my SQL performance as a result of its access patterns.  So if you ever think, oh, is my SQL being slow, that's not likely the case.  It's more likely that the act that the application is causing my SQL to run slowly.  Start with that premise and then, you know, hopefully trust that the DBAs will tell you if that is not actually the case that in the rare, rare cases that maybe something is affecting my SQL and it's not the application affecting my SQL.  And the cloud, please be aware that cloud providers are not substitute for DBAs.  So hire or contract help if needed.  So to avoid outages, because if my SQL is down, well, that's kind of the worst performance of all, right?  No response at all.  Performance in the cloud requires even stricter attention to these details and every detail because you pay for every bit of that performance.  And finally, again, the path to my SQL performance begins with query response time.  So next steps.  I know I breezed through, well, it seems like a lot of information, but it also seems like not a lot of information.  This is why I recently wrote a book on the subject.  And it's no surprise that for the next steps, these are the first eight chapters in my book.  And they are also what I would suggest to you to learn more about as you go along the path from, you know, trying to reach that shining star, that north star of performance.  So start with query response time and query metrics, then learn about indexes and indexing, then learn about data, especially with respect to keeping data size under control and efficient data access, then learn about access patterns.  You know, make your application, engineer your application as a Ferrari for high performance in every detail.  Then you'll probably have to learn about sharding scaling out my SQL.  Then you need to learn about server metrics.  Then you should learn about replication lag.  Why?  Because the risk there is data loss.  And then you should learn about transactions and row locking because that is a fundamental part of a transactional data store.  So that is the path that I have laid out when I'm teaching engineers how to achieve better performance with my SQL.  I would highly encourage you to read the latest books.  Start with what O'Reilly has published.  For example, just recently they published three updated books about my SQL.  Again, there's my book, Efficient My SQL Performance.  There is also the fourth edition of High Performance My SQL, and there is also the second edition of Learning My SQL, all published by O'Reilly.  Highly suggest that you read them and they will help you learn these aspects along the path.  Then also read the relevant sections of the MySQL manual.  That is the sections of the MySQL manual related to the things that you read in these books.  You can also go to my website hackmysql.com slash help where I keep a list of these things, publishers, blogs, etc, etc.  And finally, the best thing to do is get in there and try it.  Start learning and doing these things.  Begin with query metrics.  Find the slowest queries.  Explain those queries.  Start going along these path, learning these things.  Optimize those queries.  Reduce query response time, and MySQL performance will improve.  So thank you for attending this session.  Thank you for attending FOSM 2022, and I hope you have a great rest of your day.
efficient_mysql_0_59.png efficient_mysql_60_107.png efficient_mysql_108_155.png efficient_mysql_156_455.png efficient_mysql_456_551.png efficient_mysql_552_1967.png efficient_mysql_1968_2519.png efficient_mysql_2520_2927.png efficient_mysql_2928_3047.png efficient_mysql_3048_3791.png efficient_mysql_3792_4079.png efficient_mysql_4080_4187.png efficient_mysql_4188_5447.png efficient_mysql_5448_5543.png efficient_mysql_5544_6095.png efficient_mysql_6096_6611.png efficient_mysql_6612_7487.png efficient_mysql_7488_8291.png efficient_mysql_8292_9431.png efficient_mysql_9432_10379.png efficient_mysql_10380_11063.png efficient_mysql_11064_11135.png efficient_mysql_11136_11615.png efficient_mysql_11616_13439.png efficient_mysql_13440_16067.png efficient_mysql_16068_19847.png efficient_mysql_19848_20039.png efficient_mysql_20040_21947.png efficient_mysql_21948_22091.png efficient_mysql_22092_22895.png efficient_mysql_22896_27143.png efficient_mysql_27144_29411.png efficient_mysql_29412_31835.png efficient_mysql_31836_34139.png efficient_mysql_34140_44987.png efficient_mysql_44988_46775.png efficient_mysql_46776_47279.png efficient_mysql_47280_47879.png efficient_mysql_47880_51899.png efficient_mysql_51900_52823.png efficient_mysql_52824_53351.png efficient_mysql_53352_53483.png efficient_mysql_53484_53879.png efficient_mysql_53880_55811.png efficient_mysql_55812_59507.png efficient_mysql_59508_59723.png efficient_mysql_59724_59795.png efficient_mysql_59796_59867.png efficient_mysql_59868_59880.png