SingleStore DB

Configure Monitoring
Configure SingleStore DB Monitoring

Notice

You may continue to use the historical monitoring, SingleStore’s former monitoring solution, if you are currently unable to migrate to SingleStore’s native monitoring solution.

Please note that historical monitoring has been deprecated in favor of SingleStore’s native monitoring solution and may no longer function in future versions of SingleStore DB.

SingleStore’s native monitoring solution is designed to capture and reveal SingleStore DB cluster events over time. By analyzing this event data, you can identify trends and, if necessary, take action to remediate issues.

Terminology

Throughout this guide, the cluster that is being monitored will be referred to as the Source cluster, and the cluster that stores the monitoring data will be referred to as the Metrics cluster. The databases that store monitoring data will be referred to as the metrics database.

SingleStore Native Monitoring Solution High-Level Architecture
16083b579406d5.png

In SingleStore’s native monitoring solution, the Metrics cluster utilizes a SingleStore pipeline to pull the data from the exporter process on the Source cluster and stores it in a database named metrics. Note that this metrics database can either reside within the same cluster as the Source cluster, or within a dedicated cluster.

When these event data is then analyzed through the associated Grafana dashboards, trends can be identified and, if necessary, actions taken to remediate issues.

The provided Grafana dashboards include:

Dashboard

Description

Active session history

Aggregated resource consumption by activity and activity type

Activity history

Resource consumption by a specific activity over time

Detailed cluster view

A birds-eye view of a single SingleStore DB cluster

Information schema view

Provides a view into information_schema views PROCESSLIST, TABLES, and TABLE_STATISTICS

Memory usage

Granular breakdown of memory use for a host

SingleStore DB status and variables view

Collected status variables from each host in the cluster

Node breakout

System metrics from each host in the cluster

Node drilldown

System metrics from each host in the cluster, with the ability to focus on a specific metric subsystem

Prerequisites

Notice

These instructions have been developed for SingleStore DB clusters that have been installed and deployed via .rpm or .deb packages as a sudo user.

If your cluster was deployed via tarball as a non-sudo user, change to the directory (cd) in which singlestoredb-toolbox was untarred, and run all sdb-admin commands as ./sdb-admin.

Note that the Grafana instructions will require a user with sudo access to install and configure the associated Grafana components.

  • A SingleStore DB 7.3 or later cluster to monitor (the Source cluster).

  • Optional: A separate SingleStore DB 7.3 or later cluster to collect monitoring data (the Metrics cluster).

    • This can be the same as, or separate from, the Source cluster.

    • If you opt to use a separate cluster, we recommend a cluster with two aggregator nodes and two leaf nodes, each with 2TB disks and with high availability (HA) enabled.

  • Clusters are managed with SingleStore DB Toolbox 1.9.3 or later.

  • A Grafana 6.0.0 or later instance that can access the Metrics cluster.

  • SingleStore DB Toolbox is recommended for managing the clusters as automation during setup is provided through sdb-admin commands. While monitoring can be enabled through a series of SQL commands, the preferred method is to use SingleStore DB Toolbox.

Port Configuration

Default Port

Used by

Invoked by

3000

Grafana

User browser

3306

SingleStore DB

memsql_exporter

8080

SingleStore DB Studio

User browser

9104

memsql_exporter

SingleStore pipelines

Enable the Exporter Process

Notice

In most cases, both SingleStore DB Toolbox and SQL methods are provided to perform a given task. While either method may be used, the selected method must then be used throughout the remainder of the guide.

The memsql-exporter process (or simply the exporter) collects data about a running cluster. The user that starts the exporter (other than the SingleStore DB root user) must have the following permissions at a minimum:

GRANT CLUSTER on *.* to <user>
GRANT SHOW METADATA on *.* to <user>
GRANT SELECT on *.* to <user>
Stop the Existing Exporter Process

If you are currently using SingleStore’s former monitoring solution and are migrating to SingleStore’s new monitoring solution, stop the existing SingleStore DB exporter process by running the following commands on your Source cluster’s Master Aggregator host. Otherwise, skip to Start the New Exporter Process.

Note: Perform the following steps on the Source cluster Master Aggregator.

  1. Obtain the SingleStore DB exporter process PID.

    ps aux | grep memsql_exporter
    
  2. Kill the SingleStore DB exporter process.

    kill  <PID>
    
