 Hemant Nagy from Oracle MySQL, and he will be talking about the MySQL group replication. So here you go. Hello, everyone. My name is Hemant Nagy, and I work for MySQL group replication team, and today's topic for our presentation is the state of art on MySQL group replication. In this topic, I would be basically telling you the basics of group replication, and mostly would be focusing on new 8.0 features, which we have introduced recently. This safe harbor statement, like whatever I am presenting here, don't make your decision on purchase based on my presentation. This is just for information. As I told you, my program agenda, like basics of what we have currently in 5.7 in group replication, I would be telling you that, and the main focus would be on 8.0 features, which we have introduced in 8.0. What is MySQL group replication? It's a single multiple primary update everywhere application plugin for MySQL. With this, you can have an automated distributed recovery, you can have a conflict detection and group membership. MySQL group replication, this plugin is like what for the development side, we actually divide this plugin into two parts. One is MySQL group replication, the other part we call as a MySQL GCS, group replication communication system. The GCS component is this XCOM, so XCOM is the tool which actually maintain this communication between different members of the group. This is a Paxos based on Paxos, and this tool main functionality is order delivery, dynamic membership, and failure detection. Like if you go in this whole group replication plugin, this XCOM communicates with the group. This group replication plugin actually is the interface between this XCOM GCS and the rest of MySQL where we capture incoming data and give it to XCOM and XCOM broadcast it to all other member. I will go in detail like how exactly group replication work and later on slide. Basically group replication needs a few things. Like if you are using a group replication, you need to have a primary key because it has to distinguish. If two similar keys are added at the same time on different members, so how if they are same then there wouldn't be a collision. So for that it needs a unique primary key and InnoDB. InnoDB is a must for group replication. Group replication can be configured in different modes. By default, we have a single primary mode. Single primary mode is similar to a master slave asynchronous replication. So we have a primary, we will have a multiple secondaries. Primary is a master which we terminology which we use in asynchronous replication and slaves are second which we call here in group replication. So by default, if you start a GR, GR is configured in a single primary mode and with the GR, it's automatic conflict detection, automatic failure handling. So if any node goes down, if primary goes down, primary is the node where you will be writing the data and secondary you would be reading them. Secondary are readable and primary is write and read both. So if primary goes down, so we have to have a new primary. In asynchronous replication, we do a slave promotion. Here, this is automatic. Whenever primary fails, primary memory election starts on every individual node independently of other nodes, and it will choose a new primary and you would be writing on this primary and others would be read only. This primary would be changed to read and write. The other mode we have is a multi primary mode. This is all members would be primary. You can enable it using group probably in single primary mode. This is a system variable to make it off and it would be enabled. So you can write all members would be read and write in a read and write mode. You can write on any members. But if two similar statement has been executed, the first whoever would be reached would get committed and the other would get rolled back. So example where A is a primary key here, so two different statement, like they won't call idea. So they get executed and both would get committed. If we execute two statement which has A is equal to A1, A is equal to A1 as a primary key, they are similar statement and they can collide. So whoever they executed at the same time on different members. So whoever is first would win and the other would get rolled back. This only one would be first because of the order delivery, which I told you happen because of the XCOM and Paxos. How group application exactly works here? We have a group of five members. We have clients, three clients. One client is inserting one, one client is other client is inserting two here, and it reaches the MySQL server. So before it gets committed, like if you are writing a transaction, you write commit colon. So before it actually gets committed in the server, this writesets and GTID executed is broadcasted. First writeset is generated. Writeset is actually a hash value of primary key, foreign key, and table name, and other data. So that writeset, GTID executed, and data is broadcasted to all the members. On the receiver node, it is first certified. What does certification means? It is when you are getting the data, it is actually maintaining a certification DB. Certification DB contains this writeset, and it would be verifying that if this insert, this unique, this primary key, is not there from other member also. GTID executed makes sure that this statement is executed is the gap between this statement and other statement. So they are in continuous. So this would make sure, this would on the receiver side if it's certified, and if certification is successful, then it gets committed. If certification is unsuccessful, then the one like that would be rolled back. This happened individually on every node. There is no communication like here when this certification is happening, there won't be acknowledgement to other member, that this has to be rolled back. To make it fast, they work independently. They just maintain the state with the GTID executed. So every member is having the same GTID executed. So they are in the same state, and you don't have to acknowledge every statement from the other member to make it fast. Now what we have in like 5.7 already, we have in group replication features which we have in 5.7, I will tell you in brief about that. Automated distributor server recovery, whenever new member joins, all the data which that new member doesn't have, it's replicated and it's brought to the same state to which other members are in. So that happens automatically. You don't have to set up any kind of replication, a simple replication, you don't have to transfer any data here. That happens automatically and that is handled by group replication. Full GTID support, from the start, it's based on GTID only without GTID to make keep the state we use GTID. So it's completely suppose GTID. Auto increment configuration handling, if you want inserting data and you don't want to see that many collisions, and you want auto increment key. So you can set up auto increment key where, like suppose if you have three members, then you can put server ID as the start of the auto increment key and offset as is default as seven. It can be changed with this group revision auto increment increment. So if you have three servers, so server ID, you have first server, you have again server ID one, second two, and third server, server ID three. So the inserts would be like this one, on the first server one, eight, 15, on the second server, second, 16 plus seven, on the third server three, nine, and plus seven. So there won't be any collision between these inserts. Plug-in version access control, we are maintaining on this group replication, we are maintaining that there is, like when a group has a lower member version, like 5.7 and a new member joins with the 8.0. So if this new member joins with the 8.0, it would be set up in a read-only mode. It would be set up in a read-only mode. So that new changes which we have brought in 8.0, which are not compatible with the older version doesn't affect the working of group. And when we have 8.0 group, then a 5.7 member is not allowed to join. Because it, like the older nodes, newer nodes would be writable and those changes can't be, won't be compatible with the older version. So this is inbuilt in group replication. We have a read-only mode. Whenever a member leaves a group, read-only mode is automatically enabled. So that when a new data, by mistake of a user is writing new data on that node, and if it tries to rejoin later on, that data would mismatch with the rest of the group. We keep a state with the GTID executed, so this would create issues. So we keep the member which, we make the member which leaves as a read-only mode. So this kind of mistake doesn't happen. Full-stake secure connection. In read-only mode, we actually put all the secondary members in the single primary mode. We make them also as a read-only. Like user doesn't have to do, they are enabled as a read-only by group replication plugin only. Full-stake secure connection, like this is group replication, like supports secure connection along the complete stack. Like group replication from the start, has this enable this secure connection. And you can, like in the Cloud, it can have a issue that okay, if a host tries to join a group which you doesn't want. So you can whitelist IPs that only are allowed to join the group to make it secure. Parallel appliers support, if you have seen that Venkatesh presentation right-set based, which makes this applier faster, and in parallel can be applied. So that right-set-based applier is there in MySQL group application from the start. From the 5.7, it is always there. In replication, they would be enabling it soon. Now in 8.0, we have enabled, now in 8.0, we have enabled, we have put a lot of new features based on community request, and most of the features were added to make the group replication stable and secure. One of the feature was primary election before this, who will be selected as a primary was based on the UUID. In 8.0, we added this weightage. So you can put weight on different members. If you have a stronger server, you can assign it a higher weight that would be selected as a primary. If that goes away, the member with lesser weight than that, would be selected as a primary, would get selected as a new primary, and so on. How it actually works if you look at the MySQL queries, like there are three members in the group, one is a primary. If you, like I am setting for this member A, group replication member weight is equal to 0 and assigning this member C as 100. For B, its value is 90. We shut down this server, MySQL A, we shut down. Then we run this query. We would see guest chosen as primary now. All the information about the group, member weight, versions, if it's online, offline, it's always present, like it's always there on all other members. You don't have to go and check individual member that whether it's online or not. You can query any of the performance. You can query performance schema, this replication group members, and you will know status of other members also. Caring about data. What I told you, in 8.0, we added a lot of features which related to making the members stable and secure. So, like this A member, A is joining here. Like it's in a single primary mode, A joins, A becomes a secondary. It's automatically enabled as a read-only member. If it leaves or it's kept as a read-only member, so that it can join and it doesn't have unnecessary data. If you look at the MySQL queries, this member is a primary. You check super-read-only mode. Super-read-only mode is zero here. You stop group replication, the member becomes offline, but still super-read-only mode becomes one here. It leaves, super-read-only mode is enabled. A synchronous replication, member leaves the group, asynchronous replication is also stopped. If it's on secondary, asynchronous replication are not allowed because they are read-only, and if there is already asynchronous replication, it's not allowed to become a secondary of the group. We have this automatic flow control where if one of the node is not able to cope up with the other members, then this throttling mechanism, flow control mechanism kicks in, it will slow down the other servers. Here in this case, B is not able to cope up, A and C, throttling is enabled, and when B becomes stable and it's able to, like it's in the same state like others, so this throttling stops and everything works normal. Network partition, this was one of the community request, where if network partitioning happened, network partitioning what happened, A and B are not able to communicate with the C and D. If they are in the same network and they are not able to communicate A and B, not communicate with C and D, but A and B both can communicate with each other, they can see each other, C and D, both can communicate with each other. We added on this group revelation unable unreachable majority timeout. You said it's value, like you said it's value as a one minute 60 second, then after 60 second, the members which are in minority will stop automatically. Here they are four members, so minority both are in minority, these two both groups are in minority, so whole group goes dissolved. We also added a group propagation force members. If this partition happened, and you can give IP address of particular members, they will stay and others will get shut down. If you want, okay, like they are two partition and you want one of the partition to get dissolved, so you can do it manually with the force member. This is the end goal of what we are looking in the master group application, what we want in master group application. Basically, we also have a InnoDB scripting API, so to set up master group application more easily, that you can do with the InnoDB cluster. And you can set up multiple replicas across when, this is our end goal and with the multiple single primary clusters and it can be with the router, it writes can be sent to masters and reads can be directed to second reads. This way we want everything this whole system to evolve. Right now, you can get master group application GA in 5.7.21 and 8.04 RC like we have, but very soon 8.05 GA is also coming, which will be having these all features, but you want to test, you can go for 8.04 RC also. Lot of these blocks about all these features we have added in the master high availability. You will get all the blocks about replication, group replication, all the replication related features you will get blocks about in master high availability. Documentation you can get in the deomyscule.com and you can download from the MySQL website. Thank you. Questions? You were saying when you had a new new new, you do the IP whitelist, right? Does that need a restart? I'm like, IP whitelisting should be there before, when this new member is coming. In the rest of the members. Yes. So they allow it to join the group. Yeah, so it will be a new IP. So you're doing IP whitelisting. IP whitelisting, okay, I'm not sure. I have to check whether it restart, but most probably IP whitelisting won't require a restart. I'm not sure of whether it's a dynamic or static. I'm like, if it's a dynamic, then it won't require a restart. I have to just check. The data it gets pulled automatically. Yeah, data it gets pulled automatically. Can we configure where it needs to pull it? No, no, that will be selected by a MySQL group publication ultimately. You just have to provide group elevation group name and what all the rest of the parameters, configuration parameters you have, nothing else. It would decide from where it, it sets up asynchronous replication between this new member and one of the member which is chooses to send the data. And that all is automatically, you will never see. Like if you enable group replication, you will see two asynchronous replication channels being made inside the server. One is recovery. This recovery actually is used for, recovery channel is used for this data transfer. And applyer is used for when you get data from the rest of that broadcast which I showed you, that is applied using this applyer channel. Can you please explain how throttling is done? Throttling, throttling actually, like Venkatesh, you know better than throttling. Flow control. For example, the case where you have three nodes in the group, one node is not able to catch up. Okay. You said there is some throttling. Yeah, I mean like you. Okay, so your question is how does it internally work? Yeah. How does it, when you said throttling is done until one node is about. So we have queues at every node where these are the queues that you can imagine it has a really long queues. So we know every node will be communicating the size of the queue that it has to be applied. For example, on a node two you have something like a hundred transactions that are there to apply. So it will give you the information saying that okay, my queue is getting full. That means I'm not able to cope with you guys. Can you please reduce? So what, on the circle that is pretty fast, what they do is they produce a slight delay in their coming. So in the transaction from, yes. It will happen. There are five groups. And this is a group reputation. So you want all those five nodes to be in sync. You can. Correct, there is a performance. Correct, there is a performance here. So that's the reason we, you can identify whether there is a one particular node that is going back and then it is making the system to slow, you will be able to make out using the performance scheme. Then you can actually see that okay, so fourth node is actually the main purpose of the node. So then you can kick him out. And then just on the four guys we'll Venkatesh, let me interrupt. See, this can be enabled and disabled. It's based on the user. It's you are, nobody's forcing you to enable it, okay? And there are a lot of group replication, flow control variables through which you can configure how exactly you want, what the size, a lot of things are there through which you can configure. Like if you're writing on one of the node and reading from others, you want all in the same state. There's no data difference here. So flow control would help in that. If one node is not able to catch up, remove from the group until it catches up. You don't have to actually remove, I mean it's going to be a pretty temporary situation. So the node C, node C's buffer is full and it's just telling to other people saying that, okay, you guys are pretty fast, I have some problems, so let me just execute my buffer and then wait. It's not going to be a major delay, just a very minor delay to reduce the performance of other nodes so that is there. And you don't want that to happen. You can investigate. It could be because of the CPU or it could be because of some other issues. You can kick him out, do the repair and then put him back in. But as long as you're there in the group, you have to make sure that all of the five people are perfectly running at the same time. Sink. Otherwise there would be a lot of other issues that the particular node is out in state data and the lag is too much. That node, even though he is, I mean even though that node is in the group, there is no use. If you really have no problem with this lag, then you can use asynchronous replication also. Group replication is a virtually synchronous replication plugin. So it has to make sure that everything is in same state. So flow control would help here. Or otherwise use very good servers. You will never have a problem. Ha ha ha. Sir, any of your customers using this feature? Huh? Any of your customers using this feature? Group replication? Yeah. Ricky can tell you better. Yeah. Ha ha ha. Allow me to answer that. I'll give you two minutes. Group replication, right now we have a lot of a number of customer expressing interests, especially because it is easier for them. Previously they have been using InnoDB and then now the cluster is running. No, my question is there are lots of persons expressing interest. But if they do production. It is. Okay, for instance, for example, since you guys in Singapore are not supposed to say anything, but I would just say that one of the most prominent payment processors that we always use everywhere to pay. The one is that guy is using group replications in almost every single project and in the future also every single application. You. You can, you really want to know the name. You really want to know the name. You can read the blocks of famous people from these companies, few companies. You will find I can tell the name. But yeah, they are two big companies which are using group replication. You can read the blog from them and they are the most like feedback provider for this group replication. I can tell you the name. I don't know anything. I will just forget everything. One more thing is Tesla is actually evaluating us. Tesla. Oh, stop it. They are evaluating them to construct group replication. The table, it is not shattering. It is just for high availability. Yes, it's not for sharing, it is high availability. So is there any plan in future going for shattering? He explained in the land group. Yeah, it's in the land group. We are going towards there. Whatever you are. So MySQL router was sitting outside if when it actually can this will have the sharding feature, then it can shard between different groups. Yeah. So it will have a transaction manager also. Not only resource manager, it will have a transaction manager. Why do you need a transaction manager? It's when it is sharded. It should be data-aware. The application needs to be aware of the sharding. If you are going through the router, the application doesn't have to be there. Oh, I understand. So that means you are going to incorporate the two more. All the things that you are thinking will be incorporated into the router. I hope so. Because he earned it. Yeah, that's the question. Actually, you answered a lot of questions. You asked three questions. Three questions. I was so sad that you left. You should introduce some godly. No, thank you so much.