2

In the following official MariaDB blog Getting Started with MariaDB Galera and MariaDB MaxScale the author configures a MaxScale host over a three node Galera cluster, and uses the Galer monitor (OK) and the readwritesplit router,

It is not clear for me, if I spread all transactions equally (or optionally weighted) on all nodes, including writes, why would I bother to split read and writes? What would be the benefit of this added complexity?

In reagards of performance what would be the preferred MaxScale router?

1 Answer 1

4

TL;DR: Don't write to multiple Galera nodes, you get deadlocks on COMMIT and it's not what Galera was designed for. MaxScale helps you abstract the cluster into a single database endpoint that your applications can use without having to think about load balancing.

Is it beneficial to write to multiple Galera nodes?

Writing to multiple Galera nodes gives you no performance benefits over writing to a single node: all the nodes end up performing roughly the same IO as they apply the transactions replicated via WSREP to the database itself.

Writing to multiple nodes also comes with the risk of conflicting transactions during COMMIT. From my experience applications are rarely written to handle this gracefully which means you'll risk increasing your error rate at no benefit to write throughput if you do your load balancing in a round-robin manner.

Why should I use a database proxy?

The reason why you'd want to use something like MaxScale with readwritesplit in front of your Galera cluster is to delegate the load-balance of reads to MaxScale while still being able to interact with only a single "database" server. Using MaxScale gives you improved read throughput without a risk for deadlocks on commit while still keeping the HA guarantees given by Galera: if a node in the cluster fails, MaxScale will detect it and redirect writes to a different node.

Which router to use?

As for performance, the optimal approach for mixed workloads is to use the readwritesplit router. This makes it so that you can just point your application at MaxScale without having to worry about which node you're doing your writes on. If you know your application only does reads and never modifies the database, you can use the simpler readconnroute router to do simple connection-based load balancing while still getting some of the benefits of having a dedicated database proxy like MaxScale (active monitoring, single entry point, a convenient GUI, pre-validation of users etc.).

The end result of all of this is that you can use a cluster of database nodes with MaxScale in front of them without having to change your application at all.

Disclosure: I'm a part of the MariaDB MaxScale development team.

3
  • many thx for the comprehensive answer. Seeing a the sample MaxScale configurations, I do notice those are using the Galera monitor (and the readwritesplit router, what this OP is about). However the config has say [server1], [server2], [server3] neither one is distinguished. How will MaxScale know/decide which node will be the single write node? Commented Jan 23, 2023 at 11:02
  • MaxScale uses the wsrep_local_index value from the cluster to determine which node is the "root node". This node is then used for writes by all MaxScale instances that point to the same cluster. With this, the possibility of conflicts on commit are minimal and can optionally be completely eliminated with the root_node_as_master monitor parameter.
    – markusjm
    Commented Jan 24, 2023 at 9:23
  • great help, thx. Commented Jan 24, 2023 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.