Tags

, , , ,

As stated in the MySQL Cluster documentation:

Partition.  This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster. Each node is responsible for keeping at least one copy of any partitions assigned to it (that is, at least one replica) available to the cluster.

According to my understanding for the previous paragraph, if we have a cluster of 6 datanodes we should have 6 partitions for each NDB table. I claim that this is not true for all cases – at least, after the introduction of ndbmtd (Multi-Threaded Daemon) in MySQL Cluster 7.2 .
In this post, I’ll do some test cases to show that the number of partitions in the cluster has a relation with the number of LDM threads as well as the number of data nodes.

Test Cases:

In the following test cases I’ll change the value of MaxNoOfExecutionThreads which in turn will change the value of LDM threads. Check this table for the corresponding LDM threads number for each MaxNoOfExecutionThreads value. Each test case requires a cluster restart (or rolling restart for all cluster nodes) to have the change applied.

MySQL Cluster test configuration:

  • 6 datanodes
  • 3 sqlnodes
  • 1 management node
  • ndbmtd is being used on all data nodes and for all test cases.
  • Default values were being used for most of the variables in config.ini

Test case 1:

MaxNoOfExecutionThreads=2 (i.e. the default value, LDM=1):

  1. Create a simple NDB table:
    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)
  2. Check the number of partitions:
    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)

Note: Number of partitions = 1 x number of the data nodes = 6.

Test case 2:

MaxNoOfExecutionThreads=4 (LDM=2):

  1. Create a simple NDB table:
    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)
  2. Check the number of partitions:
    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)

Note: Number of partitions = 2 x number of the data nodes = 12.

Test case 3:

MaxNoOfExecutionThreads=8 (LDM=4):

  1. Create a simple NDB table:
    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)
  2. Check the number of partitions:
    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)

Note: Number of partitions = 4 x number of the data nodes = 24.

Test case 4:

MaxNoOfExecutionThreads=16 (LDM=8):

  1. Create a simple NDB table:
    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)
  2. Check the number of partitions:
    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: Number of partitions = 8 x number of the data nodes = 48.

Conclusion:

  1. Only when using ndbd or setting the default value for MaxNoOfExecutionThreads, the number of partitions is equal to the number of data nodes participating in the cluster.
  2. The correct equation should be: Number of partitions = LDM threads x number of data nodes in the cluster.
  3. Tables created prior to changing the LDM value will still have the same number of partitions before the change unless you recreate or reorganize them.
  4. I believe that the number of partitions in the cluster has a performance impact but I didn’t confirm that yet. (maybe in a future post)
  5. I filed a bug report (Bug #84209) about this issue if you are interested in following it up.
Advertisements