SingleStore Tools is our newer approach to helping you configure and manage downloadable SingleStore software. (If you choose SingleStore Managed Service, we do the administration for you.) Most users are already on SingleStore Tools. In recent months, we’ve introduced a slew of new features that will encourage most remaining SingleStore users to move to Tools from our former solution, SingleStore Ops. In this blog post, I’ll describe the new features, the philosophy behind SingleStore Tools, and why we encourage you to use SingleStore Tools exclusively going forward.
SingleStore introduced SingleStore tools and SingleStore Studio the year before last, to replace SingleStore Ops. Both the tools and Studio are updated every two weeks. This blog post focuses on improvements to SingleStore Tools which have occurred over the last few months, and we encourage you to use Tools to help with your transition to SingleStore DB 7.1. We will describe changes to SingleStore Studio in a separate blog post in the near future.

What’s New in SingleStore Tools

We can summarize the new features in a (long) sentence: we have pre-installation configuration checking; online upgrades (meaning, no noticeable downtime for your database); improvements to backup management; new cluster reporting; and the put file capability, useful for index files, for example. These capabilities are only available in SingleStore Tools, so make sure you are on the latest version, to take full advantage of everything we have added. Read on for more details.

Improvements to the Reporting Tool

To begin with, if you are installing SingleStore for the first time on new hardware (in the cloud, or on-premises), you will want to use a new feature of
memsql-report
: the
--validate-env
flag. This is so important, when you do need it, that we gave it a whole separate blog post.
In the near future, you will see a new flag for
memsql-report
. The —
include-performance-checks
flag is one of the reporting features that’s designed to be used on a SingleStore cluster that is already in operation. It will allow you to run a number of performance-oriented checks on the cluster, ensuring that everything is running optimally.

Online Upgrade

We have added integrated and automated support for rolling online upgrades into SingleStore Tools version 1.4.4. By running memsql-deploy upgrade –online, Tools will upgrade and restart the SingleStore nodes sequentially, by availability group, maintaining cluster availability through the upgrade. This is especially important for mission-critical applications where it’s difficult or impossible to take the cluster down for version upgrades.

Backup Management

SingleStore Tools version 1.5.0 added backup creation and management directly into the toolset (rather than requiring backups to be run as SQL queries to the cluster). This allows for enhanced backup management, and it means you can validate and restore backups – all with the native toolset.
Some of the features now available natively for backup management in SingleStore tools:
  • memsql-admin create-backup
    – back up one or more databases to a repository
  • memsql-admin delete-backup
    – delete a backup
  • memsql-admin list-backups
    – list all the latest backups
  • memsql-admin restore-backup
    – restore a database from a backup
  • memsql-admin summarize-backups
    – print summary information for backups
  • memsql-admin validate-backup
    – verify that a backup is correct
We have also added backup-specific enhancements to the tool, such as speeding up backup deletion by parallelizing the operation, and including backup tags (using -t) to create custom attributes. This allows you to search for those attributes when selecting from existing backups.

Cluster Reports

SingleStore Cluster Reports was originally a feature that was used by SingleStore Support to collect cluster information and diagnose problems with the cluster, including incorrect configurations. The team has now included this capability directly in our toolset as
memsql-report
.
These reports started with two main operations.
collect
creates a report from the cluster. check allows you to evaluate the report to see if there are issues with the cluster or configuration. We also support
collect-local
, which runs the report on a single machine. This option is useful if there is an issue with connectivity, or if you just need the output from part of the cluster

Put File

To easily copy a file – such as an index file – to all nodes in a cluster, use this new command option:
memsql-admin put-file
. You can specify the source path (
-s
) and target path (
-t
) to easily copy a file to all nodes in the cluster. This makes it easy to distribute files around the cluster, without having to use a third party command or tool.

How We Got Here

If you are still using an older version of SingleStore, you may well still be using SingleStore Ops, our old deployment and management tool. SingleStore Tools was introduced a year and a half ago to incorporate many improvements. SingleStore Tools includes the SingleStore Toolbox and SingleStore Client, described here. SingleStore Tools also includes SingleStore Studio; we will provide an update on Studio in the near future.
To begin with, each of the components in SingleStore Tools is separate from the SingleStore core software. This simplifies product development, increases flexibility, reduces the potential for configuration issues, and improves stability for all of SingleStore’s suite of software solutions. The new toolset also simplifies integration and improves security.
SingleStore Tools:
  • Provides a stateless runtime that communicates directly with the SingleStore engine
  • Is secure by default, with all communication over SSH and our internal protocol, compatible with MySQL wire protocol
  • Features built-in cluster reporting and diagnostics
  • Offers fully automated online upgrades to new SingleStore releases
  • Delivers simple and scalable cluster deployment, using cluster configuration templates
  • Integrates directly with existing DevOps tools (Ansible, Chef, Puppet, and others)
  • Includes machine-parsable outputs for every command
New versions of Tools are released every two weeks. Here’s how the pieces fit together.

SingleStore Toolbox

SingleStore Toolbox contains all the command line tools needed to deploy, administer, and manage your cluster. This includes registering host machines with
memsql-toolbox-config
, installing memsql to host machines in the cluster with
memsql-deploy
, managing your cluster with
memsql-admin
, and generating and checking reports using
memsql-report
.
Each of the tools in the toolbox communicates with the engine through
memsqlctl
, a low-level command interface directly integrated into the SingleStore engine. This interface means SingleStore tools has no client modules to deploy, and can never be out of sync with your engine version.
Having a well-defined communication path also means thay commands can be combined or layered with each other to provide a simplified user experience. For instance, deployment of SingleStore can be done manually by registering host machines, installing SingleStore, and then adding aggregators and leaves. Or it can be done in an automated way using
setup-cluster
, which aggregates and runs these underlying operations all at once.

