Skip to main content
This page describes some common logging use cases, their relevant logging channels, and examples of notable events to be found in the logs: We provide an example file sink configuration for each use case. These configurations are entirely optional and are intended to highlight the contents of each logging channel. A sink can include any combination of logging channels. Moreover, a single logging channel can be used in more than one sink in your logging configuration. Your deployment may use an external service (e.g., Elasticsearch, Splunk) to collect and programmatically read logging data.
All log examples on this page use the default crdb-v2 format, except for the network logging configuration, which uses the default json-fluent-compact format for network output. Most log entries for non-DEV channels record structured events, which use a standardized format that can be reliably parsed by an external collector. All structured event types and their fields are detailed in the Notable events reference.Logging channels may also contain events that are unstructured. Unstructured events can routinely change between CockroachDB versions, including minor patch revisions, so they are not officially documented.
‹ and › are placed around values in log messages that may contain sensitive data (PII). To customize this behavior, see Redact logs.

​
Operational monitoring

A database operator can use the OPS, HEALTH, and SQL_SCHEMA channels to monitor operational events initiated by users or automatic processes, DDL changes from applications, and overall cluster health. In this example configuration, the channels are grouped into a file sink called ops. The combined logging output will be found in a cockroach-ops.log file at the configured logging directory.
When monitoring your cluster, consider using these logs in conjunction with Prometheus, which can be set up to track node-level metrics.

​
OPS

The OPS channel logs operational events initiated by users or automation. These can include node additions and removals, process starts and shutdowns, gossip connection events, and zone configuration changes on the SQL schema or system ranges.

​
Example: Node decommissioning

This node_decommissioning event shows that a node is in the decommissioning state:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • TargetNodeID shows that the decommissioning node is 4.
  • RequestingNodeID shows that decommissioning was requested by node 1. You will see this when specifying the node ID explicitly in addition to the --host flag.

​
Example: Node restart

This node_restart event shows that a node has rejoined the cluster after being offline (e.g., by being restarted after being fully decommissioned):
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • NodeID shows that the restarted node is 1.
  • StartedAt shows the timestamp when the node was most recently restarted.
  • LastUp shows the timestamp when the node was up before being restarted.
All possible OPS event types are detailed in the reference documentation.

​
HEALTH

The HEALTH channel logs operational events initiated by CockroachDB or reported by automatic processes. These can include resource usage details, connection errors, gossip status, replication events, and runtime statistics.

​
Example: Runtime stats

A runtime_stats event is recorded every 10 seconds to reflect server health:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
runtime_stats events are typically used for troubleshooting. To monitor your cluster’s health, see Monitoring and Alerting.

​
SQL_SCHEMA

The SQL_SCHEMA channel logs changes to the SQL logical schema resulting from DDL operations.

​
Example: Schema change initiated

This alter_table event shows an ALTER TABLE ... ADD FOREIGN KEY schema change being initiated on a movr.public.vehicles table:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • ApplicationName shows that the events originated from an application named movr. You can use this field to filter the logging output by application.
  • DescriptorID identifies the object descriptor (e.g., movr.public.vehicles) undergoing the schema change.
  • MutationID identifies the job that is processing the schema change.

​
Example: Schema change completed

This finish_schema_change event shows that the above schema change has completed:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • DescriptorID identifies the object descriptor (e.g., movr.public.vehicles) affected by the schema change.
  • MutationID identifies the job that processed the schema change.
Note that the DescriptorID and MutationID values match in both of the above log entries, indicating that they are related.
All possible SQL_SCHEMA event types are detailed in the reference documentation.

​
Security and audit monitoring

A security engineer can use the SESSIONS, USER_ADMIN, PRIVILEGES, and SENSITIVE_ACCESS channels to monitor connection and authentication events, changes to user/role administration and privileges, and any queries on audited tables. In this example configuration, the channels are grouped into a file sink called security. The combined logging output will be found in a cockroach-security.log file at the configured logging directory. In addition, the security channels are configured as auditable. This feature guarantees non-repudiability by enabling exit-on-error (stops nodes when they encounter a logging error) and disabling buffered-writes (flushes each log entry and synchronizes writes). This setting can incur a performance overhead and higher disk IOPS consumption, so it should only be used when necessary (e.g., for security purposes).

​
SESSIONS

The SESSIONS channel logs SQL session events. This includes client connection and session authentication events, for which logging must be enabled separately. For complete logging of client connections, we recommend enabling both types of events.
These logs perform one disk I/O per event. Enabling each setting will impact performance.

​
Example: Client connection events

