Description of Logging Options

Logging options fall into two groups:

Logging Categories

Tell NuoDB processes what to log

Logging Levels

Define how much logging, if any, is desired (how verbose should the logging be).

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.

Specifying Logging

Logging can be configured using any of the following commands:

nuocmd create database --db-name DBNAME --default-options LOG_ARGS …​

Set default logging options for any TE or SM started for this database.

nuocmd create process --db-name DBNAME --options LOG_ARGS …​

Set default logging options for just this new process.

nuocmd update database-options --db-name DBNAME --default-options LOG_ARGS …​

Set default logging options for any new TE or SM started for this database; any running TEs and SMs are unchanged.

nuocmd get log-messages --db-name testdb --log-options LOG_OPTIONS

Enable additional on-demand, dynamic logging to the console.

  • LOG_ARGS is one or more of:

    • verbose LOG_OPTIONS

    • syslog LOG_OPTIONS

    • remote-syslog LOG_OPTIONS

  • LOG_OPTIONS is a comma-separated list if logging levels and/or logging categories.

Using Filter

The filter option is combined with both logging levels and categories to restrict the logging generated. 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.

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 debug purposes.
When enabling the logging categories (see below) you are typically enabling their debug level logging. However debug level typically generates a lot of extra output and is not recommended for default logging, it is primarily intended for on-demand, dynamic logging.

Note that if you wish multiple levels to be logged, you must list multiple levels. For example, the default value of verbose is error,warn, generating both errors and warnings in the NuoDB Admin log. This is in contrast to how logging levels are defined in nuoadmin.conf. There is no implied hierarchy of levels, only the exact levels you specify will be generated.

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.

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 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 and Storage Managers. 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 histograms data used for index optimization.

hotcopy

Logs related to hotcopy (online backup) execution. 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. Also TDE cipher creation logs (from V4.3).

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. Includes TDE cipher creation logs prior to V4.3 (see net category).

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-params are shown only if sql-statements are also enabled.

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 performed when executing a query (such as open database, create statement, get result set and so on). When used in combination with sql-params any parameters will be included in the workload.

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 transaction engines about SQL DDL changes.

threads

Logs information about threads in Transaction Engines and Storage Managers.

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.

Other logging categories exist, in addition to those described here, but are intended for internal use only. They should not be used unless requested by NuoDB Support.

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 even more detail:

    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.