MemSQL & Delphix Express - Win Win Option
Actors
Delphix: "Delphix Positioned as a Leader in the Gartner Magic Quadrant for Structured Data Archiving and Application Retirement"
 |
| Source: twitter.com/delphix |
 |
| Source: http://www.delphix.com/products/daas-overview/ |
- MemSQL 4.1.9 Community Edition
According to Wikipedia (
https://en.wikipedia.org/wiki/MemSQL)
"MemSQL is a distributed, in-memory database that is part of the NewSQL movement.[2] It is an ACID-compliant RDBMS that most notably converts SQL into C++ through code generation.[3] It is being developed by MemSQL Inc., that was founded in 2011 and is a graduate of the Y Combinator startup program. MemSQL Inc. has raised more than $40 million to date from a variety of investors including First Round Capital, IA Ventures, NEA, and several prominent angels including Paul Buchheit, Max Levchin, Aaron Levie, and Ashton Kutcher.[4] MemSQL Inc. launched its database to the public on June 18, 2012."
MemSQL & Gartner:
"Gartner Positions MemSQL in the Visionaries Quadrant of the Magic Quadrant for Operational Database Management Systems
MemSQL scores high for its completeness of vision, technology innovation and ability to execute against its R&D model"
http://www.memsql.com/releases/gartner-magic-quadrant-for-operational-database-management-systems/
 |
| Source: memsql.com Home Page |
Plan
I'm trying to test 2 things
- Is it possible to use Delphix vFiles functionality to handle persistence in MemSQL?
- What is its the impact on MemSQL performance?
Test Environment
 |
| Delphix Express in VMWare Player 7 |
 |
| Delphix Express Console |
 |
| MemSQL Linux VM |
 |
| MemSQL Linux VM Box Parameters |
Test Steps Breakdown
- Install MemSQL
- Create a table and fill with some data
- Test performance (using MemSQL Ops)
- Shutdown MemSQL
- Replace Data directory with resource provisioned with Delphix
- Copy existing data to new provisioned resource
- Startup MemSQL
- Check if everything works
- Check performance again (using MemSQL Ops)
- Conclusions
Test Steps
1. Install MemSQL
[memsql@volt1 ~]$ tar -xvzf memsql-ops-4.1.9.tar.gz
[memsql@volt1 ~]$ cd memsql-ops-4.1.9
[memsql@volt1 memsql-ops-4.1.9]$ ls
install.sh memsql-ops README
[memsql@volt1 memsql-ops-4.1.9]$ sudo install.sh
 |
| MemSQL Ops |
2. New Table and Some Data
[memsql@volt1 memsql]$ mysql -u root -h volt1 -P 3306 --prompt="memsql> "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
memsql> -- create a new user
memsql> grant all on *.* to 'myuser'@'%' identified by 'mypassword';
Query OK, 0 rows affected (0.38 sec)
memsql> -- show all memsql leaf nodes
memsql> show leaves;
+-----------------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+
| Host | Port | Availability_Group | Pair_Host | Pair_Port | State | Opened_Connections | Average_Roundtrip_Latency_ms |
+-----------------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+
| 192.168.111.141 | 3307 | 1 | NULL | NULL | online | 1 | 0.255 |
+-----------------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+
1 row in set (0.00 sec)
memsql> -- create a new database
memsql> create database mymemsqldb;
Query OK, 1 row affected (3.25 sec)
memsql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| memsql |
| mymemsqldb |
| sharding |
+--------------------+
4 rows in set (0.01 sec)
memsql> use mymemsqldb;
Database changed
memsql> -- create a new table
memsql> create table mytable (id INT PRIMARY KEY, name varchar(10));
Query OK, 0 rows affected (10.03 sec)
memsql> show tables;
+----------------------+
| Tables_in_mymemsqldb |
+----------------------+
| mytable |
+----------------------+
1 row in set (0.00 sec)
memsql> describe mytable;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.01 sec)
memsql> -- perform data query operations
memsql> insert into mytable values (1, 'hello');
Query OK, 1 row affected (1.66 sec)
memsql> select * from mytable;
+----+-------+
| id | name |
+----+-------+
| 1 | hello |
+----+-------+
1 row in set (3.10 sec)
3. Test performance (using MemSQL Ops)
 |
