Map SQL Activity using an Application Name

CockroachDB enables you to programmatically create and tag your SQL connections with a custom name or label. This practice lets you to quickly identify which part of your application is potentially degraded due to database health and design. By identifying problematic transactions and statements, you can quickly trace back to parts of your applications that generated those transactions and statements for a holistic understanding of the application performance impact as well as potential mitigation and .

This page shows how to set and filter database workloads by application name.

Set the application name

It is best practice to set the application name with CockroachDB. You can do this in the postgres://root@<servername:26257/mydb?application_name=movr_app or at the :

SET application_name = movr_app;

Filter database workloads by application name

Once you set the application name, the lets you .

If parts of your applications or known microservices are experiencing performance degradation, you can filter for the database workload tracing statements and transactions back to that part of your application directly in the DB Console. You can quickly identify whether there were database performance problems and if so, troubleshoot the issue using SQL observability touch points in the DB Console.

You can also programmatically filter crdb_internal tables and by application name. This example shows the first 60 characters of query text and statement statistics for queries associated with the movr_app application:

SELECT app_name, substring(metadata ->> 'query',1,60) AS statement_text,
   metadata -> 'distsql' AS is_distsql,
   metadata -> 'fullScan' AS has_full_scan,
   metadata -> 'vec' AS used_vec,
   statistics -> 'execution_statistics' -> 'contentionTime' -> 'mean' AS contention_time_mean,
   statistics -> 'statistics' -> 'cnt' AS execution_count,
   statistics -> 'statistics' -> 'firstAttemptCnt' AS num_first_attempts,
   statistics -> 'statistics' -> 'numRows' -> 'mean' AS num_rows_returned_mean,
   statistics -> 'statistics' -> 'rowsRead' -> 'mean' AS num_rows_read_mean,
   statistics -> 'statistics' -> 'runLat' -> 'mean' AS runtime_latency_mean
FROM movr.crdb_internal.statement_statistics
WHERE app_name = 'movr_app';

  app_name |                        statement_text                        | is_distsql | has_full_scan | used_vec | contention_time_mean | execution_count | num_first_attempts | num_rows_returned_mean | num_rows_read_mean | runtime_latency_mean
-----------+--------------------------------------------------------------+------------+---------------+----------+----------------------+-----------------+--------------------+------------------------+--------------------+------------------------
  movr_app | SELECT * FROM users                                          | true       | true          | true     |                    0 |               1 |                  1 | 5E+1                   | 5E+1               |              0.002202
  movr_app | SELECT name, count(rides.id) AS sum FROM users JOIN rides ON | true       | true          | true     |                    0 |               1 |                  1 | 1E+1                   | 5.5E+2             |              0.005338
...

Trace SQL activity using metrics

Often signals to problems start at high-level metrics. The has metrics for , , , etc., that can quickly signify potential issues with your application.

The statement and transaction statistics described in the preceding section enable you to correlate high-level metrics with lower level SQL activity information where you can identify the specific statements and transactions that were running during that time.

See also