Logging Options

The two groups of logging options are:

When defining logging options, these combine to produce the logging.

Examples:

verbose error,warn

This is the default verbose setting. All errors and all warnings from any logging category will be logged.

syslog error,warn,sql-statements

In addition to all errors and warnings, SQL statement logging, at any level (including debug), will be generated and sent to the engine host’s system log (syslog is supported on Unix hosts only).

remote-syslog error,warn,info

All errors, warnings, and info logs will be sent to a remote syslog server. Remote syslog is a common way to export NuoDB logging to third party log management tools such as Logstash, Papertrail, Splunk, SumoLogic, and many more.

Logging Levels

The available logging levels are:

Table 1. Engine Logging Levels
Level Description

error

Designates error events that might still allow the application to continue running.

warn

Designates potentially harmful situations.

info

Designates informational logs that highlight the progress of the application at a coarse-grained level.

debug

Designates fine-grained informational events that are most useful for debugging purposes.
When enabling the logging categories, you are enabling their debug level logging. However, the debug level generates a lot of extra output and is not recommended for default logging. It is primarily intended for dynamic or on-demand logging.

To log multiple logging levels, you must specify all the required levels in a comma-separated list.

For example, the default value verbose generates errors and warnings in the nuoadmin.log. This is in contrast to how logging levels are defined in nuoadmin.conf. There is no implied hierarchy of levels, only the specified logging levels will be generated. So, to generate error and warning in nuoadmin.conf, the logging levels error and warn must be specified.

Logging Categories

When configuring logging, any combination of the following logging categories can be specified to indicate the type of log output that should appear in nuoadmin.log or sent to syslog. Enabling a log category, without filter, enables all logging produced by that category at all logging levels (including debug).

Logging to nuoadmin.log is controlled by the categories listed with the verbose database option.

The same logging categories can be used when configuring logs for local or remote syslog, database options syslog and remote-syslog respectively.

It is not recommended to configure default logging with any categories that emit a large volume of log output, degrading the performance of the database. Typically, that means avoiding the debug logging level.

Logging can be streamed dynamically using the NuoDB Command subcommands nuocmd get log-messages and nuocmd show log-messages.

As with the default logging database options, any combination of the following categories can be specified with the --log-options argument, for example: --log-options msgs,client-msgs,checkpoint. See About Dynamic/On-Demand Logging for details.

Table 2. Engine Logging Categories
Logging Category Description

advanced-tx

Distributed transaction processing. Mostly for internal use and advanced network diagnostics.

archive

Information related to updating the archive of a Storage Manager (SM).

atoms

Low-level atom processing information.

atom-gc

Logs garbage collection of deleted table, schema, index, or sequence atoms.

bootstrap

Logs SQL bootstrap operations. This usually involves creating, updating, and loading system tables during the creation of the initial system schema, or during an upgrade to the system schema as a result of a software version upgrade.

checkpoint

Journal logging related to checkpoint counter operations.

client-msgs

Logs the messages sent to and received from SQL clients.

ddl-audit

Generates a log entry for each DDL statement executed. For an example of the log entries generated by ddl-audit, see Examples of Log Entries.

failure

Failure detection messages from Transaction Engines (TEs) and SMs. This information is available only when the ping-timeout database option is not set to 0. A setting of 0 disables automatic detection of process failure.

gc

Garbage collection logging.

histograms

Logs related to SQL histogram data used for index optimization.

hotcopy

Logs related to hotcopy (online backup) execution. For an example, see Example of Hot Copy Log Message.

index

Low-level logging related to index maintenance.

journal

Low-level logging related to journal maintenance (SMs only)

login-audit

Captures successful login attempts by the user identifying the user and the time.

msgs

Logs messages sent between NuoDB processes.

net

Logs network socket connections between NuoDB processes.

It also logs TDE cipher creation logs.

opt-index-selection

SQL index selection optimization logging.

opt-joins

SQL join plan optimization debugging information.

ping

Logs network connectivity between NuoDB processes.

records

Logs the internal records that represent data in NuoDB.

record-reuse

Logs assignment and reuse of record IDs.

registry

Logs information about databases, schemas, and sequences.

security

