0

I have a MySQL single server setup with all tables using the InnoDB engine (because I need support for foreign keys). As response times are getting longer, I now believe I need to create a cluster of servers to be able to cope with an ever-growing database load.

Is it possible to create a cluster of MySQL servers using my existing databases which use InnoDB? If not, what are my options?

1

Yes and no.

No, there is no way to apply multiple cores or servers to a single InnoDB transaction.

Yes, you can build a cluster with InnoDB. But it is a Galera (PXC or MariaDB) Cluster, which means multiple servers replicating to each other. Overall throughput increases, but response time for individual queries won't. This could help "load", but not "response time". Is your problem really both?

No, InnoDB does not work in NDB Cluster.

Let's look at one of your slow queries. It may be as simple as making a compound index, or reformulating the query. Or, if you are doing Data Warehousing, creating Summary tables. Please provide EXPLAIN SELECT ... and SHOW CREATE TABLE.

Sometimes speeding up the worst query dramatically helps both "load" and "response time", even for other queries.

2

Moving to Galera would be an option. Then your application has to be aware of 'deadlock detected try restarting transaction' errors. Another possibility is to set up a read slave and configure your application to use this slave for read_only queries. If one slave isn't enough you can add more and use them with a loadbalancer of any kind. Examples are maxScale, ha-proxy on application layer or ipvs on transport layer.

1

You should be able to migrate to MySQL Cluster (NDB Cluster), as new versions support foreign keys already and it is easy for scaling the load.

However, as stated by Rick, maybe it would be good first to make sure, if dealing with response times by putting additional servers is the only option here.

Please also note MySQL Cluster keeps all data in memory, it is rather not good fit for big data.

1

As of MySQL 5.7 ( or even better 8.0 ) you can use InnoDB Cluster. Which let you run multiple MySQL instances as a cluster. Which can be running as Single Master: One member of the cluster allows write operations while the rest are in read only mode OR Multi Master: All the members in the cluster support both read and write operations. For further details you can check: MySQL manual

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.