MemSQL Documentation

MemSQL is a high-performance, in-memory database that combines the horizontal scalability of distributed systems with the familiarity of SQL.

Get Started    

Quick Start On-Premises

The fastest way to deploy MemSQL on-premises (i.e. on bare metal, virtual machines, or private clouds) is using MemSQL Ops, the MemSQL-supported tool for installation, configuration, management and monitoring of MemSQL clusters.

If you would like to deploy MemSQL quickly using Docker, AWS, or Azure, consult these other quick start guides.

To successfully run through these steps, make sure that you are running on a 64-bit Linux operating system. For a full list of MemSQL system requirements, see the System Requirements section.

For each host where you plan to install and run MemSQL, make sure ports 3306 and 9000 are open. Port 3306 is used by the MemSQL database, and port 9000 is used by MemSQL Ops.

Now you're ready to set up your MemSQL cluster! Shown below is the startup screen of MemSQL Ops that explains how to set up a cluster.

Follow the steps below to set up your MemSQL cluster:

1. Download MemSQL from memsql.com

Download latest version of MemSQL from memsql.com/download. This will download the MemSQL Ops installer which comes packaged as a compressed tar.gz.

2. Choose a host for the MemSQL Ops primary agent

You also need to choose the host for the MemSQL Ops primary agent. In a MemSQL Ops-deployed MemSQL cluster, every host machine must have a MemSQL Ops agent locally installed. Among the installed agents, the primary agent is the keeper of MemSQL Ops metadata. In a cluster, it is "followed" by the other MemSQL Ops agents. By default, the MemSQL Ops primary agent is on the same host as the MemSQL Database master aggregator, but this is not required.

3. Install MemSQL Ops on your chosen host for the primary agent

Within your chosen host for the MemSQL Ops primary agent, unpack the downloaded MemSQL Ops installer. This expands into the MemSQL Ops folder, memsql-ops-<version>.

tar zxvf memsql-ops-<version>.tar.gz

Following this, install MemSQL Ops with sudo privileges using the install.sh script. This will install MemSQL Ops on the primary host. Specifically, it will do the following:

  • Setup the memsql user and group on the host
  • Install memsql-ops in /var/lib/memsql-ops
  • Set up MemSQL Ops to initialize at system startup
  • Configure Linux system parameters that ensure optimal MemSQL database performance
cd memsql-ops-*
sudo ./install.sh

Running install.sh will prompt:

Do you want to install MemSQL on this host only? [y/N]

Choosing "y" will install a MemSQL cluster completely inside the current host. The installed MemSQL cluster will include one master aggregator and one leaf node.

To make the install process more efficient, if you already know that you would like to run a MemSQL cluster completely within a single host for quick testing, run the following install command instead:

sudo ./install.sh --simple-cluster --host=127.0.0.1

Specifying host=127.0.0.1 is recommended for clusters deployed inside a single host, as it protects against the hostname changing in the future.

Important Note

If you choose to install MemSQL on a single host, you do not need to go through the web UI setup steps. Using the web UI for set up is only necessary when setting up MemSQL across multiple hosts.

Once MemSQL Ops is installed on the primary host, the MemSQL Ops web interface will be accessible on http://<primary_host_name>:9000. Point your browser to that URL and follow the steps to configure additional MemSQL Ops hosts and finish configuring your MemSQL cluster.

If you are not able to open the MemSQL Ops web interface URL, verify that port 9000 is accessible on your host. If it is not possible to expose port 9000, you can either:

  1. Run the MemSQL Ops agent on a different port and proceed with the installation process below.
  2. Install a MemSQL cluster on a single machine purely through MemSQL Ops command-line functions. This requires knowledge of the MemSQL Ops CLI.
  3. Install a MemSQL cluster manually without MemSQL Ops. This requires knowledge of manual MemSQL cluster configuration.

4. Go through the MemSQL Ops web UI to complete MemSQL Database deployment

MemSQL Ops will first ask if you would like to install the Enterprise or Community Edition.

If you choose the Enterprise Edition, MemSQL Ops will ask for a valid Enterprise license key. If you do not have one already, you can sign up for a new Enterprise license in the next screen.

Proper completion of the form generates an Enterprise license, which sends an email to the email address specified. The email will contain an Enterprise license key.

Community Edition users do not need to specify a license key.

MemSQL Ops will then ask if you would like to install MemSQL On Multiple Hosts or On a Single Host.

A multiple host installation sets up a MemSQL cluster across several hosts. A single host installation sets up an entire MemSQL cluster with one master aggregator and one leaf node in a single host.

Setting up an entire MemSQL cluster using the single host option is possible and recommended for quick functional testing. That said, it is not recommended for production environments where performance and high availability are critical. When configuring a MemSQL cluster in a single box, it is important to ensure port numbers of MemSQL aggregator and leaf nodes do not conflict.

If you choose the On Multiple Hosts option, you will see the Add Hosts screen, where you can input information about your cluster.

As is shown in the image above, this screen allows you to type in the hostnames, and the SSH access credentials - username, password and ssh private key. Through SSH, the primary MemSQL Ops agent will install itself into those other hosts.

You will then see a prompt that summarizes the hosts to be configured. You can edit the configuration to your liking. Once you are satisfied, click the Provision Hosts button. This will install the MemSQL Ops agent in each of the hosts.

MemSQL Ops will then download the MemSQL database binary, install it on each host, and set up the MemSQL cluster according to your desired configuration.

Once this step is finished, you have successfully deployed a MemSQL cluster!

5. Add more host machines and MemSQL nodes

You can add more hosts later by opening the Add Hosts screen at any time. Simply click the "+" button in the Cluster screen and select Add Hosts.

Once hosts are set up, you can deploy MemSQL nodes. If you select the full cluster option, you should see the Deploy MemSQL screen immediately after you have configured hosts. Specifically, you will see a prompt that recommends a possible configuration (master aggregator, child aggregators, leaf nodes) for your cluster. You can edit the configuration to your choosing. Once you are satisfied, click Deploy MemSQL Cluster.

6. Test that MemSQL is deployed

To test that everything is set up correctly, connect to the master aggregator using any valid MySQL client driver.

Download a MySQL driver either through the MySQL website directly, or use your OS package manager to install the MySQL driver. For example, in Ubuntu:

sudo apt-get install mysql-client

When you have a MySQL client installed, you can then connect to the MemSQL database. For example:

mysql -u root -h <master_aggregator_hostname> -P 3306 --prompt="memsql>; "

You can now start creating databases and querying MemSQL! Here's a set of sample commands to get you started. You can copy-paste these directly into your MemSQL session.

-- create a new user
grant all on *.* to 'myuser'@'%';
-- create a new database
create database mymemsqldb;
show databases;
use mymemsqldb;
-- create a new rowstore table
create table mytable (id INT PRIMARY KEY, name varchar(10));
-- create a new columnstore table
create table mycolumnstoretable (id INT, groupid INT, name varchar(10), key (groupid) using clustered columnstore, shard key (id));
show tables;
describe mytable;
describe mycolumnstoretable;
-- perform data query operations
insert into mytable values (1, 'row');
select * from mytable;
insert into mycolumnstoretable values (1, 2, 'column');
select * from mycolumnstoretable;
select mytable.name, mycolumnstoretable.name from mytable inner join mycolumnstoretable on mytable.id = mycolumnstoretable.id;
-- determine which memsql version you are using
select @@memsql_version;

Should you run into any errors due to system configuration issues or constraints in your development environment, see the Full Installation Guide section for more information.

Quick Start On-Premises