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."
