MySQL Cluster Management : Part 1

0
115
10 min read

(Read more interesting articles on MySQL High Availability here.)

Configuring multiple management nodes

Every MySQL Cluster must have a management node to start and also to carry out critical tasks such as allowing other nodes to restart, running online backups, and monitoring the status of the cluster. However, it is strongly recommended for a production cluster to ensure that a management node is always available, and this requires more than one node. In this recipe, we will discuss the minor complications that more than one management node will bring before showing the configuration of a new cluster with two management nodes. Finally, the modification of an existing cluster to add a second management node will be shown.

Getting ready

In a single management node cluster, everything is simple. Nodes connect to the management node, get a node ID, and join the cluster. When the management node starts, it reads the config.ini file, starts and prepares to give the cluster information contained within the config.ini file out to the cluster nodes as and when they join.

This process can become slightly more complicated when there are multiple management nodes, and it is important that each management node takes a different ID. Therefore, the first additional complication is that it is an extremely good idea to specify node IDs and ensure that the HostName parameter is set for each management node in the config.ini file.

It is technically possible to start two management nodes with different cluster configuration files in a cluster with multiple management nodes. It is not difficult to see that this can cause all sorts of bizarre behavior including a likely cluster shutdown in the case of the primary management node failing. Ensure that every time the config.ini file is changed, the change is correctly replicated to all management nodes. You should also ensure that all management nodes are always using the same version of the config.ini file.

