Mysql – Will InnoDB or NDB cluster boost performance

clusterinnodbMySQLmysql-cluster

We have fairly large MySQL database (about 35GB total) and topping about 900qps. Performance is not a big issue for now, but the project is growing constantly and I'd rather start thinking about optimisation beforehand.

I recently learned about InnoDB/NDB cluster solutions, where you can add at least 3 servers for database fault tolerance, but i was wondering, will it improve overall performance as there are multiple servers involved in handling the traffic?

The project basically is public advertisement platform, and has only a few central tables that receive most traffic – users, ads and some more. I cannot point a single weak point, there's lots of different things that can be optimized with infrastructure (for example there is internal instant messaging between users – I plan to test how it will perform with MongoDB, I think messages suit this database architecture well)

Here is the query statistics for database server:

query statistics

Best Answer

There are tradeoffs. Multiple writable servers in a cluster still need to send all the writes to all the other machines. Reads benefit from clustering and/or Slaves. Writes benefit only slightly from clustering. (Sharding is the real solution for write scaling.)

Even adding two or more Slaves onto a single Master helps writes some. This is because the reads are now spread across at least the two Slaves, thereby competing less with the writes.

When growing fast:

  • If the disk becomes more than half full, watch out. If you need to ALTER a large table, it may need enough space for a full copy. And you don't want to run out of disk space.
  • Dumps and ALTERs take longer and longer.
  • Watch the writes (see above)
  • Usually there are slow queries even in the best-tuned systems. They will raise their ugly heads as you grow.
  • At some point (perhaps before 35G, perhaps long after 35G), the amount of RAM (think "innodb_buffer_pool_size" and NDB's equivalent) will become a serious concern. Often that can be postponed by avoiding table scans and other 'optimizations'.
  • Watch out for long-running transactions. Even 1 second may have an impact on your 900 qps.
  • If you have "spikes", they likely portend worse things to come.

If you are currently using InnoDB, you will find that there are several structural changes to move to NDB. The Transaction model is quite different ("eventual consistency"). Depending on the type of app, the could be a problem, or even a "brick wall".

Within just MySQL/MariaDB, there is "InnoDB Cluster" (MySQL 8.0) and Galera Cluster (PXC, MariaDB). They are possibly equally "good".

All of the above need at least 3 machines, preferably spread across at least 3 data centers. (Yes, datacenters can go down.)

Perhaps I can say more if you provide more details on your app. Even SHOW CREATE TABLE for your largest table may provide a lot of insight.

If you like, follow the instructions here ; I may be able to help you scale some more before changing topology or hardware.

More

Since nearly all queries are SELECTs, any replication or clustering topology can help you scale. Any number of Slaves can provide any amount of scaling. Those slaves can hang off a single Master (or InnoDB Cluster) or the 3 nodes of a Galera Cluster. NDB works differently, but can also be arbitrarily scaled for reading.

So the simple answer to your question is "yes". As for which solution, the is nothing (yet) to say which solution would be better for you. Perhaps the best that can be said is "Pick a solution and run with it."

Related Topic