# How to split reads/writes with ProxySQL

**URL:** <https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885>\
**Category:** Percona Operator for MySQL\
**Tags:** mysql, percona\
**Created:** [January 12, 2022, 3:01pm UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885 "2022-01-12T15:01:34Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![katajistok](https://avatars.discourse-cdn.com/v4/letter/k/9f8e36/32.png) [@katajistok](https://forums.percona.com/u/katajistok)\
**Post date:** [January 12, 2022, 3:01pm UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/1 "2022-01-12T15:01:34Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\
**Post date:** [January 12, 2022, 3:23pm UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/2 "2022-01-12T15:23:32Z")

</div>

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](https://www.percona.com/blog/2019/02/20/proxysql-native-support-for-percona-xtradb-cluster-pxc/)  
query rules: [ProxySQL Query Rules: Notes From Production | Official Pythian®® Blog](https://blog.pythian.com/proxysql-query-rules-production-notes/)  
setting default hostgroup example: [change MYSQL USER default\_hostgroup not effect immediately · Issue #1265 · sysown/proxysql · GitHub](https://github.com/sysown/proxysql/issues/1265)

Regards

---

<div class="post-metadata">

**Author:** ![katajistok](https://avatars.discourse-cdn.com/v4/letter/k/9f8e36/32.png) [@katajistok](https://forums.percona.com/u/katajistok)\
**Post date:** [January 12, 2022, 3:57pm UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/3 "2022-01-12T15:57:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Sergey\_Pronin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/sergey_pronin/32/14887_2.png) [@Sergey\_Pronin](https://forums.percona.com/u/Sergey_Pronin)\
**Post date:** [January 17, 2022, 9:20am UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/4 "2022-01-17T09:20:01Z")

</div>

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](https://jira.percona.com/browse/K8SPXC-735)

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.

---

<div class="post-metadata">

**Author:** ![katajistok](https://avatars.discourse-cdn.com/v4/letter/k/9f8e36/32.png) [@katajistok](https://forums.percona.com/u/katajistok)\
**Post date:** [January 17, 2022, 9:32am UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/5 "2022-01-17T09:32:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Alvaro\_Aguilar-Tabla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/alvaro_aguilar-tabla/32/6236_2.png) [@Alvaro\_Aguilar-Tabla](https://forums.percona.com/u/Alvaro_Aguilar-Tabla)\
**Post date:** [May 5, 2022, 11:49am UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/6 "2022-05-05T11:49:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Alvaro\_Aguilar-Tabla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/alvaro_aguilar-tabla/32/6236_2.png) [@Alvaro\_Aguilar-Tabla](https://forums.percona.com/u/Alvaro_Aguilar-Tabla)\
**Post date:** [May 6, 2022, 11:51am UTC](https://forums.percona.com/t/how-to-split-reads-writes-with-proxysql/13885/7 "2022-05-06T11:51:41Z")

</div>

Hi @Sergey_Pronin . Solving this issue is as easy as having mysql\_servers like this:

```nohighlight
+--------------+----------------------------------------------+------+-----------+--------+----------+-------------+-----------------+---------------------+---------+----------------+---------+
| 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:

```auto
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:

```auto
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 | |
+--------------+----------------------------------------------+------+-----------+---------+----------+-------------+-----------------+---------------------+---------+----------------+---------+

```