To log SQL client connection events to the SESSIONS channel, enable the server.auth_log.sql_connections.enabled cluster setting:
In addition to SQL sessions, connection events can include SQL-based liveness probe attempts.
These logs show a client_connection_start (client connection established) and a client_connection_end (client connection terminated) event over a hostssl (TLS transport over TCP) connection:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • Network shows the network protocol of the connection.
  • RemoteAddress shows the address of the SQL client, proxy, or other intermediate server.

​
Example: Session authentication events

To log SQL session authentication events to the SESSIONS channel, enable the server.auth_log.sql_sessions.enabled cluster setting on every cluster:
These logs show certificate authentication success over a hostssl (TLS transport over TCP) connection:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • The two client_authentication_info events show the progress of certificate authentication. The Info fields show the progress of certificate validation.
  • The client_authentication_ok event shows that certificate authentication was successful.
  • User shows that the SQL session is authenticated for user roach.
These logs show password authentication failure over a hostssl (TLS transport over TCP) connection:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • The two client_authentication_info events show the progress of certificate authentication. The Info fields show that password authentication was attempted, in the absence of a client certificate.
  • The client_authentication_failed event shows that password authentication was unsuccessful. The Detail field shows the related error.
  • The client_session_end event shows that the SQL session was terminated. This would typically be followed by a client_connection_end event.
  • User shows that the SQL session authentication was attempted for user roach.
All possible SESSIONS event types are detailed in the reference documentation. For more details on certificate and password authentication, see Authentication.

​
SENSITIVE_ACCESS

The SENSITIVE_ACCESS channel logs SQL audit events. These include all queries being run against audited tables, when enabled, as well as queries executed by users with the admin role.
Enabling these logs can negatively impact performance. We recommend using SENSITIVE_ACCESS for security purposes only.
This feature is in preview and subject to change. To share feedback and/or issues, contact Support.
To log all queries against a specific table, enable auditing on the table with ALTER TABLE ... EXPERIMENTAL_AUDIT.

​
Example: Audit events

This command enables auditing on a customers table:
This sensitive_table_access event shows that the audited table customers was accessed by user root issuing an INSERT statement:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • AccessMode shows that the table was accessed with a read/write (rw) operation.
  • ApplicationName shows that the event originated from the cockroach sql shell. You can use this field to filter the logging output by application.
All possible SENSITIVE_ACCESS event types are detailed in the reference documentation. For a detailed tutorial on table auditing, see SQL Audit Logging.

​
PRIVILEGES

The PRIVILEGES channel logs SQL privilege changes. These include DDL operations performed by SQL operations that modify the privileges granted to roles and users on databases, schemas, tables, and user-defined types.

​
Example: Database privileges

This change_database_privilege event shows that user root granted all privileges to user roach on the database movr:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • ApplicationName shows that the event originated from the cockroach sql shell. You can use this field to filter the logging output by application.
  • GrantedPrivileges shows the privileges that were granted.
All possible PRIVILEGE event types are detailed in the reference documentation.

​
USER_ADMIN

The USER_ADMIN channel logs changes to users and roles. This includes user and role creation and assignment and changes to privileges, options, and passwords.

​
Example: SQL user creation

This create_role event shows that a user roach was created and assigned a password by user root. Note that the password in the SQL statement is pre-redacted even if redact is set to false for the logging sink. For more details on redaction behavior, see Redact logs.
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • ApplicationName shows that the event originated from the cockroach sql shell. You can use this field to filter the logging output by application.
  • RoleName shows the name of the user/role. For details on user and role terminology, see Users and roles.
All possible USER_ADMIN event types are detailed in the reference documentation.

​
Performance tuning

An application developer can use the SQL_EXEC and SQL_PERF channels to examine SQL queries and filter slow queries in order to optimize or troubleshoot performance. In this example configuration, the channels are grouped into a file sink called performance. The combined logging output will be found in a cockroach-performance.log file at the configured logging directory.

​
SQL_EXEC

The SQL_EXEC channel reports all SQL executions on the cluster, when enabled. To log cluster-wide executions, enable the sql.trace.log_statement_execute cluster setting:
Each node of the cluster will write all SQL queries it executes to the SQL_EXEC channel. These are recorded as query_execute events.
Logging cluster-wide executions by enabling the sql.trace.log_statement_execute cluster setting will incur considerable overhead and may have a negative performance impact.

​
Example: SQL query

This event details a SELECT statement that was issued by user root:
Note the FullTableScan value in the logged event, which shows that this query performed a full table scan and likely caused a performance hit. To learn more about when this issue appears and how it can be resolved, see Statement Tuning with EXPLAIN.
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • ApplicationName shows that the event originated from the cockroach sql shell. You can use this field to filter the logging output by application.

