In an ideal world, all workloads are perfect and never face performance issues. In the real world, that’s hard to achieve. Your workload might not always follow best practices, or it could contain problematic queries that cause trouble.
The causes of workload performance issues vary. Your workload could have a statement using a non-performant index that slows down your reads and writes, or it might have a transaction that runs concurrently with another transaction using the same table, resulting in contention. There could be an index that was created but never used, or even a bad statement that retries or fails on execution.
Solving these issues could cut costs and provide a better experience for your application’s end users. But it’s difficult to pinpoint the exact cause of these issues, so many of them never get fixed.
The CockroachDB Console provides historical and real-time information about your workloads in an interactive UI. For example, the Console’s SQL Activity page displays statistics about statements and transactions, such as latency, retries, contention, and explain plans, and you can decide what is important to you by adding filters or choosing which columns on the tables you want to see and order by.
Having a lot of information can be useful, but sometimes you want to focus on just the optimization opportunities and the most problematic executions, and you need a way to easily identify them. To help with this scenario, we’re introducing the new Insights page to our Console in CockroachDB v22.2.
The Insights page exposes problems that CockroachDB has detected in your workloads and schemas then offers recommendations to improve the performance of your workload.
On the Workload Insights tab, we display insights from your workloads that contain slow executions, alongside the possible causes for poor performance. There is one view for Transaction and another for Statements.
The Transaction Executions view displays the latest execution by transaction fingerprint that experienced high contention. The remaining executions that experienced contention and are not being displayed are available on the table
crdb_internal.transaction_contention_events. We consider a contention duration high when it is greater than 100ms (set by the cluster setting
Clicking on a value in the Latest Transaction Execution ID column brings you to its details page. This page contains more information about that particular execution, including which transactions it waited on.
Knowing which transaction is causing contention can help you adjust your workload and follow contention best practices.
On this table, we show the latest execution by statement fingerprint that was triggered by any of the two conditions:
>p99 && >2*p50and took longer than 50ms to execute (defined by the cluster setting
The historical executions triggered but not displayed on the console are available on
The possible insights for flagged statements are:
Similar to the Transaction view, clicking on any value on the Latest Statement Execution ID column, will bring you to its details page. On this page you will see more information about the execution and the insights detected.
Each Insight has its own details, with more information about what the Insight means and suggestions for how to fix it.
The Schema Insights tab focuses on index recommendations that will improve read and write performance.
Warning: Schema changes consume additional resources and can potentially negatively impact workload responsiveness during execution.
We display a drop index recommendation when the index has not been used for over 7 days (defined by cluster setting
Index recommendations to create, replace, or alter an index, are shown based on the plan used for each statement execution. You must execute the same fingerprint at least 6 times to receive a recommendation, because CockroachDB assumes that you are no longer merely experimenting with a query up until that point.
The last column of the Schema Insights table shows an “Apply” button (available only for self hosted and DB Console). If the SQL user logged into the Console has schema change permissions, you can apply the recommendation directly from the UI, otherwise you have the option to copy the SQL command and execute it using a SQL client with a user that has the right permissions.
The drop recommendations can also be found on the Databases page, where you will see an alert indicating that a particular Database/Table has recommendations.
The create, replace, and alter recommendations are also displayed on the Explain Plan tab of the Statement Details page. While the Schema Insights only displays the latest recommendations for that statement fingerprint, the Explain Plan tab displays all recommendations generated by all plans. When you execute the same statement after creating or updating an index, the recommendation disappears from the Schema Insights page.
These recommendations can also be seen when executing the
EXPLAIN command from a SQL client, but without the need to execute the statement multiple times.
Workload & Schema insights provides developers with a seamless experience when building their world-changing applications with a distributed database like CockroachDB. The initial set of insights was motivated based on internal telemetry data that allowed us to understand how developers were struggling with tuning their database for optimal performance. Based on this internal telemetry data, we’ve curated these insights directly in the console and we’re excited to have these insights accessible for all end-users to take advantage of and maximize their experience with CockroachDB.
If a video demonstration of the tool is helpful, check out this demo created by my colleague, Kevin Ngo (Sr Product Manager):
If there is a new feature that you want or you’re having a performance issue and you need our assistance, use one of our support resources to reach out to us, we’re happy to help!
To learn more about query performance, check a few other resources:
• SQL Performance Best Practices
• SQL Query Tuning with DB Console
• How to use indexes for better workload performance
Observability is how you understand the current state of your database: how it is behaving, plus any potentially …Read more
I’m often asked by developers how they can squeeze the most performance out of their database. By far the easiest …Read more
Indexes are a crucial part of your database schema. They improve workload performance by helping the database locate …Read more