MySQL Cluster On FlashArray Implementation and Best Practices

Last updated
Save as PDF

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.

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.
[ndbd default]\ MaxNoOfExecutionThreads	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