Setting up NDB cluster connection pooling with MySQL Cluster Manager

Cluster connection pooling

The new MySQL Cluster 7.6 GA supports NDB connection pooling – using multiple NDBAPI connections from a single mysqld to NDB – to allow increased concurrency and throughput.

Cluster connection pooling has been supported in MySQL Cluster for quite a while. However, the previous 7.5 release added a ndb_connection_pool_nodeids configuration attribute which allows assigning a specified set of nodeids to a mysqld for cluster connection pooling. This addition allowed MCM to fully support cluster connection pooling.

Read on, and I’ll show you how to set up NDB cluster connection pooling using MCM.

Setting up

There are three simple steps needed to setup cluster connection pooling

1. Add ndbiapi slots for connection pooling to our cluster
2. Allocate ndbapi connection pooling slots to mysqlds
3. Enable connection pooling

I’ll bootstrap a 7.6 GA MySQL Cluster using MCM 1.4.6 to get us started.

Adding ndbapi slots

Figuring out the optimal number of connections per mysqld to NDB is outside the scope of this blog. Let’s assume I need 3 connections per mysqld for my targeted throughput. Each connection in the pool requires an additional ndbapi slot in the cluster configuration.

My bootstrapped cluster does not have sufficient ndbapi slots to allow pooling with 3 connections per mysqld, as shown by the list processes command:

mcm> list processes mycluster;
+--------+----------+----------+
| NodeId | Name     | Host     |
+--------+----------+----------+
| 49     | ndb_mgmd | thinkpad |
| 1      | ndbmtd   | thinkpad |
| 2      | ndbmtd   | thinkpad |
| 50     | mysqld   | thinkpad |
| 51     | mysqld   | thinkpad |
| 52     | ndbapi   | *        |
+--------+----------+----------+
6 rows in set (0.09 sec)

The mysqlds already have one connection, so I need to 2 additional ndbapi slots per mysqld. For easier identification I add these as nodeids 100 through 103 using the add process command:

mcm> add process -R ndbapi:100@*,ndbapi:101@*,ndbapi:102@*,ndbapi:103@* mycluster;
+----------------------------+
| Command result             |
+----------------------------+
| Process added successfully |
+----------------------------+
1 row in set (1 min 54.30 sec)
 
mcm> show status --process mycluster;                                                                                                                  +--------+----------+----------+---------+-----------+-----------+
| NodeId | Process  | Host     | Status  | Nodegroup | Package   |
+--------+----------+----------+---------+-----------+-----------+
| 49     | ndb_mgmd | thinkpad | running |           | mypackage |
| 1      | ndbmtd   | thinkpad | running | 0         | mypackage |
| 2      | ndbmtd   | thinkpad | running | 0         | mypackage |
| 50     | mysqld   | thinkpad | running |           | mypackage |
| 51     | mysqld   | thinkpad | running |           | mypackage |
| 52     | ndbapi   | *        | added   |           |           |
| 100    | ndbapi   | *        | added   |           |           |
| 101    | ndbapi   | *        | added   |           |           |
| 102    | ndbapi   | *        | added   |           |           |
| 103    | ndbapi   | *        | added   |           |           |
+--------+----------+----------+---------+-----------+-----------+
10 rows in set (0.07 sec)

The 4 new ndbapi slots are shown at the end, and are ready to be used for pooling.

One shot pooling

There are two configuration attributes controlling NDB cluster connection pooling:

mcm> get -d ndb_cluster_connection*:mysqld:50 mycluster;
+-------------------------------------+-------+----------+---------+----------+---------+---------+---------+
| Name                                | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level   | Comment |
+-------------------------------------+-------+----------+---------+----------+---------+---------+---------+
| ndb_cluster_connection_pool         | 1     | mysqld   | 50      |          |         | Default |         |
| ndb_cluster_connection_pool_nodeids | NULL  | mysqld   | 50      |          |         | Default |         |
+-------------------------------------+-------+----------+---------+----------+---------+---------+---------+
2 rows in set (0.06 sec)

As shown, each mysqld is by default using a single connection to NDB.

To set up cluster connection pooling we assign connection pool nodeids to the respective mysqlds, and enable pooling by specifying the number of slots to use per mysqld. As cluster is running, we have to collapse these two steps in a single set command. If not, the mysqlds will fail during restart due to an inconsistent clsuter connection pool configuration. Note that the first nodeid in the ndb_cluster_connection_pool_nodeids csv list must be the nodeid of the mysqld itself:

mcm> set
ndb_cluster_connection_pool:mysqld=3,
ndb_cluster_connection_pool_nodeids:mysqld:50="50,100,101",
ndb_cluster_connection_pool_nodeids:mysqld:51="51,102,103"
 mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (21.54 sec)

Verification

One very simple way of verifying connection pooling is enabled is using the show status command:

mcm> show status --process mycluster;                                                                                                                  +--------+----------+----------+-----------+-----------+-----------+
| NodeId | Process  | Host     | Status    | Nodegroup | Package   |
+--------+----------+----------+-----------+-----------+-----------+
| 49     | ndb_mgmd | thinkpad | running   |           | mypackage |
| 1      | ndbmtd   | thinkpad | running   | 0         | mypackage |
| 2      | ndbmtd   | thinkpad | running   | 0         | mypackage |
| 50     | mysqld   | thinkpad | running   |           | mypackage |
| 51     | mysqld   | thinkpad | running   |           | mypackage |
| 52     | ndbapi   | *        | added     |           |           |
| 100    | ndbapi   | *        | connected |           |           |
| 101    | ndbapi   | *        | connected |           |           |
| 102    | ndbapi   | *        | connected |           |           |
| 103    | ndbapi   | *        | connected |           |           |
+--------+----------+----------+-----------+-----------+-----------+
10 rows in set (0.08 sec)

The status of the the 4 additional ndbapi slots 100 through 103 are now shown as connected .

That’s all there is to it. Quite simple, really.