How does MySQL Cluster determine which data nodes to search for a SELECT query?

Question

I'm researching how to resolve a situation where a client needs all data for a particular customer (and only the data for that customer) to be stored on a geographically disparate database server.

For example, all data should be stored in database servers on the main cloud, except for all data relating to customer ID 92, which should be stored in servers on a different cloud in another location.

There are a couple of constraints I am working with that are making this a little tricky, but so far, MySQL Cluster seems like the best approach.

However, it is unclear to me how it selects data nodes when executing queries. E.g., if I were to submit a query that did not require any data for customer ID 92, would it still ping data nodes in the other cloud and introduce latency?

How does MySQL Cluster determine which data nodes to search during a SELECT query? Are there ways that I can hint in a query that certain data nodes can be ignored?

Answer 1

Ouch.. that is not how MySQL Cluster works.

By default MySQL Cluster partitions data on the PRIMARY KEY. It is however possible to use user-defined partitioning and partition on part of the PRIMARY KEY. This is extremely useful to group related data together and to ensure locality of data within one partition. Since related data is then kept in one partition it is then possible to scale from 2 to 48 data nodes without sacrificing performance - it will be constant. See more details at http://dev.mysql.com/doc/refman/5.5/en/partitioning-key.html

By default, the API will calculate a hash (using the LH3* algorithm, which uses md5) on the PRIMARY KEY (or the used defined part of the primary key) to determine which partition to send a query. The hash calculated is 128 bits, and 64 bits determines the partition and 64 bits determines the location in a hash index on the partition. As a user you don't have the insight exactly which node that has the data (or who will store the data), but practically it does not really matter.

Regarding the original question about distributing one MySQL Cluster across 2 clouds and partitioning data. Data Nodes need reliable low latency access to each other, so you would not want to spread the nodes out unless they are under 50-100 miles from each other.

Answer 2

First note that Mysql Cluster is not designed for WAN, usually the best to have less than 20ms propagation delay between your nodes.

Mysql Cluster does sharding (evenly distributes the data between data nodes) + replication ( every fragment of data stored twice).

So simple table like,

| test  | CREATE TABLE `test` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `v1` char(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=1871780 DEFAULT CHARSET=latin1

If you check the information_schema, you will see partitions for this table

mysql> select partition_name,table_rows from information_schema.PARTITIONS where     table_name='test' and table_schema='test1';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |     518667 |
| p1             |     518900 |
| p2             |     517385 |
| p3             |     519050 |
+----------------+------------+
4 rows in set (0.02 sec)

Partition p0,p2 stands for data node 1, and p1,p3 for node 2. The data is distributed based on the PRIMARY KEY (or and artificial key, if now primary key defined).

Select chooses the node to read from based on this partitioning, so if you use explain

mysql> explain partitions select id,v1 from test where id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: p3
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

mysql> explain partitions select id,v1 from test where id=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: p2
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL

The record for id=92 will be read from only one of the data nodes (may the geographical distributed one), but unfortunately it is not just for id 92.

The best is to create a separate table for customer id 92 (on a separate node), and rewrite your application to read from that table/node. To have a solution transparent to the app, you might use Mysql Proxy

Answer 3

Hi I'm afraid the answer is "no". MySQL cluster is sometimes called sharding but it's really not... It's arbitrary distribution of data from every table by the PK with no control and no thinking about which data is accessed together and which nodes are needed for every access and every query or transaction.

Sharding, and a good data distribution policy is one that keeps data that is accessed together, on the same database, so when a transaction needs data, it'll end-up using this 1 DB, processing (join, group) will be pushed to this database (closer to the data, good!) and other databases will be left to deal with other transactions (and there are many.......).

So we get 2 things from storing together-accessed data on one database:

1. Less latency for queries/transaction needing this data and finding it on 1 node
2. Queries/transaction are distributed, not multiplied on all databases

So if I understand your question, this is what you want to achieve, MySQL Cluster can't give that, if it's OK for now, it will come back and bite you when data/concurrency/writes grow.....

You probably need a good old sharding, or today there are tools that actually automates sharding process (disclaimer: I work for ScaleBase, one option for that, using just databases required, and yes, also supporting hints (usually not needed) ).