Uninstalling MemSQL

Steps:
On Master Aggregator: 

• memsql-ops memsql-list 
• memsql-ops memsql-unmonitor (it will ask for node. you have to unmonitor the memsql from all nodes one by one.)
  Select an option: 1 (Example)
• memsql-ops memsql-list
  No MemSQL nodes were found.
• memsql-ops uninstall
  WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 192.168.2.240 and port 3306 the last time MemSQL Ops connected to it.
  We will not be able to uninstall this MemSQL Ops agent until /var/lib/memsql is empty. Make sure that there are no running MemSQL nodes in that directory before deleting anything from it.
   (so go on all nodes one by one and uninstall first there then)
• memsql-ops agent-uninstall --all
  This will completely delete all MemSQL Ops agents in the cluster, including this one. This operation will delete all MemSQL Ops data in the cluster and cannot be undone.
  If you want to delete all MemSQL Ops agents, please type the word DELETE: DELETE
  WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 192.168.2.240 and port 3306 the last time MemSQL Ops connected to it.
  Stopping MemSQL Ops
  Waiting up to 60 seconds for a clean exit.
  Done.
  Successfully uninstalled MemSQL Ops
• netstat -tunlp 
• kill -9 (pid which occpying memsql port)
•  

 On Child Aggregator and on Leaves)

• memsql-ops uninstall
  WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 192.168.2.249 and port 3307 the last time MemSQL Ops connected to it.
  WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 192.168.2.249 and port 3306 the last time MemSQL Ops connected to it.
  We will not be able to uninstall this MemSQL Ops agent until /var/lib/memsql is empty. Make sure that there are no running MemSQL nodes in that directory before deleting anything from it.
•  rm -rf /var/lib/memsql
• memsql-ops uninstall
  WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 192.168.2.249 and port 3307 the last time MemSQL Ops connected to it.
  WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 192.168.2.249 and port 3306 the last time MemSQL Ops connected to it.
  This will completely delete this MemSQL Ops agent along with all of its data
  If you want to delete this MemSQL Ops, please type the word DELETE: DELETE
  Local agent is now a primary.
  Stopping MemSQL Ops
  Waiting up to 60 seconds for a clean exit.
  Done.
  Successfully uninstalled MemSQL Ops
• netstat -tunlp 
• kill -9 (pid which occpying memsql port)

Removing Leaf from cluster

All steps to be performed on Master Aggregator

STEP 1
######
memsql-ops memsql-list

STEP 2
######
memsql-ops agent-list

STEP 3
######
show leaves;

STEP 4
######
show partitions on db1;

STEP 5
######
show partitions extended;

STEP 6
######
memsql-ops memsql-unmonitor 4B4FD60
memsql-ops memsql-unmonitor 73E66DE

STEP 7
######
memsql-ops agent-uninstall --force Aa10a01
memsql-ops agent-uninstall --force A36a36a

STEP 8
######
memsql-ops memsql-list
memsql-ops agent-list


STEP 10
######
REMOVE LEAF '172.27.1.218':3306;
REMOVE LEAF '172.27.3.68':3306;
OR
REMOVE LEAF '172.27.1.218':3306 force;
REMOVE LEAF '172.27.3.68':3306 force ;
REBALANCE PARTITIONS ON db1;

STEP 11
######
SHOW LEAVES;
SHOW AGGREGATORS;
show partitions extended;
SHOW CLUSTER STATUS;

MemSQL After installation

After basic installation of MemSQL, Now we need to understand the important parameters that effects the cluster. The three main parameters that affects the cluster are

• durability
• redundancy_level
• master-aggregator

The above three parameters should be added in master aggregator at /var/lib/memsql/memsql.cnf and after adding the above parameters restart the server using "service memsql restart" command

USE & IMPACTS

###############

• durability : - This is very important feature of MemSQL. If this is on then MemSQL takes periodic snapshot and log changes to disk and if its off then it will save only metadata to disk. This is the beauty of MemSQL that your data is secure on disk as well as available in-memory for high-availability. 

• redundancy_level : - To add redundancy to the database we can set redundancy_level which means data is available in separate groups called availability groups which contains a copy of every partition in the system- some as masters and some as slaves. Currently MemSQL supports two level of redundancy level 1 and 2 which means each partition of database on master has a copy of slave on other leaf.

• master-aggregator : - MemSQL is actually two tiered clustered architecture which has two types of server. Aggregator and the Leaves. Aggregators are the nodes which acts as mediator between client request and the cluster nodes (leaves). Aggregators only contains metadata and the leaves contains actual data. Aggregators are of two types Master and the Child aggregaors. To make master aggregator one should have change in memsql.cnf file is "master-aggregator" and restart the MemSQL.

TO BE CONTINUED............................

MemSQL Installation

MemSQL installation is very simple. But before installation its important to choose the suitable package. Earlier MemSQL subscription was free for one month but as per my knowledge they have extended the trial period upto 3 months. So its very convenient for end user to evaluate the product with time. No need to hurry.

As per my experience MemSQL has separate subscription for trial and for production. The production subscription has lot more as compare to trial subscription. So its better to go for easiest installation option for trial.

One of the common industry installation options is rpm. In this entire blog I will discuss the things based on rpm installation.

How do we start rpm installation (step wise)

1. REGISTER ON MEMSQL SITE www.memsql.com
2. YOU WILL GET MAIL FROM MEMSQL LIKE BELOW
3. Name: Jeetendra Ranjan                  Trial License Key: 91b7328a50e748c89bfa8702d55c1111
                     License File: Click here to download
                             now go to the bottom of the page and you will see the rpm installation link like below
   
   The following installation options require installing MemSQL on each node of your cluster. For instructions on installing MemSQL, click here.
   MemSQL Download Links
   $ wget http://download.memsql.com/91b7328a50e748c89bfa8702d55c1111/memsql-3.2.x86_64.rpm
   Here I would like to share one more link which you will see. this is memsql-ops. So memsql-ops is very useful monitoring tool for memsql as this is very difficult to monitor all the servers(aggregators and leaves individually. So go for memsql-ops installation also. here is the link
   $ wget http://download.memsql.com/ops-3.1.1/memsql-ops-3.1.1.x86_64.rpm
4. Before we start installation few very important point. First is MemSQL does not support 32-bit architecture so don't try to install on 32-bit servers. Secondly as we know MemSQL is two tiered architecture so same installation should be done on all the aggregators as well as on leaves. Very important is MemSQL does not have their own client tool so it support MySQL as a client. So on all the servers MySQL client should be installed.
5.  [root@localhost opt]#wget http://download.memsql.com/91b7328a50e748c89bfa8702d55c1111/memsql-3.2.x86_64.rpm
6.  [root@localhost opt]# ls
     memsql-3.2.x86_64.rpm
7. 4. [root@localhost opt]# rpm -i memsql-3.2.x86_64.rpm
    [root@localhost opt]# rpm -qa  grep -i memsql
    memsql-3.2-1.x86_64
    [root@localhost opt]# rpm -qa  grep -i mysql (i
    MySQL-client-5.5.27-1.linux2.6.x86_64
8. add below at memsql.cnf (/var/lib/memsql) at aggregator
   
    port  = 22407
    redundancy-level=2
    master-aggregator
9. [root@localhost opt]# service memsql start
10. mysql -u root --password='' -h 127.0.0.1 -P 22407 --prompt="memsql> "
11. memsql> add leaf 'root'@'192.168.2.2':23976; (at aggregator)
     memsql> add leaf 'root'@'10.20.1.3':23976; (at aggregator)
12. memsql> show leaves;
    +---------------+-------+--------------------+---------------+-----------+--------+--------------------+---------------------------+
     Host           Port   Availability_Group  Pair_Host      Pair_Port  State   Opened_Connections  Average_Roundtrip_Latency
    +---------------+-------+--------------------+---------------+-----------+--------+--------------------+---------------------------+
     192.168.2.2  23976                   1  10.20.1.3        23976  online                   1                      0.832
     10.20.1.3    23976                   2  192.168.2.2     23976  online                   1                      0.381
    +---------------+-------+--------------------+---------------+-----------+--------+--------------------+---------------------------+
    2 rows in set (0.01 sec)

                 

How MemSQL is unique

MemSQL is High performance in-memory database with unique features like:

1.  MemSQL in-memory data nodes regularly commit transactions to disk as logs. thus we have advantage to have two copy of dataset per node. One on disk and other on in-memory. hence it delivers results at high speed beating latency that results from reading and writing to disk without any risk of data loss.
2. MemSQL provides lock free transaction so read do not block writes and write do not block read.
3. Provides two tiered architecture. Aggrgators(Master & Child) to accept requests from client and gather information and deliver to them and the nodes which acts as data storage and free for data storage only called leaf.
4. Advantage of keeping database tables in two architecture. Distributed table  that have million of records which is sharded across nodes and reference tables which may have relatively small set of records and can be store on all leaves..

 

MemSQL experience so far

Being DB professional we have a challenge to deliver super performance to our valued customer. High performance could not be delivered with traditional approach so we must need to take modern approach. Here in-memory database technology comes in our mind. this is the only technology which can beat disk base traditional approach.
Now in todays market we have number of in memory options. Choosing one of them is very challenging task as all promise to deliver high performance but as a user we can ensure ourself based on few points
1) Licensing term and fee
2) Maintenance
3) Easy to use.

              I also tested few options available in market but found MemSQL a better option for us. The few reasons are:
1) Simplicity.
2) Lock free DB.
3) In memory with disk storage parallel.

             I am new to this DB but explored quite a bit and like to share all that I explored so far day by day. I hope you will also try by downloading the free subscription available on www.memsql.com .

Good Luck. Cheers.

You can also reach me by email at jitendra_ranjan@yahoo.com and by phone at +91 9810825306