Logs security events in the system. This includes all login attempts (success or failure), all DDL statements that return a SECURITY_ERROR error code and failed attempts to access objects due to insufficient privileges. It also includes TDE cipher creation. For more information, see net.

sql

Miscellaneous logging of SQL statement and query processing such as internal caching and garbage collection.

sql-params

Logs parameters used in SQL parameterized statements.

sql-results

Logs query results to facilitate debugging and diagnostics; includes connection and transaction IDs.

sql-statements

Logs the text of each SQL statement executed.

sql-statement-explain-plans

Logs the query execution plan that was selected for each SQL statement executed, complete with execution counters, where available.

sql-statement-metrics

Logs the following information:

  • All information in the system table LASTSTATEMENT.

  • The following metrics: COMPILETIME, RUNTIME, NROWS, NUMPARAM, USER, SCHEMA, CONNID, EXECID, TIMESTAMP, and TRANSID.

  • When used in combination with sql-params, the PARAMS metric will also be included.

sql-workload

Logs the application workload — the internal steps such as open database, create statement, and get result set, performed during the execution of a query. When used in combination with sql-params any parameters will be included in the workload. To replay the workload, enable sql-params.

stats

Logs SQL related performance statistics. Similar to nuocmd get stats, statistics are generated continuously but a much smaller subset of metrics is generated.

table-events

Logs messages sent between TEs about SQL DDL changes.

threads

Logs information about threads in TEs and SMs.

tx

Logs transaction processing information.

xa

Logs XA transaction processing information.

NuoDB Support may request that you enable additional categories, such as index, registry, and table-events.

In addition to the categories described in the Engine Logging Categories table, other categories exist for internal use. Use them only if requested by NuoDB Support.

Using Filter

The filter option is combined with logging levels and logging categories to restrict the generated logging. It is most useful with on-demand logging to minimize output to the console.

Compare these examples to see the effect of using filter. The order of items in the list is irrelevant.

error,warn,sql-statements

Generates all logging for any category at error or warn level, plus all logging in the sql-statements category at any level (error, warn, info and debug).

error,warn,sql-statements,filter

Generates only errors and warnings associated with sql-statements logging. No other category will be logged.

filter,sql-statements,error,warn

Same as previous example. Item order is unimportant.

error,warn,info,sql-statements,tx,filter

error, warn, and info logging, for SQL statement and transaction categories only. No other category or level will be logged.

Specify Logging

Use any one of the following commands to configure logging.

  • To create a database with default logging options set for any TE or SM, use:

    nuocmd create database --db-name DBNAME --default-options LOG_ARGS ...

  • To create a new process with default logging options set, use:

    nuocmd create process --db-name DBNAME --options LOG_ARGS ...

  • To update the default logging option setting for any new TE or SM started for a database, without changing the default logging options for any existing TEs and SMs, use:

    nuocmd update database-options --db-name DBNAME --default-options LOG_ARGS ...

  • To enable additional dynamic or on-demand logging to the console, use:

    nuocmd get log-messages --log-options LOG_OPTIONS [--db-name DB_NAME | --start-id START_ID]

  • To enable additional dynamic or on-demand logging to the console in JSON format, use:

    nuocmd show log-messages --log-options LOG_OPTIONS [--db-name DB_NAME | --start-id START_ID]

where,

  • LOG_ARGS is one or more of:

    • verbose LOG_OPTIONS

    • syslog LOG_OPTIONS

    • remote-syslog LOG_OPTIONS

  • LOG_OPTIONS is a comma-separated list of Logging Levels, Logging Categories, or both.

For more information on nuocmd, see NuoDB Command Reference.

Examples

  1. For security and audit purposes, log changes to database DDL and database accesses:

    nuocmd create database --db-name DBNAME --default-options error,warn,ddl-audit,security

  2. Debugging SQL activity using dynamic logging:

    nuocmd show log-messages --db-name testdb --log-options sql-statements

    For more details:

    nuocmd show log-messages --db-name testdb --log-options sql-statements,sql-params

  3. Debugging slow queries using dynamic logging by showing explain plans:

    nuocmd show log-messages --db-name testdb --log-options sql-statement-explain-plans

    You may prefer to use the System.QueryStats table to do this as it can be configured to only record queries taking more than a specified duration.