Our team is building a computer cluster with all commodity components for AI/ML use case. For monitoring and management we are planning to use Zabbix which supports MySQL, Postgres and MariaDB for data store for the logs etc.
I am looking for HA for the database and while they suggest 3 nodes of MySQL, I like the idea of row level replication vs database level replication employed by MySQL.
Q1. Can I use Yugabyte in place of Postgres or MySQL for my use case? I looked at the limitations of the Yugabute for transition from MySQL, I don’t think I would be needing any of those features.
Q2. If this is possible, I would like to have 2 servers maintaining 4 copies of the data locally in my datacenter and another EC2 instance for a back up in asynchronous mode. I want redundancy from failures of HD ( hence 2 copies of data per box ) failures of Box ( Hence 2 boxes ) and one back up in AWS ( I do not want delays from synchronous setting. Just an automatic back up and few minutes of data loss is something I can tolerate )
Do you suggest any changes here? Am I on right track?
So you’ll have 3 servers in your local datacenter (for synchronous replication and fast failover) and can have another xCluster (1 or more servers, since this cluster can also have replication inside it) in another datacenter(AWS) that you can use for DR since you can afford to lose a little data.
I reviewed the reference docs and I see that Yugabyte can run on VMs as well as containers. So, 2 physical local servers with 2 VMs per machine should create RF of 4. Plus an additional xCluster for backup. Does this work?
As my use case is for log data for ~500 nodes and another 200 switches, I do not anticipate lot of volume. Perhaps couple of GBs per day at the most. My servers are Dual 12 core Xeons with NVMe Drives, so I am not too concerned about performance, but more about availability.
If you lose 1 server then 2 VMs might go down, losing quorum and you won’t be able to read/write. (assuming RF3). And there’s no reason to have 2VMs in 1 server having the same data. You’ll need 3 servers or 3VMs on 3 separate servers as a minimum.
This works, assuming in separate server(s) from above.
The redundancy setup you have in mind is quite fundamentally focussed on protecting data from a single place. This is understandable, and that is what most HA and DR solutions provide for monolithic databases in one way or another.
It would make sense to learn a bit about the way our distributed database works. The ‘distributed’ part of our database means that identical copies of data are distributed over multiple independent servers, where the true independent nature depends on how you place the ‘independent servers’, which we call masters and tablet servers.
The essence is that with setting the replication factor to 3, which is the most common used replication factor, gives you 3 independent copies of the data, for which the copies (we call ‘replicas’) are placed on separate servers by the cluster. Additionally, you can use tablespaces to organise placement of the replicas in it. This is HA, and the replication and placement by default is done completely automatic.
A replication factor of 3 gives you 3 copies (replicas), and allows 1 replica to be unavailable and the table to still be available for read and write. If another replica becomes unavailable, there is no majority (quorum), and therefore object becomes unavailable too.
As for your question about using the database in one place, and storing a copy in the cloud, that would typically matches with our xcluster replication, like @dorian_yugabyte mentioned. That allows you to replicate one or more tables from one ‘universe’ to another.