How to split reads/writes with ProxySQL

MySQL & MariaDB Percona Operator for MySQL
,
1

Hello,

Reads and writes goes primary node by default, right? Within our organization we have one team which has their own, custom built setup and all database nodes respond to read queries.

How we should configure ProxySQL so that it would use all database nodes (pxc-0, 1, 2) for reads? Can this be done also with writes without split brain or any negative effect?

1 Like
2

Hi Katajistok,

You can route traffic to different nodes by using query rules , hostgroups and default hostgroups for users as explained on the following links:
PXC proxysql example setup: ProxySQL Native Support for Percona XtraDB Cluster (PXC) - Percona Database Performance Blog
query rules: ProxySQL Query Rules: Notes From Production | Official Pythian®® Blog
setting default hostgroup example: change MYSQL USER default_hostgroup not effect immediately · Issue #1265 · sysown/proxysql · GitHub

Regards

2 Likes
3

What is the proper way to make changes to ProxySQL configuration? I know that configuration can be initially set on cr.yaml and creating a new configmap also works, but how about changing runtime configuration? I found out that from ProxySQL Pod and proxysql container I can change the runtime configuration with proxysql-admin cli.
Is there some examples of how to change hostgroups and node weights in example?
I was able to change weights for nodes, but still only one MySQL node was taking all the reads. I didn’t make any changes on rules.

1 Like
4

We use proxysql-admin in the operator to configure proxysql.
So whatever changes you make in the hostgroups - they will be overwritten by proxysql-admin and the operator.

We have this issue right now that is exactly about using only one server for reads: [K8SPXC-735] ProxySQL uses only one server for reads - Percona JIRA

We are working on its resolution, but it requires some work.
As a workaround some of our users tend to use haproxy with Operator and setup proxySQL as a standalone server.

2 Likes
5

Thank you. This explains it. So we will try with HAProxy and read-only-replicas and configure application so that it’s able to use two endpoints for database queries.

1 Like
6

Hello! Is there any new about this issue? It has NO sense using proxysql if operator doesn’t allow splitting reads and writes.

7

Hi @Sergey_Pronin . Solving this issue is as easy as having mysql_servers like this:

+--------------+----------------------------------------------+------+-----------+--------+----------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                     | port | gtid_port | status | weight   | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------------------------------------+------+-----------+--------+----------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | test-mysql-pxc-0.test.svc.cluster.local | 3306 | 0         | ONLINE | 10000000 | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | test-mysql-pxc-0.test.svc.cluster.local | 3306 | 0         | ONLINE | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | test-mysql-pxc-1.test.svc.cluster.local | 3306 | 0         | ONLINE | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | test-mysql-pxc-2.test.svc.cluster.local | 3306 | 0         | ONLINE | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 3            | test-mysql-pxc-1.test.svc.cluster.local | 3306 | 0         | ONLINE | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 3            | test-mysql-pxc-2.test.svc.cluster.local | 3306 | 0         | ONLINE | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
+--------------+----------------------------------------------+------+-----------+--------+----------+-------------+-----------------+---------------------+---------+----------------+---------+

Set writer_is_also_reader=1.

My host groups are as follows:

MySQL [(none)]> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1                | 3                       | 2                | 4                 | 1      | 1           | 1                     | 100                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

With this configuration, the config in runtime after a “LOAD MYSQL SERVERS TO RUNTIME” is this:

MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+----------------------------------------------+------+-----------+---------+----------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                     | port | gtid_port | status  | weight   | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------------------------------------+------+-----------+---------+----------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | ninguno-mysql-pxc-0.alvaro.svc.cluster.local | 3306 | 0         | ONLINE  | 10000000 | 0           | 1000            | 0                   | 1       | 0              |         |
| 3            | ninguno-mysql-pxc-2.alvaro.svc.cluster.local | 3306 | 0         | ONLINE  | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 3            | ninguno-mysql-pxc-1.alvaro.svc.cluster.local | 3306 | 0         | ONLINE  | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | ninguno-mysql-pxc-2.alvaro.svc.cluster.local | 3306 | 0         | ONLINE  | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | ninguno-mysql-pxc-1.alvaro.svc.cluster.local | 3306 | 0         | ONLINE  | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | ninguno-mysql-pxc-0.alvaro.svc.cluster.local | 3306 | 0         | ONLINE  | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 1            | ninguno-mysql-pxc-2.alvaro.svc.cluster.local | 3306 | 0         | SHUNNED | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
| 1            | ninguno-mysql-pxc-1.alvaro.svc.cluster.local | 3306 | 0         | SHUNNED | 1        | 0           | 1000            | 0                   | 1       | 0              |         |
+--------------+----------------------------------------------+------+-----------+---------+----------+-------------+-----------------+---------------------+---------+----------------+---------+
1 Like