Sign inTry Free

Customize Monitoring

On this page

Configure Monitoring using SQL

Note

Using SingleStore Toolbox to configure cluster monitoring is the easier, preferred, and default method.

Enable the Exporter Process

The memsql-exporter process (or simply “the exporter”) collects data about a running cluster.

Set the Exporter User

The exporter is typically owned by the database root user. To start the exporter as a database user other than root, run the following SQL statements and specify the desired database user for <exporter-user> and this user’s password for <secure-password>.

Note: The database user that starts the exporter process can differ from the database user that starts the monitoring process.

CREATE USER '<exporter-user>' IDENTIFIED BY '<secure-password>';
GRANT CLUSTER, SHOW METADATA, SELECT, PROCESS ON *.* to '<exporter-user>'@'%';

Start the Exporter Process

Perform the following steps on the Source cluster's Master Aggregator.

Note that the exporter process is restarted when the exporter_port variable is set to any non-zero value, including the current value.

Run either of the following to start the exporter. These SQL statements will start the exporter using the specified database user and default port (9104). The exporter username and password are used to access the Source cluster.

Note: If starting the exporter as a database user other than root, substitute the database user for root and this user’s password for <secure-password>.

HTTP Connections

Run the following SQL statements to start the exporter.

SET GLOBAL exporter_user = root;
SET GLOBAL exporter_password = '<secure-password>';
SET GLOBAL exporter_port = 9104;
HTTPS Connections

  1. Copy the server certificate and key to the Master Aggregator host of the Source cluster. This certificate will be used by the exporter process and must be readable by the user under which the nodes are running on the host (typically the memsql user).

  2. Run the following SQL statements to start the exporter.

    SET GLOBAL exporter_user = root;
    SET GLOBAL exporter_password = '<secure-password>';
    SET GLOBAL exporter_use_https = true;
    SET GLOBAL exporter_ssl_cert = '/path/to/server-cert.pem';
    SET GLOBAL exporter_ssl_key = '/path/to/server-key.pem';
    SET GLOBAL exporter_ssl_key_passphrase = '<passphrase>';

    Note: The exporter_ssl_key_passphrase variable should only be included if the server key has a passphrase.

Stop the Exporter Process

You may stop the exporter at any time by running the following command on the Source cluster's Master Aggregator.

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 monitoring process configures the metrics database and starts monitoring the cluster.

Set the Monitoring User

Monitoring is typically owned by the database root user. To start monitoring as a database user other than root, run the following SQL statements and specify the desired database user for <monitoring-user> and this user's password for <secure-password>.

Note: The database user that starts the monitoring process can differ from the database user that starts the exporter process.

CREATE USER '<monitoring-user>' IDENTIFIED BY '<secure-password>';
GRANT SELECT, CREATE, INSERT, UPDATE, DELETE, EXECUTE, INDEX, ALTER, DROP, CREATE DATABASE, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE ON metrics.* to '<monitoring-user>'@'%';
GRANT CREATE PIPELINE, DROP PIPELINE, ALTER PIPELINE, START PIPELINE, SHOW PIPELINE ON metrics.* to '<monitoring-user>'@'%';

metrics Database DDL

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. To create the metrics database as a user other than root, run the following SQL statements and specify the desired database user for dbmon and this user’s password for <password>.

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

Create the metrics database and associated tables by copying, pasting, and running each set of SQL statements below.

You may also download the latest metrics database DDL SQL file from SingleStore.

Pipelines DDL

Run the following in a 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.

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

HTTP Connections

  1. Start the metrics pipeline.

    CREATE OR REPLACE PIPELINE metrics.metrics AS
    LOAD DATA prometheus_exporter 
    "http://<exporter-host>:<exporter-port>/cluster-metrics"
    CONFIG '{"is_memsql_internal":true, "high_cardinality_metrics":false, 
    "monitoring_version":"<SingleStore version of the 'metrics' cluster>"}' 
    batch_interval 15000
    INTO PROCEDURE `load_metrics` FORMAT JSON;
    START PIPELINE IF NOT RUNNING metrics.metrics;

  2. Start the blobs pipeline.

    CREATE OR REPLACE PIPELINE metrics.blobs AS
    LOAD DATA prometheus_exporter 
    "http://<exporter-host>:<exporter-port>/samples"
    CONFIG '{"is_memsql_internal":true,
    "download_type":"samples", "sample_queries":true,
    "monitoring_version":"<SingleStore version of the 'metrics' cluster>"}'
    batch_interval 15000
    INTO PROCEDURE `load_blobs` FORMAT JSON;
    START PIPELINE IF NOT RUNNING metrics.blobs;
HTTPS Connections

  1. Copy the CA certificate to the same file path on each host of the Metrics cluster.

    Alternatively, a directory containing multiple CA certificates can be provided, and this path must be the same on all hosts. This directory will be used by the monitoring pipelines and must be readable by the user under which the nodes are running on the host (typically the memsql user).

  2. Start the metrics pipeline.

    CREATE OR REPLACE PIPELINE metrics.metrics AS
    LOAD DATA prometheus_exporter 
    "https://<exporter-host>:<exporter-port>/cluster-metrics"
    CONFIG '{"is_memsql_internal":true, "high_cardinality_metrics":false, 
    "monitoring_version":"<SingleStore version of the 'metrics' cluster>",
    "ssl_ca":"path to ca cert" OR "ssl_capath":"path to ca dir"}'
    batch_interval 15000
    INTO PROCEDURE `load_metrics` FORMAT JSON;
    START PIPELINE IF NOT RUNNING metrics.metrics;

  3. Start the blobs pipeline.

    CREATE OR REPLACE PIPELINE metrics.blobs AS
    LOAD DATA prometheus_exporter 
    "https://<exporter-host>:<exporter-port>/samples"
    CONFIG '{"is_memsql_internal":true,
    "download_type":"samples", "sample_queries":true,
    "monitoring_version":"<SingleStore version of the 'metrics' cluster>",
    "ssl_ca":"path to ca cert" OR "ssl_capath":"path to ca dir"}'
    batch_interval 15000
    INTO PROCEDURE `load_blobs` FORMAT JSON;
    START PIPELINE IF NOT RUNNING metrics.blobs;

Stop the Monitoring Process

You may stop the monitoring process at any time by manually stopping the pipelines created earlier.

STOP PIPELINE metrics;
STOP PIPELINE blobs;

Last modified: September 13, 2024

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK