Insights Page

The Insights page exposes problems that CockroachDB has detected in your workloads and schemas. The page also offers recommendations to improve the performance of your workloads. These are called insights in the DB Console. The Insights page helps you:

• Identify SQL statements with , , or .
• Identify that should be created, altered, replaced, or dropped to improve performance.

​

Workload Insights tab

The Workload Insights tab displays insights related to and executions.

​

Transaction Executions view

To display this view, click Insights in the left-hand navigation of the DB Console and select Workload Insights > Transaction Executions. The Transaction Executions view provides an overview of all that have been flagged with insights. The transaction insights table has the following columns:

​

Transaction Execution details

The transaction execution details view provides more information on a transaction execution insight.

​

Default details

This information is displayed for all insight types.

​

Conditional details

Additional information is displayed for the following insight types:

1. High Contention: All transaction executions flagged with this insight type will display a Transaction with ID {transaction ID} waited on section which provides details of the blocking transaction execution.
2. Failed Execution: Certain transaction executions flagged with this insight type will display a Failed Execution section with Conflicting Transaction and Conflicting Location information. The following 3 conditions are required:
   • The cluster setting is set to true (default).
   • Error Code is 40001, a serialization_failure.
   • Error Message includes - failed preemptive refresh due to conflicting locks.

​

Statement Executions view

The Statement Executions view provides an overview of all that have been flagged with insights. To display this view, click Insights in the left-hand navigation of the DB Console and select Workload Insights > Statement Executions. Click Columns to select the columns to display in the table. The statement insights table has the following columns available:

​

Statement Execution details

The statement execution details view provides more information on a statement execution insight.

​

Default details

This information is displayed for all insight types.

​

Conditional details

This information is displayed for only the High Contention insight type. All statement executions flagged with this insight type will display a Statement with ID {statement ID} waited on section which provides details of the blocking transaction execution.

​

Workload Insight types

The Workload Insights tab surfaces the following type of insights:

• Failed Execution
• High Contention
• High Retry Count
• Slow Execution
• Suboptimal Plan

​

Failed Execution

The transaction or statement execution failed. The following screenshot shows a failed transaction execution: The following screenshot shows the default details of the preceding failed transaction execution. The Insights column shows the name of the insight, in this case Failed Execution. The Details column provides the Error Code and Error Message. CockroachDB uses PostgreSQL Error Codes. In this example, Error Code 40001 is a serialization_failure.

​

Serialization conflict due to transaction contention

The following screenshot shows the conditional details of the preceding failed transaction execution. In this case, there was a serialization conflict, also known as an isolation conflict, due to . (For transaction contention that causes lock contention, see High Contention). To capture more information in the event of a failed transaction execution due to a serialization conflict, set the cluster setting to true (default). With this setting enabled, when the Error Code is 40001 and the Error Message specifically has due to conflicting locks, a conditional Failed Execution section is displayed with Conflicting Transaction and Conflicting Location information. To troubleshoot, refer to the performance tuning recipe for .

​

High Contention

The transaction or statement execution experienced high time according to the threshold set in the . This type of contention is also known as lock contention. (For transaction contention that causes serialization conflict, see serialization conflict due to transaction contention.) To troubleshoot, refer to the performance tuning recipe for . The following screenshot shows the execution of a transaction flagged with High Contention: The following screenshot shows the execution details of the preceding transaction execution:

​

High Retry Count

The statement execution experienced a high number of according to the threshold set in the .

​

Slow Execution

The statement (or a statement in the transaction) experienced slow execution. Depending on the settings in Configuration, either of the following conditions trigger this insight:

• Execution time is greater than the value of the .
• Anomaly detection is enabled ( sql.insights.anomaly_detection.enabled ), execution time is greater than the value of sql.insights.anomaly_detection.latency_threshold, and is greater than the p99 latency and more than double the median latency. For details, see Detect slow executions.

​

Suboptimal Plan

The plan could be improved for some statement(s) in the transaction execution. Possible causes include outdated statistics and missing indexes. The statement execution has resulted in one or more index recommendations that would improve the plan. The following screenshot shows the statement execution of the query described in : The following screenshot shows the execution details of the preceding statement execution: The Insights column shows the name of the insight, in this case Suboptimal Plan. The Details column provides details on the insight, such as a Description with the cause of the suboptimal plan and a Recommendation with a CREATE INDEX statement. The final column contains a Create Index button. Click the Create Index button to execute the recommended statement to mitigate the cause of the insight.

​

Schema Insights tab

To display this view, click Insights in the left-hand navigation of the DB Console and select Schema Insights. This view lists the that have not been used and should be dropped, and/or the ones that should be created, altered, or replaced (based on statement execution).

• The drop recommendations are the same as those on the page.
• The create, alter, and replace recommendations are the same as those on the on the Statements page. Whereas the Explain Plans tab shows all recommendations, the Schema Insights view shows only the latest recommendations for that statement fingerprint. If you execute a statement again after creating or updating an index, the recommendation disappears.

The following screenshot shows the insight that displays after you run the query described in 6 or more times: CockroachDB uses the threshold of 6 executions before offering an insight because it assumes that you are no longer merely experimenting with a query at that point.

• Insights: Contains one of the following insight types: Create Index, Alter Index, Replace Index, Drop Unused Index.
• Details: Details for each insight. Different insight types display different details fields:
  • Create Index, Alter Index, or Replace Index: A Statement Fingerprint field displays the statement fingerprint that would be optimized with the creation, alteration, or replacement of the index; and a Recommendation field displays the SQL query to create, alter, or replace the index.
  • Drop Unused Index: An Index field displays the name of the index to drop; and a Description field displays the reason for dropping the index.

will see an action button in the final column, which will execute the SQL statement suggested by the schema insight, for example “Create Index”. Upon clicking the action button, a confirmation dialog displays a warning about the cost of and the option to copy the SQL statement for later execution in a SQL client.

​

Search and filter

By default, the Workload Insights view shows all statements or transactions that have insights. By default, the Schema Insights view shows all Schema Insights.

​

Search

To search using the search field:

1. Enter a string in the search box at the top of the tab. To search for exact terms in order, wrap the search string in quotes.
2. Press Enter. The list is filtered by the string.

​

Time interval

In the Workload Insights view, to see transactions or statement executions within a specific time interval, select a time interval from the selector at the top of the tab. The time interval field supports preset time intervals (1 Hour, 6 Hours, 1 Day, etc.) and custom time intervals. To select a custom time interval, click the time interval field and select Custom time interval. In the Start (UTC) and End (UTC) fields select or type a date and time. Use the arrow buttons to cycle through previous and next time intervals. To select the most recent interval, click Now. When you select a time interval, the same interval is selected in the page.

​

Filter

To filter the results on the Workload Insights or Schema Insights view:

1. Click the Filters field.
   • To filter by , select Application Name and select one or more applications.
     • Queries from the SQL shell are displayed under the $ cockroach app.
     • If you haven’t set application_name in a client connection string, it appears as unset.
   • To filter by one or more insight types, select Workload Insight Type or Schema Insight Type and select one or more types.
2. Click Apply

​

Configuration

You can configure the behavior of insights using the following .

​

Workload insights settings

You can configure Workload Insights with the following :

​

Detect slow executions

There are two different methods for detecting slow executions. By default, they are both enabled and you can configure them based on your workload. The first method flags all executions running longer than . This is analogous to checking the . The second method attempts to detect unusually slow executions. You can enable this detection with and configure it with . CockroachDB will then keep a streaming histogram in memory for each distinct statement fingerprint that has seen an execution latency longer than sql.insights.anomaly_detection.latency_threshold, and will flag any execution with a latency in the 99th percentile (greater than p99) for its fingerprint. Additional controls filter out executions that are less actionable:

• The execution’s latency must also be longer than twice the median latency ( > 2*p50 ) for that fingerprint. This ensures that the elevated latency is significant enough to warrant attention.
• The execution’s latency must also be longer than sql.insights.anomaly_detection.latency_threshold. Some executions are slower than usual, but are still fast enough for the workload.

The cluster setting limits the amount of memory available for tracking these streaming latency histograms. When this threshold is surpassed, the least-recently touched histogram is evicted. The default setting is sufficient for tracking about 1,000 fingerprints. You can track the sql.insights.anomaly_detection.memory and sql.insights.anomaly_detection.evictions to determine if the settings are appropriate for your workload. If you see a steady stream of evictions or churn, you can either raise the sql.insights.anomaly_detection.memory_limit cluster setting, to allow for more storage; or raise the sql.insights.anomaly_detection.latency_threshold cluster setting, to examine fewer statement fingerprints.

​

Schema insights settings

You can configure the index recommendations in the Schema Insights tab, , and with the following :

​

See also