Add datanodes to MySQL cluster

1. change config.ini
[NDBD]
# the IP of the SECOND SERVER (Data Node)
HostName=db3.altium.local
NodeId=11

[NDBD]
# the IP of the SECOND SERVER (Data Node)
HostName=db4.altium.local
NodeId=12

#add below lines:
[NDBD]
# the IP of the SECOND SERVER (Data Node)
HostName=db3.altium.local
NodeId=13

[NDBD]
# the IP of the SECOND SERVER (Data Node)
HostName=db4.altium.local
NodeId=14

I reduced the data memory and index memory since I ran 2 data nodes on each physical server


2. restart all mgmt nodes with --reload or --initial
ndb_mgm db1:1286
ndb_mgm> show
[ndbd(NDB)] 4 node(s)
id=11 @10.134.98.164 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0, Master)
id=12 @10.134.98.165 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0)
id=13 (not connected, accepting connect from db3.altium.local)
id=14 (not connected, accepting connect from db4.altium.local)


3. restart all exist datanodes
ndb_mgm> 11 restart
Node 11: Node shutdown initiated
Node 11: Node shutdown completed, restarting, no start.
Node 11 is being restarted

validate it has been restarted
ndb_mgm> all status
Node 11: started (mysql-5.1.56 ndb-7.1.15)
Node 12: started (mysql-5.1.56 ndb-7.1.15)
Node 13: not connected
Node 14: not connected

ndb_mgm> 12 restart

ndb_mgm> all status
Node 11: started (mysql-5.1.56 ndb-7.1.15)
Node 12: started (mysql-5.1.56 ndb-7.1.15)
Node 13: not connected
Node 14: not connected

4. Perform a rolling restart of all cluster SQL nodes


5. Perform an initial start of the new data nodes.

6. validate
tablespaces and log file groups were created on the new nodes:

SELECT TABLESPACE_NAME, extra,
ROUND(SUM(total_extents*EXTENT_SIZE)/1024/1024) total_MB,
ROUND(SUM((total_extents-free_extents)*EXTENT_SIZE)/1024/1024) used_MB
FROM INFORMATION_SCHEMA.FILES
WHERE file_type = 'DATAFILE'
GROUP BY tablespace_name,extra;

+-----------------+-----------------+----------+---------+
| TABLESPACE_NAME | extra | total_MB | used_MB |
+-----------------+-----------------+----------+---------+
| ts1 | CLUSTER_NODE=11 | 4224 | 0 |
| ts1 | CLUSTER_NODE=12 | 4224 | 0 |
| ts1 | CLUSTER_NODE=13 | 4224 | 0 |
| ts1 | CLUSTER_NODE=14 | 4224 | 0 |
+-----------------+-----------------+----------+---------+

ndb_mgm> all status;
Node 11: started (mysql-5.1.56 ndb-7.1.15)
Node 12: started (mysql-5.1.56 ndb-7.1.15)
Node 13: started (mysql-5.1.56 ndb-7.1.15)
Node 14: started (mysql-5.1.56 ndb-7.1.15)

The nodegroups were not allocated

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=11 @10.134.98.164 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0, Master)
id=12 @10.134.98.165 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0)
id=13 @10.134.98.164 (mysql-5.1.56 ndb-7.1.15, no nodegroup)
id=14 @10.134.98.165 (mysql-5.1.56 ndb-7.1.15, no nodegroup)


6. create a new nodegroup
ndb_mgm> CREATE NODEGROUP 13,14;
Nodegroup 1 created
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=11 @10.134.98.164 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0, Master)
id=12 @10.134.98.165 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0)
id=13 @10.134.98.164 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 1)
id=14 @10.134.98.165 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 1)

Notes, 13,14 are the nodeid, the number of new nodegroup will be automatically created.


Replica is always 2 which means we have one backup, then there are 2 datanodes in each nodegorup, they are the backup to each

other.
nodegroups can be added for IO scale out, we can access the a table when there is a started datanode in all nodegroups.
partions are the implementation of the replica and IO scale out. each datanode has 2 partions where Replica is 2.


