Oracle Cloud Infrastructure Documentation

Metrics

Metrics enable you to measure useful quantitative data of the MySQL DB system such as current connections, statement count, CPU utilization, and backup time. You can use the metrics to create queries and alarms.

Viewing Metrics of a DB System

The Metrics section in the DB system details page displays a default set of charts for the current DB system.

Using the Console

Use the Console to view MySQL HeatWave metrics of the current DB system.

This task requires the following:
  • Correct policy that permits you access the monitoring services as well as the resources being monitored. See Mandatory Policies.
Do the following to view the metrics for your DB system:
  1. Open the navigation menu and click Databases. Under MySQL, click DB systems.
  2. Choose the Compartment from the List scope.
  3. Click the name of the DB system to open the DB system details page.
  4. Under Resources, click Metrics.
  5. (Optional) Use the Metrics data filter to view metrics for a particular resource:
    • All: Displays metrics for all resources.
    • DB system: Displays metrics for the current DB system.
    • HeatWave: Displays metrics for the HeatWave cluster attached to the current DB system.
    • Backup: Displays metrics for all backups of the current DB system.
    • Read replica: Displays metrics for all read replicas of the current DB system.
  6. (Optional) To edit a query related to a metric, click Options present on the metric, and then select View query in Metrics Explorer. See Building Metric Queries.
  7. (Optional) To create an alarm on the query, click Options present on the metric, and then select Create an alarm on this query. See Managing Alarms.
The Metrics section displays a default set of charts for the current DB system.

Viewing Metrics of All DB Systems

The Service metrics page displays a default set of charts for all DB systems in the selected compartment.

Using the Console

Use the Console to view MySQL HeatWave metrics of all DB systems present in a compartment.

This task requires the following:
  • Correct policy that permits you access the monitoring services as well as the resources being monitored. See Mandatory Policies.
Do the following to view the metrics for multiple DB systems:
  1. Open the navigation menu and click Observability & Management. Under Monitoring, click Service Metrics.
  2. Select your Compartment
  3. Under Metric namespace, select oci_mysql_database.
  4. (Optional) Filter the metrics using Dimensions. See To filter results and To select different resources.
  5. (Optional) To edit a query related to a metric, click Options present on the metric, and then select View Query in Metrics Explorer. See Building Metric Queries.
  6. (Optional) To create an alarm on the query, click Options present on the metric, and then select Create an alarm on this Query. See Managing Alarms.
The Service Metrics page displays a default set of charts for all DB systems in the selected compartment.

Using Metrics to Create Queries

You can use metrics to create queries to actively monitor the MySQL HeatWave Service resources.

Create queries for metrics in the oci_mysql_database namespace to actively monitor the resources. For example, you can use DbVolumeUtilization metric in the oci_mysql_database namespace to create a query to actively monitor the space utilization of the DB system.

Related Topics

Analyzing Queries

You can use the performance schema and sys schema to analyze queries executed on the DB system.

The performance schema provides a way to inspect internal execution of the server at runtime, and it focuses primarily on performance data while the sys schema helps to interpret data collected by the performance schema.

For example, if you want to find the query that consumes most of the execution time (latency), run the following:

SELECT schema_name, format_pico_time(total_latency) tot_lat,
                exec_count, format_pico_time(total_latency/exec_count)
                latency_per_call, query_sample_text
     FROM sys.x$statements_with_runtimes_in_95th_percentile as t1
     JOIN performance_schema.events_statements_summary_by_digest as t2 on t2.digest=t1.digest 
     WHERE schema_name not in ('performance_schema', 'sys')
     ORDER BY (total_latency/exec_count) desc limit 1\G
*************************** 1. row ***************************
      schema_name: employees
          tot_lat: 21.54 s
       exec_count: 4
 latency_per_call: 5.38 s
query_sample_text: select * from salaries where salary > 80000
1 row in set (0.0127 sec)
Sys schema also contains a statement analysis view that you can use to hunt the bad queries. For example:
SELECT * FROM sys.statement_analysis 
     WHERE db notin('performance_schema','sys')limit1\G
***************************1.row***************************
            query: SELECT`new_table`.`title`,...`title`ORDERBY`salary`DESC
               db: employees
        full_scan: 
       exec_count: 11
        err_count: 0
       warn_count: 0
    total_latency: 38.96 s
      max_latency: 5.15 s
      avg_latency: 3.54 s
     lock_latency: 33.00 us
      cpu_latency:   0 ps
        rows_sent: 77
    rows_sent_avg: 7
    rows_examined: 13053117
rows_examined_avg: 1186647
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 22
  tmp_disk_tables: 11
      rows_sorted: 77
sort_merge_passes: 0
           digest: 922701de9e5c51847f9f7de245b88fef4080b515ba8805082cd90c32830714eb
       first_seen: 2022-10-1220:45:50.770465
        last_seen: 2022-10-1311:49:13.1402281rowinset(0.0022 sec)

Using Metrics to Create Alarms

You can use metrics to create alarms to passively monitor the MySQL HeatWave Service resources.

Create alarms for metrics in the oci_mysql_database namespace to passively monitor the resources and notify you when metrics meet alarm-specified triggers.

For example, you can use DbVolumeUtilization metric in the oci_mysql_database namespace to create an alarm to get notifications when the DbVolumeUtilization metric meets the alarm-specified triggers.

Using Metrics to Create Alarms

Use the metrics in the oci_mysql_database namespace in the Console to create alarms.

