Running MySQL on ARM
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.
Bauskar, Krunal
MySQL joined the ARM ecosystem with 8.x release. This opened up a completely new vertical and provided a cost-effective alternative to users. With multiple cloud providers providing ARM instances more and more users/developers are getting interested in running MySQL on ARM. Let's explore what it means to run MySQL on ARM through enhancement done, what more could be optimized, patches already in pipelines, features that could benefit from more cores on ARM, state of ecosystem how it could be further improved, any special configurations to tune, performance, stability, notes on migration, do and don't, etc...
 Welcome all. Today we are going to talk about running MySQL on ARM. So we will see why it is advantageous to run MySQL on ARM and what more as a developer we could do to make it further optimized for ARM. With that quick note, let's get started. My name is Krunal Bhouskar. I have been driving this MySQL on ARM initiative. That's quick intro about me. I have been working in MySQL space for more than a decade now. In past I have worked with Perkona, Oracle, Yahoo Labs, KickFile Terraria data. Currently I am working with Huawei as part of the open source DB group where we are trying to make all the open source database not only the database but also the complete ecosystem optimal for ARM. So that quick intro about me, let's jump to our today's agenda. So we will first talk about ARM ecosystem to get everyone on the same page and then we will talk about the state of MySQL ecosystem from user perspective and then developer perspective. We will end it back with the community and what community can contribute to. I am sure all of us have heard about ARM processors. They are widely used in all kind of applications. Very primarily they are used in mobile phones and network equipment. They are also used in lot of smart home appliances and IoT devices. They are also growing use of ARM processors in smart driving cars and of course with the space programs. What is something new and has been catching lot of traction lately is use of ARM for high performance computing. Now what exactly has fueled this? ARM low cost of ownership because ARM consume lesser power, the overall cost of ownership over period of time is lesser. So that has been one of the key driving factor. Also ARM processors are pretty powerful. They can provide better or on-pun or par performance and not only that they comes with more cores. That means you will get more computing powers and that could be used to get better throughput out of the same cost. Not only on the hardware front, on software front too, there has been quite good development. Most all OS provider now releases their port for ARM. Most of the software has already been ported to ARM. Open source databases, most of the popular open source database including MySQL has been ported to ARM. And importantly there is a growing user and developer base. Lot of new generation developers who have been working on ARM maybe through Raspberry Pi and RenoKit something of that sort are already used to the ARM ecosystem. So they are every year the developer base is growing and they are joining and that is also fueling the complete ARM ecosystem. But also another important incident that happened in last couple of years has been easy availability of ARM instances through cloud providers and even now with desktop machines. So Huawei offers ARM instances using its Koen Peng processor, Amazon does it through Gavioton or Ekl plans to offer it too. We are already aware Apple have released M1 which is powering their Macbooks. There is a news that Microsoft is also working on some kind of ARM chips. So overall everyone knows that the ARM has been gaining a lot of traction and all vendors are trying to port their respective software to ARM. And even on the hardware front it is getting stronger and stronger and the ecosystem in general is growing. So with that quick note about ARM ecosystem now let's talk about state of my SQL ecosystem from user perspective. So whenever a user think of moving from one system to other system user would like to consider these four parameters being an open source community support also makes sense. But general things are like whether the new system will have features at complete. What about the performance? What about the ecosystem? So let's try to evaluate my SQL from using these four parameters. Now on feature set front Oracle has been releasing packages for my SQL starting with 8.x and they are very well tested. They are marked GA. So we can be rest assured that they are coming with the same quality directly from the upstream Oracle. Also we haven't found any feature or sub feature that has been marked beta or experimental. So all the features that you get on the other platforms are available on ARM. Extended features like binlog replication or group replications are also available with the same quality and basically they are also supported. So user can say that the complete feature set is you know my SQL feature set is complete on ARM. If you talk about performance so my SQL on ARM scales well on ARM. So of course we are going to look at some of the graphs for it. But the good part is that you know my SQL has been there are a lot of still good number of patches that are waiting to get accepted. And once they get accepted the whatever already it is scaling better then it will start scaling further you know better than what it current levels are. My SQL has been already also working on more cores or kind of developing the new features they are developing they are trying to tune it so that they are compatible with more number of cores. So that is also helping. On ecosystem front as we all understand database is not a standalone component. There are a lot of other supporting component maybe it's a backup load balancers. So we can say that a full stack ecosystem can we boot a full stack ecosystem using open source component on ARM and yes we can. Almost all the at least one or two components from each category have been available on ARM. Most of them they are available directly from the upstream repo and this list of the tools is just growing more and more vendors are finding it important to start porting their software to ARM. If you talk about community support there is a pretty active community more than 30 plus patches has been contributed. There are developers from all over organizations are different all over world from different organizations. There is also a MySQL on ARM slack dedicated channel on the MySQL community slack where you could of course ask questions and you know if you have any queries to be shared that you know community would of course would be happy to reply back to you. So let's understand the ecosystem thing right we just talked about. So what we try doing is like we try selecting only open source products and we try to see if they are available on ARM and can we actually mark the complete ecosystem complete full stack complete. So of course the server is directly coming from Oracle now Binlog replication or group replication are the inherent part of the server so Hs solution is taken care of backup we can use Percona extra backup with community value added it works on ARM. Load balancer front proxy SQL and MySQL router both have official packages from their respective repo for ARM. PMM again community evaluated and Percona engineer evaluated it also works on ARM. Connector again connectors have official packages directly from Oracle repository. DB tool front shell MySQL shell again has official packages Percona toolkits most of them are Perl script they just work out of box community have already tested them. So overall if you look at if user wants to boot up a full stack on ARM then it is pretty much possible because from every category there are at least one or two tools available and which are available work or available on ARM and this list is just keep on growing the feature was very different one year back now we have more components and a lot of more components are getting added to each and every category and if you care about running things on you know Kubernetes orchestration kind of thing then of course Kubernetes and Docker swarm are also available on ARM. If you talk about performance sorry yeah if you talk about a performance then you know ARM is a different processor so if you want to compare ARM with its counterpart then you know there are two different models in which you can compare so one is the model where we continue to keep cost constant and all other parameter constant and allow the compute power to differ of course ARM being cheaper ARM get more compute power almost 2.5x 2.5x more compute power and as you could see with this extra compute power ARM is able to scale well you know against its counterpart and in fact in some cases the difference is almost 2x and with higher scalability once the when the scalability point is crossed in few cases ARM just continue to scale and maintain the lead with significant margin. Now that's with that was with uniform this is with ZPN which means there is a more contention and as you could see again even with more contention use case ARM is able to scale very well and all these point the cost is constant so for the same cost a user is getting more TPS or more work done. If you go back to the traditional model where we used to have you know let the cost remain different of course we know ARM is cheaper but let's keep give the same resources including the same compute power to both of the you know machines right so in this case too some may get surprised that you know ARM is powerful enough and it is able to beat its counterpart you know with higher scalability as you could see for in fact for some cases it has there is a significant margin and other cases too it is either on par and once the scalability point is crossed it is able to consistently beat the things. In rewrite case for higher scalability ARM may lag but as we said there are a lot of good patches that has been contributed by community and that gap we tried experimenting with it and one single patch itself reduces that gap by 50% and there is already a small gap and that further get reduced by just one single patch. So there is a huge potential like you know even with the same compute power ARM is able to beat its counterpart and that means you are getting all this or same performance or better performance for almost 50% lesser cost or in fact more but 50% on an average lesser cost. So that was with uniform and again this is with Zephian that is with more contention and here too we could see the same pattern that ARM is able to consistently beat its counterpart. So with that quick intro I am sure user probably now feel that you know might have got at least an idea that what is the state of MySQL on ARM and what advantage they could expect by moving to ARM. But then as a developer we could further push it and let's understand what are the some of the things which we can look for. See ARM is a different architecture so whenever you want to port or even your tune I would say any software for ARM you need to consider some of these key changes that you can concentrate on. For example ARM is a weaker memory model so you should use proper optimal memory barriers. Low level hardware instructions would be different if your software is using them. Cache line differences are there, optimized atomics which was introduced in ARM 8.1 standards, hardware parallelism through neon instruction sets, more cores, more new oneodes. We are going to see some example about it. Branching differences are there, 64 bit optimizations. Most of the time using 64 bit registers some optimization loop is done but that may be enabled only for let's say x86 processors. So you can also add to the list name of the ARM processors. ARM can also get advantage out of it. Low level construct like timer, spin loops, all these things could be tuned for ARM. So overall any software that needs to get tuned for ARM has to address all these aspects and MySQL is no exception so lot of patches are present on these category. We will see what could be done further. Global counter and state variable. We all understand global counters are pretty important for any software including MySQL. The problem with the global counter is with increasing number of threads the contention increases because all threads need to access the same variable and also that creates another problem of cache line sharing and this drastically affect the performance. So MySQL switched back to use distributed counters. So the idea is very simple that a counter is distributed into end parts and the aggregate value of all these end parts make a value of the counter. This was a good idea. It helped reduce contention but MySQL uses random number generation to select a part. So maybe core one may at point in time you update its counter P3 and some point in time it may get update it may update P5 and sometime it may update P7. Why because it is random number generated. So this actually helps reduce the contention but may cause cache line sharing issues to help and may also result in cross-neuma moment. We will see about it in next slide. But the possible solution would be having a kind of a static mapping. So for example core one always access or updates P1, core 2 updates P2, core 3 updates P3 that way. Now we could use something like schedule get CPU which will help us give this static mapping. Now the only thing to watch out is that latency of schedule get CPU shouldn't you know override the latency of moving or cross-neuma moment. Now to stretch this further one other problem you would discover is that if core one actually allocates all this counter then the counter memory will be actually allocated only on new manode which is closer to core one or attached to core one. But since the part of the counter like P5, P6, P7 will be updated by the cores which are located on say other new manode that doesn't make sense for them to always you know incur the cross-neuma latency. So what if we could actually allocate the parts in distributed fashion and keep them closer to the respective new manodes or respective node cores where attached new manodes. So this is possible by using new man memory allocation. The problem with new man memory allocation is that it is always aligned to the page size. So even if you ask for let's say core one wants to allocate in its own new might as for 256 byte it will get 4k 16k or 64k based on whatever is the page size. So that is a problem. So what could how we could solve it we could have a central allocator because there are so many distributed counters. So every counter can request central allocator. Counter allocator can interleave the memory across multiple new manodes and give slots that could be then used by respective cores and those slots will be closer or be attached to the respective core new manodes. So that would help in this thing to reduce the cross-neuma latency. That is one of the idea. Now not all of the global variables needs to be distributed. So you could actually have a normal variable which is just getting incremented but you should use a proper memory barrier. This is just a quick experiment we did and as you could see the difference is almost 10 to 15 percent with higher scalability if you use proper memory barrier versus the default memory barrier. Same thing with state variable. State variable holder discrete value and the traditional way of mutex lock and update and unlock may not be a good idea. So we could always switch back to use the norm atomics and as we could see through a micro benchmarking thing we did the difference or gain you could see is roughly around 50 percent or so. So this is the magnitude of gain that you could observe by switching from the traditional mutex to the update atomics related things. Now more new manodes. So one aspect. So let's understand this from an experiment. So what we did is we started our MySQL server using 28 vCPU and we executed a workload which we were using sysbench with 1 0 2 4 threads. So we got some XTPS. Now when we increase the vCPUs from 28 to 56 we doubled it. We of course saw reduction in the contention and we of the performance or the TPS went up. When we increase it further from 56 to 112 we saw the degradation in performance. Now that was bit surprised right because we have given more number of vCPUs and still we are instead of seeing a improvement we are seeing a pretty decent degradation almost by 40 to 50 percent. So here the problem is the distance between the two new manode. For example distance between the node 1 and node 2 is 16 units because they are located on the same socket but distance between node 1 and node 4 is 33 units because new manode 1 and new manode 4 are located on different socket. So this socket differences, new man differences, new man distancing play a very critical role while you know you start running software with more number of cores. So what we expected is since we gave it a more number of computing power we expected lesser contention and better throughput. But what we found out is exactly opposite. So cache line sharing, data relocation, weight graph all these things took over and they have disturbed the equation. We did some quick per profiling and what we found out is that of course with one from one new manode to two new manode you could see there is a reduction in the contention but moment we move from 2 to 4 new manode there is increase in the contention almost double the contention. So what we have been fixing traditionally or talking about it is scalability bottleneck getting more from same number of cores. Now we should talk about new man bottleneck getting more with more number of cores but these cores are distributed across new man. So how do how can we get more performance from with increasing number of cores but distributed across new man. Configuration based setting this is an interesting thing you may be aware of spin loop inside in ODB. Now it is tuned probably based on whenever it was created it was tuned for x86 kind of processors. Now with ARM processor this tuning may not hold exactly so you probably will have to figure out what is the best possible value for your ARM processor. Now for the ARM processor I had access to what I figured out is that the performance get optimal when the value is 24 and I see a 6% improvement versus default. But I tuned it for my ARM processor let's say you but there are so many vendors who come up with their ARM processors and there is a common set of guidelines but the implementation of low level things could be different. So it is not that for every ARM processor the 24 value will work as the optimal value. So how do you actually make sure that you know if you can make this code completely resilent to the underlining architecture that would be the best way. Now till x86 it was still fine because you know it was coming from one or two vendors only but with ARM there are so many vendors everyone would have their own way or protocol of implementing some things and maybe if it worked 24 was optimal for me maybe it will be something else for some other ARM processor. Workload distribution we all know InnoDB actually allocates his memory in interlude fashion when it goes to multiple NUMANodes. So if you have 100 GB of memory and 4 NUMANode each NUMANode will have 25 GB of memory. Now that is so good but what about the system and user thread are they also allocated or binded in the same way unfortunately no. So we leave it to OS scheduler to do it. Now sometime OS scheduler may do a better job but sometime OS scheduler may create a skewed distribution. This is an example if you have more than 64 thread I have 4 NUMANodes as you could see I was able to get a pretty you know uniform distribution but when I moved to 8 threads most of the threads got allocated to only NUMANode 2 in this case and other NUMANodes were free. So this create a skewed distribution. So this is an idea to explore if we go ahead and have a uniform distribution even for the smaller number of threads and especially even for system threads can we go ahead and get a better performance and a more stable performance especially if we can do some kind of soft binding to the user and system threads so they don't leave their NUMANode space and if that helps improve performance. Now INODB has buffer pool instances up to 64 right so if there are NUMANode which if there are threads that are flushing from a flush list then they will be localized to the said NUMANode they will not have to make a cross-trip to a page which is located on other NUMANode. Every way purged locality could be explored and all these things will also benefit the parallel query module so you know MySQL have already enabled parallel query module which I think is getting evolved further so you know parallel query module will be also get benefit out of all these even workload distribution. So there are few of the things which we talked about but beyond that also community has contributed a lot of other things for example checksum optimizations, weak memory model, timer spin loop optimizations, more NUMANode 64-bit optimization branching so overall there have been good number of patches that has been contributed and over a period of time when these get accepted you could see that already MySQL on ARM is scaling well it will further cross its own limit and you know scale way better than its counterparts. So with that you know overview from development perspective let's talk about community. So you know the community is pretty widespread we have seen developers from different organization contributing all over the world there is a dedicated MySQL on ARM channel if you have any ideas or you would like to discuss anything you can always you know put it there community will surely respond back to you and this is more like community wish list for MySQL or Ackle so if they could start improving the distro support which is currently limited to CentOS and REHL and with CentOS going phasing out you know having more distro support will surely help bit more efforts on evaluating patches from community some of them patches are 4 plus year old across the board testing which has started gaining pace lot of new patches are being tested on different you know across ARM or on ARM also and looking beyond the mainline path covering other things like whether it's Perf schema or clustering or tools like connectors and so on making those things also optimal for ARM would surely help. So with that we will now open the session for Q&A before that thanks organizers and sponsor for making this possible and providing opportunity to share the ideas and of course you can remain connected we have a blog where we normally write about the new things that is happening and of course you can join the Slack channel and follow the tweet. Thank you.
 Welcome all. Today we are going to talk about running MySQL on ARM. So we will see why it  is advantageous to run MySQL on ARM and what more as a developer we could do to make it  further optimized for ARM. With that quick note, let's get started. My name is Krunal  Bhouskar. I have been driving this MySQL on ARM initiative. That's quick intro about me.  I have been working in MySQL space for more than a decade now. In past I have worked with  Perkona, Oracle, Yahoo Labs, KickFile Terraria data. Currently I am working with Huawei as  part of the open source DB group where we are trying to make all the open source database  not only the database but also the complete ecosystem optimal for ARM. So that quick intro  about me, let's jump to our today's agenda. So we will first talk about ARM ecosystem  to get everyone on the same page and then we will talk about the state of MySQL ecosystem  from user perspective and then developer perspective. We will end it back with the community and  what community can contribute to. I am sure all of us have heard about ARM processors.  They are widely used in all kind of applications. Very primarily they are used in mobile phones  and network equipment. They are also used in lot of smart home appliances and IoT devices.  They are also growing use of ARM processors in smart driving cars and of course with the  space programs. What is something new and has been catching lot of traction lately is  use of ARM for high performance computing. Now what exactly has fueled this? ARM low  cost of ownership because ARM consume lesser power, the overall cost of ownership over  period of time is lesser. So that has been one of the key driving factor. Also ARM processors  are pretty powerful. They can provide better or on-pun or par performance and not only  that they comes with more cores. That means you will get more computing powers and that  could be used to get better throughput out of the same cost.  Not only on the hardware front, on software front too, there has been quite good development.  Most all OS provider now releases their port for ARM. Most of the software has already  been ported to ARM. Open source databases, most of the popular open source database  including MySQL has been ported to ARM. And importantly there is a growing user and developer  base. Lot of new generation developers who have been working on ARM maybe through Raspberry  Pi and RenoKit something of that sort are already used to the ARM ecosystem. So they  are every year the developer base is growing and they are joining and that is also fueling  the complete ARM ecosystem. But also another important incident that happened in last couple  of years has been easy availability of ARM instances through cloud providers and even  now with desktop machines. So Huawei offers ARM instances using its  Koen Peng processor, Amazon does it through Gavioton or Ekl plans to offer it too. We  are already aware Apple have released M1 which is powering their Macbooks. There is  a news that Microsoft is also working on some kind of ARM chips. So overall everyone knows  that the ARM has been gaining a lot of traction and all vendors are trying to port their respective  software to ARM. And even on the hardware front it is getting stronger and stronger  and the ecosystem in general is growing. So with that quick note about ARM ecosystem  now let's talk about state of my SQL ecosystem from user perspective. So whenever a user  think of moving from one system to other system user would like to consider these four parameters  being an open source community support also makes sense. But general things are like whether  the new system will have features at complete. What about the performance? What about the  ecosystem? So let's try to evaluate my SQL from using these four parameters. Now on feature  set front Oracle has been releasing packages for my SQL starting with 8.x and they are  very well tested. They are marked GA. So we can be rest assured that they are coming with  the same quality directly from the upstream Oracle. Also we haven't found any feature  or sub feature that has been marked beta or experimental. So all the features that you  get on the other platforms are available on ARM. Extended features like binlog replication  or group replications are also available with the same quality and basically they are also  supported. So user can say that the complete feature set is you know my SQL feature set  is complete on ARM. If you talk about performance so my SQL on ARM scales well on ARM. So of  course we are going to look at some of the graphs for it. But the good part is that you  know my SQL has been there are a lot of still good number of patches that are waiting to  get accepted. And once they get accepted the whatever already it is scaling better then  it will start scaling further you know better than what it current levels are. My SQL has  been already also working on more cores or kind of developing the new features they are  developing they are trying to tune it so that they are compatible with more number of cores.  So that is also helping. On ecosystem front as we all understand database is not a standalone  component. There are a lot of other supporting component maybe it's a backup load balancers.  So we can say that a full stack ecosystem can we boot a full stack ecosystem using open  source component on ARM and yes we can. Almost all the at least one or two components from  each category have been available on ARM. Most of them they are available directly from  the upstream repo and this list of the tools is just growing more and more vendors are  finding it important to start porting their software to ARM. If you talk about community  support there is a pretty active community more than 30 plus patches has been contributed.  There are developers from all over organizations are different all over world from different  organizations. There is also a MySQL on ARM slack dedicated channel on the MySQL community  slack where you could of course ask questions and you know if you have any queries to be  shared that you know community would of course would be happy to reply back to you.  So let's understand the ecosystem thing right we just talked about. So what we try doing  is like we try selecting only open source products and we try to see if they are available  on ARM and can we actually mark the complete ecosystem complete full stack complete. So  of course the server is directly coming from Oracle now Binlog replication or group replication  are the inherent part of the server so Hs solution is taken care of backup we can use  Percona extra backup with community value added it works on ARM. Load balancer front  proxy SQL and MySQL router both have official packages from their respective repo for ARM.  PMM again community evaluated and Percona engineer evaluated it also works on ARM. Connector  again connectors have official packages directly from Oracle repository. DB tool front shell  MySQL shell again has official packages Percona toolkits most of them are Perl script they  just work out of box community have already tested them. So overall if you look at if  user wants to boot up a full stack on ARM then it is pretty much possible because from  every category there are at least one or two tools available and which are available work  or available on ARM and this list is just keep on growing the feature was very different  one year back now we have more components and a lot of more components are getting added  to each and every category and if you care about running things on you know Kubernetes  orchestration kind of thing then of course Kubernetes and Docker swarm are also available  on ARM. If you talk about performance sorry yeah if you talk about a performance then  you know ARM is a different processor so if you want to compare ARM with its counterpart  then you know there are two different models in which you can compare so one is the model  where we continue to keep cost constant and all other parameter constant and allow the  compute power to differ of course ARM being cheaper ARM get more compute power almost  2.5x 2.5x more compute power and as you could see with this extra compute power ARM is able  to scale well you know against its counterpart and in fact in some cases the difference is  almost 2x and with higher scalability once the when the scalability point is crossed  in few cases ARM just continue to scale and maintain the lead with significant margin.  Now that's with that was with uniform this is with ZPN which means there is a more contention  and as you could see again even with more contention use case ARM is able to scale very  well and all these point the cost is constant so for the same cost a user is getting more  TPS or more work done.  If you go back to the traditional model where we used to have you know let the cost remain  different of course we know ARM is cheaper but let's keep give the same resources including  the same compute power to both of the you know machines right so in this case too some  may get surprised that you know ARM is powerful enough and it is able to beat its counterpart  you know with higher scalability as you could see for in fact for some cases it has there  is a significant margin and other cases too it is either on par and once the scalability  point is crossed it is able to consistently beat the things.  In rewrite case for higher scalability ARM may lag but as we said there are a lot of  good patches that has been contributed by community and that gap we tried experimenting  with it and one single patch itself reduces that gap by 50% and there is already a small  gap and that further get reduced by just one single patch.  So there is a huge potential like you know even with the same compute power ARM is able  to beat its counterpart and that means you are getting all this or same performance or  better performance for almost 50% lesser cost or in fact more but 50% on an average lesser  cost.  So that was with uniform and again this is with Zephian that is with more contention  and here too we could see the same pattern that ARM is able to consistently beat its  counterpart.  So with that quick intro I am sure user probably now feel that you know might have got at least  an idea that what is the state of MySQL on ARM and what advantage they could expect by  moving to ARM.  But then as a developer we could further push it and let's understand what are the some  of the things which we can look for.  See ARM is a different architecture so whenever you want to port or even your tune I would  say any software for ARM you need to consider some of these key changes that you can concentrate  on.  For example ARM is a weaker memory model so you should use proper optimal memory barriers.  Low level hardware instructions would be different if your software is using them.  Cache line differences are there, optimized atomics which was introduced in ARM 8.1 standards,  hardware parallelism through neon instruction sets, more cores, more new oneodes.  We are going to see some example about it.  Branching differences are there, 64 bit optimizations.  Most of the time using 64 bit registers some optimization loop is done but that may be enabled  only for let's say x86 processors.  So you can also add to the list name of the ARM processors.  ARM can also get advantage out of it.  Low level construct like timer, spin loops, all these things could be tuned for ARM.  So overall any software that needs to get tuned for ARM has to address all these aspects  and MySQL is no exception so lot of patches are present on these category.  We will see what could be done further.  Global counter and state variable.  We all understand global counters are pretty important for any software including MySQL.  The problem with the global counter is with increasing number of threads the contention  increases because all threads need to access the same variable and also that creates another  problem of cache line sharing and this drastically affect the performance.  So MySQL switched back to use distributed counters.  So the idea is very simple that a counter is distributed into end parts and the aggregate  value of all these end parts make a value of the counter.  This was a good idea.  It helped reduce contention but MySQL uses random number generation to select a part.  So maybe core one may at point in time you update its counter P3 and some point in time  it may get update it may update P5 and sometime it may update P7.  Why because it is random number generated.  So this actually helps reduce the contention but may cause cache line sharing issues to  help and may also result in cross-neuma moment.  We will see about it in next slide.  But the possible solution would be having a kind of a static mapping.  So for example core one always access or updates P1, core 2 updates P2, core 3 updates P3 that  way.  Now we could use something like schedule get CPU which will help us give this static mapping.  Now the only thing to watch out is that latency of schedule get CPU shouldn't you know override  the latency of moving or cross-neuma moment.  Now to stretch this further one other problem you would discover is that if core one actually  allocates all this counter then the counter memory will be actually allocated only on  new manode which is closer to core one or attached to core one.  But since the part of the counter like P5, P6, P7 will be updated by the cores which  are located on say other new manode that doesn't make sense for them to always you know incur  the cross-neuma latency.  So what if we could actually allocate the parts in distributed fashion and keep them  closer to the respective new manodes or respective node cores where attached new manodes.  So this is possible by using new man memory allocation.  The problem with new man memory allocation is that it is always aligned to the page size.  So even if you ask for let's say core one wants to allocate in its own new might as  for 256 byte it will get 4k 16k or 64k based on whatever is the page size.  So that is a problem.  So what could how we could solve it we could have a central allocator because there are  so many distributed counters.  So every counter can request central allocator.  Counter allocator can interleave the memory across multiple new manodes and give slots  that could be then used by respective cores and those slots will be closer or be attached  to the respective core new manodes.  So that would help in this thing to reduce the cross-neuma latency.  That is one of the idea.  Now not all of the global variables needs to be distributed.  So you could actually have a normal variable which is just getting incremented but you  should use a proper memory barrier.  This is just a quick experiment we did and as you could see the difference is almost  10 to 15 percent with higher scalability if you use proper memory barrier versus the default  memory barrier.  Same thing with state variable.  State variable holder discrete value and the traditional way of mutex lock and update  and unlock may not be a good idea.  So we could always switch back to use the norm atomics and as we could see through a micro  benchmarking thing we did the difference or gain you could see is roughly around 50 percent  or so.  So this is the magnitude of gain that you could observe by switching from the traditional  mutex to the update atomics related things.  Now more new manodes.  So one aspect.  So let's understand this from an experiment.  So what we did is we started our MySQL server using 28 vCPU and we executed a workload which  we were using sysbench with 1 0 2 4 threads.  So we got some XTPS.  Now when we increase the vCPUs from 28 to 56 we doubled it.  We of course saw reduction in the contention and we of the performance or the TPS went  up.  When we increase it further from 56 to 112 we saw the degradation in performance.  Now that was bit surprised right because we have given more number of vCPUs and still  we are instead of seeing a improvement we are seeing a pretty decent degradation almost  by 40 to 50 percent.  So here the problem is the distance between the two new manode.  For example distance between the node 1 and node 2 is 16 units because they are located  on the same socket but distance between node 1 and node 4 is 33 units because new manode  1 and new manode 4 are located on different socket.  So this socket differences, new man differences, new man distancing play a very critical role  while you know you start running software with more number of cores.  So what we expected is since we gave it a more number of computing power we expected  lesser contention and better throughput.  But what we found out is exactly opposite.  So cache line sharing, data relocation, weight graph all these things took over and they  have disturbed the equation.  We did some quick per profiling and what we found out is that of course with one from  one new manode to two new manode you could see there is a reduction in the contention  but moment we move from 2 to 4 new manode there is increase in the contention almost  double the contention.  So what we have been fixing traditionally or talking about it is scalability bottleneck  getting more from same number of cores.  Now we should talk about new man bottleneck getting more with more number of cores but  these cores are distributed across new man.  So how do how can we get more performance from with increasing number of cores but distributed  across new man.  Configuration based setting this is an interesting thing you may be aware of spin loop inside  in ODB.  Now it is tuned probably based on whenever it was created it was tuned for x86 kind of  processors.  Now with ARM processor this tuning may not hold exactly so you probably will have to  figure out what is the best possible value for your ARM processor.  Now for the ARM processor I had access to what I figured out is that the performance  get optimal when the value is 24 and I see a 6% improvement versus default.  But I tuned it for my ARM processor let's say you but there are so many vendors who  come up with their ARM processors and there is a common set of guidelines but the implementation  of low level things could be different.  So it is not that for every ARM processor the 24 value will work as the optimal value.  So how do you actually make sure that you know if you can make this code completely  resilent to the underlining architecture that would be the best way.  Now till x86 it was still fine because you know it was coming from one or two vendors  only but with ARM there are so many vendors everyone would have their own way or protocol  of implementing some things and maybe if it worked 24 was optimal for me maybe it will  be something else for some other ARM processor.  Workload distribution we all know InnoDB actually allocates his memory in interlude fashion  when it goes to multiple NUMANodes.  So if you have 100 GB of memory and 4 NUMANode each NUMANode will have 25 GB of memory.  Now that is so good but what about the system and user thread are they also allocated or  binded in the same way unfortunately no.  So we leave it to OS scheduler to do it.  Now sometime OS scheduler may do a better job but sometime OS scheduler may create a skewed  distribution.  This is an example if you have more than 64 thread I have 4 NUMANodes as you could see  I was able to get a pretty you know uniform distribution but when I moved to 8 threads  most of the threads got allocated to only NUMANode 2 in this case and other NUMANodes  were free.  So this create a skewed distribution.  So this is an idea to explore if we go ahead and have a uniform distribution even for  the smaller number of threads and especially even for system threads can we go ahead and  get a better performance and a more stable performance especially if we can do some kind  of soft binding to the user and system threads so they don't leave their NUMANode space and  if that helps improve performance.  Now INODB has buffer pool instances up to 64 right so if there are NUMANode which if  there are threads that are flushing from a flush list then they will be localized to  the said NUMANode they will not have to make a cross-trip to a page which is located on  other NUMANode.  Every way purged locality could be explored and all these things will also benefit the  parallel query module so you know MySQL have already enabled parallel query module which  I think is getting evolved further so you know parallel query module will be also get  benefit out of all these even workload distribution.  So there are few of the things which we talked about but beyond that also community has contributed  a lot of other things for example checksum optimizations, weak memory model, timer spin  loop optimizations, more NUMANode 64-bit optimization branching so overall there have been good  number of patches that has been contributed and over a period of time when these get accepted  you could see that already MySQL on ARM is scaling well it will further cross its own  limit and you know scale way better than its counterparts.  So with that you know overview from development perspective let's talk about community.  So you know the community is pretty widespread we have seen developers from different organization  contributing all over the world there is a dedicated MySQL on ARM channel if you have  any ideas or you would like to discuss anything you can always you know put it there community  will surely respond back to you and this is more like community wish list for MySQL or  Ackle so if they could start improving the distro support which is currently limited  to CentOS and REHL and with CentOS going phasing out you know having more distro support will  surely help bit more efforts on evaluating patches from community some of them patches  are 4 plus year old across the board testing which has started gaining pace lot of new  patches are being tested on different you know across ARM or on ARM also and looking beyond  the mainline path covering other things like whether it's Perf schema or clustering or tools  like connectors and so on making those things also optimal for ARM would surely help.  So with that we will now open the session for Q&A before that thanks organizers and  sponsor for making this possible and providing opportunity to share the ideas and of course  you can remain connected we have a blog where we normally write about the new things that  is happening and of course you can join the Slack channel and follow the tweet.  Thank you.
mysql_arm_0_179.png mysql_arm_180_755.png mysql_arm_756_1355.png mysql_arm_1356_1847.png mysql_arm_1848_5651.png mysql_arm_5652_5759.png mysql_arm_5760_6203.png mysql_arm_6204_10127.png mysql_arm_10128_12611.png mysql_arm_12612_16847.png mysql_arm_16848_17135.png mysql_arm_17136_17363.png mysql_arm_17364_19187.png mysql_arm_19188_19247.png mysql_arm_19248_19799.png mysql_arm_19800_24059.png mysql_arm_24060_25463.png mysql_arm_25464_25535.png mysql_arm_25536_27443.png mysql_arm_27444_28823.png mysql_arm_28824_30863.png mysql_arm_30864_31631.png mysql_arm_31632_32399.png mysql_arm_32400_33695.png mysql_arm_33696_33815.png mysql_arm_33816_33971.png mysql_arm_33972_34511.png mysql_arm_34512_34667.png mysql_arm_34668_35171.png mysql_arm_35172_36311.png mysql_arm_36312_36983.png mysql_arm_36984_37031.png mysql_arm_37032_37404.png