Deploy MemSQL min read


Notes on users and groups

The user that deploys MemSQL via MemSQL Toolbox must be able to SSH to each host in the cluster. When memsql-server is installed via an RPM or Debian package when deploying MemSQL, a memsql user and group are also created on each host in the cluster.

This memsql user does not have a shell, and attempting to log in or SSH as this user will fail. The user that deploys MemSQL is added to the memsql group. This group allows most Toolbox commands to run without sudo privileges, and members of this group can perform many Toolbox operations without the need to escalate to sudo. Users who desire to run MemSQL Toolbox commands must be added to the memsql group on each host in the cluster. They must also be able to SSH to each host.

Manually creating a memsql user and group is only recommended in a sudo-less environment when performing a tarball-based deployment of MemSQL. In order to run MemSQL Toolbox commands against a cluster, this manually-created memsql user must be configured so that it can SSH to each host in the cluster.

Minimal deployment

MemSQL has been designed to be deployed with at least two nodes:

  • A Master Aggregator node that runs SQL queries and aggregates the results, and
  • A single leaf node, which is responsible for storing and processing data

These two nodes can be deployed on a single host (via the cluster-in-box option), or on two hosts, with one MemSQL node on each host.

While additional aggregators and nodes can be added and removed as required, a minimal deployment of MemSQL always consists of at least these two nodes.

Deployment options

Info

The user that deploys MemSQL via the UI must also be able to SSH into each host in the cluster without using a password.

As of MemSQL Toolbox 1.6, MemSQL can be deployed via browser-based UI. This option describes how to deploy MemSQL using this UI. Please review the MemSQL Prerequisites prior to deploying MemSQL.

In order to use the UI, the user (and the user account that will deploy MemSQL) must:

  • Be able to install MemSQL and MemSQL Toolbox 1.6 using RPM or Debian packages

    • Tarball-based deployments via the UI are not currently supported, but will be in the future

    • To deploy MemSQL via tarball in the interim, refer to either “Option 3: Cluster deployment via YAML file,” or the stand-alone Deploy MemSQL via Tarball guide.

  • Deploy a “standard” MemSQL configuration that requires only basic options. Advanced options, such as those available with a cluster deployment via YAML file, will be available in the UI in the future.

If any of these requirements are not met, we recommend that you deploy MemSQL using another option listed on this page.

Start the UI

Run the following command to start the UI.

memsql-deploy ui

This command will display a link with a secure token that you can use to deploy MemSQL via the UI.

For additional options that can be used with memsql-deploy ui, refer to the associated reference page.

Access the UI

Copy and paste this link into a Chrome or Firefox browser to access the UI.

Note: You may need to modify the URL by changing localhost to a hostname or IP address depending on how and where you installed MemSQL tools. The hostname or IP address must be that of the main deployment host, which is typically the Master Aggregator.

Deploy MemSQL

Follow the instructions in the UI to deploy MemSQL.

Troubleshooting

  • Message: unknown command "ui" for "memsql-deploy"

    Solution: Confirm that MemSQL Toolbox v1.6 or later has been installed on the main deployment host.

  • Message: memsql-deploy ui is not currently supported by MemSQL.

    Solution: The installed version of MemSQL Toolbox does not support deploying MemSQL via the UI. Please select another deployment option.

  • Message: Registered hosts detected. MemSQL Toolbox supports managing only one cluster per instance. To view them, run 'memsql-toolbox-config list-hosts'. To remove them, run 'memsql-toolbox-config unregister-host'

    Solution: MemSQL Toolbox can only manage a single instance of MemSQL.

Info

Note: The following instructions assume that the deploying user has sudo privileges. For non-sudo deployments, you may either use a cluster file, or refer to the Deploy MemSQL via Tarball guide.

You can deploy MemSQL onto each host from the main deployment host and create the MemSQL nodes for your cluster.

From the main deployment host, deploy the MemSQL on all of your hosts using the setup-cluster command. Hosts are specified via the --master-host, --aggregator-hosts, and --leaf-hosts flags as comma-separated host names. The --password flag specifies the password for the root database user.

Other than the main deployment host being specified as the --master-host, the other hosts in your cluster can be used as hosts for the child aggregator or leaf nodes.

