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:
-
Reduce partition iterations during processing
-
In certain cases, reuse memory allocated by index scans
-
Improve the search algorithm when computing a set of pruned partitions
-
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
andsecurity
in section, Description of Logging Options. -
Added support for the
ROWNUM()
function in theSELECT
,GROUP BY
andORDER 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()
optionalWITHIN 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 theidle-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 theDROP 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 toINSERT
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 typedouble
(double-precision), and returns adouble
. For more information, see SQL Mathematical Functions and Operators. -
Added several new SQL system information functions, for example
GetPid()
,GetReleaseVersion()
andIs_Member()
. For more information, see SQL System Information Functions. -
Added data type conversion support for converting a
DATE
data type to and from aSTRING
data type. The functions added areDATE_FROM_STR
andDATE_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
andSHOW 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
andSHOW TABLES
now display a table’s partition information if exists. For more information, see SHOW.
-
-
Added the
LEAST
andGREATEST
SQL functions. For more information, see SQL Conditional Expressions. -
Added additional optimizations for the
ORDER BY
andGROUP 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 theSQL_DISKSPILL
andDISKSPILL_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 theANALYZE TABLE
andANALYZE 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
For more information, see NuoDB Command Reference and Docker-specific NuoDB Command Reference.
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.