Start the New Exporter Process

Note: Perform the following steps on the Source cluster Master Aggregator.

SingleStore DB Toolbox

Run the following command to start the exporter. This command will start the exporter using the SingleStore DB root user and default port (9104). The exporter username and password are used to access the Source cluster. Refer to sdb-admin configure-monitoring for more information.

sdb-admin configure-monitoring \
--exporter-user root \
--exporter-password <secure-password>
SQL

You may start the exporter in the SingleStore DB engine by passing a user, port, and password to the exporter. The following example shows the the exporter being started with the SingleStore DB root user:

SET GLOBAL exporter_user = root;
SET GLOBAL exporter_password = '<secure-password>';
SET GLOBAL exporter_port= 9104;
How to Stop the Exporter Process

You may stop the exporter at any time by performing either of the following commands on the Source cluster Master Aggregator.

SingleStore DB Toolbox
sdb-admin configure-monitoring --stop-exporter
SQL

Use an engine variable to stop the exporter process by setting the port to 0.

SET GLOBAL exporter_port = 0;
Configure the metrics Database

The metrics database can either reside in the Source cluster, in a dedicated Metrics cluster, or in a remote SingleStore DB cluster that the Metrics cluster can access.

Note that the user that creates the metrics database may differ from the one that started the exporter. This user must have the ability to create tables, databases, and pipelines. At minimum, it requires the following permissions:

SingleStore DB Toolbox

The following command creates a database named metrics with a 10-day retention period for the data, and will also create the associated metrics and blobs pipelines. These pipelines are responsible for extracting data from the exporter and storing it in the metrics database.

The exporter-host value must be the Master Aggregator host in the Source cluster. The user and password are the credentials for the Metrics cluster. The variable retention-period defines the number of days that data is retained before being deleted. Refer to sdb-admin start-monitoring for more information.

Please note the following:

  • The database that is created to store monitoring data is named metrics by default. You may change the database name by updating the value of the --database-name option.

  • The database user is root by default. When creating the metrics database with a database user other than root, the SUPER privilege must be granted to this user. For example, for a dbmon user:

    GRANT SUPER ON *.* TO 'dbmon'@'%';

  • The IP address provided in the exporter-host argument must be resolvable by all hosts in the cluster. If the cluster you are monitoring has hosts that contain leaf nodes (as is common in a multi-host vs. single-host deployment), do not use localhost. Instead, provide the explicit IP address or hostname that the other hosts in the cluster can resolve.

  • This command must be run on the Metrics cluster.

sdb-admin start-monitoring \
--database-name metrics \
--exporter-host <exporter-hostname-or-IP-address> \
--user root \
--password <secure-password> \
--retention-period 10

Notice

For Metrics clusters running SingleStore DB 7.3.12 and earlier that are managed by Toolbox 1.11.7 and earlier, when a new Source cluster is monitored, the specified --retention-period will apply to all existing Source clusters.

For Metrics clusters running SingleStore DB 7.3.13 and later that are managed with Toolbox 1.11.8 and later, a unique --retention-period can be specified for each Source cluster. Therefore, when a new Source cluster is monitored, the specified --retention-period will only apply to that Source cluster.

metrics Database DDL

Alternatively, you may run the following SQL scripts to configure the database.

Please note the following:

  • The database that is created to store monitoring data is named metrics by default. You must edit these SQL statements manually to change the database name.

  • The database user is root by default. When creating the metrics database with a database user other than root, the SUPER privilege must be granted to this user. For example, for a dbmon user:

    GRANT SUPER ON *.* TO 'dbmon'@'%';

  • These commands must be run on the Master Aggregator of the Metrics cluster.

Create the metrics database and associated tables by copying, pasting, and executing each set of SQL statements below. This can be done via the SQL Editor in SingleStore DB Studio, or your favored SQL client.

These SQL statements are also available in a downloadable metrics-database-ddl_73.zip file from SingleStore.

CREATE DATABASE IF NOT EXISTS metrics;

USE metrics;

