Deploy ProxySQL on kubernetes

[peppy]

The version we are running is quite outdated, and has a rare tendency to fall over. It would be beneficial if we can run one (or more) instances on kubernetes, to allow for easier upgrades and better resilience.

Things that need consideration:

• Management of configuration (needs to be easy to make quick changes). I don't know what kind of import/export options are available, potentially just a mysql-dump.
• If we run multiple instances, I'd hope kubernetes can round robing between them automatically while only giving a single host specification to deployments
• I'd probably limit usage to only apps deployed to kubernetes. We can keep a separate instance for other usage as we migrate.

For reference, ProxySQL is high cpu, low everything-else

[ThePooN]

I identified two ways we can deploy a ProxySQL high-availability cluster.

Using Kubernetes-style approach

This solution is showcased in this repo: https://github.com/ProxySQL/kubernetes
It's not very documented and based on older versions, but easy to recreate and maintain.

Basically, it makes each ProxySQL standalone, without using native clustering capabilities. It relies on Kubernetes ConfigMap to maintain the config, in a .cnf file rather than using the admin interface.

This is easy to deploy and maintain, however it is not very suited for migration from our current deployment.

Using native ProxySQL clustering capabilities

Introduced early 2020 in 1.4.2: https://proxysql.com/blog/proxysql-cluster/

Current documentation: https://proxysql.com/documentation/ProxySQL-Cluster/

It doesn't seem to have evolved much in the mean time.

Advantages:

• We can probably import/export our config as one would expect with mysqldump
• Fairly easy to set-up and administer across Kubernetes and non-Kubernetes environments

Drawbacks:

• It doesn't seem mature, seems stable but their roadmap exposes a lot of missing features to make a feature-complete replicated setup.
• Doesn't seem to do load-balancing of its own; we would need to do this with another proxy/LB in front
• Lack of automated cluster members maintenance (adding or removing instances to each instance's replication config)

They don't seem to have a concept of master/slave instances at the moment. However, from what I understand, their implementation can still somewhat simulate this, as adding an instance to another instance's config only enables it to pull newer config from it (which is what makes one a "master"). So you could spin up an instance without registering it to every other instance, and it would only lack the ability of being pulled config from others, effectively making it a "slave".

So we could work-around this lack of functionality by only keeping a handful of "fully" joined instances in the config and maintaining it manually. If they were deployed in our cluster, that would be very easy as they could be referenced via DNS as "proxysql-0", "proxysql-1", "proxysql-2". They would act as masters from which we can update config from.
I added 3 instances as master in this example, but having just one wouldn't compromise the high-availability aspect of the deployment. It would only make us unavailable to update the config if it were down, but slaves should continue operating normally.

We could use this functionality to add ProxySQL to our Kubernetes cluster, synchronizing it with the current main instance. Config would only be editable from our main instance.
Kubernetes would address the load-balancing issue on its own, just like any other service. We can also expose our in-k8s-ProxySQL cluster to the rest of our infrastructure, letting Kubernetes handling ProxySQL load-balancing for the whole infrastructure.

Later on, we could let one or multiple of the in-Kubernetes ProxySQL instance(s) become our master(s) to complete the migration process.

TL;DR: Basic, but enough features are implemented for simple config-replication as one would expect for a stateless service.

I haven't tried this yet, this is only the result of reading doc, articles, and their kubernetes repo example linked above.

[peppy]

Honestly I'm liking the first solution more.

The admin interface can be pretty jank to work with, so I believe that a config file may be more manageable from that angle too.

Are you able to export the current production config to a file and shoot that over so I can take a look (if that's possible)? Would need to be kept private as I think it contains IPs at very least.

[ThePooN]

If you don't actually need the admin interface, then 1st solution is obviously worth looking into.

Current config can be exported to the config file format using SELECT CONFIG FILE;. Output looks good to me, but as you're the one operating it, better double-check on your end. This is all we need to deploy a ProxySQL cluster to Kubernetes today.

However, we cannot synchronize this new config to ProxySQL instances outside of Kubernetes. Let's say we want to 100% migrate to ProxySQL in-Kubernetes then.

We can expose the service from all Kubernetes nodes as NodePort inside our VPC. However, the DigitalOcean Kubernetes nodes IP addresses are not static and we also need some kind of load balancing to avoid all the traffic going through one node (although it would share the CPU load among all ProxySQL instances inside the cluster, it's a lot of bandwidth to handle on 1 node).
We can work-around this issue by using https://github.com/kubernetes-sigs/external-dns (as recommended by DigitalOcean on their docs). Registering multiple A entries should make all DNS clients pick a random IP within the A entries, effectively achieving load-balancing.

[ThePooN]

As an update, in-Kubernetes ProxySQL seems to be working as expected on private osu-web testing. We should probably start wiring more in-Kubernetes services to it rather than our legacy deployment, and then figure out how to open it to non-k8s droplets.