Visual Monitoring of MatrixOne with Superset

Overview

Superset is an open-source, modern, and lightweight BI analysis tool that can connect to multiple data sources, provide rich visualizations, and support custom dashboards, making it easy for users to explore and present data.

MatrixOne version 1.0 now supports integration with the data visualization tool Superset. This guide will walk you through the quick deployment of MatrixOne and Superset environments. Combining MatrixOne with Superset’s visualization capabilities allows you to create a simple monitoring dashboard to track the ‘system_metric’ data within the MatrixOne database.

If you wish to extend the functionality further, you can explore other configuration options to monitor various aspects of the entire MatrixOne database.

Before you start

Connecting MatrixOne with Superset

  1. Access the Superset login page, typically at http://ip:8080. Then, enter your username and password to log in to Superset.

    Superset Login Page

    Note: The port for Superset may be either 8080 or 8088, depending on your configuration. The username and password are the ones you set during the Superset deployment.

    After logging in, you will see the main interface of Superset.

    Superset Main Interface

  2. Create a database connection:

    In Superset, you first need to create a database connection to MatrixOne. Click on Settings in the top right corner and select Database Connections.

    Click the + DATABASE button on the Database Connections page and choose MySQL as the database type.

    Fill in the connection information for the MatrixOne database, including the host, port, username, and password.

    Create Database Connection

    After filling in the details, click the CONNECT button and then click FINISH.

    Create Query

Creating Visual Monitoring Dashboards

Now, you can use the MatrixOne database to create a monitoring dashboard.

  1. Click on SQL > SQL Lab on the page, select the MatrixOne database connection you created earlier, and write SQL queries to select the tables you want to monitor.

    image-20230807201143069

    You can write multiple queries to monitor different metrics. Here are example SQL statements for some queries:

    • CPU Usage:

    SELECT metric_name, collecttime, value
FROM metric
WHERE metric_name = 'sys_cpu_combined_percent' OR metric_name = 'sys_cpu_seconds_total'
ORDER BY collecttime DESC;

    • Storage Usage:

    SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'server_storage_usage'
ORDER BY collecttime DESC;

    • Number of Connections:

    SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'server_connections'
ORDER BY collecttime DESC;

    • Disk Read and Write:

    SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'sys_disk_read_bytes' OR metric_name = 'sys_disk_write_bytes'
ORDER BY collecttime DESC;

    • Network Receive and Send:

    SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'sys_net_sent_bytes' OR metric_name = 'sys_net_recv_bytes'
ORDER BY collecttime DESC;

    • Memory Usage:

    SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'sys_memory_available' OR metric_name = 'sys_memory_used'
ORDER BY collecttime DESC;

    • Transaction Errors:

      SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'sql_transaction_errors' OR metric_name = 'sql_transaction_total'
ORDER BY collecttime DESC;

    • SQL Errors:

      SELECT metric_name, value, collecttime
FROM metric
WHERE metric_name = 'sql_statement_errors' OR metric_name = 'sql_statement_total'
ORDER BY collecttime DESC;

  2. Click SAVE > Save dataset > SAVE & EXPLORE to save each of the queries above and use them as data sources for subsequent charts.

  3. Edit the charts:

    Here, we’ll use one of the queries as an example to demonstrate how to edit a visual chart. First, select the ‘disk_read_write’ query as the data source for the chart. In the SQL Lab, click CREATE CHART below the corresponding query, or if you’ve saved the query in the previous step, the page will redirect to the Chart editing page:

    Create Dashboard

  4. In the chart editing page, choose chart type, time field, metric columns from the query, grouping columns, and other options. Once configured, select RUN:

    View Dashboard

  5. Click UPDATE CHART > SAVE to save the edited chart.

Organizing Dashboards

  1. After creating multiple charts, you can assemble them in Superset to create a monitoring dashboard:

    Click on Dashboards, then click + DASHBOARD to create a new dashboard or edit an existing one.

    image-20230808101636134

  2. In the dashboard editing page, you can drag the charts you’ve created from the CHARTS list on the right onto the dashboard for assembly. You can also freely adjust the position of charts, add titles, and more.

    image-20230808102033250

You have successfully connected the MatrixOne database with Superset and created a simple monitoring dashboard to visualize key metrics of the MatrixOne database.