Using Grafana dashboards for monitoring SAS SpeedyStore

[#1]

SingleStore have announced that they are depreciating SingleStore DB Studio as their monitoring solution and are moving to using Grafana dashboards. In this post we will look at implementing the Grafana dashboards for a SAS SpeedyStore deployment, using the SAS Viya Monitoring for Kubernetes Git project as the base.

This post ended up being longer than expected, so if you don’t have time at the moment, but you would like to gain experience in implementing Grafana dashboards to monitor SAS SpeedyStore, skip to the end.

TL;DR, see the SAS SpeedyStore workshop in learn.sas.com

With that out of the way. Let’s have a look at implementing the Grafana dashboards.

The SingleStore documentation supports SingleStore deployments in a variety of environments. Therefore, to implement their monitoring solution you need to make sure you identify the steps specifically for running SingleStore on Kubernetes. Then translate those instructions to make sense for the SAS SpeedyStore platform and using the SAS Viya Monitoring for Kubernetes project found in GitHub.

So, let me piece together the steps for you… 😊

SingleStore’s native monitoring solution utilizes a SingleStore pipeline to pull the data from the exporter process on the Source Cluster and store it in a database named “metrics” found in the SingleStore “Metrics Cluster”. This is shown in the diagram below. Note that the metrics database can either reside within the same cluster as the Source Cluster, or within a dedicated cluster.

Source: SingleStore documentation

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

In our case, in this example of using monitoring with a SAS SpeedyStore deployment, the metrics database is kept in the SpeedyStore cluster.

The first step is to deploy the SAS Viya Monitoring for Kubernetes framework; found on GitHub.com in the sassoftware repository of projects (viya4-monitoring-kubernetes). This will provide the monitoring framework and the Grafana instance that we will use for the SingleStore Grafana Dashboards.

With this in place you are ready to configure the SingleStore monitoring.

Configure the SingleStore pipeline and metrics database

You have a few options to implement the pipeline, exporter and metrics database. You can manually create the configuration, or you can use the SingleStore Toolbox. The SingleStore Toolbox is used to deploy, administer, and manage a SingleStore cluster. However, most, or nearly all, the Toolbox functions are targeted at non-Kubernetes deployments. But there are a few functions that we can use for a SAS SpeedyStore deployment.

One function is the sdb-admin start-monitoring-kube command which is used to configure and start the monitoring. It has a number of flags to control its operations. See the SingleStore documentation for more information: start-monitoring-kube

A core part of the monitoring is the exporter process. Due to the required permissions, the exporter process is typically run as the SingleStore root user, but it is possible to run the process as another user. That user requires low level permissions to create and control the metrics database and pipelines.

In the SingleStore diagram above, you can see that the export process needs to run on the Master Aggregator. Therefore, you need to target the Master node, the node-sas-singlestore-cluster-master-0, node/pod in a SAS SpeedyStore deployment. This is the “exporter-host” for the sdb-admin command.

At a minimum, to configure and start the monitoring, including the metrics database, we need the following:

sdb-admin start-monitoring-kube --cluster-name sas-singlestore-cluster --namespace {NS} --user root --password {root_PWD} --exporter-host {clusterMasterIP}

Working through the command, the default name for the SingleStore cluster in a SAS SpeedyStore deployment is: sas-singlestore-cluster

It is possible to change this name, so it is important to confirm the cluster name. For this you can use the SingleStore CLI. You would use the following command to get the cluster name:

show global variables like 'cluster_name%';

As SingleStore runs in the SAS Viya namespace, the ‘namespace’ parameter is set to the SAS Viya namespace name.

This example is showing running the exporter process using the SingleStore “root” user. Therefore, you need to get the password for the root user. You can use the following command to get the password:

NS="viya_namespace_name"
root_PWD=$(kubectl -n ${NS} get secret sas-singlestore-cluster -o yaml | grep "ROOT_PASSWORD"|awk '{print $2}'|base64 -d --wrap=0)

Finally, you need to provide the fully-qualified host name or IP address for the exporter host. The name needs to be resolvable by the host running the sdb-admin command. This host also needs the KUBECONFIG for the Kubernetes cluster. It has to be able to access the cluster and the SAS Viya namespace.

By default, the KUBECONFIG environment variable or the ~/.kube/config file are used to discover the cluster. Alternatively, the --config-file option can be used to specify the kube config.

In the command example above, you can see that I’m passing the IP address for the Master node. For this you can use the following command:

NS="viya_namespace_name"
clusterMasterIP=$(kubectl -n ${NS} get pods -o wide | grep 'node-sas-singlestore-cluster-master-0' | awk '{print $6}')

This gets the ClusterIP for the master node.

After running the command, the exporter process, the pipeline and the metrics database are created. To confirm this, I used the SingleStore Studio. For example:

Configure the “Monitoring user”

To implement the Grafana dashboards, Grafana needs to connect to the metrics database. A database user is required, you need what I call the “monitoring user”. While the Grafana requirements are fairly simple in terms of database permissions, I would suggest that a good approach can be use the “monitoring user” more like an administrator. In this case it would need additional permissions.

For example, you might grant the following permissions:

GRANT CLUSTER, SHOW METADATA, SELECT, PROCESS ON *.* to '{S2MonitorUser}'@'%';
GRANT SELECT, CREATE, INSERT, UPDATE, DELETE, EXECUTE, INDEX, ALTER, DROP, CREATE DATABASE, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, CREATE PIPELINE, DROP PIPELINE, ALTER PIPELINE, START PIPELINE, SHOW PIPELINE ON metrics.* to '{S2MonitorUser}'@'%';

Alternatively, you might GRANT the “monitoring user” minimal permissions, and create a “monitoring administrator” user to manage the metrics database, pipelines and the exporter process.

With the database user in place, the Grafana connection profile and dashboards can be created.

Configuring Grafana

One of the advantages of using the Grafana instance from the SAS Viya Monitoring for Kubernetes project is that the monitoring components are deployed into the Kubernetes cluster running SAS Viya. Because the SAS SpeedyStore offering is there, too, then this means that the dashboards for monitoring SAS Viya and SingleStore are all in the same place.

Given that Grafana is running in the same cluster as the metrics database, the Grafana connection profile (the datasource definition for the metrics database) can use the internal Kubernetes DNS name for the DDL service. The Kubernetes internal DNS naming format is:

service-name.namespace.svc.cluster.local

Therefore, if my SAS Viya namespace was called “viya”, the DNS name for the DDL service is:

svc-sas-singlestore-cluster-ddl.viya.svc.cluster.local

The datasource definition can be created manually through the Grafana web application’s user interface or programmatically. For example, you could create a file called “speedystore-datasource.yaml” to define the datasource connection.

apiVersion: 1

datasources:
  - name: monitoring
    type: mysql
    url: svc-sas-singlestore-cluster-ddl.viya.svc.cluster.local:3306
    database: metrics
    user: {Monitoring_User}
    secureJsonData:
      password: {Monitoring_User_Password}
    isDefault: false
    version: 1

Note, the ‘isDefault’ value is set to false. This is because we do not want this new datasource to be the default datasource used by Grafana for other dashboards, we only want to use it with the SAS SpeedyStore dashboards.

The datasource can be defined using a Kubernetes ConfigMap or secret. Given that the datasource definition has the database username and password, the best approach is to use a secret. The secret needs to be created in the namespace where Grafana is running.

Here is an example of creating the secret from the yaml file. The Grafana namespace is called ‘v4mmon’ and the secret is called 'grafana-metrics-connection’.

kubectl -n v4mmon create secret generic grafana-metrics-connection --from-file=$HOME/project/grafana/speedystore-datasource.yaml

Once the secret has been created, you need to apply a label of “grafana_datasource=1” to the secret, this will trigger the automatic provisioning (Ioading) of the datasource by Grafana. For example:

kubectl -n v4mmon label secret grafana-metrics-connection "grafana_datasource=1"

Loading the SingleStore Grafana Dashboards

You can download the cluster monitoring dashboards from SingleStore, using this link.

You could use the Grafana user interface to manually load the SingleStore dashboards. However, the SAS Viya Monitoring for Kubernetes project provides a utility to load additional dashboards. The script called 'deploy_dashboards.sh'.

Note, the dashboard files provided by SingleStore have file names that are mixed case and have spaces. To use the 'deploy_dashboards' script the file names must be lower-case alphanumeric without spaces.

Also, the dashboards do not have SingleStore in the title, nor do they have any tags associated with them. I would recommend updating the dashboard names and adding a tag to the dashboards. In my testing I added two tags: ‘sas-speedystore’ and ‘singlestore’.

Having the tags applied makes it easier to manage the dashboards. For example, the image below shows the dashboards loaded into Grafana.

As you can see from the image, without adding ‘SAS SpeedyStore’ or ‘SingleStore’ to the titles of the dashboards, by default they have names like “Cluster View”, “Disk Usage” and “Memory Usage”. Therefore, it is hard to identify them as SingleStore dashboards.

With the dashboards loaded, the configuration is complete. You are all set to monitor SAS SpeedyStore (SAS Viya and the SingleStore cluster).

Running a load test

With the configuration in place, I wanted to see if the dashboards were working, so I ran a few load tests. I used the SAS Viya batch CLI to create workload. The following image is from running this load test.

The load test used the SASHELP tables. It loaded a subset into a SingleStore database and then generated three new “large” tables based on them. The throughput graphs show the peaks from running the load test.

Conclusion

While the SingleStore configuration can be created manually, using the SingleStore Toolbox definitely simplifies the setup. I also recommend taking the time to update the dashboard titles and to add the tags. Having the tags allows you to filter for the SingleStore dashboards.

I wouldn’t totally get rid of SingleStore Studio for the moment as it still provides useful functions to view the databases and as a query tool (if you don’t have any other DB query tool).

For my part, I was working in Azure. This post is based on my work to create a monitoring exercise for the SAS SpeedyStore workshop, that is available in learn.sas.com.

Finally, if you would like to learn more about SAS SpeedyStore, and practice deploying SAS SpeedyStore in Azure, register for the workshop!

SAS® SpeedyStore: Architect and Deploy the SAS® Viya® Platform with SingleStore

Find more articles from SAS Global Enablement and Learning here.