New Features

New Features in NuoDB 4.2.2

Additional improvements in VEE (Vectorized Execution Engine)

  • Added DDL statement audit logging. For more information, see logging categories ddl-audit and security in section, Description of Logging Categories.

  • Added support for the IDLE_CONNECTION_TIMEOUT SQL system property. This system property is the maximum time (in seconds) which indicates how long idle connections are left open. The idle connection timeout can also be set on a per-connection basis by using the idle-timout connection property, see Connection Properties. For additional information, see SQL System Properties.

  • Added the removal of all foreign key indexes during a DROP SCHEMA ... CASCADE operation that reference tables being deleted. Likewise, remove the foreign key referential integrity constraints for any dropped table as part of the DROP SCHEMA command.

  • Added the INSERT ... ON DUPLICATE KEY UPDATE clause which provides several processing options when an insert of a duplicate row is attempted. For more information, see INSERT.

  • Added the REPLACE SQL statement which functions similar to INSERT except that if an existing row in the table has the same PRIMARY KEY or UNIQUE index value as a new row being inserted, then the existing row values are replaced with the new column values provided. For more information, see REPLACE.

  • Added support for the LISTAGG() optional WITHIN GROUP() clause. For more information, see SQL Aggregate Functions.

  • Added support for the ROWNUM() function in the SELECT, GROUP BY and ORDER BY clauses. For more information, see SQL System Information Functions.

  • Added the atan2(y,x) SQL function which calculates the inverse tangent of y / x. This function operates on the decimal value data type double (double-precision), and returns a double. For more information, see SQL Mathematical Functions and Operators.

  • Added several new SQL system functions, for example GetReleaseVersion and Is_Member. For more information, see SQL System Information Functions.

  • Added data type conversion support for converting a DATE data type to and from a STRING data type. The functions added are DATE_FROM_STR and DATE_TO_STR. For more information, see SQL Data Type Conversion Functions.

  • Added the QUOTENAME string function which returns a string with delimiters added to make the input string a valid delimited identifier. For more information, see SQL String Functions and Operators.

  • Added additional display attributes to the SHOW command.

    • The SHOW TABLE and SHOW TABLES commands now display the table and column(s) that a foreign key references.

    • A new command SHOW PARTITIONS FOR table has been added to easily discover the relationships between tables and their partitions, and conversely, SHOW TABLE and SHOW TABLES now display a table’s partition information if exists. For more information, see SHOW.

  • Added the LEAST and GREATEST SQL functions. For more information, see SQL Conditional Expressions.

  • Added the spill to disk performance optimization for large ORDER BY sort operations. The feature is configured using the SQL_DISKSPILL and DISKSPILL_MEMORY_THRESHOLD_MB system properties. (12014)
    For more information, see Reducing Memory Pressure on Transaction Engines and SQL System Properties.

  • Improved LEFT [OUTER] JOIN performance by applying an outer to inner join optimization when applicable. (12125)

Index statistics collection enhancements

  • A new column was added to the SYSTEM.INDEXSTATISTICS pseudo table to determine whether stats collection was enabled or disabled for a given index. For more information, see INDEXSTATISTICS System Table Description.

  • A new command option, PARTITION <partition> has been added to the ANALYZE TABLE and ANALYZE INDEX commands that allows users to generate statistics for a single partition for a specified table or index. (11106,11992)
    For more information, see ANALYZE.

  • Index statistics collection has been optimized, reducing memory usage and improving collection runtime response by 10x.

Added verbose logging of all resources fetched by the Kubernetes API

The Kubernetes resource log messages are written to a new log file on the NuoDB Admin server, "nuoadmin_k8sclient.log". Information about fetched API resources and notifications received are logged with DEBUG level. To reduce logging verbosity, Kubernetes API resources used to make decisions in the NuoDB Kubernetes Aware Admin (KAA) module are logged with INFO level if the domain state has been changed. Process labels are included in the logging message in case the domain process is resynchronized by the KAA module.

Added several new command switches to nuocmd get diagnose-info that assist with core file collection

To better assist our Customer Support team with problem resolution, several new command switches have been added, --collect-cores, --modified-after, and --live-cores. For more information, see NuoDB Command Reference.

For Physical/VM host deployments, collect system OS dependencies, binaries, and non-live cores when running nuocmd get diagnose-info

Previously, in physical/VM host deployments (i.e., not Kubernetes environments) system OS dependencies and NuoDB binary files were collected only if live cores were requested. To improve supportability, system dependencies, binaries, and non-live core files are now packaged and collected if a core file is generated by a database process. This aids the NuoDB Support team when opening and analyzing the core files received when triaging a customer trouble ticket.

In Kubernetes deployment environments, send all database process container logs to NuoDB Admin

To improve diagnostics and supportability, all database process container logs are automatically collected and sent to the Admin Process (AP) that manages the database process, including the very first log messages that contain configuration parameters. Previously, all the database process log messages from its startup were missing and the only way to retrieve them was with kubectl logs <engine pod>. Also, in the event of database process startup failure, the container logs are sent to NUOCMD_API_SERVER which can be defined as a load-balancer endpoint and represents any ready AP pod.

New Features in NuoDB 4.2.1

Additional improvements in VEE (Vectorized Execution Engine)

Improved SQL query performance on Partitioned tables (11781,11858,11452,12032)

Several optimizations have been added to improve performance on partitioned tables, including:

