From a0d97b7373730f241b3637f9e5ab03cd8f99cf87 Mon Sep 17 00:00:00 2001 From: Franciszek Przewozny Date: Fri, 11 Sep 2026 00:13:27 +0200 Subject: Fix ProxySQL not using backup writer node for reads Set writer_is_also_reader to 2 in the Galera hostgroup configuration so that the backup writer node also serves reads, instead of sitting idle while all read traffic went to the single remaining reader node. The writer node is never added to the reader hostgroup regardless of this setting, only the backup writer is affected. See bug report for performance difference details. Closes-Bug: #2167037 Change-Id: Ib5145c200c3cd70f861824abd967748faf61e1cf Signed-off-by: fprzewozn (cherry picked from commit 42eec877ff572fd724af8a1c0fa64acf5e824c7b) --- .../roles/loadbalancer/templates/proxysql/proxysql.yaml.j2 | 2 +- .../fix-proxysql-distributed-reads-5142e40995499611.yaml | 13 +++++++++++++ 2 files changed, 14 insertions(+), 1 deletion(-) create mode 100644 releasenotes/notes/fix-proxysql-distributed-reads-5142e40995499611.yaml diff --git a/ansible/roles/loadbalancer/templates/proxysql/proxysql.yaml.j2 b/ansible/roles/loadbalancer/templates/proxysql/proxysql.yaml.j2 index f7a8b0a08..549051383 100644 --- a/ansible/roles/loadbalancer/templates/proxysql/proxysql.yaml.j2 +++ b/ansible/roles/loadbalancer/templates/proxysql/proxysql.yaml.j2 @@ -111,6 +111,6 @@ mysql_galera_hostgroups: offline_hostgroup: {{ _offline_group }} max_connections: {{ proxysql_backend_max_connections }} max_writers: 1 - writer_is_also_reader: 0 + writer_is_also_reader: 2 comment: "Galera cluster for shard {{ shard_id }}" {% endfor %} diff --git a/releasenotes/notes/fix-proxysql-distributed-reads-5142e40995499611.yaml b/releasenotes/notes/fix-proxysql-distributed-reads-5142e40995499611.yaml new file mode 100644 index 000000000..1ec90b0fd --- /dev/null +++ b/releasenotes/notes/fix-proxysql-distributed-reads-5142e40995499611.yaml @@ -0,0 +1,13 @@ +--- +fixes: + - | + Fix ProxySQL Galera configuration so that the backup writer + node also serves read queries, instead of leaving it idle + and sending all reads to the single remaining reader node. + This is done by setting ``writer_is_also_reader`` to ``2``, + which adds backup writer hostgroup nodes to the reader + hostgroup while still excluding the active writer node. See + the `ProxySQL documentation + `__ + for details. + `LP#2167037 `__ -- cgit v1.2.3