4

I'm having a MySQL dual-master Replication of MySQL, each one of the servers have 21 GB memory, I'm now searching for a clustering solution for this project, It's possible to have 6 server: two of them with 20 GB of RAM and others with 10 GB of RAM. in the MySQL Cluster documentation it has written below formula for total memory and each node RAM, It seems that I need much more RAM of my existing Replication, and It's hard to make my company agree with this kind of RAM requirement, besides the purpose of clustering is to instead of making a big server use smaller (in resources) servers attached to each other but with this formula each node of clustering is much bigger than my existing servers.

If designing a completely new database, the following calculations can be used to help determine the approximate memory sizing requirements for the data nodes:

• (in memory) Data Size * Replicas * 1.25 = Total Database Memory Requirements Example: 50 GB * 2 * 1.25 = 125 GB

• (Data Size * Replicas * 1.25)/Nodes = RAM Per Node Example: (2 GB * 2 * 1.25)/4 = 31.25 GB

In this situation I have some questions :

  • Is it necessary to obey from these formulas? can I use less RAM for nodes?
5
  • Why are you moving away from your existing setup? Have you considered Galera (in PXC, MariaDB, etc)?
    – Rick James
    Mar 14, 2016 at 1:15
  • @RickJames : I'm searching for a solution, it seems I should select between Galera cluster and Mysql Cluster but still I'm searching. you think that Galera can be better solution?
    – amir jj
    Mar 14, 2016 at 9:07
  • "Searching for a solution" -- a solution to what? Clustering for the sake of clustering? Read scaling? Write scaling? HA? Sharding? All of the above? Something else?
    – Rick James
    Mar 14, 2016 at 18:46
  • Sorry I didn't mentioned, I need a solution for Write/Read scaling and HA .
    – amir jj
    Mar 15, 2016 at 5:46
  • Please elaborate on "write scaling"; there are often workarounds. (And that is the toughest of your 3 requirements.)
    – Rick James
    Mar 15, 2016 at 5:48

1 Answer 1

6

When evaluating clustered solutions you need to think about the read/write profile of your application and match that against available solutions.

For a heavy read based profile of an application, you would be able to scale up reads by having multiple MySQL replicas off of your MySQL master-master replication setup.

For write heavy profile, you are implementing some of the obvious choices in master-master replication.

When choosing between clustered MySQL options there are two technology options: NDB based Oracle MySQL Cluster and Galera based options from Percona (XtraDB Cluster) and MariaDB (MariaDB Cluster), and Galera Cluster for MySQL. They are architected differently and have different advantages and disadvantages.

A good webinar on the distinction is here:

http://severalnines.com/blog/galera-cluster-mysql-vs-mysql-ndb-cluster-high-level-comparison-webinar-replay-slides

The formula mentioned is primarily used in measuring the projected per node requirements for a NDB cluster. Originally MySQL Cluster, previously NDB Cluster, was a memory only database, which required all of the data to reside in the memory of available nodes. If all your data was required to stay in memory, then if you didn't enough RAM to hold all your data, you are under provisioned in terms of hardware.

Nowadays, each node in MySQL cluster, namely a data node, is a MySQL instance. Even though you should keep your data in memory for optimal performance, it is not required. So, basically if you don't "obey" the formulas, you will end up dropping down to disk and would have to deal with all the IO performance of the particular hardware available to your nodes.

1
  • I wouldn't necessarily recommend it, but another technology option which is available now (in 2018) is MySQL InnoDB Cluster, which I believe is similar in principle to Galera cluster.
    – dbdemon
    Apr 10, 2018 at 22:05

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.