Logging Best Practices

This page provides guidance and best practices for consuming CockroachDB logs for critical information. It outlines how to externalize logs. For details on which log messages to externalize and how to interpret them, refer to .

Types of logs

CockroachDB has two different types of logs, structured and unstructured.

Structured logs

Structured logs contain detailed, machine-readable information that can be easily parsed and analyzed. They are particularly useful for:

• : Capturing significant events in a structured format allows for easier querying and analysis. For example, job state changes or specific events like backups.
• : Structured logs are ideal for audit trails and compliance monitoring because they provide a consistent format that can be easily searched and filtered.
• : When tracking performance metrics, structured logs can help in identifying patterns and anomalies due to their detailed and consistent format.

Unstructured logs

Unstructured logs are more free-form and human-readable compared to machine-processed structured logs. They are particularly useful for:

General Troubleshooting: They are useful for capturing a wide range of information that may not fit into a predefined structure, such as error messages or stack traces. Events not documented on will have an unstructured format in log messages.

Storage considerations for file sinks

route events from specified to destinations outside CockroachDB. These destinations currently include , network sinks ( servers and servers), and the .

With a file sink, when provisioning for your CockroachDB cluster, decide : either the same volume as the main data store or on a separate volume from the main data store. To determine this, measure and consider the following factors:

• How is your workload.
• How many log messages are written to files on disk.
  • This number can be managed and configured with the . Log and audit only what you need.
  • This number can be controlled or reduced by outputting logs to a network sink.
• What is the operational overhead and cost to provision a separate volume.

For greater disk resilience, consider the following:

• Write logs to a network sink. However:
  • This may not be possible due to your environment. You need to have a deployed network sink (a or server).
  • This could create more operational overhead. You need to manage that network sink.
  • This will impact troubleshooting using which relies on log files written to disk. Collecting these log files is critical when submitting issues to Cockroach Labs support. There is a tradeoff of disk resilience versus observability with more collected logs.
• Use the for file sink if you do not need auditing.
• Use fine-grained auditing ( or ) to reduce IO and potential impact during .

CockroachDB can still hold mutexes during disk stalls when logging a message (without buffering enabled), which could impact workload stability. Therefore, reducing the frequency of writing logs to disk or enabling buffering can increase workload resilience.

Provision a separate volume for message logging

Segregating database transaction logging (the in CockroachDB) from all other I/O, particularly from the small-sized, sequential, non-stop message logging, is a standard practice for databases. Ideally, the CockroachDB LSM store should be placed on a dedicated volume to ensure that no other I/O interferes with the primary storage I/O. The I/O profile of CockroachDB LSM is significantly different from the sequential, small-size append I/O of message logging, so it is crucial to prevent the latter from interfering with the former.

To achieve this, move message logging I/O to a different device. The most suitable device depends on your platform. For bare metal, the OS disk may be an appropriate location. In a cloud environment, such as AWS, you might consider using an inexpensive GP3 volume with no additional IOPS provisioned, since logging does not require much I/O. The goal is to segregate message logging not because it consumes significant I/O bandwidth, but because its I/O profile is different and could disrupt LSM I/O.

Regarding the storage size for message logging, you will not need much space. The amount of message logging is strictly controlled, so it will not grow uncontrollably. Therefore, it is acceptable to use the OS disk as long as it persists through power-off events. The message log files are managed by log rotation, with configurable maximum file size and the number of files retained using max-file-size and max-group-size , respectively. Calculate the required space based on the default settings, or adjust the settings to fit your disk capacity. Log rotation will ensure that the space is maintained.

Use json format with third-party tools

With third-party tool consumption that read logs programmatically, use format rather than parse the format. The JSON object is guaranteed to not contain unescaped newlines or other special characters, and the entry as a whole is followed by a newline character. This makes the format suitable for processing over a stream unambiguously.

Prioritize logs based on severity

The various are guaranteed to contain a for each log event.

• With format, if tag-style: compact is specified, use sev field, while if tag-style: verbose, use severity field.
• With format, use the first character of the prefix of each log entry. Possible values are I for INFO, W for WARNING, E for ERROR, and F for FATAL.

Use logs for performance tuning

Use the SQL_EXEC and SQL_PERF to examine SQL queries and filter slow queries in order to optimize or troubleshoot performance. Refer to . It is also possible to , which uses the TELEMETRY channel.

Use logs for security and compliance

Use the SESSIONS, USER_ADMIN, PRIVILEGES, SENSITIVE_ACCESS to monitor connection and authentication events, changes to user/role administration and privileges, and any queries on audited tables. Refer to .

Consider workload performance impact

Enabling logging of events in the following may have a performance impact:

• : Logging cluster-wide executions by enabling the will incur considerable overhead and may have a negative performance impact.
• : Setting to a non-zero time enables tracing on all queries, which impacts performance. After debugging, set the value back to 0s to disable the log.
• : Logging client connection and session authentication events are enabled by the and the cluster setting respectively. These logs perform one disk I/O per event. Enabling each setting will impact performance.
• : Enabling or can negatively impact performance. Log only what you require to limit impact to your workload. Use this channel for security purposes only.

Customize buffering of log messages

Depending on the use case, a log sink can be configured to be auditable or buffered. However, there is a tradeoff between auditing requirements and performance.

Auditable

In the case of , use the logging YAML file to to be auditable, by setting auditable to true. This guarantees non-repudiability for any logs in the sink, but can incur a performance overhead and higher disk IOPS consumption. When auditable is enabled:

• exit-on-error is enabled which stops the CockroachDB node if an error is encountered while writing to the sink. This prevents the loss of any log entries.
• buffered-writes is disabled if the sink is under file-groups.

File-based audit logging cannot coexist with the buffering configuration, so disable either buffering or auditable.

Buffered

Use the logging YAML file to configure buffering settings to optimize log performance, refer to . For example, modify the following buffering options:

• max-staleness: The maximum time logs can stay in the buffer before being flushed.
• flush-trigger-size: The size threshold that triggers a buffer flush.
• max-buffer-size: The maximum buffer size. If this limit is exceeded, new log messages are dropped until the buffer size falls below this value.

Disable buffering for specific log channels if needed, by setting buffering: NONE for a given channel.

Override default buffering settings for specific channels to ensure timely log flushing.

File-based audit logging cannot coexist with the buffering configuration, so disable either buffering or auditable.

For detailed configurations and examples, refer to .

Use epoch timestamp

By default, the log output format has a timestamp field that contains epoch values for backward-compatibility. When sending log output to a third-party log collector, the log collector can be configured to transform the epoch values in the timestamp field into a human-readable format. When inspecting a json formatted log file produced by CockroachDB, you can use the command to convert the log into format which includes timestamps in the rfc3339 format, for example “2006-01-02T15:04:05.999999999Z”.

There is an optional , which contains values in human-readable format. However, enabling the datetime field introduces CPU overhead.

See also