memsql-deploy setup-cluster -i /path/to/yourSSHkey \
    --license [YOUR LICENSE KEY] \
    --master-host <main_IP_address> \
    --aggregator-hosts <child_agg_IP_address> \
    --leaf-hosts <leaf1_IP_address>,<leaf2_IP_address> \
    --password <secure_password> \
    --version 7.1

If you are deploying in an environment with no Internet access, you must specify the absolute path to the memsql-server RPM or Debian package you downloaded in the previous step.

memsql-deploy setup-cluster -i /path/to/yourSSHkey \
    --file-path <memsql-server-package> \
    --license [YOUR LICENSE KEY] \
    --master-host <main_IP_address> \
    --aggregator-hosts <child_agg_IP_address> \
    --leaf-hosts <leaf1_IP_address>,<leaf2_IP_address> \
    --password <secure_password>

For large clusters with many hosts, it may be inconvenient to have to input all the host names in the command line. In place of the individual flags, you may instead use a cluster file. See the Cluster File section for more information.

Note: If your license key is not shown in the code block above, you can retrieve it from the MemSQL Customer Portal.

Info

If your host does not have the which command available, you will need to specify the correct package through the --force-package-format {rpm|deb} flag when running the setup-cluster command.

The setup-cluster command does several things for you:

  • Installs the latest memsql-server package on all hosts in your cluster.

  • Deploys MemSQL engine across all of the hosts in your cluster.

  • Creates the master aggregator. In this tutorial, the master aggregator resides on the main deployment host.

  • Creates any child aggregators specified in either the host file or in the command-line.

  • Creates leaf nodes for your cluster. Note: The setup-cluster command only creates one node per host. If your host is NUMA capable and has more than one NUMA node, you can install additional leaf nodes using the manual steps described in the Comprehensive Install Guide.

  • By default, the setup-cluster command will also enable High Availability. To disable High Availability, use the flag --high-availability=false in the setup-cluster command.

After you have deployed your cluster, run memsql-admin optimize. This command checks your current cluster configuration against a set of best practices and either makes changes to maximize performance or provides recommendations for you. For hosts with NUMA support, this command will bind the leaf nodes to specific NUMA nodes.

memsql-admin optimize

If you encounter errors running either of these commands, verify that your deployment environment satisfies the following conditions:

  • You can SSH to every host in the cluster using the IPs specified in the setup-cluster step above.

  • Your deployment user has root or sudo privileges in order to install packages on all hosts:

    sudo apt-get install ...
    
  • Port 3306 on all hosts is open to all other hosts in the cluster.

If your environment does not meet these requirements, or if you require additional customization during the deployment process, you can deploy this same cluster using the Comprehensive Install Guide.

As of MemSQL Toolbox 1.3.0, the memsql-deploy setup-cluster command now accepts a YAML-based cluster configuration file (or simply “cluster file”), the format of which is validated before attempting to set up the specified cluster.

Using a cluster file is the recommended method for creating new MemSQL clusters.

The command is designed to be consistent, where re-running the memsql-deploy setup-cluster command with the same cluster file will always produce the same cluster. This methods is also resilient, allowing errors encountered at any stage of the cluster construction process to be corrected, and memsql-deploy setup-cluster re-run, in order to generate the desired cluster.

Cluster File Format

license:                      <LICENSE | /path/to/LICENSE-file> [Required to bootstrap Master Aggregator]
high_availability:            <true | false>
memsql_server_version:        <the version of memsql you want to install (6.7+)>
memsql_server_file_path:      <path to the downloaded memsql server file>
package_type:  		          <deb|rpm|tar> [Required if multiple package present]
root_password:		          <default password to be used for all nodes>
optimize:                     <true | false>
optimize_config:
  memory_percentage:          <percentage of memory you want memsql to use>
  no_numa: 			          <true|false>
