MemSQL Cluster Installation and Configuration.

MemSQL is a very fast database. It is a distributed database that performs its transaction in memory or users can choose disk as well. MemSQL is a Relational Database Management System and has a lock-free data structure.

In this article, we will set up the MemSQL cluster with two nodes. One node is an aggregator node and it acts as a gatekeeper that takes queries from the user and pulls the relevant data from the leaf nodes. The second node is Leaf node, it stores all data in memory.  We can install memsql in a single server but here we will configure it with two nodes.

Lab environment details are below.

Operating system: Centos 7

CPU : 4 core ( Recommended )

Memory: 4G (Recommended)

Node1: 192.168.0.72   prod-node1.com ( Master aggregator node)

Node2: 192.168.0.23  prod-node2.com ( Leaf node)

MemSQL version: memsql-server 6.8.7

Download MemSQL

To download MemSQL you must register with the MemSQL website https://www.memsql.com/download/. Add your user name, password, and email address to register with MemSQL.

Once you complete registration with MemSQL you will get the license key to install the software.

Here we will explain the steps to install MemSQL in CentOS,

MemSQL installation

Add MemSQL repository to the repository list

[root@prod-node1 ~]# yum-config-manager --add-repo https://release.memsql.com/production/rpm/x86_64/repodata/memsql.repo

Install MemSQL tools, client application, and studio.

[root@prod-node1 ~]# yum install -y memsql-toolbox memsql-client memsql-studio

Deploy MemSQL

Once the MemSQL installation is complex on our node1 (Aggregator node)  we can deploy MemSQL to other hosts (leaf nodes) from here with the help of deploy command.

Execute the below command for deployment.

# memsql-deploy setup-cluster  --high-availability=false --license BDY5MTkwZmE5NGVlOTQ2MjU5NjJkZTEyNjg5OGQ2NjFiAAAAAAAAAAAEAAAAAAAAAAwwNQIYPFNa7XwfSCTfyu+IuPDj0/RC9k8IN84DAhkAseOmHpjkttVM5kbKEeEcEiO+uf4hELuKAA==  --master-host prod-node1.com  --leaf-hosts prod-node2.com --password redhat123

Here I am set high availability to true ( this will help add additional nodes for high availability ) and added the license key that we opted at the time of registration. password command will set the password for your MemSQL, this password is required to login to the database.

If you are running the above command as the root user, then you will get below error.

[root@prod-node1 ~]# memsql-deploy setup-cluster  --high-availability=false --license BDY5MTkwZmE5NGVlOTQ2MjU5NjJkZTEyNjg5OGQ2NjFiAAAAAAAAAAAEAAAAAAAAAAwwNQIYPFNa7XwfSCTfyu+IuPDj0/RC9k8IN84DAhkAseOmHpjkttVM5kbKEeEcEiO+uf4hELuKAA==  --master-host prod-node1.com --aggregator-hosts prod-node1.com --leaf-hosts prod-node2.com --password Redhat123

 Toolbox does not support implicitly running as root. Please add 'user = "root"' to /root/.config/memsql-toolbox/toolbox.hcl if you want to run as the root user. See https://docs.memsql.com/toolbox-redir/implicit-root

Add below line in the /root/.config/memsql-toolbox/toolbox.hcl file ( Create this file)

user = “root”

So I have created the above file and added user details. Now I am running the deploy command again and let’s see what will happen.

error running memsqlctl: error running command: `"/usr/bin/ssh" "-oBatchMode=yes" "-q" "-tt" "-oControlPath=/run/user/0/memsql-toolbox208074700/1.socket" "prod-node1.com" "'memsqlctl'" "'--json'" "'--yes'" "'add-leaf'" "'--host'" "'prod-node2.com'" "'--port'" "'3306'" "'--secure-password'" "'r0DxoNoMckIiVTQZHhRFcwdT++qWOkQryZgY23DzHc5QoYohHw=='"`: exit status 1 stderr: dial tcp 192.168.0.23:3306: getsockopt: no route to host

This time I got another error “no route to host”

I found that my firewall is blocking connection on my nodes. For installation purposes, I temporarily disabled the firewall on both nodes and executed the command again.

Yes, this time I could successfully install the MemSQL on all my nodes.

The deploy command will automatically install packages on all nodes, create master aggregator and leaf nodes.  Now execute the “memsql-admin optimize” command to optimize the nodes.

[root@prod-node1 ~]# memsql-admin optimize

root@prod-node1.com's password: 

root@prod-node2.com's password: 

toolbox will perform the following actions:

  · On host prod-node2.com:0:

    - Run 'memsqlctl optimize'

  · On host prod-node1.com:0:

    - Run 'memsqlctl update-config --set-global --key default_partitions_per_leaf --value 4'

Would you like to continue? [y/N]: y

✓ Ran 'memsqlctl update-config --set-global --key default_partitions_per_leaf --value 4' on host prod-node1.com   

✓ Ran 'memsqlctl optimize' on host prod-node2.com     

Operation completed successfully

I got two suggestions after executing the optimize command, so we have to make the changes to the mentioned nodes.

[root@prod-node1 ~]# memsqlctl update-config --set-global --key default_partitions_per_leaf --value 4

memsqlctl will perform the following actions:

  · Update configuration setting on node with MemSQL ID 5E5C4A1374BA3B2A4D8FE57A464DD87305E45157 on port 3306

    - Execute command `SET GLOBAL default_partitions_per_leaf=4`

Would you like to continue? [y/N]: y

✓ Executed SET GLOBAL command on node with MemSQL ID 5E5C4A1374BA3B2A4D8FE57A464DD87305E45157 

Executed the first command as per the recommendation.

Then execute the second command, this one we have to execute on prod-node2.com.

[root@prod-node2 ~]# memsqlctl optimize

Warning: The maximum memory per node, 3410 MB, is less than MemSQL's minimum recommendation of 7372 MB per node.

All node settings are already at their optimized values.

Yes, now we have completed optimization on both nodes ( Aggregator and leaf node).

On the next page, we will check about how to login to the MemSQL and MemSQL studio.