MySQL HeatWave on AWS Metrics and Performance Tools - OCI

As you’re working with your data, you’ll want to view performance data and monitor the overall health of your HeatWave cluster. Here we will explore using OCI monitoring to view performance metrics and configure automated alerts. We will also explore some of the performance data that is captured in the performance_schema DB that gets created when you set up your HeatWave cluster.

Yes, you can use HeatWave metrics to create queries to monitor metrics actively or set an alarm to passively monitor.

HeatWave Metrics in Console

Let’s start with the Console’s UI to view some metrics:
Databases > DB Systems

1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
2. Choose your compartment from the List Scope.
3. In the HeatWave filter, select Attached to filter the DB systems that have a HeatWave cluster attached.
4. In the list of DB systems, find your DB system, and click the name of the DB system to open the DB System Details page.
5. Select Metrics from the Resources list.
6. In the Metrics Data filter, select HeatWave to filter and view the HeatWave metrics.

More fun stuff!

To edit a query related to a metric, click Options present on the metric, and then select View Query in Metrics Explorer. In Metrics Explorer you’re given additional options to evaluate the metrics data, which is helpful in establishing operating baselines. You can also create custom metric queries and append them to OCI Dashboards, allowing centralized visibility. For a deep dive into Building Metric Queries, check out the documentation.

Alarms can be used to automatically alert you and your team when operating parameters break a designated threshold. Either from the canned metrics or a custom metric query, you can create the desired alarms.

When creating an alarm, you can choose between notification and streaming. Notification is a service that provides email delivery based on subscribed address. Streaming is a data ingestion service that could be used to integrate your alarms with a 3rd party SIEM too. To learn everything you ever wanted to know about alarms, you will find the documentation here.

HeatWave Node Status

I’d like to tell you its all sunshine and rainbows from here, but we both know that isn’t true. Despite the better efforts of countless engineers, sometimes things may go awry. Luckily, its easy to check node status within your cluster, or add / remove nodes as you see fit.

Referring to Connecting and Managing MySQL HeatWave on OCI [LINK TK], select your favorite connectivity method and log into the database.

Run the following command:

mysql> SELECT ID, STATUS FROM performance_schema.rpd_nodes;
     +----+---------------+
     | ID | STATUS        |
     +----+---------------+
     |  0 | AVAIL_RNSTATE |
     |  1 | AVAIL_RNSTATE |
     +----+---------------+

To view memory usage,

mysql> SELECT ID, MEMORY_USAGE, MEMORY_TOTAL, BASEREL_MEMORY_USAGE FROM performance_schema.rpd_nodes;

Ever get impatient waiting? You can monitor your load progress this way:

mysql> SELECT VARIABLE_VALUE
            FROM performance_schema.global_status
            WHERE VARIABLE_NAME = 'rapid_load_progress';
     +----------------+
     | VARIABLE_VALUE |
     +----------------+
     | 100.000000     |
     +----------------+

and check table loading status with:

mysql> USE performance_schema;
mysql> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id
            WHERE rpd_tables.ID = rpd_table_id.ID AND SCHEMA_NAME LIKE 'tpch';

(Obviously substitute your own tables!)

Want to know how much data is loaded?

mysql> USE performance_schema;
mysql> SELECT rpd_table_id.TABLE_NAME, rpd_tables.NROWS, rpd_tables.LOAD_STATUS, rpd_tables.SIZE_BYTES FROM rpd_table_id, rpd_tables 
            WHERE rpd_table_id.ID = rpd_tables.ID ORDER BY SIZE_BYTES;

And you can have some runoffs with this handy command to view how long it took to load a table (again subbing in your favorite table):

mysql> USE performance_schema;
mysql> SELECT rpd_table_id.TABLE_NAME, rpd_tables.NROWS, rpd_tables.LOAD_STATUS, rpd_tables.LOAD_END_TIMESTAMP FROM rpd_table_id, rpd_tables 
            WHERE rpd_table_id.ID = rpd_tables.ID;

Want to know more? Join the discussion in our public Slack channel!