SingleStore Studio

SingleStore Studio is a browser-based graphic user interface that allows cluster administrators to quickly and easily monitor and debug any SingleStore deployment. SingleStore Studio can be deployed anywhere, and communicates with SingleStore clusters using standard SQL queries to the master aggregator, making it simple to deploy and configure.
Since first building Studio we have enhanced it with a number of key additions including a complete physical and logical monitoring system, expanded authentication support, secure user connections, and expanded usage metrics. All these features combine to make Studio the best interface to date for monitoring SingleStore deployments. We’ll give an update on Studio in a blog post in the near future.

SingleStore Client

SingleStore client is a lightweight client application that allows you to run SQL queries against SingleStore directly from any terminal window. You use it in the same way as you use the mySQL client. This makes it incredibly simple to connect to your cluster as soon as you have deployed it.

Installing SingleStore with SingleStore Tools

SingleStore Tools make installation of a cluster easy by giving you several ways to deploy SingleStore:
  • Basic installations can be done with
    setup-cluster
    , which takes host and leaf inputs, and configures and deploys everything needed for a production cluster.
  • For larger and more complex configurations,
    setup-cluster
    can take a yaml configuration file for simple and repeatable deployment.
  • Developers looking to deploy a simple SingleStore cluster with only the bare minimum can use the “cluster-in-a-box” deployment to launch just a master aggregator and a leaf node onto a single machine.

Simple Install with Setup-Cluster

A simple setup-cluster deployment is done by specifying all of the cluster attributes directly in the deploy command:
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 6.8

Large Cluster Install with Setup-Cluster and a Cluster Configuration File

Larger cluster configurations can be configured using a yaml configuration, and deployed by running setup-cluster, with the –cluster-file flag pointing to the configuration file:
memsql-deploy setup-cluster --cluster-file </path/to/cluster-file>

license: <license-from-portal-singlestore.com>

    memsql_server_version: 7.0.16

    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
As you can see in the above example, we specify a few cluster settings – the license, SingleStore version, package type, and root password – and then hosts are defined by hostname or IP, and SingleStore nodes are nested underneath. This way it’s possible to deploy one or more SingleStore nodes to each host in the cluster. Users can optionally also specify other custom attributes, such as non-standard directories for things like auditlogs, data, plancache, and tracelogs.
Lastly, each node deployed will have a role, such as a master (aggregator) or a leaf node, and a port, unique per host. (If you deploy multiple nodes to one host, make sure each node has its own port; we often use 3306, 3307, etc.)

Manual Cluster Installation with SingleStore tools

For a full manual deployment, each toolbox step can be run manually to register hosts, install memsql, and create each node in the cluster:
memsql-toolbox-config register-host --localhost --host <IP_address>

memsql-toolbox-config list-hosts

memsql-deploy install --all --version 6.8

memsql-admin create-node --host <IP_address> --password <secure_password>

memsql-admin bootstrap-aggregator --memsql-id <SingleStore_ID> --license [YOUR LICENSE KEY]

memsql-admin create-node --host <IP_address> --password <secure_password>

memsql-admin add-aggregator --memsql-id <SingleStore_ID> --password <secure_password>

memsql-admin create-node --host <IP_address> --password <secure_password>

memsql-admin add-leaf --memsql-id <SingleStore_ID> --password <secure_password>

memsql-admin optimize
This is a great way to deploy if you have non-standard configurations that prevent you from installing with setup-cluster, such as running SingleStore as a different user than the SSH user that is installing the software. (This would require an update to the memsqlctl.hcl file on each of the hosts, after registering them and installing SingleStore).

Developer Installation with Cluster in a Box

The memsql-deploy cluster-in-a-box installation provides a basic installation of one master aggregator and one leaf node on a single machine. It’s a quick and easy way to deploy SingleStore in its simplest form for testing and basic development activities:
memsql-deploy cluster-in-a-box --license AAAAAAa/Aaa/AA==

Migrating from SingleStore Ops to SingleStore Tools

While many of our customers are already using SingleStore Tools to manage their clusters, for customers with existing deployments using the legacy SingleStore Ops, we have developed an easy-to-use migration process, making it easy to begin managing your cluster with SingleStore Tools.
This integrated process migrates from SingleStore Ops to SingleStore Tools by putting Ops in manual control mode; applying a new, updated license; outputting the cluster configuration into a yaml cluster file; and then using that cluster file to register the cluster with tools.
memsql-ops cluster-manual-control --enable

memsql-ops license-add --license-key <license>

sudo memsql-ops migration-setup

memsql-deploy setup-cluster --cluster-file <cluster_file>

memsql-toolbox-config list-hosts

memsql-admin list-nodes

memsql-admin restart-node --all

memsql-ops memsql-list

memsql-ops memsql-unmonitor 44CDE71

memsql-ops memsql-list

sudo memsql-ops agent-uninstall --uninstall-ops-only --all

Note: If you are transitioning to SingleStore Tools, you can see a mapping from the cluster operations in the old SingleStore Ops tool to the new SingleStore Tools commands here.

Try the Latest SingleStore with SingleStore Tools

There is a lot of other great functionality in both the SingleStore database software and in  SingleStore Tools, and the best way to experience all of it is to try it for yourself. Check out our latest deployment guide using SingleStore tools here, or check out our SingleStore channel on YouTube, to see the latest and greatest capabilities of the world’s fastest scale-out SQL database. You can also try SingleStore Software or SingleStore Managed Service for free.