CREATE TABLE IF NOT EXISTS `metrics` (
    `labels`                                          JSON,
    `name`                                            TINYBLOB NOT NULL,
    `memsql_tags`                                            JSON COLLATE utf8_bin,
    `cluster` AS `memsql_tags`::$cluster                     PERSISTED TINYBLOB,
    `host` AS `labels`::$host                           PERSISTED TINYBLOB,
    `port` AS `labels`::$port                           PERSISTED SMALLINT(6),
    `role` AS `labels`::$role                           PERSISTED TINYBLOB,
    `extractor` AS substring_index(`name`, '_', 1)    PERSISTED TINYBLOB,
    `subsystem` AS substring_index(substring_index(`name`, '_', 2), '_', -1) PERSISTED TINYBLOB,
    `job` AS `memsql_tags`::$push_job                        PERSISTED TINYBLOB,
    `value`                                           DOUBLE NOT NULL,
    `intval` AS floor(`value`)                        PERSISTED BIGINT(20),
    `time_sec`                                        BIGINT(20) NOT NULL,
    KEY `name` (`cluster`,`extractor`,`subsystem`,`host`,`role`,`name`,`time_sec`) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);

CREATE TABLE IF NOT EXISTS act_samples (
    `cluster` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `memsql_tags` JSON NOT NULL,
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `ACTIVITY_TYPE` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `ACTIVITY_NAME` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `AGGREGATOR_ACTIVITY_NAME` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `DATABASE_NAME` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `NODE_ID` smallint(6) DEFAULT NULL,
    `PARTITION_ID` smallint(6) DEFAULT NULL,
    `CPU_TIME_MS` bigint(4) DEFAULT NULL,
    `CPU_WAIT_TIME_MS` bigint(4) DEFAULT NULL,
    `ELAPSED_TIME_MS` bigint(4) DEFAULT NULL,
    `LOCK_TIME_MS` bigint(4)  DEFAULT NULL,
    `NETWORK_TIME_MS` bigint(4)  DEFAULT NULL,
    `DISK_TIME_MS` bigint(4) DEFAULT NULL,
    `DISK_B` bigint(4) DEFAULT NULL,
    `DISK_LOGICAL_READ_B` bigint(4) DEFAULT NULL,
    `DISK_LOGICAL_WRITE_B` bigint(4) DEFAULT NULL,
    `DISK_PHYSICAL_READ_B` bigint(4) DEFAULT NULL,
    `DISK_PHYSICAL_WRITE_B` bigint(4) DEFAULT NULL,
    `LOCK_ROW_TIME_MS` bigint(4) DEFAULT NULL,
    `LOG_FLUSH_TIME_MS` bigint(4) DEFAULT NULL,
    `LOG_BUFFER_TIME_MS` bigint(4) DEFAULT NULL,
    `LOG_BUFFER_WRITE_B` bigint(4) DEFAULT NULL,
    `NETWORK_B` bigint(4) DEFAULT NULL,
    `NETWORK_LOGICAL_RECV_B` bigint(4) DEFAULT NULL,
    `NETWORK_LOGICAL_SEND_B` bigint(4) DEFAULT NULL,
    `MEMORY_BS` bigint(4) DEFAULT NULL,
    `MEMORY_MAJOR_FAULTS` bigint(4) DEFAULT NULL,
    `RUN_COUNT` bigint(4)  NULL DEFAULT '0',
    `SUCCESS_COUNT` bigint(4)  NULL DEFAULT '0',
    `FAILURE_COUNT` bigint(4)  NULL DEFAULT '0',
    KEY (`cluster`, `ACTIVITY_TYPE`,`ACTIVITY_NAME`,`AGGREGATOR_ACTIVITY_NAME`,`DATABASE_NAME`,`NODE_ID`,`PARTITION_ID`,ts) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);

CREATE TABLE IF NOT EXISTS mv_queries
(
    `cluster` varchar(512) NOT NULL,
    `memsql_tags` JSON NOT NULL,
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `ACTIVITY_NAME` varchar(512),
    `QUERY_TEXT` varchar(8192),
    `PLAN_WARNINGS` varchar(8192),
    KEY (`cluster`, `ACTIVITY_NAME`) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);