| Reference Performance Score |
4. Shutdown MemSQL
[memsql@volt1 memsql]$ memsql-ops memsql-stop
Index ID Agent Id Process State Cluster State Role Host Port
1 DB6DBFC Ad92cbe RUNNING CONNECTED MASTER 192.168.111.141 3306
2 F0031A1 Ad92cbe RUNNING CONNECTED LEAF 192.168.111.141 3307
Select a MemSQL node: 2
2015-11-05 19:24:33: J60c2bf [INFO] Stopping MemSQL node F0031A1E49417690DC51BD4F718863DCD901DE6B on Agent Ad92cbe2bc08845b8b5958b69673e26ec
2015-11-05 19:24:38: J60c2bf [INFO] Successfully stopped MemSQL node F0031A1E49417690DC51BD4F718863DCD901DE6B
[memsql@volt1 memsql]$ memsql-ops memsql-stop
Index ID Agent Id Process State Cluster State Role Host Port
1 DB6DBFC Ad92cbe RUNNING CONNECTED MASTER 192.168.111.141 3306
2 F0031A1 Ad92cbe NOT RUNNING OFFLINE LEAF 192.168.111.141 3307
Select a MemSQL node: 1
2015-11-05 19:24:44: J476b0d [INFO] Stopping MemSQL node DB6DBFCC07F087C826D73597A7F2488A5E7F51DD on Agent Ad92cbe2bc08845b8b5958b69673e26ec
2015-11-05 19:24:47: J476b0d [INFO] Successfully stopped MemSQL node DB6DBFCC07F087C826D73597A7F2488A5E7F51DD
5. Replace Data directory with resource provisioned with Delphix
[memsql@volt1 leaf-3307-1]$ pwd
/var/lib/memsql/leaf-3307-1
We have to find data folder and replace it with Delphix provisioned folder
[memsql@volt1 data]$ pwd
/var/lib/memsql/leaf-3307-1/data
 |
| Data folder renamed. |
Data folder of the MemSQL leaf renamed and new empty folder created.
 |
| Environment Registered in Delphix |
We have to register host with MemSQL in Delphix.
 |
| Provisioning Delphix Storage to MemSQL Host |
Next we have to provision Delphix storage to volt1 host and mount it in new empty data folder.
 |
| Provisioning to Path /var/lib/memsql/leaf-3307-1/data |
 |
| Storage Provisioned to /var/lib/memsql/leaf-3307-1/data |
We can check this using mount command.
 |
| Storage mounted from Delphix platform |
6. Copy existing data to new provisioned resource
Now we are copying contents of old __data__ folder to provisioned one.
 |
| Copying Data to Delphix Storage |
Now snapshot of data in Delphix.
 |
| Snapshot done |
Now time for starting MemSQL nodes.
7. Startup MemSQL
 |
| Nodes started. |
 |
| MemSQL Ops shows running nodes |
Success! Nodes are up and running. Now SQL check.
8. Check if everything works
 |
| Database is running fine. Select statement returned correct response. |
9. Check performance again (using MemSQL Ops)
 |
| Performance after switching to Delphix storage |
|
|
|
|
10. Conclusions
- It is possible to use Delphix storage as persistence store for MemSQL nodes.
- Performance is not affected.
- Technically using Delphix provisioned storage has to be orchestrated using some automation tool. We have to replace storage for every leaf node.
Summary
Possibility of symbiosis between MemSQL and Delphix is really interesting.
- We get live backup of MemSQL.
- We get amazing refresh/rewind/clone features of Delphix.
- We optimize storage required for persistence purposes.
- What more?
