Selection of Proxy & Replication

MySQL & MariaDB Percona Operator for MySQL
1
  • HAProxy - a load balancing and proxy service compatible with both asynchronous replication and group replication between MySQL instances
  • MySQL Router - a proxy solution which can be used instead of HAProxy when group replication between MySQL instances is turned on.
  • ProxySQL - ?? Any replication preference for the ProxySQL?

What is logic or thumb rule behind the selection of Proxy and Replication?

2

HAProxy is a layer 4 TCP port traffic manager. It can do simple load balancing based on rules like ‘least connections’ or ‘round-robin’. Works well with MySQL. Cannot do query pattern matching; no rules for query processing; no cache.

MySQL Router is a mysql-specific router much like HAProxy and has the same cons. It is not widely used in the ecosytem due to performance issues.

ProxySQL is a layer 7 traffic manager/load balancer/connection multiplexer. it “speaks” mysql and can route queries based on the actual contents of the SQL itself. It has a rule engine for complex routing/splitting and has native query caching. proxysql also understands group replication, xtradb clustering, and typical async replication.

The rule of thumb is whatever you are comfortable with. My default is ProxySQL because it is highly performant (X0,000 connections) and has many features.

1 Like
4

Thanks @matthewb and @Road_Tax_Calculator

Sorry, I hope, my Question is not clear. Let me try to rephrase it.

Is there any relationship between Proxy module and Replication logic? Is there any inter-dependency between Proxy and Replication? For example, Async/Group replication may not work well with specific proxy module.

Based on the previous answer, I think, there is no dependency. If there is any, plz throw some light on it.

1 Like
6

@midson Replication and Proxy are 2 completely different topics. You can do replication without any proxy. ProxySQL can be made aware of replication, and proxysql can monitor replication state, but it has nothing to do with replication itself.

7

Thanks @matthewb.

I got the doubt after reading below text. (Sorry, took a while to recollect this place). And nothing mentioned about ProxySQL here.

Proxy solution used by the Operator for the database cluster depends on the replication type used in the cluster. [HAProxy] can be used with both replication types, while [MySQL Router] can be used with Group Replication

8

Hi @midson,
I’m very surprised by that documentation. I’m talking with our mysql and operators product teams. MySQL Router is not very performant.

You need a proxy of some sort within a K8S environment to handle high-availability connections.

9

Thanks @matthewb

Also I need to know, why ProxySQL is supported in Percona Operator for MySQL based on Percona XtraDB Cluster" and NOT in “Percona Operator for MySQL based on Percona MySQL Server”

10

@midson I spoke with our MySQL product manager and he said proxysql was considered too complex for simple mysql replication. It makes sense to have it built-in for PXC because that’s a cluster with simpler topology. You are certainly free to use Proxysql yourself inside its own container with the base percona mysql operator. Personally, if you are wanting a stable HA mysql, you should go with the PXC operator anyways. If you need just a single mysql server, use the mysql operator.