2 data nodes configure:
It has a nodegroup, each table has 2 same partions. any datanode has full set of data.

4 data nodes configure:
It has 2 nodegroups, each table has 4 partions. partion 0, 2 are in nodegroup 0; and partion 1, 3 are in nodegrup 1. datanodes in

the same nodegroup have exact same data.



7. Redistribute cluster data.
Existing data and indexes are not automatically distributed to the new node group's data nodes yet:

SELECT node_id,memory_type,
ROUND(total/1024/1024) total_mb,
ROUND(used/1024/1023) used_MB
FROM ndbinfo.memoryusage
GROUP BY node_id, memory_type;
+---------+--------------+----------+---------+
| node_id | memory_type | total_mb | used_MB |
+---------+--------------+----------+---------+
| 11 | Data memory | 7000 | 2935 |
| 11 | Index memory | 500 | 159 |
| 12 | Data memory | 7000 | 2935 |
| 12 | Index memory | 500 | 159 |
| 13 | Data memory | 7000 | 2 |
| 13 | Index memory | 500 | 0 |
| 14 | Data memory | 7000 | 2 |
| 14 | Index memory | 500 | 0 |
+---------+--------------+----------+---------+
ALTER ONLINE TABLE Blog.tblBlogs REORGANIZE PARTITION

+---------+--------------+----------+---------+
| node_id | memory_type | total_mb | used_MB |
+---------+--------------+----------+---------+
| 11 | Data memory | 7000 | 2935 |
| 11 | Index memory | 500 | 159 |
| 12 | Data memory | 7000 | 2935 |
| 12 | Index memory | 500 | 159 |
| 13 | Data memory | 7000 | 3 |
| 13 | Index memory | 500 | 0 |
| 14 | Data memory | 7000 | 3 |
| 14 | Index memory | 500 | 0 |
+---------+--------------+----------+---------+


get the partition informaiton of a table
ndb_desc -c db1:1286 -p -d catalog CAT_SItemPricing
before alter
-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory Extent_space Free extent_space
1 42 42 32768 32768 0 0
0 42 42 32768 32768 0 0

after alter
-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory Extent_space Free extent_space
1 23 80 32768 32768 0 0
3 19 19 32768 32768 0 0
2 22 22 32768 32768 0 0
0 20 86 32768 32768 0 0


Release memory from old datanodes:
ALTER ONLINE TABLE catalog.CAT_SItemPricing OPTIMIZE PARTITION all;
+---------+--------------+----------+---------+
| node_id | memory_type | total_mb | used_MB |
+---------+--------------+----------+---------+
| 11 | Data memory | 7000 | 2862 |
| 11 | Index memory | 500 | 152 |
| 12 | Data memory | 7000 | 2862 |
| 12 | Index memory | 500 | 152 |
| 13 | Data memory | 7000 | 169 |
| 13 | Index memory | 500 | 10 |
| 14 | Data memory | 7000 | 169 |
| 14 | Index memory | 500 | 10 |
+---------+--------------+----------+---------+


8. doing it in batch
--get a list of tables
SELECT table_schema,table_name, data_length

FROM information_schema.tables
WHERE ENGINE ='ndbcluster'
AND table_type='BASE TABLE'
ORDER BY data_length;



tee reort.sql
SELECT concat('ALTER ONLINE TABLE ',table_schema,'.',table_name,' REORGANIZE PARTITION;')

FROM information_schema.tables
WHERE ENGINE ='ndbcluster'
AND table_type='BASE TABLE'
ORDER BY data_length;

SELECT concat('ALTER ONLINE TABLE ',table_schema,'.',table_name,' OPTIMIZE PARTITION ALL;')

FROM information_schema.tables
WHERE ENGINE ='ndbcluster'
AND table_type='BASE TABLE'
ORDER BY data_length;


mysql -u root < reorg.sql > tmp.sql
nohup mysql -u root < tmp.sql &