CREATE TABLE IF NOT EXISTS cluster_info
(
    `cluster` varchar(512) NOT NULL,
    `memsql_tags` JSON NOT NULL,
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` JSON NOT NULL,
    KEY (cluster, ts) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);

CREATE TABLE IF NOT EXISTS db_status
(
    `memsql_tags` JSON NOT NULL,
    `keys` JSON NOT NULL,
    `values` JSON NOT NULL,
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `cluster` AS `memsql_tags`::$cluster                 PERSISTED TINYBLOB,
    database_name AS `keys`::$database_name       PERSISTED TINYBLOB,
    `role` AS `keys`::$role                       PERSISTED tinyblob,
    num_partitions AS `values`::$num_partitions   PERSISTED SMALLINT(6),
    `online` AS `values`::$online                 PERSISTED SMALLINT(6),
    `offline` AS `values`::$offline               PERSISTED SMALLINT(6),
    replicating AS `values`::$replicating         PERSISTED SMALLINT(6),
    recovering AS `values`::$recovering           PERSISTED SMALLINT(6),
    pending AS `values`::$pending                 PERSISTED SMALLINT(6),
    transition AS `values`::$transition           PERSISTED SMALLINT(6),
    unrecoverable AS `values`::$unrecoverable     PERSISTED SMALLINT(6),
    ref_db_state AS `values`::$ref_db_state       PERSISTED TINYBLOB,
    summary AS `values`::$summary                 PERSISTED TINYBLOB,
    KEY (cluster, database_name, ts) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);

CREATE TABLE IF NOT EXISTS mv_events
(
    `memsql_tags` JSON NOT NULL,
    `keys` JSON NOT NULL,
    `values` JSON NOT NULL,
    ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    event_ts TIMESTAMP,
    `cluster` AS `memsql_tags`::$cluster                 PERSISTED TINYBLOB,
    `event_type` AS `keys`::$event_type           PERSISTED TINYBLOB,
    `origin_node_id` AS `keys`::$origin_node_id   PERSISTED SMALLINT,
    `ip_addr` AS `values`::$ip_addr               PERSISTED TINYBLOB,
    `port` AS `values`::$port                     PERSISTED SMALLINT,
    `type` AS `values`::$type                     PERSISTED TINYBLOB,
    `severity` AS `keys`::$severity               PERSISTED TINYBLOB,
    `details` AS `values`::$details               PERSISTED TINYBLOB,
    KEY (cluster, origin_node_id, event_ts) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);

CREATE TABLE IF NOT EXISTS mv_nodes
(
    `memsql_tags` JSON NOT NULL,
    `keys` JSON NOT NULL,
    `values` JSON NOT NULL,
    `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `cluster`                AS `memsql_tags`::$cluster            PERSISTED TINYBLOB,
    `node_id`                AS `keys`::$node_id                   PERSISTED SMALLINT,
    `ip_addr`                AS `keys`::$ip_addr                   PERSISTED TINYBLOB,
    `port`                   AS `keys`::$port                      PERSISTED SMALLINT,
    `role`                   AS `keys`::$role                      PERSISTED TINYBLOB,
    `external_host`          AS `keys`::$external_host             PERSISTED TINYBLOB,
    `external_port`          AS `keys`::$external_port             PERSISTED SMALLINT,
    `state`                  AS `values`::$state                   PERSISTED TINYBLOB,
    `availability_group`     AS `values`::$availability_group      PERSISTED SMALLINT,
    `num_cpus`               AS `values`::$num_cpus                PERSISTED BIGINT,
    `max_memory_mb`          AS `values`::$max_memory_mb           PERSISTED BIGINT,
    `max_table_memory_mb`    AS `values`::$max_table_memory_mb     PERSISTED BIGINT,
    `memory_used_mb`         AS `values`::$memory_used_mb          PERSISTED BIGINT,
    `table_memory_used_mb`   AS `values`::$table_memory_used_mb    PERSISTED BIGINT,
    `total_data_disk_mb`     AS `values`::$total_data_disk_mb      PERSISTED BIGINT,
    `available_data_disk_mb` AS `values`::$available_data_disk_mb  PERSISTED BIGINT,
    `uptime`                 AS `values`::$uptime                  PERSISTED BIGINT,
    `version`                AS `values`::$version                 PERSISTED TINYBLOB,
    KEY(`cluster`, `node_id`, `ts`) USING CLUSTERED COLUMNSTORE,
    SHARD KEY()
);


