MySQL Cluster On FlashArray Implementation and Best Practices
MySQL NDB Cluster (MySQL Cluster) is a deployment type for MySQL providing high availability and scalability. MySQL Cluster makes use of the NDB/NDBCLUSTER storage engine for MySQL, and separates processes across disparate systems (nodes). There are three different node types:
- Management Nodes - Performs configuration and monitoring of the cluster. This node type will start or restart the cluster. The ndb_mgmd process is responsible for the node type.
- Data Nodes - These nodes store database data. Tables are sharded across all of the data nodes in the cluster resulting in the ability to handle load balancing, replication, failover, and self-healing. The ndbd (single threaded) or ndbmtd (multi-threaded) process is responsible for this node type.
- SQL Node - These nodes connect to the data nodes to store and retrieve data. The mysqld process is used to operate this node type.
More information about MySQL Cluster can be found in the reference documentation.
The operating system requirements are similar to a standalone MySQL Implementation.
Implementing MySQL Cluster on FlashArray
Regardless of the deployment operating system, the following steps will showcase a recommended configuration for MySQL Cluster on FlashArray.
Installing MySQL Cluster
The installation process for the respective operating systems can be found in the MySQL documentation.
Prepare Storage for MySQL Cluster
For each of the node types being deployed the following storage should be provisioned from FlashArray.
Node Type | Storage Requirement | Volumes |
---|---|---|
Management Node |
The management node will store event logs in the form of Cluster Logs and Node Logs. Ensure that the volume size is sufficient to store events as long as they need to be retained. It is possible to set a specific LogDestination but it is recommended that the logs are sent to the same destination as the datadir. |
Per Management Node: 1 x min 128Gb volume. |
Data Node |
Data nodes store persisted database information. Each Data node needs to have (at minimum) a single volume with sufficient space for checkpoint data and redo logs. It is recommended to have a separate volume/location for backups. |
Per Data Node: 1 x data storage volume (1.5x the amount of database data to be stored). 1 x backup volume (Number of retained backups x database data size) The backup volume can be an NFS volume exported from FlashArray or FlashBlade. |
SQL Node |
SQL Nodes only require enough storage capacity for executables and local log files. An SQL Node can store InnoDB data locally but this guide assumes only NDB clustering is required. |
Per SQL Node: 1 x min 50Gb volume. |
As a MySQL cluster operates a Shared-Nothing architecture, the volumes only need to be connected to the respective node types.
Volumes are not shared between nodes thus each node type needs to have its own volume(s).
Once each of the nodes has the relevant storage connected to them, ensure each contains a file system and is mounted at the required locations.
Linux Example file system locations
XFS and EXT4 are both acceptable file systems for FlashArray volumes holding NDB data.
- Management Node: /var/lib/mysql-cluster (DataDir)
- Data Node: /usr/local/mysql/data (DataDir)
- Data Node: /usr/local/mysql/backup(BackupDataDir)
- SQL Node: /var/lib/mysql (dataDir)
Microsoft Windows Example File System Locations
The NTFS filesystem is recommended for FlashArray volumes holding NDB data on Microsoft WIndows.
- Management Node: C:\Users\user1\my_cluster\ndb_data (DataDir)
- Data Node: c:\Users\user1\my_cluster\ndb_data (DataDir)
- Data Node: c:\Users\user1\my_cluster\ndb_backup (BackupDataDir)
- SQL Node: c:\\Users\\user1\\my_cluster\\mysqld_data (dataDir)
Initial MySQL Cluster Configuration
On the management node, create a file (config.ini) on the management node directory intended for datadir.
An example starting configuration can be found in the MySQL documentation.
The following sections and variables can be used for a basic deployment:
Parameter Name | Description | Default Value | Suggested Value |
---|---|---|---|
[ndb_mgmd default]\DataDir | The Data Directory on the management node. | N/A | N/A |
[ndb_mgmd]\HostName |
The hostname of the management node. For multiple management servers duplicate the [ndb_mgmd] section. |
N/A | N/A |
[ndbd default]\NoOfReplicas |
The number of fragment replicas. A minimum of two is required to guarantee the availability of data. |
2 | Min 2 NoOfReplicas for redundancy. Max 4. |
[ndbd default]\DataMemory | This is the amount of memory available to data being stored in the NDB cluster. | 98M | > 1Gb |
[ndbd default]\ NoOfFragmentLogFiles |
Sets the number of REDO log files per node. | 16 | If checkpointing is slow increase this value. |
[ndbd default]\ODirect |
If this parameter is enabled then NDB will attempt to use O_DIRECT writes for storage operations. |
false | Recommended for Linux systems when using EXT4. |
[ndbd default]\ RealTimeScheduler |
If enabled data node threads will be done using real-time scheduling. | false | Enabling real-time scheduling can increase Data Node throughput. |
[ndbd default]\ TimeBetweenGlobalCheckpoints |
Defines the time between global checkpoints in milliseconds. | 2000 | < 3000 |
[ndbd default]\ RedoBuffer |
Sets the size of the buffer in which the REDO log is written. | 32MB | Increase this value for systems with frequent updates to data. |
Only if using ndbmtd affects the number of execution threads on a Data Node. | 2 | Depending on CPU > 16 | |
[ndbd default]\MaxDiskDataLatency |
Controls the maximum allows mean latency for disk access. Recommendation for FlashArray and controlling IO Subsystem pressure: QOS. |
0 | 0 |
[ndbd]\ HostName |
The Hostname or IP Address of the Data Node. | N/A | N/A |
[ndbd]\ NodeId |
(Optional) The Node ID of the Data Node. | N/A | N/A |
[ndbd]\ DataDir |
The Data directory where NDB data will be stored on a Data Node. | N/A | N/A |
[ndbd]\BackupDataDir | The directory in which backups are placed. | DataDir/FilesystemPath |
Path to FlashArray MySQL Cluster Backup Volume for Node for NFS share for Node. Each Node should have its own Backup directory. |
[mysqld]\ HostName |
The Hostname of a SQL Node. | N/A | N/A |
This is an example config.ini file:
[ndb_mgmd default] # Directory for management node log files DataDir=/var/lib/mysql-cluster [ndb_mgmd] # Management Node IP Address HostName=10.21.227.36 [ndbd default] NoOfReplicas=2 # Number of replicas DataMemory=368G # Memory allocate for data storage TimeBetweenGlobalCheckpoints=3000 [ndbd] # Data Node HostName=10.21.227.39 NodeId=2 DataDir=/usr/local/mysql/data BackupDataDir=/usr/local/mysql/backup [ndbd] # Data Node HostName=10.21.227.40 NodeId=3 DataDir=/usr/local/mysql/data BackupDataDir=/usr/local/mysql/backup [mysqld] # SQL Node HostName=10.21.227.37 [mysqld] #SQL Node HostName=10.21.227.38
Initial Startup of the MySQL Cluster
Once the config.ini has been created the cluster can be started.
Step 1. On a Management Node execute the ndb_mgmd command with the confi.ini file path:
ndb_mgmd -f /path/to/config.ini
[root@DB-06 ~]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini MySQL Cluster Management Server mysql-8.0.23 ndb-8.0.23
Step 2. On the Data Nodes execute the ndbd or ndbmtd command to start the respective process after setting the option file with the ndb connection string:
In Linux the option file is located at /etc/my.cnf and in Microsoft Windows it is located at C:\ProgramData\MySQL\MySQL Server <version>\my.ini
[mysql_cluster] # Options for NDB Cluster processes: ndb-connectstring=10.21.227.36 # location of management server
ndbd
ndbmtd
[root@DB-09 ~]# ndbmtd 2021-03-05 00:55:20 [ndbd] INFO -- Angel connected to '10.21.227.36:1186' 2021-03-05 00:55:20 [ndbd] INFO -- Angel allocated nodeid: 2
Ensure the firewall is not blocking the ndbd, ndbmtd, or ndb_mgmd process on the respective node.
Data Nodes communicate with the Management Node on port 118. Ensure this port is open for in the firewall.
Step 3. Start the MySQL process on the SQL Nodes after setting the option file properties for the ndb cluster.
[mysqld] # Options for mysqld process: ndbcluster # run NDB storage engine ndb-use-exact-count=0 ndb-index-stat-enable=0 ndb-force-send=1 optimizer-switch=engine_condition_pushdown=on default_storage_engine=ndbcluster port=3306 max_allowed_packet=1073741824 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql_cluster] # Options for NDB Cluster processes: ndb-connectstring=10.21.227.36 # location of management server
Once the option file has been created/edited on the SQL nodes, the MySQL cluster can be started.
Microsoft Windows - Run the following command in a PowerShell terminal (for those running MySQL 8 the service name is mysql80, for mysql 5.7 it is mysql57):
net start MySQL80
Linux - Run the following command in a terminal:
systemctl start mysqld