1. Reduce partition iterations during processing
2. In certain cases, reuse memory allocated by index scans
3. Improve the search algorithm when computing a set of pruned partitions
4. Extend runtime partition pruning for parameterized queries

Additional partitioned table performance optimizations may be added in future releases.

Improved database process node distribution across the available Admin processes (AP)s for Docker and Kubernetes deployments

Previously, for externally started database processes, the database processes may not distribute evenly across the available Admin processes (APs). A new start te and start sm parameter is used to supply the Admin with distribution policies used to ensure better distribution.

New Features in NuoDB 4.2

A sub-set of these features may be available in maintenance releases for prior versions.

Asynchronous Storage Managers (ASMs): Deploy a passive DR site over WAN to support business continuity plans

The Asynchronous SM feature allows administrators to configure NuoDB in an active / passive configuration across long distance regions for operational and disaster recovery protection in the event of a primary site outage. The Asynchronous SM feature replicates all database changes to a remote passive data center asynchronously so that there is limited impact due to higher network latency that is common to Wide Area Networks (WANs). In the event of a primary site disaster, the Asynchronous SM can be converted to a regular SM so that the database can be restarted quickly and put back into service. For more information, see Asysnchonous SM.

A new Hot Copy backup and restore option is available that allows a backup to be immediately usable

This new capability allows users to create a hot copy backup, using nuocmd hotcopy with the --type simple argument, and that backup (or a copy of that backup) can then be used directly to start a database. This feature was previously available in the NuoDB Nuoagent administrative tier and has been added to the NuoDB Admin administrative tier.

A new DEFINER security option is now available for users who have been granted SELECT access on a VIEW object

The new system-wide database DEFINER security option allows a user to read a database VIEW object after being granted a single Grant Select on View privilege. This new option eases database administration and improves security when using a DEFINER security model is preferred. The database default remains the INVOKER security option. To enable the DEFINER security option, the new system property DEFAULT_VIEW_SECURITY must be set to DEFINER.
For more information when using the Scalar Execution Engine, see SQL System Properties.
For more information when using the Vector Execution Engine, see SQL System Properties.

Added a NuoDB Admin Transparent Data Encryption (TDE) configuration and monitoring service

The service streamlines configuration with third-party Key Management Systems and is used for TDE initial password configuration as well as password rotation.

Added several new monitoring metrics

Added two new Transaction Engine (TE) timing metrics, SQLListenerIdleTimeStatement and SQLListenerIdleTimeTransaction that narrow down SQLListenerIdleTime to when statements or transactions are open respectively. A new "Time" monitor metric was also added that timestamps when each monitoring period ended expressed in milliseconds. For more information, see Metrics Published by Database Processes.

Added resilience to network congestion events during failure detection and resolution processing (11570)

Previous behavior could lead to a full database shutdown during failure detection and resolution processing when the database environment is experiencing a heavy network congestion event. Failure resolution processing will now continue to run and complete (possibly taking a little longer in some scenarios) but will avoid an unwanted forced database shutdown event.

Added index build queuing to improve Storage Manager (SM) resource management

Previously the number of index builds which may run concurrently on a single SM was not limited. If many concurrent long-running index builds were issued, the Storage Manager (SM) could become resource constrained and slow end user application response time. To avoid excessive index build resource consumption, a new limit of 4 concurrent index build jobs is now enforced. For more information, see either reference,
Scalar Execution Engine: CREATE INDEX.
Vectorized Execution Engine: CREATE INDEX.

SQL query termination when client memory is critically low

If a Transaction Engine is unable to relieve memory pressure using spill-to-disk or garbage collection then it will terminate client SQL operations consuming the most memory until memory pressure is relieved. For more information, see Reducing Memory Pressure on Transaction Engines.

Raft log compaction is now managed by a scheduled task that checks periodically if compaction should be performed

Previously Raft log compaction was performed in the critical path of commits whenever the number of committed and uncompacted log entries exceeded raft.compactionThreshold (1024 by default). This would delay the command response that triggered compaction and also disrupt leadership. Now compaction is performed by a task that is scheduled every 30 seconds by default and is configurable by the nuoadmin.conf file raft.compactionIntervalSec property. If the compaction task detects that the number of committed and uncompacted entries in the Raft log is equal to or greater than raft.compactionThreshold, compaction is performed.

Several new command switches and improvements have been added to the nuocmd check command

New switch options added are --wait-for-acks, --fail-on-process-exit, and --check-converged. In addition, the nuocmd check process command now will fail-fast if the process exits during the command invocation, and a new nuocmd check subcommand server has been added. To learn more about these enhancements, and when to use them, see NuoDB Command Reference.

NuoDB Admin REST API audit capabilities

A new auditing capability has been added in NuoDB Admin which allows logging of all REST API requests and responses. The type and verbosity of the logged messages are configurable. By default all authentication and authorization failures are logged, and the type and verbosity of the logged messages are configurable. For more information, see the reference documentation for the NuoDB Admin Audit Log file.

Added new engine TCP timeout configuration for SQL clients

When a network connection between a Transaction Engine and a SQL client is terminated, some virtual environments do not notify the engine of the socket termination. Without a TCP notification, the engine will keep the socket open until a timeout is reached. On most systems, the default timeout is two hours. The new --sql-client-tcp-keepalive allows users to override the system defaults using the TCP_KEEPIDLE:TCP_KEEPCNT:TCP_KEEPINTVL format.