delimiter //
CREATE OR REPLACE PROCEDURE purge_metrics() AS
BEGIN
    DELETE FROM metrics WHERE TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(time_sec), NOW()) > @@monitoring_retention_period;
END //
delimiter ;

delimiter //
CREATE OR REPLACE PROCEDURE purge_blobs() AS
BEGIN
    DELETE FROM act_samples WHERE TIMESTAMPDIFF(MINUTE, ts, NOW()) > @@monitoring_retention_period;
    DELETE FROM mv_events WHERE TIMESTAMPDIFF(MINUTE, ts, NOW()) > @@monitoring_retention_period;
    DELETE FROM mv_queries WHERE TIMESTAMPDIFF(MINUTE, ts, NOW()) > @@monitoring_retention_period;
    DELETE FROM cluster_info WHERE TIMESTAMPDIFF(MINUTE, ts, NOW()) > @@monitoring_retention_period;
    DELETE FROM db_status WHERE TIMESTAMPDIFF(MINUTE, ts, NOW()) > @@monitoring_retention_period;
    DELETE FROM mv_nodes WHERE TIMESTAMPDIFF(MINUTE, ts, NOW()) > @@monitoring_retention_period;

END //
delimiter ;


delimiter //
CREATE OR REPLACE PROCEDURE load_metrics (metrics_pipe query(`labels` JSON, `name` TINYBLOB, `memsql_tags` JSON COLLATE utf8_bin,`value` DOUBLE NOT NULL,`time_sec` BIGINT(20) NOT NULL)) AS
BEGIN
    INSERT INTO metrics (
        `labels`,
        `name`,
        `memsql_tags`,
        `value`,
        `time_sec`
    ) SELECT
        `labels`,
        `name`,
        `memsql_tags`,
        `value`,
        `time_sec`
        FROM metrics_pipe;
    CALL purge_metrics();
END //
delimiter ;


