2

We currently have a database running in production that's using MySQL 5.5 with InnoDB tables.

As an evaluation, I've setup a MySQL Cluster (7.3) with MySQL (5.6) which has 2 Data Nodes, 2 SQL Nodes, and 1 Mgm Node; all on separate machines. All of the tables now use NDB (ndbcluster) rather than InnoDB.

The original intention was to provide redundancy with a distributed system that could serve data globally and efficiently.

However, the efficiency (performance) is greatly impacted. Am I mistaken in thinking that multi-table queries will ever be close in performance to InnoDB?

paiego
  • 253
  • 2
  • 8
  • "could serve data globally" you had your data nodes on the same local network right? – KCD Oct 12 '15 at 04:56

2 Answers2

2

If you have only InnoDB tables in your system, I would recommend using MySQL Galera Cluster for replication. It doesn't have the same restrictions as the NDBCluster tables have in MySQL Cluster version

Tero Kilkanen
  • 34,499
  • 3
  • 38
  • 58
  • 1
    Percona XtraDB cluster is a good option, as well. Uses Galera under the hood. – ceejayoz Feb 27 '14 at 19:36
  • @Tero: Have you used Galera? The issue I have with MySQL Cluster is query latency, which is a result of Sharding and Data Nodes being distributed on a network, rather than the InnoDB engine. It appears that Galera's nodes all store data on the node's file system, as a typical mysqld does. Is this correct? – paiego Feb 27 '14 at 19:49
  • 1
    @paiego that's correct. Every node in a galera cluster has all the data, and does not need to communicate with the other nodes at all on `SELECT` queries. An isolated node, however, will by design refuse to answer queries, because it assumes its data is stale. With 3 nodes, and a 2/1 network isolation split, the 2 work normally and the 1 will stop answering. If you really want only 2 nodes, a 3rd dummy node can be set up so that on an isolation, the server that can still see the dummy will stay online since the logic is based on a quorum... but 3 full nodes is preferable. – Michael - sqlbot Feb 27 '14 at 23:22
  • I have used Galera in two production servers for over a year, and it has been working fine. There were a couple of issues early on, but they were fixed in the code. – Tero Kilkanen Feb 28 '14 at 01:52
1

As far as I know, If you want to use MySQL Cluster, you must change engine to 'ndbcluster'. InnoDB tables will be stored on SQL nodes locally.

incous
  • 136
  • 4
  • Yes, InnoDB was the original DB's engine and I needed to change all of the tables to NDB (ndbcluster) in order for them to work in the cluster; become stored in the Data Nodes. I'm seeing a significant performance hit. – paiego Feb 27 '14 at 19:31