0

My Galera Cluster is on very high load. To run it fine, I´m using strong hardware and for sure a lot of custom MariaDB settings and custom OS tweaks.

Now, I want to add Slaves to get "realtime" Backups of the Cluster.

Now my question, does my (read only?) Slave even need a solid or the same hardware as the Master nodes?

According MySQL Documentation

The master has a thread, called the dump thread, that continuously reads the master's binlog and sends it to the slave.

I do not know if this also is the case for Mariadb Galera.

But if the Master sends data like this to the Slaves, am I right that those Slave Nodes wont need any "special hardware" to work fine?

In addition, if I could use "low end" hardware on the Slave Nodes, might they increase latency of the Masters or might cause somehow other issues because of the "weak" Slave?

3
  • add Slaves to get "realtime" Backups -- how do you plan to restore or use these backups?
    – mustaccio
    Sep 25, 2022 at 12:26
  • i just want to keep the slave up in case the main cluster completely breaks for some reason. so i might be able to setup a new cluster without data loss. Sep 25, 2022 at 13:01
  • Another significant factor -- What is the latency between nodes?
    – Rick James
    Sep 26, 2022 at 17:12

2 Answers 2

0

I want to add Slaves to get "realtime" Backups of the Cluster

Your use case is still unclear. If you want the standby nodes to accept the application workload if the main cluster is unavailable, they need the same or comparable hardware as the main nodes.

If you only want them to exist as a backup copy of your data, they need to be powerful enough to keep up with the stream of changes being sent from the primary, if you expect the standbys to be anywhere near "realtime" copies.

3
  • im sorry, i tried to be clear as possible, i might not have the right keywords to be fully clear. before i was doing mysqldumps daily, beside that they slowed down my cluster they are not "realtime backups". imagine you do a backup of your database and 5 hours later your cluster dies for any reason, you lost 5 hours of data. this is what i try to avoid. thats why i have the plan to use a slave to have my data always up to date and "safe" on "one more" location / datacenter. Sep 26, 2022 at 8:05
  • So, suppose the event you want to protect from happens and your primary location is destroyed. What is your next step?
    – mustaccio
    Sep 26, 2022 at 12:30
  • thes slave will be hosted somewhere else, so obviously running a new cluster and initially pushing slaves data into it. Sep 27, 2022 at 8:35
0

You have Galera active, but are using it as a "single-Primary" with "multiple Replicas", correct?

All writes occur on all servers. This is a fact of any replication topology. There is some optimization of a "write" as it moves from the Primary to the Replica, but this does not necessarily allow the Replicas to be less "solid" than the Primary.

As for your quote -- that applies to standard replication that comes with MySQL/MariaDB. When adding in Galera, the "gcache" is used instead of the "binlog".

Somewhere else the documentation recommends that the Replicas be as powerful as the Primary. But that assumes you are also using the Replicas as read-only workhorses, which does not seem to be your case.

Here's a guess at what you are experiencing. You have some particular type of complex write-query that costs just as much to perform on the Replica as on the Primary, and/or you have less RAM or HDD instead of SDD on the Replicas. A possible example: an UPDATE that modifies most of the rows of a million-row table.

It may be possible to keep the same hardware but "fix" that query. But first, you need to identify it; then we can see if there is a way to rephrase it or add some composite index. Turn on the slowlog and have long_query_time=1 on all servers. After a while, use pt_query_digest to locate the "worst" queries. Then start a new Question to discuss speeding it up in a Galera environment. More: SlowLog

More

As I understand your picture, you have a 3-node Galera setup with one of the nodes having an asynchronous (ond fashioned) replication leading to a Replica. The main factor in whether that Replica is "up to date" is whether the binlog from the cluster node is being sent promptly to the Replica.

Even if the Replica is less powerful, it will eventually catch up. (Unless it is much too weak.) Seconds_behind_master indicates the delay in applying the UPDATEs (etc). As long as the node that is feeding this replica as sent the updates, the Replica can eventually catch up.

If you have all 3 cluster nodes in the same rack, or even in the same data center, then you have a "single point of failure", namely the rack or cluster. Is that your topology?

6
  • Just to be clear, this is the planned setup: Image. Sep 26, 2022 at 7:59
  • > Somewhere else the documentation recommends ... Can you share a link of this (part) of the documentation? i was not able to find this informatons. Sep 26, 2022 at 8:08
  • @DLLDevStudio - Now that I see the topology, I added some more to my Answer. Sorry, I don't have a link to my quote. Anyway, it probably predates Galera Clustering.
    – Rick James
    Sep 26, 2022 at 17:22
  • All good, to bad i cant upvote yet, but your addition after reviewing my picture, answers my question. I will run with same hardware on replica(s) too, it might not necessary needed, but the "might" is enough for me to do not take the risk. thanks! PS: "asynchronous (ond fashioned)" which is the new fashioned style? im still searching for a synchronous method, is there a way? Sep 27, 2022 at 8:40
  • @DLLDevStudio - There is no fully synchronous replication. MySQL has a "semi-sync" option which covers some disaster situations; Galera is nearly sync, with the exception of "critical reads -- adding a setting before a SELECT covers that.
    – Rick James
    Sep 27, 2022 at 15:31

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.