hosts:
  - hostname:                 <host-name> [Required]
    localhost:                <true | false> 
    memsqlctl_path:           <path to memsqlctl> [ADVANCED]
    memsqlctl_config_path:    <path to memsqlctl config> [ADVANCED]
    tar_install_dir:          <path to tar install dir> [ADVANCED]
    tar_install_state:        <path to tar install state> [ADVANCED]
    ssh:                      [Required for remote Hosts]
      host:                   <ssh host name>
      port:                   <ssh port>
      user:                   <ssh user>
      private_key:            <path to your identity key>
    nodes:
    - register:               <true | false>
      role:                   <Unknown | Master | Leaf | Aggregator> (case sensitive) [Required]
      no_start:               <true | false>
      config: 
        auditlogsdir:         <path to auditlogs directory> [ADVANCED]
        baseinstalldir:       <path to base install directory> [ADVANCED]
        configpath:           <path to configuration path> [ADVANCED] [Required if register is true]
        datadir:              <path to data directory> [ADVANCED]
        disable_auto_restart: <true | false>
        password:             <secure-password>
        plancachedir:         <path to plancache directory> [ADVANCED]
        port:                 <port number> [Required for node creation]
        tracelogsdir:         <path to tracelogs directory> [ADVANCED]
        bind_address:         <bind address> [ADVANCED]

You can deploy your own MemSQL cluster with your desired cluster configuration using the cluster file template above, and/or the example cluster files below.

After creating the cluster file, you can deploy the corresponding MemSQL cluster via the memsql-deploy setup-cluster command.

For example:

For sudo deployments, run the following with the path to the cluster file as input.

memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>

For non-sudo deployments, run the following from the memsql-toolbox directory with the path to the cluster file as input.

./memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
Info

Review the cluster file examples below to see how the cluster files differ for sudo, non-sudo, online, and offline deployments.

Cluster File Notes

  • license: Use your MemSQL license from the MemSQL Customer Portal. This can be the license itself, or the full path to a text file with the license in it.

  • memsql-server_version: You may specify either a major release of MemSQL (such as 7.1) or a specific release (such as 7.1.1). When a major release is specified, the latest patch level of that release will be deployed.

  • register: Set the value of this field to false to create a new node. Set the value to true if the node is already present and you want to register it to MemSQL toolbox. The configpath field and value are also required when register is set to true. Do not set this value to true to create a new node. For more information, refer to the memsql-deploy setup-cluster reference page.

  • Indicating a Host: You may use either an IP address or a hostname when indicating a host in the cluster file.

  • Aggregator Hosts: When deploying MemSQL, it is recommended that you deploy each Aggregator to its own individual host. If the Master Aggregator goes down, the Child Aggregators can keep running queries, and coordinating and executing writes. In this scenarios, the only operations that can’t be done are DDL commands and reference table management, which must be done on the Master Aggregator.

  • Optimize the Cluster: It is recommended that you include the optimize field in the cluster file and set it to true. Doing so checks your current cluster configuration against a set of best practices and either makes changes to maximize performance or provides recommendations for you. For hosts with NUMA support, this command will bind the leaf nodes to specific NUMA nodes.

Cluster File Examples

MemSQL uses a combination of aggregator and leaf nodes that are typically configured in a specific ratio. To learn more about configuring a MemSQL cluster, refer to the Distributed Architecture overview.

The examples below deploy two different types of MemSQL cluster:

  • A multi-host, multi-node MemSQL cluster with four hosts, two aggregators, and two leaf nodes

  • A multi-host, multi-node MemSQL cluster with two hosts, a single aggregator, and two leaf nodes

These cluster file examples can be used as a starting point for deploying a MemSQL cluster that fulfills your specific requirements.

Example 1: Four Hosts, Four Nodes

For this example, you will need four hosts and the ability to ssh into each host from the main deployment host.

Online Deployments

For online deployments, where the main deployment host can connect to the Internet:

  • For sudo users, set package_type to either rpm for Red Hat distributions or deb for Debian distributions to download and deploy the appropriate memsql-server.

  • For non-sudo users, set package_type to tar to download and deploy a memsql-server tarball.

Offline Deployments

For offline deployments, where the main deployment host cannot connect to the Internet:

  • For sudo users, replace memsql_server_version with memsql_server_file_path to specify the location of the memsql-server .rpm, or .deb file downloaded previously. This must be the full path to memsql-server, including the memsql-server filename.

  • For non-sudo users, replace memsql_server_version with memsql_server_file_path to specify the location of the memsql-server tarball file downloaded previously. This must be the full path to memsql-server, including the memsql-server filename.

license: <license-from-portal-memsql.com>
high_availability: true
memsql_server_version: 7.1
package_type: rpm
hosts:
- hostname: 172.16.212.165
  localhost: true
  ssh: 
    host: 172.16.212.165
    private_key: /home/<user>/.ssh/id_rsa
  nodes:
  - register: false
    role: Master
    config:
      password: <secure-password>
      port: 3306
