MySQL Cluster Setup

[whoo_kid]

I need help regarding hardware requirements for a mysql cluster setup.
I'll be creating 4 VMs. 1 for mysql server node and manager, 1 for the mysql cluster data node and 2 data additional data nodes.

The db will primarly be used for osTicket, so there will be high read and write operations and am expecting the database size to reach 500GB within 4-5 years based on our current osTicket setup that I'm replacing.
For each of the data nodes I'll be provisioning an Ubuntu server with 16GB RAM, 2 CPUs with 4 cores and 1TB disk space.

The current setup is 1VM (mysql + Apache app server) with 2 CPUs + 4 Cores, 32GB RAM, 500GB disk

Is this an ideal setup for this?
It's the first time I'm creating a mysql cluster so I would like to make sure I get it right the first time.
I intend to add another cluster for HA in the future.

 

[Ancalagon]

My advice would be to use VMs and conduct benchmarks first. Work out whether your workload is primarily CPU bound, IO bound or memory bound. Chances are good that it is IO bound, but you need to verify this.

When you say that the data nodes will each have 2 CPUs with 4 cores - I'm confused by this. With VMs, you can't do this. They use vCPUs, which are usually equivalent to one thread of an SMT capable CPU.

1TB disk space - SSD? Hopefully SSD.

How many writes per second do you expect? If you forecast 500GB within 4-5 years (which is pretty low btw), you might be able to get away with cheaper hardware. With the writes per second known, you'll have a better idea of the peak demand on your cluster, and what you can get away with.

Then you need to have a backup strategy in place. And a growth strategy - what happens if your estimate of 500gb in 4 years is too low?

Any latency requirements? Latency is affected by synchronous replication.

Lastly, consider something like CockroachDB. It is designed as a distributed database, the nice thing about it is that it scales horizontally pretty well. You just add nodes to the cluster and it handles replication for you. Pretty nice. Generally pretty fast too. It isn't your typical relational database though - it uses RocksDB for its data storage, and the SQL dialect it uses is pretty close to (but not identical to) PostgreSQL.

 

[_kabal_]

have you done a TCO analysis on using something like AWS RDS?