MemSQL is a very fast database. It is a distributed database that perform its transaction in memory or users can choose disk as well. MemSQL is Relational Database Management System and has lock free data structure.
In this article we will setup MemSQL cluster with two nodes. One node is aggregator node and it act as a gate keeper that take queries from user and pull the relevant data from the leaf nodes. Second node is Leaf node, it store all data in memory. We can install memsql in 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
To download MemSQL you must register with 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 license key to install the software.
Here we will explain the steps to install MemSQL in CentOS,
Add MemSQL repository to 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
Once the MemSQL installation is complex on our node1 (Aggregator node) we can deploy MemSQL to other host (leaf nodes) from here with the help of deploy command.
Execute 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 password for your MemSQL, this password is required to login to the database.
If you are running the above command as 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. Noe 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 purpose I temporarily disabled 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 “memsql-admin optimize” command to optimize the nodes.
[root@prod-node1 ~]# memsql-admin optimize firstname.lastname@example.org's password: email@example.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 suggestion after executing the optimize command, so we have to make the changes on 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 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).
In the next page we will check about how to login to the MemSQL and MemSQL studio.