Bug #84209 Number of partitions in MySQL Cluster
Submitted: 15 Dec 2016 0:50 Modified: 16 Feb 2017 11:13
Reporter: Abdel-Mawla Gharieb Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Documentation Severity:S3 (Non-critical)
Version:7.2+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: LDM, ndb, partitions

[15 Dec 2016 0:50] Abdel-Mawla Gharieb
Description:
The documentation states that the number of partitions is equal to the number of data nodes in the cluster which is not true in all cases:
http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-nodes-groups.html
"Partition.  This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster"

In fact, the number of partitions is actually affected by the number of LDM threads as well as the number of data nodes in the cluster.

How to repeat:
1- Creates a MySQL Cluster setup (mine consists of 6 datanodes, 3 sql nodes and a management node) and use ndbmtd on the data nodes.
2- Starts the cluster with the default value for "MaxNoOfExecutionThreads" (the default is 2 and that means LDM=1).
3- Creates a simple table and check its partitions.
4- Change MaxNoOfExecutionThreads to 4 (LDM=2) and restart the cluster.
5- Creates another simple table and check its partitions. (the number if partitions will be affected by the increase of the number of LDM threads).
6- repeat steps 4 and 5 with different values of MaxNoOfExecutionThreads and the partitions number will increase.

The following are my test cases on a 6 datanodes cluster:

1- MaxNoOfExecutionThreads=2 (LDM=1):

mysql> create table partitions.t1 (id int(11) auto_increment primary key, name varchar(20) default null)engine=ndbcluster;
Query OK, 0 rows affected (0.19 sec)

mysql> explain select * from partitions.t1;
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3,p4,p5 | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

2- MaxNoOfExecutionThreads=4 (LDM=2):

mysql> create table partitions.t2 (id int(11) auto_increment primary key, name varchar(20) default null)engine=ndbcluster;
Query OK, 0 rows affected (0.18 sec)

mysql> explain select * from partitions.t2;
+----+-------------+-------+---------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                            | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+---------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t2    | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11 | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+---------------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

3- MaxNoOfExecutionThreads=8 (LDM=4):

mysql> create table partitions.t3 (id int(11) auto_increment primary key, name varchar(20) default null)engine=ndbcluster;
Query OK, 0 rows affected (0.16 sec)

mysql> explain select * from partitions.t3;
+----+-------------+-------+---------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                                                                            | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+---------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t3    | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23 | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+---------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

4- MaxNoOfExecutionThreads=16 (LDM=8):

mysql> create table partitions.t4 (id int(11) auto_increment primary key, name varchar(20) default null)engine=ndbcluster;
Query OK, 0 rows affected (0.23 sec)

mysql> explain select * from partitions.t4;
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                                                                                                                                                                            | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t4    | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47 | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec).

Note:
The warnings are just how the optimizer will execute the statements.

Suggested fix:
The statement in the documentation:
"There are as many cluster partitions as nodes participating in the cluster"
should be changed to something like:
"The number of partitions in the cluster can be determined by the formula:
Number of partitions  = LDM x Number of Data nodes."
[15 Dec 2016 18:00] Abdel-Mawla Gharieb
The documentation is correct until the release of ndbmtd where LDM can have values greater than 1
[16 Dec 2016 12:41] MySQL Verification Team
Hi,

Thanks for your report. Since the introduction of the ndbmtd we are lagging a bit in changing all the aspects that changed with it. 

The bug is verified and documentation should be updated soon.

best regards
Bogdan
[16 Dec 2016 21:52] Abdel-Mawla Gharieb
Thanks for the verification!

Abdel-Mawla
[15 Feb 2017 23:03] Jon Stephens
Thanks for bringing this to our attention. I'll try to have this fixed in the next day or two.
[16 Feb 2017 11:13] Jon Stephens
Thanks a lot for the good bug report. 

Fixed in the docs for NDB 7.2 and newer, in mysqldoc rev 50756.

Changes should appear online within the next day or so.

Closed.