MySQL Cluster Configuration Best Practices

  • Cluster has no inbuilt security - run ndbd and ndb_mgmd processes on hosts without a public IP address (that is, a private LAN)!
    • You can, for example, connect the cluster and MySQL Server machines using a private switch (better 2 switches for high availability); the MySQL Servers run on hosts connected to the switches, but also have a second network card connected to a possibly less restricted network for access from other machines (e.g., apache servers)
  • A cluster should run on a minimum of 3 hosts for fault tolerance: ndb_mgmd/mysqld on 1 machine, ndbds on the other 2 machines.
  • If you have a multi-core machine, you can run 1 ndbd per core on the machine, but leave 1 core for network interrupt processing. For example, on a quad-core, run 3 ndbds, and make sure you have other ndbds on other machines for high availability (if one machine crashes, there is a ndbd in its node group running on another machine).
  • The throughput and latency of reading/writing disk-based columns is much better if they are physically stored on a different disk to the NDB Data Directories.
    • Consider also using a different disk controller to further improve performance.

Frequently asked Questions

How do I convert an existing table to store some of the rows on disk? The long answer is here. A shorter answer for altering the table 'myTable' in database 'test' to store its columns on disk is to do the following: 

$NDB_HOME/scripts/mysql-client-1.sh

# create myTable in database 'test'
mysql> create table test.myTable (id int, name varchar(128)) engine=ndb;
mysql> show create table myTable\G
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `myTable` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql>quit

#
# Want to alter myTable to have disk-based columns:
# 1: Enter single-user mode for cluster.
# 2: Create a tablespace for the columns
# 3: Alter myTable to use the tablespace
# 4. Exit single-user mode
#

# 1: Enter single-user mode for cluster.
$NDB_HOME/scripts/enter-singleuser-mode.sh 3
Connected to Management Server at: localhost:1186
Single user mode entered
Access is granted for API node 3 only.

# 2: Create a tablespace for the columns
$NDB_HOME/scripts/mysql-client-1.sh 
mysql>use test;
mysql> CREATE LOGFILE GROUP lg_1
    ->     ADD UNDOFILE 'undo_1.dat'
    ->     INITIAL_SIZE 16M
    ->     UNDO_BUFFER_SIZE 2M
    ->     ENGINE NDB;
Query OK, 0 rows affected (1.17 sec)

mysql> ALTER LOGFILE GROUP lg_1
    ->     ADD UNDOFILE 'undo_2.dat'
    ->     INITIAL_SIZE 12M
    ->     ENGINE NDB;
Query OK, 0 rows affected (0.81 sec)

mysql> CREATE TABLESPACE ts_1
    ->     ADD DATAFILE 'data_1.dat'
    ->     USE LOGFILE GROUP lg_1
    ->     INITIAL_SIZE 32M
    ->     ENGINE NDB;
Query OK, 0 rows affected (3.19 sec)

# 3: Alter myTable to use the tablespace

mysql> ALTER TABLE myTable TABLESPACE ts_1 STORAGE DISK ENGINE=NDB;
Query OK, 0 rows affected (1.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table myTable\G
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `myTable` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>quit

# 4. Exit single-user mode
>./exit-singleuser-mode.sh
Connected to Management Server at: localhost:1186
Exiting single user mode in progress.
Use ALL STATUS or SHOW to see when single user mode has been exited.