delimiter //
CREATE OR REPLACE PROCEDURE load_blobs (blobs_pipe query(type text, `keys` JSON, `vals` JSON, time_sec bigint, `memsql_tags` JSON NULL)) AS
BEGIN
    INSERT INTO act_samples (
        `cluster`,
        `memsql_tags`,
        `ts`,
        `ACTIVITY_TYPE`,
        `ACTIVITY_NAME`,
        `AGGREGATOR_ACTIVITY_NAME`,
        `NODE_ID`,
        `PARTITION_ID`,
        `DATABASE_NAME`,
        `CPU_TIME_MS`,
        `CPU_WAIT_TIME_MS`,
        `ELAPSED_TIME_MS`,
        `LOCK_TIME_MS` ,
        `NETWORK_TIME_MS` ,
        `DISK_TIME_MS`,
        `DISK_B`,
        `DISK_LOGICAL_READ_B`,
        `DISK_LOGICAL_WRITE_B`,
        `DISK_PHYSICAL_READ_B`,
        `DISK_PHYSICAL_WRITE_B`,
        `LOCK_ROW_TIME_MS`,
        `LOG_FLUSH_TIME_MS`,
        `LOG_BUFFER_TIME_MS`,
        `LOG_BUFFER_WRITE_B`,
        `NETWORK_B`,
        `NETWORK_LOGICAL_RECV_B`,
        `NETWORK_LOGICAL_SEND_B`,
        `MEMORY_BS`,
        `MEMORY_MAJOR_FAULTS`,
        `RUN_COUNT`,
        `SUCCESS_COUNT`,
        `FAILURE_COUNT`
    ) SELECT
    `memsql_tags`::$cluster,
    `memsql_tags`,
    FROM_UNIXTIME(time_sec),
    `keys`::$activity_type,
    `keys`::$activity_name,
    `keys`::$aggregator_activity_name,
    `keys`::node_id,
    `keys`::partition_id,
    `keys`::$db_name,
    `vals`::cpu_time_ms,
    `vals`::cpu_wait_time_ms,
    `vals`::elapsed_time_ms,
    `vals`::lock_time_ms,
    `vals`::network_time_ms,
    `vals`::disk_time_ms,
    `vals`::disk_b,
    `vals`::disk_logical_read_b,
    `vals`::disk_logical_write_b,
    `vals`::disk_physical_read_b,
    `vals`::disk_physical_write_b,
    `vals`::lock_row_time_ms,
    `vals`::log_flush_time_ms,
    `vals`::log_buffer_time_ms,
    `vals`::log_buffer_write_b,
    `vals`::network_b,
    `vals`::network_logical_recv_b,
    `vals`::network_logical_send_b,
    `vals`::memory_bs,
    `vals`::memory_major_faults,
    `vals`::run_count,
    `vals`::success_count,
    `vals`::failure_count
    FROM blobs_pipe
    WHERE type = 'activity';

    INSERT INTO mv_events (
        `memsql_tags`,
        `keys`,
        `values`,
        `ts`,
        `event_ts`
    )
    SELECT
    `memsql_tags`,
    `keys`,
    `vals`,
    FROM_UNIXTIME(time_sec),
    FROM_UNIXTIME(`keys`::$event_time)
    FROM blobs_pipe b
    LEFT JOIN (SELECT DISTINCT cluster,
        origin_node_id,
        event_ts FROM mv_events) k
    ON b.`memsql_tags`::$cluster = k.cluster
    AND b.`keys`::$origin_node_id = k.origin_node_id
    AND b.`keys`::$event_time = k.event_ts
    WHERE type = 'event'
    AND k.cluster IS NULL
    AND k.origin_node_id IS NULL;

    INSERT INTO mv_queries (
        `cluster`,
        `ACTIVITY_NAME`,
        `memsql_tags`,
        `ts`,
        `QUERY_TEXT`,
        `PLAN_WARNINGS`
    )
    SELECT
    `memsql_tags`::$cluster,
    `keys`::$activity_name,
    `memsql_tags`,
    FROM_UNIXTIME(MAX(time_sec)),
    `vals`::$query_text,
    `vals`::$plan_warnings
    FROM blobs_pipe b
    LEFT JOIN (SELECT DISTINCT cluster, ACTIVITY_NAME FROM mv_queries) k
    ON b.`memsql_tags`::$cluster = k.cluster AND b.`keys`::$activity_name = k.ACTIVITY_NAME
    WHERE type = 'query'
    AND k.cluster IS NULL AND k.ACTIVITY_NAME IS NULL
    GROUP BY 1, 2;

    INSERT INTO cluster_info (
        `cluster`,
        `memsql_tags`,
        `ts`,
        `status`
    ) SELECT
    `memsql_tags`::$cluster,
    `memsql_tags`,
    FROM_UNIXTIME(time_sec),
    `vals`
    FROM blobs_pipe
    WHERE type = 'cluster';

    INSERT INTO db_status (
        `memsql_tags`,
        `keys`,
        `values`,
        `ts`
    ) SELECT
    `memsql_tags`, `keys`, `vals`, FROM_UNIXTIME(time_sec)
    FROM blobs_pipe
    WHERE type = 'db_state';

INSERT INTO mv_nodes (
        `memsql_tags`,
        `keys`,
        `values`,
        `ts`
    ) SELECT
    `memsql_tags`, `keys`, `vals`, FROM_UNIXTIME(time_sec)
    FROM blobs_pipe
    WHERE type = 'node';

    CALL purge_blobs();
END //
DELIMITER ;

-- For Grafana

DELIMITER //
CREATE OR REPLACE FUNCTION `trim_metric`(input varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, prefix varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) RETURNS varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AS BEGIN
  RETURN
  CASE
  WHEN input IS NOT NULL AND prefix IS NOT NULL AND locate(prefix, input, 1) > 0 THEN
    UPPER(substr(input, locate(prefix, input, 1) + length(prefix), length(input) - length(prefix)))
  ELSE input END;
END //
DELIMITER ;

DELIMITER //
CREATE OR REPLACE FUNCTION `trim_host`(input varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, c int default 1) RETURNS varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AS BEGIN
 RETURN
 CASE
 WHEN input RLIKE '([[:digit:]]{1,3}\\.){3}[[:digit:]]{1,3}' THEN
  input
 ELSE substring_index(input, '.', c) END;
END //
DELIMITER ;

-- helper table for integers 1..100
CREATE REFERENCE TABLE IF NOT EXISTS ints
(
    f int key
);
INSERT INTO ints values
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),
(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),
(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),
(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),
(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),
(71), (72), (73), (74), (75), (76), (77), (78), (79), (80),
(81), (82), (83), (84), (85), (86), (87), (88), (89), (90),
(91), (92), (93), (94), (95), (96), (97), (98), (99), (100);
Pipelines DDL

