Monitoring SQL statements with Aurora DSQL statement statistics
Aurora DSQL statement statistics provide aggregated statistics for each SQL statement that your cluster runs. For each statement, Aurora DSQL records the number of execution calls, the planning and execution time, the rows, and the distributed processing units (DPUs) that the statement used. Aurora DSQL publishes these statistics as Amazon CloudWatch OpenTelemetry (OTel) metrics. It also writes up to 32 KiB of the normalized SQL text of each statement to Amazon CloudWatch Logs.
Statement statistics are off by default. You turn them on for each cluster that you want to monitor.
Statement statistics incur additional CloudWatch and CloudWatch Logs charges. For more information, see
Amazon CloudWatch pricing
Topics
What are statement statistics?
Aurora DSQL processes each statement across its query processing and storage layers. Statement
statistics combine the work of these layers into one set of metrics for each statement. Aurora DSQL
records every successful execution of SELECT, INSERT,
UPDATE, and DELETE statements as it completes, and aggregates the
results for each statement. It doesn't count utility statements, such as BEGIN,
COMMIT, and SET, or data definition language (DDL) statements. Aurora DSQL
counts only top-level statements, which are the
statements that your application sends directly.
If a transaction fails at commit, Aurora DSQL still counts the statements that ran in it. If your application retries the transaction, Aurora DSQL counts those statements again.
Query IDs and normalized SQL text
Aurora DSQL groups the runs of a statement by its normalized SQL text. Normalization removes SQL
comments, replaces each literal value with a numbered placeholder, such as $0, and
replaces extra white space with a single space. Aurora DSQL also computes a query
ID, a short identifier of the normalized statement. Statements that differ only in
their literal values share the same normalized SQL text and query ID.
| Statement that your cluster runs | Normalized SQL text | Query ID |
|---|---|---|
SELECT name FROM users WHERE id = 1042 |
SELECT name FROM users WHERE id = $0 |
6kuw53n1h44do |
SELECT name FROM users WHERE id = 7 |
SELECT name FROM users WHERE id = $0 |
6kuw53n1h44do |
Statement statistics metrics
| Metric | Unit | Description |
|---|---|---|
aws.dsql.query.calls |
Count | The number of execution calls of the statement. Each successful execution is one call. |
aws.dsql.query.time |
Seconds | The planning and execution time of the statement. |
aws.dsql.query.rows |
Count | The number of rows that the statement returned or changed. |
aws.dsql.query.compute_dpu |
DPU | The compute DPUs that the statement used to process data, such as filtering, sorting, and joining. |
aws.dsql.query.read_dpu |
DPU | The read DPUs that the statement used to read data from storage. |
aws.dsql.query.write_dpu |
DPU | The write DPUs that the statement used to write data to storage, including secondary indexes. |
aws.dsql.query.multi_region_write_dpu |
DPU | The write DPUs that the statement used in the peered Region of a multi-Region cluster. For a single-Region cluster, the value is 0. |
aws.dsql.query.compute.time |
Seconds | The time that the statement spent in the query processor, excluding the time spent on storage requests. |
aws.dsql.query.storage.time |
Seconds | The time that the statement spent on storage servers during read operations. |
aws.dsql.query.scanned_rows |
Count | The estimated number of rows that the statement's storage scans visited. |
The metrics carry the following labels, which you use to group and filter the data.
| Label | Description |
|---|---|
db.query.id |
The fingerprint of the normalized SQL text of the statement. Use it to group by statement and to look up the normalized SQL text. |
aws.dsql.session.role.arn |
The Amazon Resource Name (ARN) of the IAM identity that the connection authenticated as. |
application.name |
The application name that you set in connection parameters. Aurora DSQL includes this label only when the connection has an application name. |
@resource.aws.dsql.cluster_id |
The cluster identifier. Use it to filter to one cluster. |
Turning on statement statistics
You turn on statement statistics by adding the STATEMENT_STATISTICS metric
group to a cluster. STATEMENT_STATISTICS is the only metric group available
today. This is a Regional, per-cluster setting. For a multi-Region cluster, turn it on for
the cluster in each AWS Region.
You can manage this setting with the Aurora DSQL console, the AWS Command Line Interface (AWS CLI), the AWS SDKs, and
CloudFormation. In the API and the SDKs, use the metricGroups parameter of the
CreateCluster and UpdateCluster operations. In CloudFormation, use the
MetricGroups property of the AWS::DSQL::Cluster resource. To change
the setting, you need the dsql:CreateCluster or dsql:UpdateCluster
permission. To read it, you need the dsql:GetCluster permission.
After you turn off statement statistics, statistics from open connections can arrive for a few minutes. Normalized SQL text can arrive for up to a day.
Accessing statement statistics data
You can access statement statistics in the following ways:
-
Amazon CloudWatch Database Insights – A curated, no-code user interface (UI) for exploring the normalized SQL text and statement statistics charts of each statement. This is the starting point for most investigations.
-
Prometheus Query Language (PromQL) – Query the underlying statement statistics metrics programmatically for integration with monitoring tools, or for interactive exploration in CloudWatch Query Studio.
-
CloudWatch Log Analytics – Look up the normalized SQL text of a statement by its query ID.
How Aurora DSQL aggregates statement statistics data
Aurora DSQL sends statement statistics to CloudWatch as one or more data points for each one-minute window. Each data point holds only the work done after the previous data point, not a running total. A time series is a list of data points. Aurora DSQL keeps a separate time series for each combination of cluster, query ID, IAM identity, and application name. For example, if two applications run the same statement, the statement has two time series.
To get the total value of a metric for a time range, sum up the data points in that range. Then add the time series of each statement together. Amazon CloudWatch Database Insights does this aggregation for you.
Using CloudWatch Database Insights
View statement statistics in Database Insights
-
Open the CloudWatch console and choose Database Insights in the left navigation pane.
-
In Database resources of the Fleet Health view, choose the DB Identifier of your Aurora DSQL cluster.
-
In Top SQL, choose a statement to see its query ID, its normalized SQL text, and its statistics charts.
Database Insights shows the following statistics for each statement.
| Statistic | Calculation | What it shows |
|---|---|---|
| Calls/s | Calls divided by seconds in the chart interval | How often the statement runs. |
| Rows/s | Rows divided by seconds in the chart interval | How many rows the statement returns or changes per second. |
| Avg duration | Time divided by calls | The mean planning and execution time of each execution call, in milliseconds. |
| DPU per call | Total, compute, read, and write DPUs, each divided by calls | Where the cost of each call goes. |
| Compute time and storage time per call | Compute time divided by calls, and storage time divided by calls | How much of each call goes to storage operations, in milliseconds. |
| Scanned rows and returned rows per call | Scanned rows divided by calls, and rows divided by calls | Whether the statement scans far more rows than it returns or changes. |
Using PromQL
You can query statement statistics with PromQL in CloudWatch Query Studio. In the CloudWatch consolecloudwatch:GetMetricData and cloudwatch:ListMetrics
permissions.
Every query follows the two steps in How Aurora DSQL aggregates statement statistics data. First, sum_over_time adds up
the data points of each time series. Then, sum by adds the time series together
by a label, such as "db.query.id". These metrics hold deltas, not running
totals, so use sum_over_time instead of rate() or
increase().
-
Because the metric and label names contain periods, use the PromQL quoted-name selector form, such as
{"aws.dsql.query.time"}. -
Replace
your_cluster_idwith your cluster identifier. -
The
[1h]range sets the time window. The maximum is 24 hours. -
For a ranked list, use the table view. For a trend, use the graph view, and set the range equal to the step, for example,
[5m]with a 5-minute step. -
A query returns at most 500 time series. Use
topkor a label filter to stay under this limit.
Statements that take the most total time
The following query ranks the 10 statements with the most total time, in seconds, over
the last hour. To rank by another metric, such as calls or DPUs, use its name in place of
aws.dsql.query.time.
topk(10, sum by ("db.query.id") ( sum_over_time({ "aws.dsql.query.time", "@resource.aws.dsql.cluster_id"="your_cluster_id" }[1h]) ) )
Mean planning and execution time per execution call
The following query divides the total planning and execution time of each statement by
its number of execution calls, and then converts seconds to milliseconds. The
>= 100 filter hides statements with fewer than 100 execution calls. Lower it
to see them.
topk(10, sum by ("db.query.id") ( sum_over_time({ "aws.dsql.query.time", "@resource.aws.dsql.cluster_id"="your_cluster_id" }[1h]) ) / (sum by ("db.query.id") ( sum_over_time({ "aws.dsql.query.calls", "@resource.aws.dsql.cluster_id"="your_cluster_id" }[1h]) ) >= 100) * 1000 )
Calls per second, as a trend
The following query shows how often each statement runs. Use the graph view with a 5-minute step. The query divides each 5-minute sum by 300 seconds.
topk(10, sum by ("db.query.id") ( sum_over_time({ "aws.dsql.query.calls", "@resource.aws.dsql.cluster_id"="your_cluster_id" }[5m]) ) / 300 )
Scanned rows per call
The following query shows how many rows each call visits in storage. A statement that scans many rows but returns few might need an index.
topk(10, sum by ("db.query.id") ( sum_over_time({ "aws.dsql.query.scanned_rows", "@resource.aws.dsql.cluster_id"="your_cluster_id" }[1h]) ) / (sum by ("db.query.id") ( sum_over_time({ "aws.dsql.query.calls", "@resource.aws.dsql.cluster_id"="your_cluster_id" }[1h]) ) > 0) )
For more information about PromQL in CloudWatch, see Query metrics with PromQL.
Using CloudWatch Log Analytics
PromQL results identify each statement by its query ID. To find the normalized SQL text
of a query ID, query the /aws/dsql/normalized-sql-text log group in your
account. All clusters in an AWS account and Region share this log group, and each cluster
has its own log stream, named with the cluster identifier.
Find the normalized SQL text for a query ID
-
Open the Log Analytics page
in the CloudWatch console. -
Choose the log group
/aws/dsql/normalized-sql-text. -
Set the time range to start three hours before the period that you investigate. Aurora DSQL writes the text again at least every three hours as long as the statement runs.
-
In the following query, replace
your_query_idwith the query ID from your results, and then run the query.fields @timestamp, queryId, normalizedText | filter queryId = "your_query_id" | limit 1
-
Aurora DSQL stores up to 32 KiB of normalized SQL text for each statement.
-
If Aurora DSQL creates the log group, it sets a retention period of 545 days. You can change the retention. If the log group already exists, Aurora DSQL doesn't change it.
Related resources
-
Query metrics with PromQL – overview of PromQL support in CloudWatch.
-
Running PromQL queries in Query Studio – the interactive query workbench in the CloudWatch console.
-
Amazon CloudWatch Database Insights – the CloudWatch UI for monitoring database load and performance.
-
Analyzing log data with CloudWatch Log Analytics – how to query the normalized SQL text log group of statement statistics.
-
Amazon CloudWatch pricing
– charges for the metrics and logs that statement statistics publish.