Configure the metrics Database min read


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.

Note: 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

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.

SQL

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

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.

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`,
    time_sec,
    FROM_UNIXTIME(`vals`::$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);

End: Show metrics database 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;

End: Show pipelines DDLs

How to Stop the Monitoring Process

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

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;