Run the following in the SQL Editor in SingleStore DB Studio, or your favored SQL client.

Note: You must edit exporter-host and port in the following SQL statements to align with where your exporter process resides.

  • The exporter-host is typically the host of your Source cluster’s Master Aggregator that’s running the exporter, and must include http://.

  • The default port for the endpoint is 9104.

Note: The IP address provided in the exporter-host argument must be resolvable by all hosts in the cluster. If the cluster you are monitoring has hosts that contain leaf nodes (as is common in a multi-host vs. single-host deployment), do not use localhost. Instead, provide the explicit IP address or hostname that the other hosts in the cluster can resolve.

The metrics pipeline:

CREATE OR REPLACE PIPELINE `metrics` AS
LOAD DATA prometheus_exporter
"<exporter-host:port>/cluster-metrics"
CONFIG '{"is_memsql_internal":true, "monitoring_version": "7.3"}'
INTO PROCEDURE `load_metrics` FORMAT JSON;
START PIPELINE IF NOT RUNNING metrics;

The blobs pipeline:

CREATE OR REPLACE PIPELINE `blobs` AS 
LOAD DATA prometheus_exporter 
"<exporter-host:port>/samples"
CONFIG '{"is_memsql_internal":true, "download_type":"samples", "monitoring_version": "7.3"}'
INTO PROCEDURE `load_blobs` FORMAT JSON;
START PIPELINE IF NOT RUNNING blobs;
How to Stop the Monitoring Process

You may stop the monitoring process at any time by performing either of the following commands.

Note: Be sure to substitute the database name and/or the associated database user if you've changed them from the defaults of the metrics database and the root user.

SingleStore DB Toolbox
sdb-admin stop-monitoring \
--exporter-host <exporter-hostname-or-IP-address> \
--user root \
--password <secure-password>
SQL

You may manually stop the pipelines created earlier.

STOP PIPELINE metrics;
STOP PIPELINE blobs;
Connect Data Visualization Tools

After configuring the metrics database, you can connect SingleStore Studio and data visualization tools such as Grafana to your cluster’s monitoring data. SingleStore Studio surfaces basic workload monitoring data, whereas Grafana provides additional dashboards for advanced analytics and troubleshooting.

SingleStore Studio

As of SingleStore DB 7.3 and SingleStore Studio 3.1.0, your SingleStore Studio instance can be configured to surface specific monitoring data of your cluster. Learn how to connect Studio to your cluster's monitoring data.

Grafana

Note: If you already have Grafana installed, you may skip to Add the Grafana Monitoring Data Source.

Install Grafana
  1. If a Grafana instance is not already available, install Grafana on the collecting Master Aggregator host.

  2. Add the Grafana pie chart panel.

    sudo grafana-cli plugins install grafana-piechart-panel
    
  3. Add the Grafana multi-bar graph panel.

    sudo grafana-cli --pluginUrl https://github.com/CorpGlory/grafana-multibar-graph-panel/archive/0.2.5.zip plugins install multibar-graph-panel
    
  4. Restart the Grafana server.

    sudo systemctl restart grafana-server
    
Add the Grafana Monitoring Data Source

Notice

For those SingleStore DB instances that don’t run database queries as root, this requires both admin privileges and SELECT and EXECUTE grants to the metrics database as per GRANT.

For example:

GRANT SELECT, EXECUTE ON metrics.* TO <user>@%;

where <user> is the user other than root under which these queries will be run.

  1. Connect to Grafana at http://<grafana-hostname>:3000 and log in using admin as the username and admin as the password.

  2. Add a monitoring MySQL data source with the following settings. The SingleStore DB data source port is 3306 by default.

    • Data source name: monitoring

    • Data source type: mysql

    • Data source host: <metrics-cluster-master-aggregator-host>

    • Data source port: 3306

    • Database name: metrics

    • User: root

    • Password: <secure-password-or-blank-if-none>

  3. Alternatively, edit the sample.yaml file in the /etc/grafana/provisioning/datasources directory and add the following content, substituting the values in angle brackets with your SingleStore DB configuration.

    # # config file version
    
    apiVersion: 1
    
    datasources:
      - name: monitoring
        type: mysql
        url: <metrics-cluster-master-aggregator-host>:<port>
        database: metrics
        user: root
        password: <secure-password-or-blank-if-none>
        isDefault: true
        version: 1
    
  4. Restart the Grafana server.

    sudo systemctl restart grafana-server
    