- hostname: 172.16.212.166
  localhost: false
  ssh: 
    host: 172.16.212.166
    private_key: /home/<user>/.ssh/id_rsa
  nodes:
  - register: false
    role: Aggregator
    config:
      password: <secure-password>
      port: 3306
- hostname: 172.16.212.167
  localhost: false
  ssh: 
    host: 172.16.212.167
    private_key: /home/<user>/.ssh/id_rsa
  nodes:
  - register: false
    role: Leaf
    config:
      password: <secure-password>
      port: 3306
- hostname: 172.16.212.168
  localhost: false
  ssh: 
    host: 172.16.212.168
    private_key: /home/<user>/.ssh/id_rsa
  nodes:
  - register: false
    role: Leaf
    config:
      password: <secure-password>
      port: 3306

Using this cluster file, memsql-deploy setup-cluster:

  1. Registers four hosts to the cluster.

  2. Enables High Availability.

  3. Installs the latest patch level of memsql-server v7.1 on all four hosts.

  4. Creates a Master Aggregator node on port 3306 on host 172.16.212.165 and sets the MemSQL password to the one specified in the cluster file.

  5. Creates a Child Aggregator node on port 3306 on host 172.16.212.166 and sets the MemSQL password to the one specified in the cluster file.

  6. Creates a leaf node on port 3306 on host 172.16.212.167 and sets the MemSQL password to the one specified in the cluster file.

  7. Creates a leaf node on port 3306 on host 172.16.212.168 and sets the MemSQL password to the one specified in the cluster file.

  8. For sudo deployments, run the following with the path to the cluster file as input.

    memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
    
  9. For non-sudo deployments, run the following from the memsql-toolbox directory with the path to the cluster file as input.

    ./memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
    

Example 2: Two Hosts, Four Nodes

For this example, you will need two hosts and the ability to ssh into each host from the main deployment host.

Online Deployments

For online deployments, where the main deployment host can connect to the Internet:

  • For sudo users, set package_type to either rpm for Red Hat distributions or deb for Debian distributions to download and deploy the appropriate memsql-server.

  • For non-sudo users, set package_type to tar to download and deploy a memsql-server tarball.

Offline Deployments

For offline deployments, where the main deployment host cannot connect to the Internet:

  • For sudo users, replace memsql_server_version with memsql_server_file_path to specify the location of the memsql-server .rpm, or .deb file downloaded previously. This must be the full path to memsql-server, including the memsql-server filename.

  • For non-sudo users, replace memsql_server_version with memsql_server_file_path to specify the location of the memsql-server tarball file downloaded previously. This must be the full path to memsql-server, including the memsql-server filename.

license: <license-from-portal-memsql.com>
memsql_server_version: 7.1
package_type: rpm
root_password: <secure-password>
hosts:
- hostname: 172.16.212.165
  localhost: true
  ssh:
    host: 172.16.212.165
    private_key: /home/<user>/.ssh/id_rsa
  nodes:
  - register: false
    role: Master
    config:
      auditlogsdir: /data/memsql/Master/auditlogs/
      datadir: /data/memsql/Master/data
      plancachedir: /data/memsql/Master/plancache
      tracelogsdir: /data/memsql/Master/tracelogs
      port: 3306
  - register: false
    role: Leaf
    config:
      auditlogsdir: /data/memsql/Leaf1/auditlogs
      datadir: /data/memsql/Leaf1/data
      plancachedir: /data/memsql/Leaf1/plancache
      tracelogsdir: /data/memsql/Leaf1/tracelogs
      port: 3307
- hostname: 172.16.212.166
  localhost: false
  ssh:
    host: 172.16.212.166
    private_key: /home/<user>/.ssh/id_rsa
  nodes:
  - register: false
    role: Leaf
    config:
      auditlogsdir: /data/memsql/Leaf2/auditlogs
      datadir: /data/memsql/Leaf2/data
      plancachedir: /data/memsql/Leaf2/plancache
      tracelogsdir: /data/memsql/Leaf2/tracelogs
      port: 3307

