Setting up NDB cluster connection pooling with MySQL Cluster Manager

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:

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:

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:

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:

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

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.

2,930 total views, 6 views today

About Thomas Nielsen

Thomas Nielsen is a software engineer, and the Team Lead for MySQL Cluster Manager, at Oracle. He has worked on different high availability products and technologies in databases, as well as JavaDB, before joining the MySQL Cluster team. He holds an engineering degree from the Norwegian University of Science and Technology, and his past-time passion is gliding instruction and cross country soaring.

Leave a Reply