ProxySQL Cluster: challenges and solutions to synchronizeconfigurationacross multiple decentralized cluster nodes
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 287 | |
| Author | ||
| License | 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. | |
| Identifiers | 10.5446/56953 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
FOSDEM 202261 / 287
2
4
6
8
12
17
21
23
31
35
37
41
44
45
46
47
50
62
65
66
67
68
71
73
81
84
85
86
90
92
94
100
102
105
111
114
115
116
117
118
121
122
124
127
131
133
135
137
139
140
141
142
145
149
150
156
164
165
167
169
170
171
172
174
176
178
180
183
184
189
190
192
194
198
205
206
207
208
210
218
220
224
225
229
230
232
235
236
238
239
240
242
243
244
245
246
249
250
253
260
262
264
267
273
274
277
282
283
287
00:00
Proxy serverService (economics)Information technology consultingClient (computing)Group actionInformationCommunications protocolGateway (telecommunications)Cache (computing)Overhead (computing)Server (computing)Data managementSQL ServerScalabilitySystem programmingInstallation artModul <Datentyp>Interface (computing)Set (mathematics)Variable (mathematics)RoutingModule (mathematics)Revision controlEnterprise architectureData centerBusiness clusterImplementationExtension (kinesiology)SynchronizationAsynchronous Transfer ModeElectronic visual displaySocial classBusiness clusterProxy serverCASE <Informatik>Physical systemProcess (computing)Multiplication signGroup actionConfiguration spaceAverageWebsiteOperator (mathematics)SoftwareHypermediaSequelInheritance (object-oriented programming)Video gameDirection (geometry)Line (geometry)WordStability theoryArithmetic meanSingle-precision floating-point formatNumberBitPoint (geometry)Configuration managementPlanningServer (computing)CausalityProduct (business)ResultantView (database)State of matterCategory of beingComplex (psychology)Extension (kinesiology)Source codeData managementWeightLatent heatImplementationSystem callRule of inferenceGodInformationClient (computing)Order (biology)Core dumpEndliche ModelltheorieMereologyElectronic mailing listEqualiser (mathematics)Context awarenessCuboidImage resolutionSuite (music)Exception handlingProper mapProcedural programmingNeuroinformatikStudent's t-testMetropolitan area networkForm (programming)Goodness of fitInstance (computer science)Twin primeComputer programmingQuicksortObservational studyReal-time operating systemCommunications protocolReverse engineeringSoftware developerSeries (mathematics)High availabilityDifferent (Kate Ryan album)Alphabet (computer science)DatabaseDatabase normalizationCartesian coordinate systemFront and back endsType theoryMobile appInternet service providerRevision controlModule (mathematics)Variable (mathematics)SynchronizationAreaQuery languageVideo game consoleStandard deviationMultiplicationComputer animation
08:29
Proxy serverBusiness clusterLatent heatSynchronizationMechanism designRegular graphCore dumpGame controllerVariable (mathematics)Order (biology)Revision controlTimestampProxy serverBusiness clusterVariable (mathematics)Table (information)Server (computing)Instance (computer science)Module (mathematics)Interface (computing)WeightConfiguration spaceException handlingFrequencyCombinational logicRevision controlPropagatorSynchronizationSoftwareSingle-precision floating-point formatPoint (geometry)2 (number)Identity managementMultiplication signSet (mathematics)CASE <Informatik>Flow separationBitView (database)TimestampStructural loadSource codeEqualiser (mathematics)NeuroinformatikMatrix (mathematics)Configuration managementDirect numerical simulationMultiplicationMechanism designBand matrixGroup actionSeries (mathematics)InformationRegular graphMereologySubsetLatent heatSoftware design patternElectronic mailing listSocial classProcess (computing)Operator (mathematics)CuboidForcing (mathematics)Arithmetic meanDirected graphWordQuicksortProcedural programmingGravitationDimensional analysisElectronic visual displayScaling (geometry)Order (biology)AuthorizationFigurate numberMathematicsWaveComputer animation
16:42
Direct numerical simulationRevision controlProxy serverBoolean algebraVariable (mathematics)SynchronizationBusiness clusterCore dumpMiniDiscImage registrationData managementMathematical analysisAsynchronous Transfer ModeFeedbackGoogolGroup actionTwitterConfiguration spaceCASE <Informatik>Instance (computer science)SynchronizationSatelliteResultantSeries (mathematics)NumberProxy serverConnected spaceSemiconductor memoryNetwork topologyDiagramBusiness clusterTwitterSoftware developerAsynchronous Transfer ModeServer (computing)Source codeImplementationMereologySingle-precision floating-point formatCore dumpSystem administratorRevision controlInterface (computing)Client (computing)Computer programmingInformationMiniDiscLimit (category theory)LastteilungFlow separationWebsiteVariable (mathematics)Group actionMultiplicationEmailElectronic mailing listMetric systemDirect numerical simulationIP addressConfiguration managementPhysical systemVermaschtes NetzRead-only memoryReplication (computing)Ocean currentMultiplication signElectronic visual displayStatisticsWeb 2.0Information retrievalType theoryTable (information)CodeProcess (computing)Torus2 (number)PlanningProcedural programmingDirection (geometry)CuboidData managementOrder (biology)Form (programming)Design by contractArithmetic meanCausalityPoint (geometry)Extension (kinesiology)Decision theoryProjective planeCircleSquare numberPhysicalismAlgebraWhiteboardDifferent (Kate Ryan album)Row (database)Reduction of orderSet (mathematics)Electric generatorSocial classState of matter40 (number)Inheritance (object-oriented programming)Rational numberVideo gameCollaborationismDistance
24:54
Computer animation
Transcript: English(auto-generated)
00:07
Hello, and thanks for joining this session about proxy SQL cluster, where we will discuss the challenges and solutions to synchronize config across multiple decentralized cluster nodes. A quick introduction, my name is Enek Anam. I have been a DBA for most of my career, so I have been a DBA for a bit more than two decades.
00:23
I started writing proxy SQL software back in 2013, and in 2016 the company with the same name was formed in order to provide service about proxy SQL product, including support, consultancy, and development. Now, where is proxy SQL? Because the topic of this session is about proxy SQL cluster capability, I will not invest a lot
00:43
of time in explaining what proxy SQL is, and I will only mention it is a reverse proxy that understands the SQL protocol. Client that connects to proxy SQL, this will evaluate the request from the client and it performs value action, like transforming the request itself, filtering, and if the request has to be executed, this is not always the case, it will determine where.
01:05
As mentioned, I won't go into the details about the features of proxy SQL, but I recommend to visit our website for more information. Once we understand what proxy SQL is, one of the most common questions is where to deploy it. Because it uses a massive protocol on both ends of the wire, client, and backend side,
01:23
it can be deployed anywhere between the two, and each type of deployment has benefit and drawback. And I will go through a quick overview of that. So, the first deployment is app server deployment. In this very common deployment where proxy SQL is installed alongside the application, this deployment has benefit but also drawback.
01:45
Some of them listed here decide itself. Relevant to our topic today is about configuration management. If proxy SQL is deployed alongside each application server, you need a way to keep those configurations aligned to each other and consistent. Then we have proxy SQL layer deployment, and the setup application server connect to a single proxy SQL instance.
02:06
On this case, there are benefits and drawback. What is important to point at is that in this specific scenario, it is extremely rare, because it doesn't provide any sort of high availability. Therefore, the middle layer is normally composed with more than one instance, with some sort of HA.
02:22
And of course, the details of the HA is out of the scope of this session, and I will skip it for now. In the setup, the configuration is very simple. There is just one single node to configure. But we can also have a way more complex setup, like this form of cascading setup, where proxy SQL instances are installed both on the application server and as a middle layer.
02:43
We also have more complex setup, where proxy SQL instances are installed on the application server and on the database server, and on a middle layer and on the database server. So really, it can be on a layer. But no matter how you create this complex setup, it is clear that configuring it properly
03:00
becomes more challenging than the previous setup, because there are way more instances and different layers. In all the setup we showed so far, we are basically facing a distributed system, where you have multiple proxy SQL instances, possibly with different roles and different needs,
03:23
that needs different configuration, that communicate and need to coordinate with each other to achieve a common goal. The reason why we rely on a distributed system is to be able to achieve scalability and redundancy. In fact, the number of instances can scale up or down based on the amount of traffic it needs to serve, and we need to avoid a single point of failure.
03:44
Any node can fail at any time, and the system should continue working without downtime. So, the next question is how to configure a distributed management system, and it is here where configuration management becomes relevant.
04:01
Configuration management are processes that maintain a system, in our case a distributed setup of proxy SQL instance, in a state that is desired, consistent, and according to requirement. In other words, it needs to ensure that the various instances that need to perform specific actions and perform in a certain way are configured in the way we want them to perform, and the configuration must be consistent with each other.
04:26
Now, the question is, there are configuration management systems. Yes, there are plenty that already support proxy SQL. For example, we have Ansible. Probably many of you are already familiar with Ansible.
04:41
This is a pretty popular configuration management tool, and thanks to community effort, it supports proxy SQL. It has several modules, and each of these is able to configure different modules of proxy SQL. The fact that it is so many modules is also an evidence that proxy SQL itself is extremely configurable.
05:03
Next, in poor alphabetical order, we have Chef, another very popular configuration management tool that supports proxy SQL thanks to elsewhere community effort. Finally, we have Puppet, that also supports proxy SQL thanks to community effort. According to the number of downloads, it seems that is the most popular among the configuration management tools listed so far.
05:29
But the study doesn't end with configuration management tool. In fact, it is very common to have proxy SQL instance being configured through the use of network configuration and discovery services like Consul.
05:43
How does it work here? A little Consul server receives a configuration that is then propagated to other Consuls and other Consul servers, and ultimately they will reach to Consul client. Upon receiving a new configuration, a Consul client can be configured to connect a proxy SQL instance that is often locally,
06:03
and to issues a series of commands to reconfigure it. This means that discovery service like Consul can be repurposed to be a configuration management tool. Without going into many details, the same can be said about other discovery service like Zookeeper. But Consul and Zookeeper are a pretty popular solution often used to reconfigure proxy SQL.
06:29
Finally, we have proxy SQL cluster. This is our own implementation of configuration management, and is native to proxy SQL itself. This is very important. It means it doesn't rely on any external configuration management tool.
06:41
In fact, the main idea is that it can configure a cluster of proxy SQL instance and reconfigure them in real time without the need of any external dependency. This doesn't mean that you should not use configuration management tools, but it gives you the freedom to not rely on any of them. Furthermore, it has an interesting advantage compared to configuration management tools.
07:02
When a new version of proxy SQL is released, native proxy SQL cluster will support any new specific features or extension that was introduced. By context, if you're using a configuration management tool, it might work for a specific version of proxy SQL and not for another version, or it might not support new features for a long time.
07:24
Finally, it allows a great deal of other enhancement, but I will go into details later. What is able to synchronize? Currently, it's able to synchronize MySQL users, MySQL server, and the various MySQL cluster configurations. So, if you're using a standard MySQL asynchronous replication, or if you're using group replication, Guerrero, AWS, and so on.
07:48
It's also able to synchronize MySQL query rules, and it's able to synchronize the list of proxy SQL nodes that are part of the cluster, and also MySQL variables, LTAB variables, and MySQL variables.
08:01
Each of them is considered a configuration module and can be enabled or disabled independently from the other. For example, you might want to configure only the synchronization of MySQL users and query rules, but you don't want to synchronize the MySQL server because, for example, you want to use console or Jupyter for that.
08:23
Those are the occasional areas. In a nutshell, the way proxy SQL cluster work is the following. So, you have various proxy SQL nodes that connect to a single instance that is configured, that is considered the configuration source of truth.
08:41
When any configuration is changed in this specific instance, the other will detect it and will pull the configuration from it. Okay, many of you probably are already pointing fingers to a pretty evident drawback of these scenarios. Basically, there is just one single point of failure, that is the proxy SQL instance that has the configuration.
09:01
So, let's extend the topology and remove the single point of failure. In here, in this diagram, we have four proxy SQL cluster nodes, and each instance is monitoring each other. So, basically, each four are always monitoring all the others. As soon as they detect that a node has a new configuration, they will all pull the new configuration from it.
09:25
What is important to note from the setup is that, currently, there is no leader, although we plan best for the features. Synchronization is time-based, each configuration has a network associated to it, and the newer configuration is the one propagated.
09:41
It also works to recreate what I already mentioned a few times. Nodes check each other at regular intervals, and they pull the configuration, so there is no actual push of configuration. Okay, how to configure proxy SQL cluster? Well, first of all, as you know, proxy SQL with the configuration is visible in tabular format, and proxy SQL cluster is no exception.
10:03
So, first of all, proxy SQL node which instances they need to monitor because they're configured in a table called proxy SQL server. The table is pretty simple, as you can see here. There is the hostname and the port of the instance, the weight that is currently not used, and a comment.
10:20
Please note that this table itself is replicated by cluster. So, if a proxy SQL instance is configured with just one proxy SQL node in proxy SQL server, it is able to connect to it and pull the whole list of proxy SQL server that are part of the cluster. So it will automatically be able to discover the rest of the cluster.
10:41
Then, we have a lot of variables related to cluster. Here, I'm only displaying a subset of the variables that are most relevant to this talk. First of all, we need a pair of username and password, those are the credentials that will be used to connect to the other instances, to the interface specifically. Then, we have a check interval that defines how often the various configurations are checked.
11:05
Then, for each module, we have a counter that defines for how many checks the configuration needs to be defined from its own configuration before re-triggering a synchronization. It is important to note that if any of those variables is zero, the configuration for that specific module is disabled.
11:25
Great, let's see this in action. So, we have other tables, it's called start proxy SQL server checksum. In this table, we can see a series of information for each proxy SQL instance and for each module. There is really a lot of information here, so now I will actually zoom in and see just one specific module, the MySQL server.
11:49
Great. So, here we can see again that the information is available for each cluster instance and we are able to connect. For each configuration, we can see the version epoch, the checksum of the configuration itself,
12:02
where a specific server got the configuration, and the last time this information was refreshed. Finally, if the remote configuration is different than the local one, we count for how many checks the configuration was different. Now, let's dive into this. To dive into this output, we need to start from the checksum.
12:22
This is the checksum of the configuration itself, and when no monitoring each other, they simply monitor that value. Basically, no are exchanging heartbeat with checksum at regular intervals. This drastically reduces bandwidth and computation because there is not much that is being exchanged, and
12:41
to verify that the configuration has changed or not, we only need to verify the checksum. Also, because they are basically exchanging heartbeat, each proxy SQL node is able to detect failures, so if a node disappeared, this is a perfect way of detecting this. Epoch is pretty self-explanatory.
13:02
Version is a very interesting matrix. Every time a configuration is slowed to runtime, no matter from where, the version is increased by one. Monitoring version, we can detect if any node is loading configuration too frequently or not loading it at all. But it can also tell us something important. If version dropped to one, it means that the node was restarted.
13:25
So when it loads from disk, it's the first time it is loading the configuration, so its version becomes one. Great. So restarting a node creates an interesting problem. What if you have a cluster running for a very long time with a configuration that doesn't change for weeks, and then a node is restarted?
13:44
The problem is restarted with a different configuration, so for example, a server is completely redeployed with absolutely no configuration. The restart node will have a different checksum, but a newer epoch. In theory, all nodes should consider it as the newer configuration and sync from it.
14:03
In reality, this doesn't happen. We prevent it. The reason behind it is that the restarted node will have version equal one and proxy SQL node to not accept configuration from a node with version equal one, because it is likely to be a node yet not configured or incorrectly configured.
14:24
Basically, it prevents a new or not validated configuration to become the source of it. On the other hand, a node with version equal one knows that itself is likely a node with incorrect configuration, even if its epoch is newer. It will then sync from the rest of the cluster, so not to the rest of the cluster, but from the rest of the cluster.
14:46
Because version equal one has a special meaning, this also means that if all the nodes have version equal one during an initial bootstrap, they won't be able to sync. As soon as a load to runtime command is executed on any of the nodes, the cluster is bootstrapped.
15:04
Note that this is a fancy mechanism in case of serious failure, and several or all the nodes are restarted. This is basically by design. Also, pay special attention to the fact that every timestamp is in seconds.
15:21
If you configure too fast synchronization, it is possible with proxy SQL cluster to try to synchronize while configuration is still changing. This means that too fast synchronization can lead, at best, to repeated synchronization, or at worst, to conflicting configuration that won't be able to solve automatically. Now, let's have a bit of a view of poor design patterns that we have solved in the past.
15:46
In the past, we witnessed some poor design patterns in which proxy SQL cluster doesn't work. In the previous slide, I already mentioned that things can go quite wrong if synchronization is configured to be too fast. This allowed two or three seconds before synchronization. By default, it's three seconds.
16:04
We also saw a situation in which multiple proxy SQL cluster instances were being configured at the same time with conflicting configuration. This can lead proxy SQL cluster to enter a setting which is not able to automatically resolve conflict. Please note that proxy SQL cluster support identical configuration applied to multiple instances at the same time, or in a short period of time,
16:26
so you can easily combine proxy SQL cluster with configuration management tool, but if the configuration is conflicting, proxy SQL cluster cannot over-resolve the conflict. Another interesting poor design we saw is the configuration of all proxy SQL cluster nodes with the same DNS name.
16:44
Why this is basically a very bad design? This was a very poor design because you can easily end up in a situation in which an instance is connected to another instance. It detects a new configuration. It then opens a new connection to put the configuration, but because it relies on memory solution,
17:04
it was instead connecting to a different instance and pulling the wrong configuration. We have seen cases in which this could also lead to configuration being rolled back, because it was detecting a new configuration node, but pulling the old configuration from a different node
17:24
and advertising it as an even newer configuration, so basically the end result was that the configuration was being rolled back. In the next series of proxy SQL, we already prevent this to happen because it checked the configuration before applying it, and if this is not what it expected, it simply skipped the configuration and goes back to checking the rest of the cluster.
17:48
Still, even if we are able to prevent this, please do not use a single DNS name for all the cluster. Similar to the DNS horror story, we had also horror stories with several proxy SQL instances configured behind a load balancer.
18:05
Configuration was being checked on one instance, then was being pulled from another instance, just because they were all behind the same load balancer. Also, this is prevented in the next proxy SQL release, but please do not use multiple proxy SQL instances behind a load balancer.
18:21
It completely defeated the purpose of using proxy SQL cluster. Proxy SQL cluster was not able to synchronize variables up to version 2.1.0. This limitation was technical. We could add new variables at any min or release of proxy SQL, and therefore two instances with a different number of variables were never able to generate the same checksum,
18:45
and so they will never be able to synchronize, and they could enter a never-ending attempt to synchronize. In version 2.1.0, we finalized this program in a very simple way. During the initial handshake, proxy SQL instances advertised their version. If the versions do not match, they will drop the connection.
19:02
The fact is that only proxy SQL instances of the same version can be part of one cluster. But this also creates a new interesting problem. What if we want to run multiple proxy SQL instances on the same server? Maybe by listening on the same port for the master SQL listener, but on a different port for the admin listener.
19:21
What if instead we want to specify the IP address in the listener, and the IP address itself is specific to the instance where proxy SQL is going to run? So for this reason, we add new variables named cluster sync interface. If false, the admin and SQL variables related to listener interface are not synchronized via proxy SQL cluster.
19:41
Note that these variables are global variables configured only in proxy SQL to cnf, because it cannot be part of variables synchronized by cluster itself. So far, we saw two cluster topologies, a single source of truth and multiple instances pulling the configuration from it, and a full mesh topology.
20:01
In reality, the most common topology is a setup in which few nodes murder each other, and all the other nodes only monitor the previous mentioned few nodes, as displayed in this diagram. Or in this diagram. Okay, I'm sorry, I'm very bad at diagrams, so forget about all those little proxy SQL instances you see, and let's try to conceptualize it.
20:22
In short, we basically have two groups of instances that can be seen as two layers. You can have a core layer, where few proxy SQL instances monitor each other. For example, three or four. And a satellite layer, where a large number of proxy SQL instances monitor the proxy SQL instances in the core layer. This setup can scale very well to thousands of nodes.
20:43
Very important hints here. Traffic should only be served by the satellite node, while core instances should not serve traffic and only focus on configuration. An important thing to remember here is that if a node in the satellite is manually modified, it becomes out of sync with the rest of the cluster,
21:01
unless the core pushes a new configuration. In a setup like this, the instance in the satellite layer doesn't know about the instance in the core layer, but what the core layer knows about the node in the satellite layer. Until 2.3.2, that is the latest release,
21:21
the answer is very simple, nothing. The core node knows nothing about the outer proxy. In the next release, that the code is already available, not only the satellite knows about the core nodes, but they will also generate a UID that is saved to disk so it becomes persistent in case of restart,
21:41
and they will advertise it to each node they connect to. In this way, the core nodes will know about the satellite nodes as well. As usual, information are visible in a table, and this table is called the StatProxy SQL Server Client Status. For a proxy SQL instance that is currently connected,
22:01
or it was connected in the past, the instances know the UID of the node from which the IP port was connected, that is very useful for debugging, but also the configured admin MySQL interface of that specific node. In this way, if we want to connect back to that proxy SQL instance, we know how to. Finally, we record when was the last time we have seen that node.
22:23
This is the last time it sent a query, no matter if it was for a public configuration, for registering, or for simply pulling the checksums. Next, at the beginning of the session I was mentioning that Nativa Proxy SQL cluster has some benefit that other configuration management tools do not have.
22:41
Specifically, the ability of the node to automatically register when they connect to a node allows more flexibility and further features to be implemented. Because core nodes become aware of a satellite node, they can be aware on how the cluster is growing, and we can implement several tools that connect and monitor those instances,
23:01
retrieves log, performs various types of troubleshooting, etc. For example, if the core nodes become aware of every new satellite node, we can automatically register such nodes into Prometheus and monitor it, or even directly connect them to their Prometheus exporter and collect their statistics in that time without Prometheus.
23:22
Finally, our next web UI will be able to automatically connect to a remote instance for troubleshooting and display metrics without the need of Prometheus. Now, quick notes about leader election. As I was mentioning in the current implementation of Proxy SQL cluster, we do not rely on leader election. Unless conflicting configurations are pushed at the same time to more than one node,
23:44
the lack of a leader election is not a problem. That says, the implementation of leader election is probably not complex to execute, as Proxy SQL has already built in a series of features that will make the implementation simpler. In fact, it is already possible to configure admin to be in read-write or read-only mode,
24:02
thus accepting or rejecting write to this new configuration. Admin can already reply to read-only monitor check, thus admin can already describe itself as a server in read-write or in read-only mode. And using read-only monitor, we can create wasgroup in a replication hostgroup,
24:20
where the leader is in the writer hostgroup while the followers are in the reader hostgroup. So what that means is that we can implement a system in which you can connect to any Proxy SQL instance and the requests are automatically forwarded to the leader. Finally, I want to thank you all for attending this session at positive, and I invite you to visit our website for further information,
24:43
to join our mailing list for any questions, to find us on GitHub for issue or simply to get updates about the development and to follow us on Twitter. Thank you.