Using this cluster file, memsql-deploy setup-cluster:

  1. Registers two hosts to the cluster.

  2. Installs the latest patch level of memsql-server v7.1 on both hosts.

  3. Creates a Master Aggregator node on port 3306 on host 172.16.212.165 and sets the MemSQL password to the one specified by the value in the root_password field.

  4. Creates a leaf node on port 3307 on host 172.16.212.165 and sets the MemSQL password to the one specified by the value in the root_password field.

  5. Creates a leaf node on port 3307 on host 172.16.212.166 and sets the MemSQL password to the one specified by the value in the root_password field.

  6. Sets the paths for the audit logs, data, plancache, and trace logs on each host. Notice that each field has its own path.

    Alternatively, you can replace these individual fields and paths on each node definition with a single baseinstalldir field and path, such as baseinstalldir: /data/memsql/Master. Each node definition would then resemble:

    nodes:
      - register: false
        role: Master
        config:
          baseinstalldir: /data/memsql/Master
          port: 3306
    
  7. For sudo deployments, run the following with the path to the cluster file as input.

    memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
    
  8. For non-sudo deployments, run the following from the memsql-toolbox directory with the path to the cluster file as input.

    ./memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
    

Option 1: Command Line

You can deploy your MemSQL cluster on a single host using the memsql-deploy cluster-in-a-box command. This command will create two nodes: A master aggregator node that runs SQL queries and aggregates the results, and a single leaf node, which is responsible for storing and processing data. These two nodes form the most basic MemSQL cluster.

Online Deployments

memsql-deploy cluster-in-a-box --license <license> \
--version 7.1 --password <secure-password>

Note: You can retrieve the license key from the MemSQL Customer Portal.

Warning

If your host does not have the which command available, you will need to specify the correct package through the --force-package-format {rpm|deb} flag when running the cluster-in-a-box command.

Offline Deployments

If you are deploying in an environment without Internet access, you must specify the absolute path to the memsql-server RPM or Debian package you downloaded in the previous step.

memsql-deploy cluster-in-a-box --license <license> \
--file-path <memsql-server-package> \
--password <secure-password>

Now that you have your cluster set up, you can connect to your cluster using the memsql client application by simply typing memsql in a terminal window from within the host.

Option 2: Cluster File

This example is equivalent to memsql-deploy cluster-in-a-box, where a single-host cluster is created with two nodes: a Master Aggregator and a single leaf node.

Online Deployments

For online deployments, where the main deployment host can connect to the Internet:

  • For sudo users, set package_type to either rpm for Red Hat distributions or deb for Debian distributions to download and deploy the appropriate memsql-server.

  • For non-sudo users, set package_type to tar to download and deploy a memsql-server tarball.

Offline Deployments

For offline deployments, where the main deployment host cannot connect to the Internet:

  • For sudo users, replace memsql_server_version with memsql_server_file_path to specify the location of the memsql-server .rpm, or .deb file downloaded previously. This must be the full path to memsql-server, including the memsql-server filename.

  • For non-sudo users, replace memsql_server_version with memsql_server_file_path to specify the location of the memsql-server tarball file downloaded previously. This must be the full path to memsql-server, including the memsql-server filename.

license: <license-from-portal.memsql.com>
memsql_server_version: 7.1
package_type: deb
hosts:
- hostname: 127.0.0.1
  localhost: true
  nodes:
  - register: false
    role: Master
    config:
      password: <secure-password>
      port: 3306
  - register: false
    role: Leaf
    config:
      password: <secure-password>
      port: 3307

Using this cluster configuration file, memsql-deploy setup-cluster:

  1. Registers a single, local host to the cluster.

  2. Installs memsql-server v7.1 on this local host.

  3. Creates a Master Aggregator node on port 3306 and sets the MemSQL password to the one specified in the cluster file.

  4. Creates a leaf node on port 3307 and sets the MemSQL password to the one specified in the cluster file.

  5. For sudo deployments, run the following with the path to the cluster file as input.

    memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
    
  6. For non-sudo deployments, run the following from the memsql-toolbox directory with the path to the cluster file as input.

    ./memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>
    

Once your MemSQL cluster is set up and ready to use, you can connect to it using the memsql client application by typing memsql in a terminal window on the main deployment host.

In addition, you can use MemSQL Studio to easily monitor, debug, and interact with all of your MemSQL clusters. See the next step for how to connect and use MemSQL Studio.