​
Example: Internal SQL query

Internal queries are also logged to the SQL_EXEC channel. For example, this event details a statement issued on the internal system.jobs table:
If you no longer need to log queries across the cluster, you can disable the setting:
All possible SQL_EXEC event types are detailed in the reference documentation.

​
SQL_PERF

The SQL_PERF channel reports slow SQL queries, when enabled. This includes queries whose latency exceeds a configured threshold, as well as queries that perform a full table or index scan. To enable slow query logging, enable the sql.log.slow_query.latency_threshold cluster setting by setting it to a non-zero value. This will log queries whose service latency exceeds a specified threshold value. The threshold value must be specified with a unit of time (e.g., 500ms for 500 milliseconds, 5us for 5 nanoseconds, or 5s for 5 seconds). A threshold of 0s disables the slow query log.
Setting sql.log.slow_query.latency_threshold 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.
To log all queries that perform full table or index scans to SQL_PERF, regardless of query latency, set the sql.log.slow_query.experimental_full_table_scans.enabled cluster setting to true.

​
Example: Slow SQL query

For example, to enable the slow query log for all queries with a latency above 100 milliseconds:
Each gateway node will now record queries that take longer than 100 milliseconds to the SQL_PERF channel as slow_query events. This slow_query event was logged with a service latency (age) of 100.205 milliseconds:
  • ApplicationName shows that the events originated from an application named bank. You can use this field to filter the logging output by application.
The following query was logged with a service latency (age) of 9329.26 milliseconds, a very high latency that resulted from a transaction retry error:
  • Preceding the = character is the crdb-v2 event metadata. See the reference documentation for details on the fields.
  • ApplicationName shows that the events originated from an application named bank. You can use this field to filter the logging output by application.
  • ErrorText shows that this query encountered a type of transaction retry error. For details on transaction retry errors and how to resolve them, see the Transaction Retry Error Reference.
  • NumRetries shows that the transaction was retried once before succeeding.
All possible SQL_PERF event types are detailed in the reference documentation.

​
Network logging

A database operator can send logs over the network to a Fluentd or HTTP server.
TLS is not supported yet: the connection to the log collector is neither authenticated nor encrypted. Given that logging events may contain sensitive information, care should be taken to keep the log collector and the CockroachDB node close together on a private network, or connect them using a secure VPN. TLS support may be added at a later date.
In this example configuration, operational and security logs are grouped into separate ops and security network sinks. The logs from both sinks are sent to a Fluentd server, which can then route them to a compatible log collector (e.g., Elasticsearch, Splunk).
A network sink can be listed more than once with different address values. This routes the same logs to different Fluentd servers.
In this case, defining separate ops and security network sinks allows us to: Otherwise, it is generally more flexible to configure Fluentd to route logs to different destinations. By default, fluent-servers log messages use the json-fluent-compact format for ease of processing over a stream. For example, this JSON message found in the OPS logging channel contains a node_restart event. The event shows that a node has rejoined the cluster after being offline (e.g., by being restarted after being fully decommissioned):
  • tag is a field required by the Fluentd protocol.
  • sev shows that the message has the INFO severity level.
  • event contains the fields for the structured node_restart event.
    • NodeID shows that the restarted node is 1.
    • StartedAt shows the timestamp when the node was most recently restarted.
    • LastUp shows the timestamp when the node was up before being restarted.
See the reference documentation for details on the remaining JSON fields.

​
Network logging with log buffering

A database operator can configure CockroachDB to buffer log messages for a configurable time period or collected message size before writing them to the log sink. This is especially useful for writing log messages to network log sinks, such as Fluentd-compatible servers or HTTP servers, where high-traffic or high-contention scenarios can result in log message write latency. Log buffering is enabled by default on the Fluentd-compatible and HTTP log sink destinations, but you may wish to adjust the buffering configuration for these log sinks based on your needs. For example, the following logging configuration adjusts the default log buffering behavior for both a Fluentd-compatible and an HTTP log sink destination:
Together, this configuration ensures that log messages to the Fluentd log sink target are buffered for up to 2MiB in accumulated size, and log messages to the HTTP server log sink target are buffered for up to 10s duration (with a limit of up to 75MiB accumulated message size in the buffer before messages begin being dropped), before being written to the log sink. Further, each long sink target is configured with an overridden value for these settings specific to log messages in the HEALTH log channel, which are flushed more aggressively in both cases. See Log buffering for more information.

​
See also