mariadb replication with data sharding and clustering

Question

We have set up three node mariaDB galera cluster, which is basically a multi-master and does the synchronous replication of data across the nodes in the cluster.Eventually , the read/write can be performed through any nodes in the cluster. However , it didn't solve our problem. As mariadb has various flavours like mariaDB maxscale for sharding , we could also apply asynchronous replication in the galera cluster. At the same time we can also make use the ReaWrite splitter and schemaRouter on the galera cluster to distribute the load. How the below requirements can be achieved with help of MariaDB The data should be distributed not in every node , meaning the data replication should be controllable .As MariaDb by default replicates to all the slaves in case of master-slave and to all the server has same data in case of galera. In case of galera the major concern is we need to have large hardware in all the nodes. I agree we can achieve high throughput for different client but the data isn't distributed which is actually mirrored. On the same note in case of Master-slave which has a bottleneck of single point failure. Please suggest , is there any alternative approach which provides high availability because our application is read/write intensive. So we need the load to be distributed across multiple nodes. In case of read/write operation the data should be distributed/aggregated to and from multiple servers based on the replication factor.How it can be achieved using mariaDB.

Answer 1

Consider a 3-node Galera cluster, each node taking writes.

To each of those nodes, hang any number of read-only Slaves.

If you really need Sharding (because of needing more write-scaling than Galera gives you), then set up multiple Galera clusters, each containing a fraction of the data. Then stick a proxy in front of it all to direct traffic to the primary cluster.

Meanwhile, turn on the slowlog with a low value of long_query_time and see if you can speed up the queries.