New Features

This section includes new features and enhancements.

If a new feature or enhancement relates to a Zendesk request, the associated Zendesk reference number is displayed, for example (10118).

New Features in NuoDB 4.3.2

Improved JSON function runtime performance

Added support for the RapidJSON library which delivers significantly improved runtime performance when executing JSON functions.

Added the REOPTIMIZE ALWAYS|NEVER query hint to allow application control of SQL recompilation behavior

For more information, see REOPTIMIZE Hint.

New Features in NuoDB 4.3.1

Added new database process metrics

The new database metrics are:

  • CleanObjectsBytes

  • DirtyObjectsBytes

  • IncomingMessagesBytes

  • OutstandingObjectRequests

For more information, see Metrics Published by Database Processes.

Added logging category sql-results

The sql-results logging category logs query results to facilitate debugging and diagnostics. For more information, see Description of Logging Options.

Improved Storage Manager (SM) write throttle memory management

The SM write throttle mechanism was improved to use memory more efficiently allowing for greater overall SM reliability and resilience by reducing memory oscillation and out of memory related process shutdown events. (12214, 12237, 12238)

Added support for Java 17

The Administrative Process (AP) and JDBC driver can now use the Java 17 Runtime Environment. For more information, see System Requirements.

Optimized queries with parameterized LIKE predicates

Accelerated queries with parameterized LIKE predicates through outer to inner join optimization when applicable, and applied further optimizations if the parameter started without a wildcard.

Added JSON built-in functions (12047, 12052, 13049)

The following JSON built-in functions were added:

  • IS_VALID_JSON

  • JSON_CONTAINS

  • JSON_CONTAINS_PATH

  • JSON_EXTRACT

  • JSON_MERGE_PATCH

  • JSON_UNQUOTE

For more information, see SQL JSON String Functions.

New Features in NuoDB 4.3

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

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.

Additional improvements in VEE (Vectorized Execution Engine)

  • Added initial support for SQL window functions. Function evaluation order and runtime performance improvements will be added in later releases. For more information, see SQL Window Functions.

  • Evaluate SQL execution operators only when referenced by the owning expression. (12126)
    For example when the owning expression is in the projection of a nested loop join, provided that the statement would benefit from delayed execution.

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

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

  • Added support for the EXTRACT() function which retrieves subfields (e.g. YEAR, HOUR, etc.) from date and time related data type values. For more information, see SQL Date and Time Functions and Operators.

  • Added support for pattern matching using the REGEXP and CONTAINING functions. For more information, see SQL Pattern Matching.

  • Added several missing data type cast conversions. For a complete list of support conversions, see SQL Data Type Conversion Functions.

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

  • 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 FOR INSERT INTO execution control flow statement for use in database triggers, SQL stored procedures and user defined functions (UDFs). For more information, see Control Flow.

  • 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 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 information functions, for example GetPid(), 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 additional optimizations for the ORDER BY and GROUP BY operations which reduces the amount of physical sorting that is required. (12225)

  • 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)

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.

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.

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 to allow 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.

NuoDB Admin database process tombstone garbage collection

If the number of database process tombstones grew large, it would negatively impact display performance when running either the nuocmd show domain or nuocmd show database commands. The number of tombstones grows when a database process is created and then is shutdown or exits for any reason. This enhancement will trigger garbage collection (the purging of the oldest database processes tombstones) if the number of tombstones grows larger than the nuoadmin.conf property variable tombstoneGarbageCollectionThreshold. For more information on how to configure this feature, see the tombstone and incarnationsToRetain property variables located here, Host Properties (nuoadmin.conf).

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.

Improved database and Admin Process (AP) liveness checking

In Kubernetes deployment environments, if the AP was restarted then the database processes (SMs or TEs) may not reconnect quickly enough to the newly started AP to prevent the AP from concluding they are unresponsive and thereby removing them from the NuoDB Admin domain. To improve database and AP communications, periodic liveness checking has been implemented from the database process to its AP, such that, if a reconnect to a new AP process is necessary, the reconnect will happen more quickly. The feature is configured using the processLivenessCheckSec and evictUnkownProcesses property values located in the nuoadmin.conf file. Perviously, the processLivenesCheckSec property only enabled liveness checking of database processes by the APs.

Liveness checking is enabled by default for Kubernetes and disabled for bare-metal.

In Kubernetes deployment environments, upgrade database protocol automatically

A new version of NuoDB database software may also introduce a new version of the database protocol. To simplify NuoDB version rollout, Kubernetes Aware Admin (KAA) is used to automatically upgrade database protocol and restart a Transaction Engine (TE) as an upgrade finalization step. The feature is configured at the database level and is disabled by default. For more information on how to configure this feature, see Automatic Database Protocol Upgrade.

Allow client applications to connect to the database using an external IP address

In Physical / Virtual machine environments, client applications can now connect to the database using a manually configured external IP address. Previously, client applications could only connect to a Transaction Engine (TE) from within the database’s local network or connect from an external IP address by using the direct=true connection property. However, the latter approach bypasses the Admin Process (AP) Load Balancer and its policy assignment feature which is useful for assigning specific TEs to client applications. For more information on how to configure external IP address database connections, see Setting External Connectivity.

Additional database process metrics have been added

The new category of metrics are prefixed by Percent and summarize process resource usage and how much time is spent either doing work or waiting on a particular resource. For more information, see Metrics Published by Database Processes.

In Kubernetes and docker deployment environments, NuoDB Command (nuocmd) now supports invoking Docker specific subcommands

The Docker specific subcommands are used to manage externally started database processes that run in containerized environments. In the NuoDB Docker image, NUOCMD_PLUGINS=$NUODB_HOME/etc/nuodocker.py defines the location that the Docker specific subcommands are discovered and registered by nuocmd. For more information, see Docker Specific NuoDB Command Reference.

The nuocmd subcommand show domain and show database now report journal recovery status and progress

When a Storage Manager (SM) restarts the above nuocmd subcommands will report journal recovery progress with a status "CONFIGURED:RECOVERING_JOURNAL(NN.N%)" which includes the percentage of the journal that has been recovered. These percentages are updated every five seconds.

Enhanced containerized deployments to allow NuoDB to run using non-root user and group

In containerized deployment environments, the NuoDB user that starts the database containers can now run as a non-root user and group. This is required to support security policies that prevent containers from running as either the root user (user ID 0) or a member of the root group (group ID 0). Previously NuoDB only supported running with an arbitrary user ID, but always required a group ID of 0. NuoDB can now run with user and group ID 1000:1000. Arbitrary user IDs (i.e. not 1000) are still supported and still require a group ID of 0, which is standard for Red Hat OpenShift environments.

Enable LBQuery filter to select Storage Managers (SMs) for Hot Copy jobs

The following --process-filter option has been added to several hot copy related commands:

  • nuocmd hotcopy database

  • nuocmd backup database

  • nuocmd get current-backup

  • nuocmd set current-backup

  • nuocmd request restore

Hot Copy status information is now available using the nuocmd subcommands get and show hotcopy-info

These new commands obtain the statuses of all hot copies that have been issued on the currently running Storage Managers (SMs) in the database. For more information, see NuoDB Command Reference.

Added the nuocmd subcommand cancel hotcopy

For more information about how to cancel a hot copy job and the required parameters, see nuocmd subcommand cancel hotcopy.

Added C driver support for the setReadOnly function

This support was also added in other client drivers that are built using the C driver, e.g., the C++ and node.js drivers.