The new HeatWave Advisor provides string column encoding and data placement key recommendations based on machine learning models, data analysis, and HeatWave query history. Implementing HeatWave Advisor recommendations can improve query performance and reduce the amount of memory required on HeatWave nodes.
The HeatWave Advisor also provides a Query Insights feature, which provides runtimes for successfully executed queries, and runtime estimates for
EXPLAINqueries, queries cancelled using
Ctrl+C, and queries that fail due to out of memory errors. Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload.
The HeatWave Advisor is implemented as a stored procedure named
heatwave_advisor, which resides in the MySQL
sysschema. Running Advisor involves issuing a
CALLstatement for the stored procedure with optional arguments.
CALL sys.heatwave_advisor (options);
For more information about the HeatWave Advisor, see Workload Optimization using Advisor.
The new HeatWave Auto Parallel Load utility automates the process of preparing and loading tables into HeatWave and loads data using an optimized number of parallel load threads.
The HeatWave Auto Parallel Load utility is implemented as a stored procedure named
heatwave_load, which resides in the MySQL
sysschema. Running Auto Parallel Load involves issuing a
CALLstatement for the stored procedure, which takes a list of schemas and options as arguments.
CALL sys.heatwave_load (db_list,[options]);
For more information about the HeatWave Auto Parallel Load utility, see Loading Data Using Auto Parallel Load.
The HeatWave query scheduling algorithm was improved. The revised algorithm prioritizes queries based on estimated cost and wait time in the queue, which enables dynamic, workload-aware query prioritization. Previously, queries were prioritized using a static cost-based prioritization model.
Support was added for multiple instances of
COUNT(DISTINT)in a query. (Bug #32422984)
Query compilation and processing was improved to permit combining aggregate operators into a single task in the physical query plan, which avoids fully materializing intermediate result sets. This enhancement reduces memory allocation and deallocation operations, memory usage, and execution time for affected queries.
The cost model that estimates HeatWave query runtimes can now use statistics from previously executed queries, which improves the accuracy of query runtime estimates.
HeatWave now supports
CREATE TABLE ... SELECTstatements where the
SELECTquery is offloaded to HeatWave and the table is created on the MySQL Database Service instance. This feature improves
CREATE TABLE ... SELECTperformance in cases where the
SELECTportion of the statement is a long running, complex query. For more information, see CREATE TABLE ... SELECT Statements.
Support was added for
REGEXP_SUBSTR()regular expression functions, and error messaging was improved for
REGEXP()function syntax mismatches, expression errors, and input argument errors.