18 Things To Do When You Have a MySQL Bottleneck
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.
Zaitsev, Peter
The presentation will be focused on dealing with an unexpected high traffic event as it is happening. We will address the impact a traffic spike can have on your database – from poor query response time to replication lag issues – and provide actionable tips and tricks. We will talk about how to bring your database under control, earn performance gains and delight your customers.
 Hello, everyone. Today we'll talk about dealing with my spill bottlenecks. We have a lot of material to cover and not a lot of time, so let's get going. In this presentation, we will focus about the traffic spikes. That means about the problems which are sudden. Yesterday my skill was working just fine and today it just cannot keep up. That means we are focused on something which you can do right now, implementing a few hours or maybe days, not weeks or months, because you don't have as much time than you're dealing with traffic spikes. Also, I have written a companion article which talks about some of those tips and ideas in more detail which I suggest you can check out. Let's go straight to some tips. The first one, especially if you run in the cloud, is what you can scale your cloud instance, which is kind of obvious. What may not be is how large you can actually go. In Amazon cloud, for example, you can go to hundreds of logical processors and terabytes of RAM. If money is not an option, you can really often get all your database into memory and get really, really good performance, which is often good to use at least as a temporary solution until you can find some more cost-effective options or at least when traffic spikes down. We've got the thing of course in the cloud. While you can all scale up, you can also scale down without having to make the commitment for a long time. Tip number two is to deploy more minus-squarell replicas. No, though, this works best for intensive applications and it also is limited in terms of gains you can get because you need multiple copies of data you can pay for and cash would not be very efficient in this case because pretty much any replica would have the same data in memory unless you do something special. The next tip would be to deploy proxy SQL, which is another secret weapon you can use for multiple reasons. It can be the great connection polling and pulling. If that is a problem, you can see the freed right splitting so you can utilize your replicas better. It supports the query cache with TTL so you can really even handle some queries on the proxy SQL side and not hit so minus-squarell to begin with. It is wonderful in many ways. But also note what the proxy SQL is not 100% minus-squarell compatible and some applications are just maybe required to do that. The next idea would be to disable heavy load application features. While this can be frowned upon and saying, oh, well, that's kind of cheating, actually it is a great practice in many cases to remove those lower value features or maybe some background job that you have to keep the most high value features working with great performance. It also can be applied to specific users too. Maybe you have some abusive users or maybe users which have too much load, frothing them some way or limited their features can provide you, help you to have a great service to everybody else. The next thing to think about is resource bottlenecks. What are those resource bottlenecks which impact my SQL performance? Well, these are typically those for your CPU, your memory, your disk storage and of course the network. If you understand what resource is being most used, you can scale that resource in particular. For example, you can get more CPU cores or faster cores. In this case, what you should check is how is the CPU used? Are you seeing what overall CPU utilization is high or do you have some of the cores which are packed at 100% percent? In the first case, you would want more cores. In the second, your workload may be single threaded and you need a faster core instead. In addition to looking at those serialization graphs, it is a great idea to take a look at the run queue latency. That pretty much tells how much time passes before the protest is ready to run on CPU and then it actually gets the CPU available to run. It should be low if it goes into high numbers like you're waiting for many milliseconds to run to get scheduled, then that adds up to the latency and creates that user experience. The next thing to consider is CPU stealing, especially for some instances in the cloud virtualized instances. You may not be getting all the CPU what you think you are and that can be the cause of substantial slowdowns. You also should consider using compute optimized CPUs if a CPU is a bottleneck. It is not going to give you massive improvements compared to a general purpose instance that the clouds have, but it can give you maybe another 20% more runway which may be important. Memory is another key item. Memory is used by database first and foremost for caching the data and accessing the data in memory is much, much faster even if compared to fastest SSD storage. Also what is important, what processing data in memory compared to fetching it from the disk and then processing stays a lot of the CPU cycles. That is another benefit you get. Note though, while having a data in memory can eliminate all the read loads, you still would need to have a write to hit in the disk because you have to persist the data on the disk in the end. You also do not want to run out of the virtual memory because if that happens, then your database will crash. It will go through a long process of crash recovery which can even segregate the problem. I like having the systems configured with swap and actually swap utilization for a bit is okay, especially those days if a fast SSD storage. What you don't want is to have a swap activity being very significant. In many cases you would be thinking about okay, how much memory my processes are using. If you have faced this question, I would suggest not to upset a virtual memory with some processes, especially written in Golan, maybe excessively large, but that doesn't mean anything because that memory is allocated and never used. What you want to look at the resident and swap, which tells us how much memory is actually committed and the real RAM is used or was swapped out and potentially will need to be swapped in if that application is active. Now, as I mentioned, memory is faster than a fast storage, but in certain cases you just can't get your database all in memory. It's just not practical and also you need a fast storage for write performance. What is important in this case is for write fast write performance is you want to focus on the latency, especially latency of such operations as F-Sync rather than the bandwidth because a lot of storage will advertise their performance in terms of their bandwidth, how many megabytes per second they can write, try to IOPS per second they can do. That is not what you care about. You should care about the latency of a single threaded IOPS. That is what often becomes the change. You need to understand what type of IOPS activity your system is having to optimize the system properly and in many cases it is a great idea to look at your disk performance and compare it to the historical latency. How do you know if your disk is overloaded and can't keep up with the load? What really happens in this case is what you would see your latency spike compared to historical averages and what normal for that storage is. Then you would know you need to probably get some more disk capacity or reduce the disk load by adding more memory or something else. Something that you always have to care of database is also looking at the F-Sync as a very special kind of IOPS which can be often the bottleneck. Another thing what you do not often get if your typical VM stats graphs is their latency distribution. You can get the tools like the biolatency. You want to make sure that you're not getting large outliers. Why is that? Because outliers can be very bad for database. Think about if your log write stalls. When that happens, the whole database stalls and it doesn't matter what there could be other IOs going very fast at that time. With database, you just want to make sure the storage performs very consistently. With your network, you want to think about the free components, the bandwidth, the latency, and the packet loss. In terms of bandwidth, I would suggest you to check how much network traffic you're really having. Compare that to what you expect your capacity is, both in Tompsovware, your local network card, as well as a general your network infrastructure. You also want to check the latency. Again, if latency, the idea is not to just check it once, but understand what the distribution is, because delays, even for some small portion of the packets, can significantly reduce your performance. The other thing to consider in a network, which is also often omitted, is retransmits. How many packets are dropped or corrupted, and they have to be retransmitted? That can be a really big problem, because retransmits can add a specific amount, a large amount of latency. Here is the cool tool, which you can consider called TCP retrans. It actually can tell you what connections have suffered from those retransmits, which when using MySQL process list, you can often connect to specific clients and maybe explain to people why particular connections were suffering or something which would not be seen on the MySQL query, because MySQL may be thinking whether to respond to queries very fast, but it actually takes a lot of time to propagate. Next tip is you want to locate and optimize the queries, which cause the load on your system. In this case, you can use the tools like PMM, what I can show here, or some other tools of your choice. Looking at the query execution details, you often can find what exactly is causing the query to run slow, and so how to fix it. You also want to check for explain. Explain is a very important tool for developers and a DBA to use to make sure what you have your queries in a good state in order. The next idea would be to add missing indexes. That is a very, very important task, because missing index can cause query to run thousands of times slower than it should be. And frankly, most applications have some indexes which are missing in our experience. You also want to drop unneeded indexes, because those are expensive and actually expensive both reads and writes. Right? Drop or duplicate near indexes or others. You can use pure corner tool kit or C schema to find the duplicate index you can have. You also want to make sure to configure MySQL server properly. MySQL defaults are not designed really for large scale production load. If you're looking to top MySQL settings to change, I would check out at least those. In their new MySQL versions, you can also use the option in a DBA dedicated server, which will adjust in a DBA related options. But I would still pay attention to other options as well. The next thing you want to may ask me, what about Linux? Well, Linux is actually pretty good at self-tuning. And you may be able to tune something out there, but it generally does okay with defaults, not as MySQL server. The most important thing in Linux we typically deal with is a limited number of open files for MySQL process. Because if you don't have that, you had substantially high, you wouldn't be able to accept many connections or many open tables to work with efficiently. So check that out. Another idea would be to ensure you are purging unneeded data. Many databases keep the data which is not needed to deal with operational stuff. Some old copies of the database, just some old data logs and so on and so forth, right? Which you often can use to move to a different server or move to archival storage. We actually have a tool for that in the corner tool kit called PTRciver. Of course, you can use other tools. But of course, be careful because archiving the data would cause additional limit value to a lot. The database maintenance kind of lies in the same order. In many cases, database can benefit and run faster after maintenance after you analyze or optimize the table. But this operation itself is expensive. So you want to be careful. Here is how you can optimize all the tables if you like. The next tip is to check your background jobs. In many cases, background jobs are not considered as performance critical and often they are not optimized very well. But they are often created a lot of aside loads, which is a problematic. And you can optimize them. Maybe you can move them to replicas or maybe you can even delay them until traffic goes by fast. Check out for data hotspots. That is another important item because in many cases application, maybe you may see what you are not seeing any high resource usage, but things just run very slowly. Why? Because of roll level locks, right? Which happen from data hotspots and you can resolve them in many cases and make application to run much faster. Data hotspots also can additionally cause excessive errors due rollbacks and lock weight timeouts which can segregate the problems for you, especially because many applications do not handle errors and rollbacks everywhere. So if you are using PMM, you can actually look at the specific queries which you have in issues like an example here where lock time takes the largest percent of the query execution time. Finally, we get to our agent tip of checking your application in server configuration. That is also where you can often get a substantial amount of benefits especially in a complicated client drivers such as JDBC which has a lot of tuning options. So as a summary, well, you should not panic you have a miniscule traffic spike as you can see there is many tools available for you. I'm not expecting for you to go ahead and pursue all 18 of them and others as well at the same time but you often can choose a few of them to use, which are easiest in your environment to get at least 3x more traffic handling capacity from your system with a relatively low handling group or maybe more. With that, that's all I have for you and I would be happy to answer some questions.
 Hello, everyone. Today we'll talk about dealing with my spill bottlenecks. We have a lot of  material to cover and not a lot of time, so let's get going. In this presentation, we will focus  about the traffic spikes. That means about the problems which are sudden. Yesterday my  skill was working just fine and today it just cannot keep up. That means we are focused on  something which you can do right now, implementing a few hours or maybe days, not weeks or months,  because you don't have as much time than you're dealing with traffic spikes. Also, I have written  a companion article which talks about some of those tips and ideas in more detail which I suggest  you can check out. Let's go straight to some tips. The first one, especially if you run in the cloud,  is what you can scale your cloud instance, which is kind of obvious. What may not be is how large  you can actually go. In Amazon cloud, for example, you can go to hundreds of logical  processors and terabytes of RAM. If money is not an option, you can really often get all your  database into memory and get really, really good performance, which is often good to use at least  as a temporary solution until you can find some more cost-effective options or at least when  traffic spikes down. We've got the thing of course in the cloud. While you can all scale up, you can  also scale down without having to make the commitment for a long time. Tip number two  is to deploy more minus-squarell replicas. No, though, this works best for intensive  applications and it also is limited in terms of gains you can get because you need multiple copies  of data you can pay for and cash would not be very efficient in this case because pretty much  any replica would have the same data in memory unless you do something special.  The next tip would be to deploy proxy SQL, which is another secret weapon you can use for multiple  reasons. It can be the great connection polling and pulling. If that is a problem, you can see the  freed right splitting so you can utilize your replicas better. It supports the query cache  with TTL so you can really even handle some queries on the proxy SQL side and not hit  so minus-squarell to begin with. It is wonderful in many ways. But also note what the proxy SQL  is not 100% minus-squarell compatible and some applications are just maybe required to do that.  The next idea would be to disable heavy load application features. While this can be frowned  upon and saying, oh, well, that's kind of cheating, actually it is a great practice in many cases to  remove those lower value features or maybe some background job that you have to keep the most  high value features working with great performance. It also can be applied to specific users too.  Maybe you have some abusive users or maybe users which have too much load, frothing them  some way or limited their features can provide you, help you to have a great service to everybody  else. The next thing to think about is resource bottlenecks. What are those resource bottlenecks  which impact my SQL performance? Well, these are typically those for your CPU, your memory,  your disk storage and of course the network. If you understand what resource is being most used,  you can scale that resource in particular. For example, you can get more CPU cores or faster  cores. In this case, what you should check is how is the CPU used? Are you seeing what  overall CPU utilization is high or do you have some of the cores which are packed at 100% percent?  In the first case, you would want more cores. In the second, your workload may be single threaded  and you need a faster core instead. In addition to looking at those serialization graphs, it is a  great idea to take a look at the run queue latency. That pretty much tells how much time passes before  the protest is ready to run on CPU and then it actually gets the CPU available to run. It should  be low if it goes into high numbers like you're waiting for many milliseconds to run to get scheduled,  then that adds up to the latency and creates that user experience. The next thing to consider  is CPU stealing, especially for some instances in the cloud virtualized instances. You may not be  getting all the CPU what you think you are and that can be the cause of substantial slowdowns.  You also should consider using compute optimized CPUs if a CPU is a bottleneck. It is not going to  give you massive improvements compared to a general purpose instance that the clouds have,  but it can give you maybe another 20% more runway which may be important. Memory is another  key item. Memory is used by database first and foremost for caching the data and accessing the  data in memory is much, much faster even if compared to fastest SSD storage. Also what is  important, what processing data in memory compared to fetching it from the disk and then processing  stays a lot of the CPU cycles. That is another benefit you get. Note though, while having a  data in memory can eliminate all the read loads, you still would need to have a write  to hit in the disk because you have to persist the data on the disk in the end.  You also do not want to run out of the virtual memory because if that happens, then your database  will crash. It will go through a long process of crash recovery which can even segregate the  problem. I like having the systems configured with swap and actually swap utilization for a bit  is okay, especially those days if a fast SSD storage. What you don't want is to have a swap  activity being very significant. In many cases you would be thinking about okay, how much memory  my processes are using. If you have faced this question, I would suggest not to upset a virtual  memory with some processes, especially written in Golan, maybe excessively large, but that  doesn't mean anything because that memory is allocated and never used. What you want to look  at the resident and swap, which tells us how much memory is actually committed and the real RAM  is used or was swapped out and potentially will need to be swapped in if that application  is active. Now, as I mentioned, memory is faster than a fast storage, but in certain cases you just  can't get your database all in memory. It's just not practical and also you need a fast storage for  write performance. What is important in this case is for write fast write performance is you want  to focus on the latency, especially latency of such operations as F-Sync rather than the bandwidth  because a lot of storage will advertise their performance in terms of their bandwidth, how  many megabytes per second they can write, try to IOPS per second they can do. That is not what you  care about. You should care about the latency of a single threaded IOPS. That is what often becomes  the change. You need to understand what type of IOPS activity your system is having to optimize  the system properly and in many cases it is a great idea to look at your disk performance and compare  it to the historical latency. How do you know if your disk is overloaded and can't keep up with the  load? What really happens in this case is what you would see your latency spike compared to historical  averages and what normal for that storage is. Then you would know you need to probably  get some more disk capacity or reduce the disk load by adding more memory or something else.  Something that you always have to care of database is also looking at the F-Sync as a very special  kind of IOPS which can be often the bottleneck. Another thing what you do not often get if your  typical VM stats graphs is their latency distribution. You can get the tools like  the biolatency. You want to make sure that you're not getting large outliers. Why is that? Because  outliers can be very bad for database. Think about if your log write stalls.  When that happens, the whole database stalls and it doesn't matter what there could be other  IOs going very fast at that time. With database, you just want to make sure the storage performs  very consistently. With your network, you want to think about the free components, the bandwidth,  the latency, and the packet loss. In terms of bandwidth, I would suggest you to check how much  network traffic you're really having. Compare that to what you expect your capacity is, both in  Tompsovware, your local network card, as well as a general your network infrastructure. You also want  to check the latency. Again, if latency, the idea is not to just check it once, but understand what  the distribution is, because delays, even for some small portion of the packets, can significantly  reduce your performance. The other thing to consider in a network, which is also often omitted,  is retransmits. How many packets are dropped or corrupted, and they have to be retransmitted?  That can be a really big problem, because retransmits can add a specific amount, a large amount of  latency. Here is the cool tool, which you can consider called TCP retrans. It actually can tell  you what connections have suffered from those retransmits, which when using MySQL process list,  you can often connect to specific clients and maybe explain to people why particular  connections were suffering or something which would not be seen on the MySQL query, because  MySQL may be thinking whether to respond to queries very fast, but it actually takes a lot of time  to propagate. Next tip is you want to locate and optimize the queries, which cause the load  on your system. In this case, you can use the tools like PMM, what I can show here, or some other  tools of your choice. Looking at the query execution details, you often can find what exactly is  causing the query to run slow, and so how to fix it. You also want to check for explain.  Explain is a very important tool for developers and a DBA to use to make sure what you have your  queries in a good state in order. The next idea would be to add missing indexes. That is a very,  very important task, because missing index can cause query to run thousands of times slower  than it should be. And frankly, most applications have some indexes which are missing in our  experience. You also want to drop unneeded indexes, because those are expensive and actually  expensive both reads and writes. Right? Drop or duplicate near indexes or others. You can use  pure corner tool kit or C schema to find the duplicate index you can have. You also want to  make sure to configure MySQL server properly. MySQL defaults are not designed really for  large scale production load. If you're looking to top MySQL settings to change, I would check out  at least those. In their new MySQL versions, you can also use the option in a DBA dedicated server,  which will adjust in a DBA related options. But I would still pay attention to other options as  well. The next thing you want to may ask me, what about Linux? Well, Linux is actually pretty good  at self-tuning. And you may be able to tune something out there, but it generally does  okay with defaults, not as MySQL server. The most important thing in Linux we typically deal with  is a limited number of open files for MySQL process. Because if you don't have that,  you had substantially high, you wouldn't be able to accept many connections or many  open tables to work with efficiently. So check that out. Another idea would be to ensure you  are purging unneeded data. Many databases keep the data which is not needed to deal with operational  stuff. Some old copies of the database, just some old data logs and so on and so forth, right?  Which you often can use to move to a different server or move to archival storage. We actually  have a tool for that in the corner tool kit called PTRciver. Of course, you can use other tools.  But of course, be careful because archiving the data would cause additional limit value  to a lot. The database maintenance kind of lies in the same order. In many cases,  database can benefit and run faster after maintenance after you analyze or optimize  the table. But this operation itself is expensive. So you want to be careful. Here is how you can  optimize all the tables if you like. The next tip is to check your background jobs. In many cases,  background jobs are not considered as performance critical and often they are not optimized very  well. But they are often created a lot of aside loads, which is a problematic. And you can optimize  them. Maybe you can move them to replicas or maybe you can even delay them until traffic  goes by fast. Check out for data hotspots. That is another important item because in many cases  application, maybe you may see what you are not seeing any high resource usage, but things just  run very slowly. Why? Because of roll level locks, right? Which happen from data hotspots and you can  resolve them in many cases and make application to run much faster. Data hotspots also can  additionally cause excessive errors due rollbacks and lock weight timeouts which can segregate  the problems for you, especially because many applications do not handle errors and rollbacks  everywhere. So if you are using PMM, you can actually look at the specific queries which  you have in issues like an example here where lock time takes the largest percent of the query  execution time. Finally, we get to our agent tip of checking your application in server  configuration. That is also where you can often get a substantial amount of benefits  especially in a complicated client drivers such as JDBC which has a lot of tuning options.  So as a summary, well, you should not panic you have a miniscule traffic spike as you can see  there is many tools available for you. I'm not expecting for you to go ahead and pursue all 18  of them and others as well at the same time but you often can choose a few of them to use,  which are easiest in your environment to get at least 3x more traffic handling capacity from  your system with a relatively low handling group or maybe more. With that, that's all I have for  you and I would be happy to answer some questions.
mysql_bottleneck_0_587.png mysql_bottleneck_588_875.png mysql_bottleneck_876_1223.png mysql_bottleneck_1224_2987.png mysql_bottleneck_2988_3191.png mysql_bottleneck_3192_3983.png mysql_bottleneck_3984_4679.png mysql_bottleneck_4680_6035.png mysql_bottleneck_6036_11915.png mysql_bottleneck_11916_13019.png mysql_bottleneck_13020_14255.png mysql_bottleneck_14256_16727.png mysql_bottleneck_16728_16955.png mysql_bottleneck_16956_17567.png mysql_bottleneck_17568_18599.png mysql_bottleneck_18600_20135.png mysql_bottleneck_20136_20399.png mysql_bottleneck_20400_21899.png mysql_bottleneck_21900_24899.png mysql_bottleneck_24900_26327.png mysql_bottleneck_26328_27191.png mysql_bottleneck_27192_28535.png mysql_bottleneck_28536_28619.png mysql_bottleneck_28620_29064.png