Do the following to use metrics to create alarms:
  1. View the metrics available to a single DB System (see Viewing Metrics of a DB System) or to all DB systems (see Viewing Metrics of All DB Systems).
  2. Review MySQL HeatWave Service Metrics for descriptions of the metrics that are present in the oci_mysql_database namespace, and take note of the mertric names for which you want to create alarms.
  3. Review Best Practices for Your Alarms to determine the optimal way to create and manage alarms for your environment.
  4. Create alarms and specify the oci_mysql_database namespace and the MySQL HeatWave Service metric name that you want during the alarm creation process. For more information, see Managing Alarms and Creating an Alarms.

MySQL HeatWave Service Metrics

Use MySQL HeatWave Service metrics present in oci_mysql_database namespace to monitor the resources and to diagnose and troubleshoot problems with the DB system. The metrics are automatically available for all DB systems you create. You need not enable monitoring on the resource to see these metrics.

Each metric includes the following dimensions:

  • resourceId: Specifies the OCID of the resource.
  • resourceName: Specifies the display name of the resource.
  • resourceType: (Optional) Specifies the node type emitting the metric, mysql, heatwave, read replica.
  • heatWaveNode: (Optional) Specifies the display name of the HeatWave node.

For a high availability DB system, all metrics are for the primary MySQL instance only.

During maintenance, MySQL HeatWave Service does not monitor the metrics.

Table 17-10 MySQL HeatWave Service Metrics

Metric Metric (as referenced in SDK, CLI, and API) Unit Description Dimensions
Active connections ActiveConnections count The number of connections actively executing statements against the MySQL DB system.
  • resourceID
  • resourceName
Backup time BackupTime ms The time taken to create a backup.
  • resourceID
  • resourceName
  • dbSystemId
  • compartmentId
Current connections CurrentConnections count The number of current connections to the MySQL DB system.
  • resourceID
  • resourceName
CPU utilization CPUUtilization percent CPU utilization for the MySQL DB system host or HeatWave nodes.
  • resourceID
  • resourceName
  • heatWaveNode
  • resourceType
Disk read operations DbVolumeReadOperations count The total number of read operations for the DB volume(s).
  • resourceID
  • resourceName
  • resourceType
Disk write operations DbVolumeWriteOperations count The total number of write operations for the MySQL DB volume(s).
  • resourceID
  • resourceName
  • resourceType
Disk read bytes DbVolumeReadBytes bytes The total bytes read from the MySQL DB system volume(s).
  • resourceID
  • resourceName
  • resourceType
Disk write bytes DbVolumeWriteBytes bytes The total bytes written to the MySQL DB system volume(s).
  • resourceID
  • resourceName
  • resourceType
Disk space utilization DbVolumeUtilization percent The total space utilization of the MySQL DB system volume(s).
  • resourceID
  • resourceName
HeatWave health status HeatWaveHealth status HeatWave health status. One of the following values:
  • 0 - HEALTHY
  • 0.5: RELOADING DATA
  • 1 - RECOVERING
  • 2 - FAILED
See HeatWave Cluster Failure and Recovery.
  • resourceID
  • resourceName
HeatWave statement count HeatWaveStatements count The number of statements executed against the MySQL DB System and were executed on HeatWave.
  • resourceID
  • resourceName
HeatWave data load progress HeatWaveDataLoadProgress percent Progress of data load into HeatWave memory.
  • resourceID
  • resourceName
Inbound channel failure ChannelFailure ms The channel failure events observed over the last interval.
  • resourceID
  • resourceName
  • dbSystemId
Inbound channel lag ChannelLag ms The channel lag, with respect to the source, observed over the last interval.

If the channel is configured with replication delay, the channel lag includes the replication delay. See Creating a Replication Channel
  • resourceID
  • resourceName
  • dbSystemId
Memory Allocated MemoryAllocated GB The total amount of memory allocated during the selected interval.
  • resourceID
  • resourceName
Memory utilization MemoryUtilization percent Memory utilization for the MySQL DB system host or HeatWave nodes.
  • resourceID
  • resourceName
  • heatWaveNode
  • resourceType
Memory Used MemoryUsed GB The maximum amount of memory used during the selected interval.
  • resourceID
  • resourceName
Network receive bytes NetworkReceiveBytes bytes Network receive bytes for the MySQL DB system.
  • resourceID
  • resourceName
  • heatWaveNode
  • resourceType
Network transmit bytes NetworkTransmitBytes bytes Network transmit bytes for the MySQL DB system.
  • resourceID
  • resourceName
  • heatWaveNode
  • resourceType
OCPU Allocated OCPUsAllocated Count The actual number of OCPUs allocated during the selected interval.
  • resourceID
  • resourceName
OCPU Used OCPUsUsed Count The actual number of OCPUs used during the selected interval.
  • resourceID
  • resourceName
Statement count Statements count The number of statements executed against the MySQL DB system.
  • resourceID
  • resourceName
Statement latency StatementLatency ms Statement latency for all executed statements.
  • resourceID
  • resourceName
Storage Space Allocated StorageAllocated GB The maximum amount of space allocated to the DB system during the interval.
  • resourceID
  • resourceName
Storage Space Used StorageUsed GB The maximum amount of space used during the interval.
  • resourceID
  • resourceName
Total backup size BackupSize bytes The aggregate size of all backups per DB system.
  • resourceID
  • resourceName
  • dbSystemId
  • compartmentId