What is the equivalent in mysql cluster for global table in mysql fabric?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

What is the equivalent in mysql cluster for global table in mysql fabric?

JohnXhark
Hi,
What is the equivalent in mysql cluster for global table in mysql fabric, i.e. for table that are too small to need any partition/sharding?
My guest is just define a table of innodb engine and copy it to every sql node. But is this correct?
Can tables of different engines be joined in a mysql node?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: What is the equivalent in mysql cluster for global table in mysql fabric?

Mauritz Sundell
Hi John

On 2016-10-27 16:07, JohnXhark wrote:
> Hi,
> What is the equivalent in mysql cluster for global table in mysql fabric, i.e. for table that are too small to need any partition/sharding?
With MySQL Cluster 7.5.4 GA we have fully replicated tables.

These tables will have all its data on all nodes in the cluster, and is
a good choice if one got relatively small and static tables.

Default for these tables (as for other tables) is to have one partition
per LDM thread in a node.
If the tables are really small it can be meaningful to only have one
partition for the table which also can be configured in 7.5.4, but
normally it is better too keep the default.
Note that in this case the table will only use one LDM thread per node
and could be a source of imbalanced use of cpu-cores

Writes to a fully replicated table will take slightly longer time than
for "normal" tables since updates must be distributed to all nodes
before transactions are regarded as committed, but normally it should
still be fast depending on network latencies.

To create a fully replicated table add a special table comment like:

CREATE TABLE ... ENGINE=NDBCLUSTER COMMENT='NDB_TABLE=FULLY_REPLICATED=1';

If you further want to minimize the number of partitions you can also
set PARTITION_BALANCE like:

CREATE TABLE ... ENGINE=NDBCLUSTER
COMMENT='NDB_TABLE=FULLY_REPLICATED=1,PARTITION_BALANCE=FOR_RA_BY_NODE';

See also:
[1]
https://dev.mysql.com/doc/refman/5.7/en/create-table-ndb-table-comment-options.html
[2]
http://mikaelronstrom.blogspot.se/2016/10/read-any-replica-in-mysql-cluster-75.html

> My guest is just define a table of innodb engine and copy it to every sql node. But is this correct?
You can do, but then you do not use ndbcluster.
And also you need to setup distribution of table data to every sql node
by your self.
Better use fully replicated tables!
> Can tables of different engines be joined in a mysql node?
They can, but especially if ndbcluster is involved it is better to only
join ndbcluster-tables since they in many cases can be pushed down to
the ndb cluster and by that avoid lots of unnecessary network traffic.
And with use of fully replicated (dimension) tables the joins will
become local within a data node and even more network traffic can be
avoided.
And also this will automatically distribute updates to all nodes in a
ACID way.

So for small tables with few updates often used in joins, use fully
replicated tables.
For bigger tables which often is read use read backup tables
(COMMENT='NDB_TABLE=READ_BACKUP=1').
For other tables with much write start using read backup, if writes are
experienced too slow one can turn of read backup later (which is an
online operation without data copying).

Regards.
Mauritz

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Loading...