Quick Start with Docker

Info

As of MemSQL 6.7, MemSQL Ops is a deprecated tool. You are encouraged to use the new MemSQL management tools for future deployments. See the installation guides for more details on how to deploy and manage a cluster using the new management tools.

MemSQL runs on 64-bit Linux-based operating systems; however the easiest way to demonstrate how to use MemSQL on any major operating system is through a pre-configured Docker image. The memsql/quickstart image is available for free and comes installed with MemSQL Ops, a single-node MemSQL cluster, and a simple benchmarking program.

Warning

Running MemSQL in a Docker container as described below is for prototyping and functional testing purposes only! If you wish to test out MemSQL for production deployments, see the on-premises quick start guide instead.

By downloading the memsql/quickstart image, you agree to the MemSQL terms of service. Users who want to use MemSQL on Docker in production should contact MemSQL Support before proceeding. Get free enterprise support by downloading a MemSQL Enterprise trial today at memsql.com/download.

Prerequisites

This guide assumes that Docker Community Edition (CE) has been installed on your machine. If you have not installed Docker CE on your machine, please refer to the Docker documentation for information on installing and setting up Docker.

In addition, make sure that Docker has access to at least four cores and 4GB of RAM on your machine. You can enable this in the Preferences dialog box in Docker CE. For more information on how to do this please consult Docker’s documentation.

Running the MemSQL Docker Image

The MemSQL docker image contains a master aggregator, which runs SQL queries across the cluster and aggregates the results, and a single leaf, which is responsible for storing and processing data. These two components form the most basic MemSQL cluster that you can run.

  1. Before spinning up a MemSQL cluster, verify that your machine meets the minimum system requirements.

    $ docker run --rm memsql/quickstart check-system
    
  2. Now spin up the MemSQL cluster on your machine by running docker run. The memsql/quickstart image will be downloaded first if it is not already on your machine. In order to connect to the web interface, you must expose port 9000 to your local machine, and to connect to the database, port 3306 must also be exposed.

    $ docker run -d -p 3306:3306 -p 9000:9000 --name memsqlcontainer memsql/quickstart
    
  3. Finally visit the MemSQL Ops web interface (requires Firefox or Google Chrome):

    $ open "http://localhost:9000"
    

    You should see the following: image

Using Persistent Storage with your Container (Optional)

Starting up the MemSQL container without attaching a volume means all data will be lost when the container is destroyed or upgraded. If you wish to persist your data, do the following:

  1. Create a data container that will store the data.

    $ docker create --name memsqldata memsql/quickstart /bin/true
    
  2. Run the MemSQL container, mounting the volume from the previous container.

    $ docker run -d -p 3306:3306 -p 9000:9000 --name memsqlcontainer --volumes-from memsqldata memsql/quickstart
    

Connect to MemSQL and Create a Database

By exposing port 3306 when starting up the MemSQL container, MemSQL is available to connect to from your machine. You can then use the memsql client within the container to perform cluster operations and run SQL queries against a database.

  1. Connect to the container and open the memsql client application.

    $ docker exec -it memsqlcontainer memsql
    
  2. Run the following MemSQL Database commands to create a table and seed it with sample data:

    memsql> create database test;
    
    memsql> use test;
    
    memsql> CREATE TABLE test_table (id BIGINT PRIMARY KEY AUTO_INCREMENT, c INT);
    
    memsql> INSERT INTO test_table (c) VALUES (1);
    
    memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table;
    
    memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table;
    
    memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table;
    
    memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table;
    
    memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table;
    
    memsql> SELECT SUM(c) FROM test_table;
    +--------+
    | SUM(c) |
    +--------+
    |    243 |
    +--------+
    1 row in set (0.00 sec)
    

Connect to MemSQL with Your Favorite Tools

In addition to running queries from within the container, you can also connect with your favorite tools like Sequel Pro, or use tools such as the MySQL client (if you have it installed) to do local development work.

$ mysql -h 127.0.0.1 -u root -P 3306 --prompt="memsql> "
Info

–-prompt="memsql> " changes the prompt in the client program to say memsql>. It is not required for using the MySQL client. Also, in this container, port 3306 corresponds to the master aggregator node and port 3307 corresponds to the leaf node. If you want to also access the leaf node through a SQL client interface, make sure to add -p 3307:3307 to your docker run command.

Upgrading the MemSQL Docker Image

To upgrade the memsql/quickstart Docker image to the latest version, run docker pull:

$ docker pull memsql/quickstart

If you have a running container, you can stop and restart it through the following commands. By restarting the container after upgrade you will lose any data stored in MemSQL, unless it is persisted outside the container as described earlier in this guide.

If you have any data that you’d like to save before upgrading, you can do a backup with mysqldump or MemSQL backup functionality.

$ docker stop memsqlcontainer
$ docker rm memsqlcontainer
$ docker run -d -p 3306:3306 -p 9000:9000 --name memsqlcontainer [--volumes-from memsqldata] memsql/quickstart

Next Steps

Multi-Threaded Queries

The Docker image contains sample code in Python that demonstrates how MemSQL can handle high numbers of concurrent writes per second. To run it, use the following docker command:

$ docker exec -it memsqlcontainer simple-benchmark

After the benchmark starts, use the MemSQL Ops UI to observe the transactional throughput of this workload. You can access the UI by running open "http://localhost:9000".

See Multi-Insert Examples for more concurrent benchmark examples.

Sample Query Workload

See How to Run Queries section for an example schema and queries to begin exploring the potential of MemSQL.

Removing the memsql/quickstart Image

When you are done testing with the memsql/quickstart image, you can clean it up by running the following command:

$ docker stop memsqlcontainer && docker rm -v memsqlcontainer

and, if you have a data container called memsqldata, you can remove it with the following command :

$ docker rm -v memsqldata
Was this article useful?