Vassili Jouikov
My experiences and learnings
Adding two ndb nodes to MySQL Cluster
In the end we’ll have:
- Two – Management servers (mgm1, mgm2)
- Two – Application servers (app1, app2)
- Four – NDB servers (ndb1, ndb2), (ndb3, ndb4)
// Start by writing 1,000,000 records to verify distribution between ndb servers
// Create a table for the test
DROP TABLE IF EXISTS clusterdb.bigdata; CREATE TABLE clusterdb.bigdata ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, note TEXT NOT NULL, DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=NDBCLUSTER COMMENT='test cluster table'; |
// Procedure for writing one million records
DELIMITER // DROP PROCEDURE IF EXISTS clusterdb.doMl // CREATE PROCEDURE clusterdb.doMl() BEGIN SET @inx = 0, @total = 1000000; REPEAT INSERT INTO clusterdb.bigdata SELECT NULL, CONCAT('Our string #',@inx), NULL; SET @inx = @inx + 1; UNTIL @inx > @total END REPEAT; END; // DELIMITER ; |
// Launch procedure on app1
mysql> CALL clusterdb.doMl(); Query OK, 0 ROWS affected (18 MIN 38.19 sec) mysql> SELECT COUNT(*) FROM clusterdb.bigdata; +----------+ | COUNT(*) | +----------+ | 1000001 | +----------+ 1 ROW IN SET (0.00 sec) |
// Verifying distribution
mgm1:/usr/local/mysql# bin/ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> all report memory Connected to Management Server at: localhost:1186 Node 11: Data usage is 4%(3217 32K pages of total 65536) Node 11: Index usage is 2%(1879 8K pages of total 64032) Node 12: Data usage is 4%(3217 32K pages of total 65536) Node 12: Index usage is 2%(1879 8K pages of total 64032) |
// Append in /etc/hosts on all machines
192.168.1.107 ndb3 # ndb Server 3 192.168.1.108 ndb4 # ndb Server 4 |
// All machines should have the following in /etc/hosts
192.168.1.101 mgm1 # Management server 1 192.168.1.108 mgm2 # Management server 2 192.168.1.105 app1 # Application Server 1 192.168.1.109 app2 # Application Server 2 192.168.1.103 ndb1 # ndb Server 1 192.168.1.104 ndb2 # ndb Server 2 192.168.1.107 ndb3 # ndb Server 3 192.168.1.110 ndb4 # ndb Server 4 |
// Preparing ndb3 & ndb4 – copy mysql.tgz (see previous post)
// Login to ndb3 & ndb4 server and extract file
mgm1: # tar xzf mysql.tgz -C /usr/local/mysql |
// Append to config.ini on mgm1 & mgm2 server for 4G memory
[ndbd] NodeId = 13 HostName = ndb3 DataDir = /usr/local/mysql/mysql-cluster LockExecuteThreadToCPU = 1 LockMaintThreadsToCPU = 0 [ndbd] NodeId = 14 HostName = ndb4 DataDir = /usr/local/mysql/mysql-cluster LockExecuteThreadToCPU = 1 LockMaintThreadsToCPU = 0 |
// restart mgm1 & mgm2 server
mdm1:/usr/local/mysql# ./ndbmgm_daemon stop NDB_MGM daemon stopped. mgm1:/usr/local/mysql# ./ndbmgm_daemon initial MySQL Cluster Management Server mysql-5.6.11 ndb-7.3.2 NDB_MGM daemon started mgm1:/usr/local/mysql# bin/ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 4 node(s) id=11 @192.168.200.103 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0) id=12 @192.168.200.104 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0, Master) id=13 (not connected, accepting connect from ndb3) id=14 (not connected, accepting connect from ndb4) [ndb_mgmd(MGM)] 2 node(s) id=1 @192.168.200.101 (mysql-5.6.11 ndb-7.3.2) id=8 @192.168.200.108 (mysql-5.6.11 ndb-7.3.2) [mysqld(API)] 2 node(s) id=31 @192.168.200.105 (mysql-5.6.11 ndb-7.3.2) id=32 @192.168.200.109 (mysql-5.6.11 ndb-7.3.2) |
// Should look like the above
// Restart id=11 & id=12
ndb_mgm> 11 restart Node 11: Node shutdown initiated Node 11 is being restarted ndb_mgm> Node 11: Started (version 7.3.2) ndb_mgm> 12 restart Node 12: Node shutdown initiated Node 12 is being restarted ndb_mgm> Node 12: Started (version 7.3.2) |
// Restart app1 & app2
app1:/usr/local/mysql# ./mysqld restart Shutting down MySQL ..... SUCCESS! Starting MySQL .... SUCCESS! app2:/usr/local/mysql# ./mysqld restart Shutting down MySQL ..... SUCCESS! Starting MySQL .... SUCCESS! |
// Start with initial ndb3 & ndb4 servers
ndb3:/usr/local/mysql# ./ndb_deamon initial 2013-08-12 19:02:00 [ndbd] INFO -- Angel connected to 'mgm1:1186' 2013-08-12 19:02:00 [ndbd] INFO -- Angel allocated nodeid: 13 NDB daemon started on mgm1 initial ndb4:/usr/local/mysql# ./ndb_deamon initial 2013-08-12 19:06:11 [ndbd] INFO -- Angel connected to 'mgm1:1186' 2013-08-12 19:06:11 [ndbd] INFO -- Angel allocated nodeid: 14 NDB daemon started on mgm1 initial |
// Verify mgm server
ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 4 node(s) id=11 @192.168.200.103 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0, Master) id=12 @192.168.200.104 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0) id=13 @192.168.200.107 (mysql-5.6.11 ndb-7.3.2, no nodegroup) id=14 @192.168.200.110 (mysql-5.6.11 ndb-7.3.2, no nodegroup) [ndb_mgmd(MGM)] 2 node(s) id=1 @192.168.200.101 (mysql-5.6.11 ndb-7.3.2) id=8 @192.168.200.108 (mysql-5.6.11 ndb-7.3.2) [mysqld(API)] 2 node(s) id=31 @192.168.200.105 (mysql-5.6.11 ndb-7.3.2) id=32 @192.168.200.109 (mysql-5.6.11 ndb-7.3.2) |
// Create a group for ndb3 & ndb4
ndb_mgm> create nodegroup 13,14 Nodegroup 1 created ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 4 node(s) id=11 @192.168.200.103 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0, Master) id=12 @192.168.200.104 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0) id=13 @192.168.200.107 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 1) id=14 @192.168.200.110 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 1) [ndb_mgmd(MGM)] 2 node(s) id=1 @192.168.200.101 (mysql-5.6.11 ndb-7.3.2) id=8 @192.168.200.108 (mysql-5.6.11 ndb-7.3.2) [mysqld(API)] 2 node(s) id=31 @192.168.200.105 (mysql-5.6.11 ndb-7.3.2) id=32 @192.168.200.109 (mysql-5.6.11 ndb-7.3.2) |
// Before
ndb_mgm> show ndb_mgm> all report memory Node 11: Data usage is 4%(3217 32K pages of total 65536) Node 11: Index usage is 2%(1879 8K pages of total 64032) Node 12: Data usage is 4%(3217 32K pages of total 65536) Node 12: Index usage is 2%(1879 8K pages of total 64032) Node 13: Data usage is 0%(1 32K pages of total 65536) Node 13: Index usage is 0%(0 8K pages of total 64032) Node 14: Data usage is 0%(1 32K pages of total 65536) Node 14: Index usage is 0%(0 8K pages of total 64032) |
// Alter
ndb_mgm> SHOW mysql> ALTER online TABLE clusterdb.bigdata reorganize partition; Query OK, 0 ROWS affected, 1 warning (1 MIN 8.61 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW warnings; +---------+------+------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'ONLINE' IS deprecated AND will be removed IN a future release. Please USE ALGORITHM=INPLACE instead | +---------+------+------------------------------------------------------------------------------------------------------+ 1 ROW IN SET (0.00 sec) mysql> OPTIMIZE TABLE clusterdb.bigdata; |
// After
ndb_mgm> show ndb_mgm> all report memory Node 11: Data usage is 4%(3217 32K pages of total 65536) Node 11: Index usage is 1%(1107 8K pages of total 64032) Node 12: Data usage is 4%(3217 32K pages of total 65536) Node 12: Index usage is 1%(1095 8K pages of total 64032) Node 13: Data usage is 2%(1539 32K pages of total 65536) Node 13: Index usage is 1%(905 8K pages of total 64032) Node 14: Data usage is 2%(1539 32K pages of total 65536) Node 14: Index usage is 1%(905 8K pages of total 64032) |
// Create another million records and check the distribution across ndb
ndb_mgm> show mysql> call clusterdb.doMl(); Query OK, 0 rows affected (19 min 30.98 sec) mysql> select count(*) from clusterdb.bigdata; +----------+ | count(*) | +----------+ | 2000002 | +----------+ 1 row in set (0.01 sec) ndb_mgm> all report memory Node 11: Data usage is 4%(3218 32K pages of total 65536) Node 11: Index usage is 2%(1874 8K pages of total 64032) Node 12: Data usage is 4%(3218 32K pages of total 65536) Node 12: Index usage is 2%(1874 8K pages of total 64032) Node 13: Data usage is 4%(3130 32K pages of total 65536) Node 13: Index usage is 2%(1849 8K pages of total 64032) Node 14: Data usage is 4%(3130 32K pages of total 65536) Node 14: Index usage is 2%(1849 8K pages of total 64032) |
// All Done!
