Use the Cockroach DB Console to Observe and Troubleshoot SQL

Last edited on January 26, 2021

0 minute read

    Whether you’re a developer or operator, we want to give you the observability tools you need to monitor your CockroachDB cluster. This includes standard tools such as logs, metrics like Prometheus and health endpoints, and alerting. But modern observability requires more than just the standard tools.

    To give you as much insight into your database as possible, CockroachDB also provides SQL commands, tables, and a native DB Console packaged with every binary to provide observability for any workload. The DB Console gives operators and developers access to important cluster data including Metrics, Databases, Sessions (new in CockroachDB 20.2), Transactions (new in CockroachDB 20.2), Statements, and more.

    The purpose of this post is to give you an overview of several key ways you can monitor CockroachDB, and provide you with a place to learn more.

    01 cockroachdb console overview

    Why do operators use the DB Console?Copy Icon

    If you’re an operator, you know that it’s not a matter of if things will go wrong with your environment, but when. We arm operators with access to critical cluster health information by evaluating node status (e.g., live, suspect, and dead nodes), replication health, as well as assess key metrics like CPU, memory, and disk usage. This allows you to prevent problems before they happen or quickly discover the root cause during an emergency.

    Why do developers use the DB Console?Copy Icon

    If you’re a developer, you know that change is the only constant for production workloads. New business requirements lead to schema migrations and new transactions with new performance goals. CockroachDB already provides zero downtime migrations to minimize the impact of modern development. The DB Console now provides you with a way to observe your workloads and the sessions, transactions, and statements that comprise them. Iterating is a way of life for developers, CockroachDB now provides a tool that makes it easy to assess how changes to schema and query design impact the workload.

    Let’s dive a bit deeper into how developers can use the DB Console.

    Sessions PageCopy Icon

    If you notice throughput has dropped or CPU usage is approaching 100%, you might suspect an errant statement is running loose somewhere in your database. You need to be able to find it and cancel it as quickly as possible. The Sessions page, introduced in CockroachDB 20.2, provides you with live access to all sessions (i.e., connections) and in-progress transactions and statements.

    05 cockroachdb console sessions details

    You can sort by session age, transaction age, statement age, or memory usage to find any problematic active sessions. More importantly, CockroachDB now allows you to cancel problematic sessions and queries:


    You can click into any given session to learn more about the number of statements executed or retried, as well as learn about memory usage.

    03 cockroachdb console sessions transactions

    Now with CockroachDB 20.2, developers have the flexibility to find and cancel sessions. Learn more about the sessions page via our documentation.

    Transactions PageCopy Icon

    It’s not always easy to understand the transactions that a complex, dynamic workload performs. Understanding which statements and tables make up each transaction, as well as the transaction’s end-to-end latency, are critical inputs into understanding and managing workload throughput and end-user latency. CockroachDB 20.2 adds visibility into transaction fingerprints on the new transactions page in the DB Console. Like the statements page introduced in previous versions, the transactions page shows historical information about already executed transactions and statements.

    06 cockroachdb console transactions

    However, unlike with the statements page, you can now see all of the statement fingerprints that comprise a specific transaction:

    04 cockroachdb console transactions details

    You can even drill down into the statements detail pages to learn more about that fingerprint.

    Learn more about the transactions page via our documentation.

    Prefer to use the SQL shell? No problem!Copy Icon

    We use SQL to power the sessions, transactions, and statements pages in the DB Console. If you’d rather access this information directly, you can use the following tables:

    For example, here’s an example of a statement aggregation from the crdb_internal.node_statement_statistics virtual table:

    root@> select * from crdb_internal.node_statement_statistics limit 1; -[ RECORD 1 ] node_id | 1 application_name | $ cockroach demo flags | key | ALTER TABLE rides ADD FOREIGN KEY (city, rider_id) REFERENCES users (city, id) anonymized | ALTER TABLE _ ADD FOREIGN KEY (_, _) REFERENCES _ (_, _) count | 1 first_attempt_count | 1 max_retries | 0 last_error | NULL rows_avg | 0 rows_var | NaN parse_lat_avg | 2.1e-05 parse_lat_var | NaN plan_lat_avg | 0.000362 plan_lat_var | NaN run_lat_avg | 0.002501 run_lat_var | NaN service_lat_avg | 0.002923 service_lat_var | NaN overhead_lat_avg | 3.899999999999954e-05 overhead_lat_var | NaN bytes_read_avg | 0 bytes_read_var | NaN rows_read_avg | 0 rows_read_var | NaN implicit_txn | true Time: 1ms total (execution 0ms / network 0ms)

    You can also review in-progress sessions, transactions, and statements via the following SQL SHOW commands:




    Try out the DB Console with CockroachDB and CockroachDB DedicatedCopy Icon

    Whether you’re a developer focused on using the database to iterate upon your application or an operator focused on keeping the entire cluster happy, CockroachDB 20.2 has the tools you need to observe and troubleshoot any problem. You can get started today with a free trial of CockroachDB Dedicated.

    Is there an observability feature that you wish we supported? We’d love to hear what it is. Drop us a line in the #product-feedback channel of our community Slack group.