As of SingleStore DB 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 SingleStore DB 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 SingleStore DB 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.

sdb-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 sdb-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 SingleStore DB Tools. The hostname or IP address must be that of the main deployment host, which is typically the Master Aggregator.

Deploy SingleStore DB

Follow the instructions in the UI to deploy MemSQL.

Troubleshooting

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

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

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

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

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

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

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 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.

sdb-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.0

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.

sdb-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.

The setup-cluster command does several things for you:

• Registers the hosts in the SingleStore DB cluster.

• Validates the deployment environment by running a series of collectors and pre-installation checks. (Learn more)

• Verifies that the cluster is in a clean state without any nodes. (For a cluster with existing nodes, a cluster file should be used instead.)

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

• Deploys the 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 sdb-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.

sdb-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 SingleStore DB Toolbox 1.3.0, the sdb-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 sdb-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 sdb-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]
      availability_group:     <availability group> 
      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 sdb-deploy setup-cluster command.

For example:

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

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

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

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

Cluster File Notes

• high_availability: Used to enable high availability on the cluster.

  • If set to true, each node may be assigned an availability group via the availability_group field.

  • Refer to Availability Groups for more information.

• 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.0) or a specific release (such as 7.0.19). 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 sdb-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.0
package_type: rpm
hosts:
- hostname: 172.16.212.165
  localhost: true
  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, sdb-deploy setup-cluster:

1. Registers four hosts to the cluster.

2. Enables High Availability.

3. Installs the latest patch level of memsql-server v7.0 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.

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

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

   ./sdb-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.0
package_type: rpm
root_password: <secure-password>
hosts:
- hostname: 172.16.212.165
  localhost: true
  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, sdb-deploy setup-cluster:

1. Registers two hosts to the cluster.

2. Installs the latest patch level of memsql-server v7.0 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.

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

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

   ./sdb-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 sdb-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

sdb-deploy cluster-in-a-box --license <license> \
--version 7.0 --password <secure-password>

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

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.

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

Option 2: Cluster File

This example is equivalent to sdb-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.0
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, sdb-deploy setup-cluster:

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

2. Installs memsql-server v7.0 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.

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

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

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