Memsql and AWS

Why the need?

More and more of my customers are either considering or moving towards a distributed scale out model of computing versus the vertically scalable architecture of something like a traditional RDMS. As this change occurs I have been testing a couple different in-memory databases to get a feel for how they install, scale and are monitored particularly during times of contention and reduced resources. The idea of unlimited scale and capacity is truly the end all be all for some customers, especially those that are trying to migrate off a slow and archaic ETL process. Memsql makes it easy to scale horizontally while keeping familiarities of SQL. There are also partner plugins for functions such as Spark and Hive.

For this demonstration I will install memsql community edition on top of several EC2 instances within one region and several AZs. Currently, there are two options when it comes to deploying memsql on top of AWS. The first is using a cluster generator, click here, that runs a cloud formation cycle within the region of your choice. The second option is to deploy a single-instance cluster from the AWS-Marketplace. For this demonstration I will deploy memsql via the first option as it is much quicker to deploy and analogous to deploying an OVF into a VMware environment. The only prerequisite is an active AWS account.

**Please note, all instances have been destroyed and all IP exhausted back into the pool.**

  1. Once you have your AWS account  setup and logged in, navigate to the cluster generator page from the url listed above.
  2. You will be presented with the following page. Let’s go through this and fill out the details. I chose the community edition as it is free and for test/dev environments. Give the generator instance an appropriate name.screen1

For this instance, I have kept the defaults for both the child aggregators and leaf nodes. We can adjust this number from a snapshot of the generator if we need to later.

3. If you haven’t already, generate an EC2 key-pair that you will use to login to the newly deployed memsql instance. If you are unsure of how to do this navigate to the AWS dashboard, click EC2 and select key pairs on the left side. EC2 will automatically back your instance with either an EBS or Ephemeral storage type. I chose to go with a 256GB volume size and chose the m3 instance as this is for demonstration purposes only.

screen2

When finished click on “generate template” and the CloudFormation screen will open in a new tab. You will be prompted to confirm the appropriate region within AWS to deploy as well as the option of copying the URL of the CloudFormation instance.

screen3

4. Clicking on the launch cluster button, will present you with the following screen. Let’s walk through this:

screen4

As we can see we have a couple different options once inside our AWS instance within CloudFormation. You have two options, design a template or choose a template. Since we configured the JSON template in the prior step the memsql package has auto populated the S3 template. Optionally, we have the option of viewing the memsql formation within the designer for additional functionally. We will leave this option alone for this demonstration.

5. Specify a stack name. I kept mine the same as in step 2.

screen5

6. Tagging and setting SNS values for alerts are presented. I added one key and value for demo purposes. The advanced section is left to default.

screen6

7. The final review screen is presented. If all the information is correct, click “submit” and the deployment will begin.

screen7

Depending on resources used, type of instance chosen, etc your deployment times may vary.

screen8

8. Let’s hop over to our EC2 instances and see cloudformation in action. As we can multiple EC2 instances are being spun up along with their appropriate EBS backed volumes, snapshots, etc.

screen9

screen10

9. Once the cluster is up and running we will SSH to the master aggregator using our putty session. You can find the master aggregator nodes IP address by expanding the name column.

screen11

10. Let’s go ahead and connect to the master aggregator instance via SSH and run some commands against the cluster. Type the following into your session to access the MemSQL database.

$ ssh -i <path to EC2 private key> ubuntu@<aggregator hostname>
$ mysql -h 127.0.0.1 -u root

 

11. After you have logged in you can begin running sample queries and workloads against the cluster. Linked at the bottom is a sample command to get you up and running.

12. Optionally, you can connect to the demo URL from the CloudFormation screen and run commands from the CLI.

screen12

Below, I have entered the sample command to create a new user, a new database and insert memsql into the database and finally insert a value into the table.

screen13

Screen grabs from the Ops console where you can drill down into the cluster, the aggregators and leafs. Optionally, you can check the status of the cluster for rows, table memory and disk capacity.

screen14

screen15

Finally, you are able to perform a speed test on the cluster, pushing millions of rows into the table in real time. I was able to push 4million inserts/sec onto 12 machines!! Insane!

screen16

That’s it, a running memsql instance on top of AWS.

Enjoy!

R.D.

 

Leave a Reply