Mysql – How Many Nodes For MySQL Cluster Setup

MySQLmysql-cluster

I quit not fully understand how exactly MySQL Cluster does to bring faster queries and faster databases operations.

I have

  • 1 MGM cluster (management cluster)
  • 1 MySQL API cluser (mysqld)
  • 2 Storage clusters (node database)

To get fast queries, should I add more storage clusters or more MySQL API?

Thanks.

Best Answer

The primary purpose of MySQL Cluster is not performance -- it's high availability. By making sure every component has a possibility of failover (though not in your case), you ensure that you can remain operating without interruption even through emergencies and crashes. Unless your whole data center goes down, of course.

Though MySQL Cluster also tends to give benefits to performance, because the more storage nodes you have, the more you split up your data. Searching half as much data for a given row is faster. Searching a quarter as much data (if you add two more storage nodes) is even faster.

Though this works best if one of your searches naturally touches data on only one storage node. If you need to run range queries, the query will have to visit many storage nodes, and then gather the results in the MySQL node. This can actually be worse for performance than a conventional, non-cluster architecture.

So it's important to keep in mind that your application and queries have to be designed carefully to take advantage of MySQL Cluster (this also applies to any other sharded architecture). For example, an app that always accesses individual rows, instead of arbitrary sets of rows. If you just port your existing database to MySQL Cluster without this in mind, you can (and likely will) see performance get worse, not better.

One other way MySQL Cluster offers a performance boost: it stores all data in RAM on all the storage nodes. RAM is about 100,000 times faster than disk, so even as you fetch data over a network, it can be a lot faster than accessing a disk in a conventional setup. I worked with a performance architect who said, "remote memory is faster than local disk."

Related Question