Log SQL Activity to Datadog

This tutorial describes how to configure logging of telemetry events, including and , to Datadog for finer granularity and long-term retention of SQL activity. The sampled_query and sampled_transaction events contain common SQL event and execution details for and .

sampled_query and sampled_transaction events, which are sent by default to the channel in and earlier versions, will instead be routed by default to the channel in a future major release.

To prepare for the change and assess potential downstream impacts on your logging setup and pipelines, review the cluster setting. After reviewing the documentation, set log.channel_compatibility_mode.enabled to false in a non-production environment to log sampled_query and sampled_transaction events to the channel. CockroachDB supports a built-in integration with Datadog which sends these events as logs via the Datadog HTTP API. This integration is the recommended path to achieve high throughput data ingestion, which will in turn provide more query and transaction events for greater workload observability.

Step 1. Create a Datadog API key

1. In Datadog, navigate to Organization Settings > API keys.
2. Follow the steps in the Datadog documentation on how to add an API key.
3. Copy the newly created API key to be used in Step 2.

Step 2. Configure an HTTP network collector for Datadog

Configure an by creating or modifying the .

In this logs.yaml example:

1. To send telemetry events directly to Datadog without writing events to disk, override telemetry default configuration by setting file-groups: telemetry: channels: to [].

Given the volume of sampled_query and sampled_transaction events, do not write these events to disk, or . Writing a high volume of sampled_query and sampled_transaction events to a file group will unnecessarily consume cluster resources and impact workload performance.

To disable the creation of a telemetry file and avoid writing sampled_query and sampled_transaction events and other to disk, change the telemetry file-groups setting from the to channels: [].

1. To connect to Datadog, replace with the value you copied in Step 1.
2. To control the ingestion and potential drop rate for telemetry events, configure the following buffering values depending on your workload:

• max-staleness: The maximum time a log message will wait in the buffer before a flush is triggered. Set to 0 to disable flushing based on elapsed time. Default: 5s
• flush-trigger-size: The number of bytes that will trigger the buffer to flush. Set to 0 to disable flushing based on accumulated size. Default: 1MiB. In this example, override to 2.5MiB.
• max-buffer-size: The maximum size of the buffer: new log messages received when the buffer is full cause older messages to be dropped. Default: 50MiB

sinks:
  http-servers:
    datadog:
      channels: [TELEMETRY]
      address: https://http-intake.logs.datadoghq.com/api/v2/logs
      format: json
      method: POST
      compression: gzip
      headers: {DD-API-KEY: "DATADOG_API_KEY"} # replace with actual DATADOG API key
      buffering:
        format: json-array
        max-staleness: 5s
        flush-trigger-size: 2.5MiB # override default value
        max-buffer-size: 50MiB
  file-groups: # override default configuration
    telemetry:  # do not write telemetry events to disk
      channels: [] # set to empty square brackets

If you prefer to keep the DD-API-KEY in a file other than the logs.yaml, replace the headers parameter with the :

      file-based-headers: {DD-API-KEY: "path/to/file"} # replace with path of file containing DATADOG API key

The value in the file containing the Datadog API key can be updated without restarting the cockroach process. Instead, send SIGHUP to the cockroach process to notify it to refresh the value. Pass the to the cockroach process with either --log-config-file or --log flag.

Step 3. Configure CockroachDB to emit query events

Enable the so that executed queries will emit an event on the telemetry :

SET CLUSTER SETTING sql.telemetry.query_sampling.enabled = true;

Set the to statement so that sampled_query events are emitted (sampled_transaction events will not be emitted):

SET CLUSTER SETTING sql.telemetry.query_sampling.mode = 'statement';

Configure the following to a value that is dependent on the level of granularity you require and how much performance impact from frequent logging you can tolerate:

• (default 8) is the max event frequency (events per second) at which we sample executed queries for telemetry. If sampling mode is set to 'transaction', this setting is ignored. In practice, this means that we only sample an executed query if 1/max_event_frequency seconds have elapsed since the last executed query was sampled. Sampling impacts the volume of query events emitted which can have downstream impact to workload performance and third-party processing costs. Slowly increase this sampling threshold and monitor potential impact.

  Logs are sampled for statements of type or .

  Logs are always captured for statements under the following conditions:

  • Statements that are not of type or . These statement types are:
  • Statements that are in .
  • Statements that are from the when is true (default). These events have ApplicationName set to $ internal-console.

The sql.telemetry.query_sampling.max_event_frequency cluster setting and the buffering options in the logs.yaml control how many events are emitted to Datadog and that can be potentially dropped. Adjust this setting and these options according to your workload, depending on the size of events and the queries per second (QPS) observed through monitoring.

Step 4. Configure CockroachDB to emit query and transaction events (optional)

Enable the so that executed queries and transactions will emit an event on the telemetry :

SET CLUSTER SETTING sql.telemetry.query_sampling.enabled = true;

Set the to transaction so that sampled_query and sampled_transaction events are emitted:

SET CLUSTER SETTING sql.telemetry.query_sampling.mode = 'transaction';

Configure the following to values that are dependent on the level of granularity you require and how much performance impact from frequent logging you can tolerate:

• (default 8) is the max event frequency (events per second) at which we sample transactions for telemetry. If sampling mode is set to 'statement', this setting is ignored. In practice, this means that we only sample a transaction if 1/max_event_frequency seconds have elapsed since the last transaction was sampled. Sampling impacts the volume of transaction events emitted which can have downstream impact to workload performance and third-party processing costs. Slowly increase this sampling threshold and monitor potential impact.

• (default 50) is the maximum number of statement events to log for every sampled transaction. Note that statements that are always captured do not adhere to this limit.

  Logs are always captured for statements under the following conditions:

  • Statements that are not of type or . These statement types are:
  • Statements that are in .
  • Statements that are from the when is true (default). These events have ApplicationName set to $ internal-console.

Correlating query events with a specific transaction

Each sampled_query and sampled_transaction event has an event.TransactionID attribute. To correlate a sampled_query with a specific sampled_transaction, filter for a given value of this attribute.

Step 5. Monitor TELEMETRY logs in Datadog

1. Navigate to Datadog > Logs.
2. Search for @event.EventType:(sampled_query OR sampled_transaction) to see the logs for the query and transaction events that are emitted. For example:

See also