Connect Grafana Dashboards

Notice

You may need Editor privileges to create new Grafana dashboards.

  1. Download the cluster monitoring dashboards from SingleStore.

  2. Import the dashboards into Grafana. To import a dashboard, click the + icon in the side menu, and then click Import. From here you can upload a dashboard JSON file, paste a Grafana.com dashboard URL, or paste dashboard JSON text directly into the text area.

  3. Alternatively, edit the sample.yaml file in the /etc/grafana/provisioning/dashboards/ directory and add the following content.

    # # config file version
    apiVersion: 1
    
    providers:
    - name: 'default'
      orgId: 1
      folder: ''
      folderUid: ''
      type: file
      options:
        path: /var/lib/grafana/dashboards
    

    Next, copy the dashboard JSON files to the /var/lib/grafana/dashboards directory and ensure that the dashboards are owned by grafana:grafana (via chown grafana:grafana *).

  4. Restart the Grafana server.

    sudo systemctl restart grafana-server
    
View Monitoring Data from Multiple Clusters (Optional)

You can view monitoring data from multiple source clusters in a single Grafana instance using the following steps:

  1. Ensure that all cluster monitoring data is stored in a single database (on the metrics cluster). This will allow a single Grafana datasource to be configured.

  2. On each source cluster, Enable the Exporter Process.

  3. On the metrics cluster, run sdb-admin start-monitoring for each source cluster and store the monitoring data in the same database.

  4. In the metrics database, update the cluster_name variable with the name of each source cluster. (e.g., set global cluster_name = 'cluster01'). This will allow Grafana to identify each cluster.

  5. Use the cluster tab in the Grafana UI to toggle between monitoring data from each source cluster.

    16083b57947b91.png
Next Steps
View the Dashboards

When all cluster monitoring components are installed, configured, and running, the Grafana dashboards can be used to monitor SingleStore DB cluster health over time.

Troubleshoot Your Monitoring Setup
Pipelines
Check the Monitoring Tables for Data
  1. Connect to the database.

  2. Run the following SQL. The default database name is metrics. If your database name is different from the default name, replace metrics with your database name.

    USE metrics;
    SELECT * FROM metrics LIMIT 10;
    

    Optional, run SELECT * FROM on all of the monitoring tables.

    If these queries return an empty set, review the pipelines error tables using the next step.

  3. Review the monitoring pipelines.

    SHOW PIPELINES;
    
  4. If a monitoring pipeline (with a name resembling *_metrics and *_blobs) is in a state other than running, start the pipeline.

    START PIPELINE <pipeline-name>;
    
  5. Check the information_schema.pipelines_errors table for errors.

    SELECT * FROM information_schema.pipelines_errors;
    
Resolve Pipeline Errors

If you receive an Cannot extract data for the pipeline error in the pipelines_error table, perform the following steps.

  1. Confirm that port 9104 is accessible from all hosts in the cluster. This is the default port used for monitoring. To test this, run the following command at the Linux command line and review the output.

    curl http://<endpoint>:9104/cluster-metrics
    

    For example:

    curl http://192.168.1.100:9104/cluster-metrics
    
  2. If the hostname of the Master Aggregator is localhost, and a pipeline was created using localhost, recreate the pipeline using the Master Aggregator host’s IP addresses. For example:

    metrics pipeline:

    create or replace pipeline `metrics` as load data prometheus_exporter 
    "http://<host-ip-address>:9104/cluster-metrics" 
    config '{"is_memsql_internal":true}' 
    into procedure `load_metrics` format json;
    
    start pipeline if not running metrics;
    

    blobs pipeline:

    create or replace pipeline `blobs` as load data prometheus_exporter 
    "http://<host-ip-address>:9104/samples" 
    config '{"is_memsql_internal":true, "download_type":"samples"}' 
    into procedure `load_blobs` format json;
    
    start pipeline if not running blobs;