It is possible to hold the config.ini file on a shared location such as a NFS share, although to avoid introducing complexity and a single point of failure, the best practice would be to store the configuration file in a configuration management system such as Puppet (http://www.puppetlabs.com/) or Cfengine (http://www.cfengine.org/).

How to do it…

The following process should be followed to configure a cluster for multiple management nodes. In this recipe, High Availability with MySQL Cluster. Initially, this recipe will cover the procedure to be followed in order to configure a new cluster with two management nodes. Thereafter, the procedure for adding a second management node to an already running single management node cluster will be covered.

The first step is to define two management nodes in the global configuration file config.ini on both management nodes.

In this example, we are using IP addresses 10.0.0.5 and 10.0.0.6 for the two management nodes that require the following two entries of [ndb_mgmd] in the config.ini file:

[ndb_mgmd]Id=1
HostName=10.0.0.5
DataDir=/var/lib/mysql-cluster
[ndb_mgmd]Id=2
HostName=10.0.0.6
DataDir=/var/lib/mysql-cluster

Update the [mysqld] section of each SQL node’s /etc/my.cnf to point to both management nodes:

[mysqld]ndb-connectstring=10.0.0.5,10.0.0.6

Update the [mysqld] section of each SQL node’s /etc/my.cnf to point to both management nodes:

[mysqld]ndb-connectstring=10.0.0.5,10.0.0.6

Now, prepare to start both the management nodes. Install the management node on both nodes, if it does not already exist.

Before proceeding, ensure that you have copied the updated config.ini file to both management nodes.

Start the first management node by changing to the correct directory and running the management node binary (ndb_mgmd) with the following flags:

  • –initial: Deletes the local cache of the config.ini file and updates it (you must do this every time the config.ini file is changed).
  • –ndb-nodeid=X: Tells the node to connect as this nodeid, as we specified in the config.ini file. This is technically unnecessary if there is no ambiguity as to which nodeid this particular node may connect to (in this case, both nodes have a HostName defined). However, defining it reduces the possibility of confusion.
  • –config-file=config.ini: This is used to specify the configuration file. In theory, passing a value of the config.ini file in the local directory is unnecessary because it is the default value. But in certain situations, it seems that passing this in any case avoids issues, and again this reduces the possibility of confusion.
[root@node6 mysql-cluster]# cd /usr/local/mysql-cluster
[root@node6 mysql-cluster]# ndb_mgmd --config-file=config.ini --initial --ndb-nodeid=2
2009-08-15 20:49:21 [MgmSrvr] INFO -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-08-15 20:49:21 [MgmSrvr] INFO -- Reading cluster configuration from 'config.ini'

Repeat this command on the other node using the correct node ID:

[root@node5 mysql-cluster]# cd /usr/local/mysql-cluster
[root@node5 mysql-cluster]# ndb_mgmd --config-file=config.ini --initial --ndb-nodeid=1

Now, start each storage node in turn. Use the storage management client’s show command to show that both management nodes are connected and that all storage nodes have been reconnected:

ndb_mgm> show
Connected to Management Server at: 10.0.0.5:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=3 @10.0.0.1 (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=4 @10.0.0.2 (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)
id=5 @10.0.0.3 (mysql-5.1.34 ndb-7.0.6, Nodegroup: 1)
id=6 @10.0.0.4 (mysql-5.1.34 ndb-7.0.6, Nodegroup: 1)
[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.0.0.5 (mysql-5.1.34 ndb-7.0.6)
id=2 @10.0.0.6 (mysql-5.1.34 ndb-7.0.6)
[mysqld(API)] 4 node(s)
id=11 @10.0.0.1 (mysql-5.1.34 ndb-7.0.6)
id=12 @10.0.0.2 (mysql-5.1.34 ndb-7.0.6)
id=13 @10.0.0.3 (mysql-5.1.34 ndb-7.0.6)
id=14 @10.0.0.4 (mysql-5.1.34 ndb-7.0.6)

Finally, restart all SQL nodes (mysqld processes). On RedHat-based systems, this can be achieved using the service command:

[root@node1 ~]# service mysqld restart

Congratulations! Your cluster is now configured with multiple management nodes. Test that failover works by killing a management node, in turn, the remaining management nodes should continue to work.

There’s more…

It is sometimes necessary to add a management node to an existing cluster if for example, due to a lack of hardware or time, an initial cluster only has a single management node.

Adding a management node is simple. Firstly, install the management client on the new node . Secondly, modify the config.ini file, as shown earlier in this recipe for adding the new management node, and copy this new config.ini file to both management nodes. Finally, stop the existing management node and start the new one using the following commands:

For the existing management node, type:

[root@node6 mysql-cluster]# killall ndb_mgmd [root@node6 mysql-cluster]# ndb_mgmd --config-file=config.ini --

initial --ndb-nodeid=2
2009-08-15 21:29:53 [MgmSrvr] INFO -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-08-15 21:29:53 [MgmSrvr] INFO -- Reading cluster configuration from 'config.ini'

Then type the following command for the new management node:

[root@node5 mysql-cluster]# ndb_mgmd –config-file=config.ini –initial –ndb-nodeid=1
2009-08-15 21:29:53 [MgmSrvr] INFO — NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-08-15 21:29:53 [MgmSrvr] INFO — Reading cluster configuration from ‘config.ini’torage node one at a time. Ensure that you only stop one node per nodegroup at a time and wait for it to fully restart before taking another node in the nodegroup, when offline, in order to avoid any downtime.

See also

Look at the section for the online addition of storage nodes (discussed later in this article) for further details on restarting storage nodes one at a time.

Obtaining usage information

This recipe explains how to monitor the usage of a MySQL Cluster, looking at the memory, CPU, IO, and network utilization on storage nodes.

Getting ready

MySQL Cluster is extremely memory-intensive. When a MySQL Cluster starts, the storage nodes will start using the entire DataMemory and IndexMemory allocated to them. In a production cluster with a large amount of RAM, it is likely that this will include a large proportion of the physical memory on the server.

How to do it…

An essential part of managing a MySQL Cluster is looking into what is happening inside each storage node. In this section, we will cover the vital commands used to monitor a cluster.

To monitor the memory (RAM) usage of the nodes within the cluster, execute the &ltnodeid> REPORT MemoryUsage command within the management client as follows:

 ndb_mgm> 3 REPORT MemoryUsage
Node 3: Data usage is 0%(21 32K pages of total 98304)
Node 3: Index usage is 0%(13 8K pages of total 131104)

This command can be executed for all storage nodes rather than just one by using ALL nodeid:

ndb_mgm> ALL REPORT MemoryUsage
Node 3: Data usage is 0%(21 32K pages of total 98304)
Node 3: Index usage is 0%(13 8K pages of total 131104)
Node 4: Data usage is 0%(21 32K pages of total 98304)
Node 4: Index usage is 0%(13 8K pages of total 131104)
Node 5: Data usage is 0%(21 32K pages of total 98304)
Node 5: Index usage is 0%(13 8K pages of total 131104)
Node 6: Data usage is 0%(21 32K pages of total 98304)
Node 6: Index usage is 0%(13 8K pages of total 131104)

This information shows that these nodes are actually using 0% of their DataMemory and IndexMemory.

Memory allocation is important and unfortunately a little more complicated than a percentage used on each node. There is more detail about this in the How it works… section of this recipe, but the vital points to remember are:

  • It is a good idea never to go over 80 percent of memory usage (particularly not for DataMemory)
  • In the case of a cluster with a very high memory usage, it is possible that a cluster will not restart correctly

MySQL Cluster storage nodes make extensive use of disk storage unless specifically configured not to, regardless of whether a cluster is using disk-based tables. It is important to ensure the following:

  • There is sufficient storage available
  • There is sufficient IO bandwidth for the storage node and the latency is not too high

To confirm the disk usage on Linux, use the command df –h as follows:

[root@node1 mysql-cluster]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/system-root
7.6G 2.0G 5.3G 28% /
/dev/xvda1 99M 21M 74M 22% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
/dev/mapper/system-ndb_data
2.0G 83M 1.8G 5% /var/lib/mysql-cluster
/dev/mapper/system-ndb_backups
2.0G 68M 1.9G 4% /var/lib/mysql-cluster/BACKUPS

In this example, the cluster data directory and backup directory are on different logical volumes. This provides the following benefits:

  • It is easy to see their usage (5% for data and 4% for backups)
  • Each volume is isolated from other partitions or logical volumes—it means that they are protected from, let’s say, a logfile growing in the logs directory

To confirm the rate at which the kernel is writing to and reading from the disk, use the vmstat command:

[root@node1 ~]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- --
---cpu------
r b swpd free buff cache si so bi bo in cs us
sy id wa st
0 0 0 2978804 324784 353856 0 0 1 121 39 15 0
0 100 0 0
3 0 0 2978804 324784 353856 0 0 0 0 497 620 0
0 99 0 1
0 0 0 2978804 324784 353856 0 0 0 172 529 665 0
0 100 0 0

The bi and bo columns represent the blocks read from a disk and blocks written to a disk, respectively. The first line can be ignored (it’s the average since boot), and the number passed to the command, in this case, the refresh rate in seconds. By using a tool such as bonnie (refer to the See also section at the end of this recipe) to establish the potential of each block device, you can then check to see the maximum proportion of each block device is currently being used.

At times of high stress, like during a hot backup, if the disk utilization is too high it is potentially possible that the storage node will start spending a lot of time in the iowait state—this will reduce performance and should be avoided. One way to avoid this is by using a separate block device (that is, disk or raid controller) for the backups mount point.

LEAVE A REPLY

Please enter your comment!
Please enter your name here